330 likes | 481 Views
11. Databases and ADO.NET. Programming Right from the Start with Visual Basic .NET 1/e. Objectives. Understand databases and the relational database model Understand primary keys and foreign keys Understand simple SQL queries Use WHERE and ORDER BY clauses Query multiple tables using JOIN
E N D
11 Databases andADO.NET Programming Right from the Start with Visual Basic .NET 1/e
Objectives • Understand databases and the relational database model • Understand primary keys and foreign keys • Understand simple SQL queries • Use WHERE and ORDER BY clauses • Query multiple tables using JOIN • Create a Connection object
Objectives (cont.) • Create and work with a DataAdapter object • Create and work with a Dataset object • Work with data bound controls • Work with the DataGrid control • Develop applications that interact with a database
11-1 Database Primer • A database is a structured collection of data. • A field is a storage location for a single piece of information. • A record is a complete collection of related fields. • A table is a complete set of related records.
Relational Databases • In the relational database model, relationships exist between tables to indicate how the data is connected. • Relationships involve the primary key from one table and a foreign key in a related table.
Relational Databases (cont.) • The primary key is a field (or set of fields) that uniquely distinguishes the records in a table. • The foreign key contains values that match primary key values in a related table. • The primary key and foreign key fields are essentialfor creating the relationships between tables.
Relational Databases (cont.) • The Customers table and the Orders table have a one-to-many relationship. • Students and Classes have a many-to-many relationship. • A many-to-many relationship is usually implemented using a linking table with foreign keys to each of the two original tables.
11-2 Structured Query Language (SQL) • An application communicates with a DBMS through queries written in a standard language called Structured Query Language (SQL). • SQL is an industry standard language that allows an application to communicate with a relational database.
SELECT Query • The SELECT command is used to select specific information from one or more tables in a database. • SELECT fields FROM table
WHERE Clause • The WHERE clause provides the database the capability to choose information based on selection criteria. • SELECT fields FROM table WHERE criteria • The WHERE clause condition can contain >, <, =, <>, >=, <= • LIKE, *, and ? can also be used
ORDER BY Clause • The information returned by a SELECT statement can be arranged in ascending or descending order with the ORDER BY clause. • SELECT fields FROM table [WHERE criteria] ORDER BY field [ASCIDESC] • ASC specifies ascending order; DESC specifies descending order.
JOIN • To create a query that combines data from multiple tables, you use the JOIN operation. • SELECT fields FROM table1 [LEFT|RIGHT] JOIN table2 ON table1.field1 = table2.field2 [WHERE criteria] [ORDER BY field]
11-3 Using ADO.NET • ADO.NET is the data access architecture for the .NET Framework. • ADO.NET provides Connection, Data-Adapter, and Dataset objects to facilitate accessing data in a database.
Creating a Connection • A Connection object establishes a link from your application to a database file. • A connection object specifies the type and location of the database file. • Step 1: Create a Connection is to select an OleDbConnection control from the Toolbox
Creating a Connection (cont.) • Step 2: Create a Connection is to add an OleDbConnection object to the component tray. • Step 3: Select a New Connection for the control. • Step 4: Select the appropriate database provider and the path/filename of the database.
Creating a DataAdapter • Once a Connection has been established, the next step is to create a DataAdapter. • A DataAdapter passes information between the database and your application. • The SQL command is part of the DataAdapter.
Creating a DataAdapter (cont.) • Step 1: Add an OleDbAdapter control which starts a Wizard • Step 2: Specify the Connection to use for the DataAdapter • Step 3: Select a Query Type • Step 4: Specify the SQL statements • Step 5: Save the settings
Generating a Dataset • Once you have established a Connection and a DataAdapter, the next step is to create a Dataset. • A Dataset in ADO.NET is a temporary, local copy of the information in the table. • To create a Dataset, right-click on the object and select Generate Dataset from the pop-up menu.
11-4 Displaying andNavigating Records • The information in a Dataset can be viewed by means of one or more data-aware controls. • A data-aware control is a control that can be bound to a Dataset; when bound, the control automatically displays the information it receives from the Dataset.
The Fill Method • The Fill method of the DataAdapter is used to populate the Dataset. • DataAdapter.Fill(DataSet)
Dataset Navigation • When text boxes are data bound they display their specified field values for the current record in the specified dataset. • Each form has a BindingContext object that keeps track of all the data sources associated with the form. • The BindingContext has a position property and a Count property.
11-5 The DataGrid Control • The DataGrid control is designed to display ADO.NET data in a scrollable grid. • The DataSource property specifies the name of the data source from which the grid will get data. • The DataMember property specifies which table or other element to bind to if the data source contains more than one bindable element.
Chapter Summary • A database is a collection of one or more structured sets of data. • A table is a two-dimensional matrix of rows and columns. • A foreign key is a field in a table that refers to the primary key in another table.
Chapter Summary (cont.) • An application communicates with a DBMS through queries written in SQL. • The SELECT command is used to select specific information from one or more tables in a database. • The WHERE clause is used to choose information based on selection criteria.
Chapter Summary (cont.) • The results of a query can be arranged in ascending or descending order with the ORDER BY clause. • Relational databases store data over multiple tables to eliminate data redundancy. • A Connection object establishes a link from your application to a database and specifies the type and location of the database.
Chapter Summary (cont.) • Once a Connection has been established, the next step is to create a DataAdapter. • Once a Connection and a DataAdapter have been established, the next step is to create a Dataset. • A Data-aware control is a control that can be bound to a Dataset.
Chapter Summary (cont.) • Each form has a BindingContext object that keeps track of all the data sources associated with the form. • The DataGrid control is designed to display ADO.NET data in a scrollable grid.
11 Databases andADO.NET Programming Right from the Start with Visual Basic .NET 1/e