1 / 33

Databases and ADO.NET

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

binah
Download Presentation

Databases and ADO.NET

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 11 Databases andADO.NET Programming Right from the Start with Visual Basic .NET 1/e

  2. 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

  3. 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

  4. 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.

  5. 11-1 Database Primer (cont.)

  6. 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.

  7. 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.

  8. Relational Databases (cont.)

  9. Relational Databases (cont.)

  10. 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. 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.

  12. SELECT Query • The SELECT command is used to select specific information from one or more tables in a database. • SELECT fields FROM table

  13. 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

  14. 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.

  15. 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]

  16. 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.

  17. 11-3 Using ADO.NET (cont.)

  18. 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

  19. 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.

  20. 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.

  21. 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

  22. Creating a DataAdapter (cont.)

  23. 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.

  24. 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.

  25. The Fill Method • The Fill method of the DataAdapter is used to populate the Dataset. • DataAdapter.Fill(DataSet)

  26. 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.

  27. 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.

  28. 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.

  29. 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.

  30. 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.

  31. 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.

  32. 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.

  33. 11 Databases andADO.NET Programming Right from the Start with Visual Basic .NET 1/e

More Related