database applications - erie city school district · database applications . ... –ado.net is the...

McGraw-Hill © 2010 The McGraw-Hill Companies, Inc. All rights reserved.

Chapter 10

Database Applications

McGraw-Hill © 2010 The McGraw-Hill Companies, Inc. All rights reserved. 10-2

Chapter Objectives

• Use database terminology correctly

• Create Windows and Web projects that display database data

• Display data in a DataGridView control

• Bind data to text boxes and labels

• Allow the user to select from a combo box or list box and display the corresponding record in data-bound controls

• Query an object using LINQ


C# and Databases

• C# applications can display and update data from database files

• C# uses ADO.NET for database access – ADO.NET is the next generation of database

technology, based on Microsoft's previous version, ActiveX Data Objects (ADO)

– Information is stored and transferred in Extensible Markup Language (XML)

• ADO. NET allows access to database data in many formats

• This text uses Microsoft's SQL Server Express (SSE), which installs automatically with Visual Studio


Database Terminology - 1

• A database file (from SQL Server Express

or Access) can hold multiple tables

• Table – Can be viewed as a spreadsheet

– Row (record) – Data for one item, person, or

transaction

– Column (field) – Stores an individual element

of data, such as account number, name,

address, etc.

• Primary key field – Uniquely identifies each record



• Relational databases contain multiple

tables and relationships between tables

– Relationships keep data compact and easy to

maintain

• Whenever a database table is open, one

record is considered the current record

– The current record changes as you move

from one record to the next



• Database table

example


XML Data - 1

• XML is an industry-standard format for storing and transferring data

• The XML needed for accessing databases is automatically generated in C#

• Data stored in XML is text, identified by tags similar to HTML tags – Can be edited by any text editor program

• Tags in XML are not predefined as they are in HTML – Tags can identify fields by name


XML Data - 2

• An XML data file is usually accompanied by a schema file – Schema describes the fields, data types, and any

constraints, such as required fields

– ADO.NET validates the data against the schema and checks for constraint violations

– Schema is also defined with XML tags and can be viewed or edited in a text editor

– An XML schema provides for strong data typing

• ADO.NET can treat the XML data as objects – Allows IntelliSense to provide information to the

programmer


Using ADO.NET and C# - 1

• In C# display data from a database on a

Windows or Web Form

• Add controls to the form and bind data to the

controls

– Labels or text boxes

– Specialized controls designed just for data, such as a

DataGridView or a DataList

• Data handling and controls differ greatly for

Windows and Web applications

– Both will be covered in this chapter


Using ADO.NET and C# - 2

• The DataGridView control is bound to a table in a dataset

• Data fields display automatically in the cells of the grid


Data Access in Visual Studio - 1

• The Data Sources window provides an easy way to create data-bound controls on a form – Drag tables and fields from the window onto a form to create

controls that are bound to the data

• Display the data in a grid or in individual fields, referred to as Details

• Drag a field from the Data Sources window and drop on an existing control, which automatically sets up data binding

• Adding data-bound controls to a form: – Adds an .xsd file to the Solution Explorer window

– Adds BindingSource, TableAdapter, DataSet, and BindingNavigator objects to the component tray


Data Access in Visual Studio - 2

• Overview of database objects – Binding source

• Establishes a link to the actual data—a specific file and/or server

– Table adapter • Handles retrieving and updating the data

• Generates SQL statements that are used to retrieve or update data

– Dataset • Contains the actual data and may come from multiple binding

sources and/or multiple table adapters

– Binding navigator • A toolbar that provides for database navigation and updating


Binding Sources

• Object establishes a link from a specific

file or database to the program

• Use a wizard to automatically create

BindingSource objects

– Add new BindingSource objects using the

Data Sources window or the Data menu


Table Adapters

• Does all the work of passing data back and forth between a data source (the binding source) and a program (the dataset)

– Data does not have to be from a database

– Data can be text file, object, or an array

• Transfers data from the source to the dataset (fills) or from the dataset back to the source (updates) via XML


Datasets

• Temporary set of data stored in memory

– Datasets are disconnected, the copy of data

in memory does not keep an active

connection to the data source

• Dataset may contain multiple tables and

relationships

• Any controls bound to the dataset will

automatically fill with data


Creating a Database Application - 1

• Start a New Project

• Add a DataGridView control to the form to display the data

– Click the Smart Tag arrow

– Drop down the list for Choose Data Source

– Select Add Project Data Source which activates the Data Source Configuration Wizard

– Select Database



– Select New Connection

• Set up the connection for the binding source object

– In the Add Connection dialog box set the Data

source and browse to the database file

• Open and test the connection

– Follow wizard prompts to add the database

file to the project (makes the project portable)



• Run the Data Application

• Examine the Components

• Format the DataGridView

– Click on the DataGridView and click on the Smart Tag arrow

– Select Edit Columns • Add, remove and reorder columns

• Set HeaderText, ToolTip Text and other properties

• Run the Application


The Grid’s Smart Tag

• Use the smart tag to:

– Edit properties of grid columns

– Add and edit columns

– Dock the grid in its parent container (the form)

• Allows grid to fill form, even if form is resized


The Database Schema File

• An .xsd file is added to the Solution Explorer for each new data source added to a project

• Contains the XML schema definition, the description and properties of the data – Names of tables and fields,

primary keys, and the table relationships

• At the bottom of the schema appears the TableAdapter for the table – Handles the Fill and GetData

methods for the table

Keys

TableAdapter


Binding Individual Data Fields - 1

• Table fields from the dataset can be bound to

many types of controls

– i.e. labels, text boxes, combo boxes, check boxes

• Controls that are connected to fields are referred

to as bound controls or data-bound controls

• Create bound controls using the automatic

binding features of the Data Sources window

– Drag the table to the form

– Creates individual text box controls for each field of

data and a navigation control


Binding Individual Data Fields - 2

• Each text box is

bound to one field

from the table

• As the user clicks

the navigation

buttons, all controls

change to display

the data for the

next record

Navigation buttons


The Data Sources Window

• Select Show Data Sources from Data menu to display

– Add a new data source

– Click on the table name and select Details from the drop-down list

– The table's icon changes from a grid to indicate details


Selecting Records from a List

• Instead of providing

navigation from one

record to the next,

allow the user to

select a record from

a drop-down list

– The rest of the fields

will fill with data

elements for the

selected record

Select a last name from the drop-down list


Converting to Combo

Box Selection - 1

• In the Data Sources

window click on the

LastName field

– Select ComboBox from the

list

• Drag the LastName field

from the Data Sources

window to the form

– Creates a Combo Box control

and label


Converting to Combo

Box Selection - 2

Use the combo box smart tag to set up data binding

Select Use data bound items to display the binding choices

Drop down list to select the binding source

Select the field to display in the list


Selecting Fields from the Table

• When you create the Data Source

– Choose Add New Data Source option from the Data

menu or the Data Sources window

– In the Choose Your Database Objects section of the

Configuration wizard

• Expand the Tables node and place a check mark on just the

fields needed

• After the Data Source is created

– Select the dataset in the Data Sources window

– Click the Configure DataSet Wizard button

• Follow same steps as above


Sorting the List Data - 1

• Cannot sort a data-bound list using the

Sorted property of the control

• Sort the data in the Select query

– Double-click the dataset’s schema file in the

Solution Explorer (.xsd file)

– In the displayed schema, click on

Fill,GetData()

– Click on the Property button (…) for the

CommandText property



Schema file

Click on the Fill command

View the Select query in the pop-up data tip

SELECT query in Command-Text property



– Modify the SQL SELECT command

• Drop down the Sort Type list and choose Ascending or Descending

(or type directly into the SELECT statement)

• SQL statement changes to include an ORDER BY clause


Choosing the Control Type for Fields

• Dragging a Details view to a form displays

text boxes by default for text fields

• Click a field name and choose a control

type in the Data Sources window

– Choose the control type for all controls and

then drag the table to the form to create the

Details view


Selecting Records Using Web Forms

• Web controls are considerably different from their Windows counterparts

• Web-based database applications have additional security issues

• Web database applications operate differently than Windows applications due to the nature of Web pages in a client/server environment

• Web pages are stateless—each time a page displays it is a new “fresh” page

• Each time the user makes a selection from a list a postback occurs and the Web page redisplays with only the selected data


Security in Web Database Applications

• Security is much tighter for Web database

applications than for Windows applications

• Require user authentication and set

permission levels, if setting up a Web

application that displays or allows

modification to data

• Visual Studio integrates security features,

which are generally strict by default


Creating the Web

Selection Application - 1

• Web version of the selection program

must have two data sources

– Drop-down list has a separate data source

from the one used for the individual fields

• Begin a new Web site

– Copy the database file into the project’s

App_Data folder


Creating the Web


• Set up the drop-down list – Add a DropDownList control

– In the smart tag select Choose Data Source

– In the wizard, drop down the list for Select a data source and select New data source

– Select Database

– Click on the New Connection button

– In the Add Connection dialog box, browse to the database file

– Select the data fields for the dataset for the drop-down list on the Configure the Select Statement page


Creating the Web


– Click on the ORDER BY button

• Select LastName to sort by

– On the Choose a Data Source page,

LastName should be selected for both the

data to display and the value of the field

– Select Enable AutoPostBack in the smart tag

for the drop-down list control

• An important step necessary to select and display

the data for the selected customer


Creating the Web


Select the fields to include in the dataset

Click the ORDER BY button to define a sort


Creating the Web


• Set up the additional fields – Add a DetailsView control to the Web page

– In the smart tag, select New data source from the Choose a Data Source list

– Select Database and select the connection for the database file that is already created

– Configure the Select Statement • Click on the box for all columns (*)

• Click the WHERE button

• In the Add WHERE Clause dialog box set up the parameter used to select the correct data for the fields

– Save all


Creating the Web


• Run the Application

– Press Ctrl + F5 to test the application

– Make selections from the list

• Data fields should change to match selection


Make the Project Portable

• Open the Web.config file from the Solution

Explorer

– Scroll to find the entry for the connection

string

– Replace the path (not the file-name) with

|DataDirectory|

• Close the project, copy the complete folder

to move it to another location


LINQ

• Language-Integrated Query (LINQ)

• General-purpose query language that can ask a question of any data – Object, database or XML

– Data source can be a database or any collection such as an array or list box collection

• LINQ to SQL component – Converts database items to objects

• LINQ to XML component – Converts XML document collections to objects


Setting up a Query - 1

• Standard operators, regardless of data source

– Primary operators in a query • from, in, where and select

• LINQ Query – General Form variableName = from itemName in objectName select fieldName |list of Fields| items

– variableName does not need a data type assigned

– The compiler can assign a type in a process called type inference

• Allows LINQ operators order by and where to be used on unspecified data types


Setting up a Query - 2

• The LINQ Query Example var belowMinimumQuery =

from an Item in amountDecimal

where an Item < 100m

select an Item;

• amountDecimal is an array, anItem is a single element not declared elsewhere

• Query is similar to a foreach

– Steps through the array, assigns each element to anItem and performs a comparison


LINQ to SQL - 1

• A LINQ query can be applied to a

relational database

– Add a “LINQ to SQL Classes” template to a

database project

– Creates a strongly typed DataContext class

– A new design surface with two panes appears

in the Document window

– Drag database tables from the Server

Explorer to the design surface


LINQ to SQL - 2

• Tables added to the left pane are referred to as entities

• Right pane holds optional stored procedures or methods

• Refer to the DataContext when writing the code

– Once the DataContext class is created, create a DataContext object in code

– Query the database using the LINQ operators


LINQ to SQL - 3

database applications - erie city school district · database applications . ... –ado.net is the...

Documents