Database, SQLand ADO.NET

1
20

• Database, SQL and ADO.NET

2

• It is a capital mistake to theorize before one
  has data.
• Arthur Conan Doyle

• Now go, write it before them in a table, and note
  it in a book, that it may be for the time to come
  for ever and ever.
• Holy Bible, Isaiah 308

• Get your facts first, and then you can distort
  them as much as you please.
• Mark Twain

• I like two kinds of men domestic and foreign.
• Mae West

3
OBJECTIVES

• In this chapter you will learn
• The relational database model.
• To write basic database queries in SQL.
• To add data sources to projects.
• To use the IDE's drag-and-drop capabilities to
  display database tables in applications.
• To use the classes of namespaces System.Data and
  System.Data.SqlClient to manipulate databases.
• To use ADO.NET's disconnected object model to
  store data from a database in local memory.
• To create XML documents from data sources.

4

• 20.1 Introduction
• 20.2  Relational Databases
• 20.3  Relational Database Overview Books
  Database
• 20.4  SQL
• 20.4.1 Basic SELECT Query
• 20.4.2 WHERE Clause
• 20.4.3 ORDER BY Clause
• 20.4.4 Merging Data from Multiple Tables INNER
  JOIN
• 20.4.5 INSERT Statement
• 20.4.6 UPDATE Statement
• 20.4.7 DELETE Statement
• 20.5  ADO.NET Object Model

5

• 20.6 Programming with ADO.NET Extracting
  Information from a Database
• 20.6.1 Displaying a Database Table in a
  DataGridView
• 20.6.2 How Data Binding Works
• 20.7 Querying the Books Database
• 20.8   Programming with ADO.NET Address Book
  Case Study
• 20.9   Using a DataSet to Read and Write XML
• 20.10 Wrap-Up
• 20.11 Web Resources

6
Fig. 20.23 Adding a data source to a project.
7
Fig. 20.24 Choosing the data source type in the
Data Source Configuration Wizard.
8
Fig. 20.25 Adding a new data connection.
9
Fig. 20.26 Choosing the Books.mdf data
connection.
10
Fig. 20.27 Saving the connection string to the
application configuration file.
11
Fig. 20.28 Choosing the database objects to
include in the DataSet.
12
Fig. 20.29 Viewing a data source listed in the
Data Sources window.
13
Fig. 20.30 Viewing a database listed in the
Solution Explorer.
14
Fig. 20.31 Design view after dragging the
Authors data source node to the Form.
15
Fig. 20.32 Displaying the Authors table in a
DataGridView.
16
Fig. 20.33 Data binding architecture used to
display the Authors table of the Books database
in a GUI.
17
Outline
DisplayTable.cs (1 of 2)
18
Outline
DisplayTable.cs (2 of 2)
19
Fig. 20.35 Viewing the BooksDataSet in the
Dataset Designer.
20
Fig. 20.36 TableAdapter Query Configuration
Wizard to add a query to a TableAdapter.
21
Fig. 20.37 Choosing the type of query to be
generated for the TableAdapter.
22
Fig. 20.38 Specifying a SELECT statement for
the query.
23
Fig. 20.39 Query Builder after adding a WHERE
clause by entering a value in the Filter column.
(Part 1 of 2.)
24
Fig. 20.39 Query Builder after adding a WHERE
clause by entering a value in the Filter column.
(Part 2 of 2.)
25
Fig. 20.40 The SELECT statement created by the
Query Builder.
26
Fig. 20.41 Specifying names for the methods to
be added to the TitlesTableAdapter.
27
Fig. 20.42 Dataset Designer after adding Fill
and Get methods to the TitlesTableAdapter.
28
Outline
DisplayQueryResult.cs (1 of 5)
29
Outline
DisplayQueryResult.cs (2 of 5)
30
Outline
DisplayQueryResult.cs (3 of 5)
31
Outline
DisplayQueryResult.cs (4 of 5)
32
Outline
(c)
DisplayQueryResult.cs (5 of 5)
33
Outline
AddressBook.cs (1 of 3)
34
Outline
AddressBook.cs (2 of 3)
35
Outline
AddressBook.cs (3 of 3)
36
Fig. 20.45 Selecting the control(s) to be
created when dragging and dropping a data source
member onto the Form.
37
Fig. 20.46 Displaying a table on a Form using a
series of Labels and TextBoxes.
38
Fig. 20.47 Dataset Designer for the
AddressBookDataSet after adding a query to
AddressesTableAdapter.
39
Fig. 20.48 Design view after adding controls to
locate a last name in the address book.
40
Fig. 20.49 Viewing the DataBindings.Text
property of a TextBox in the Properties window.
41
Outline
XMLWriter.cs (1 of 3)
42
Outline
XMLWriter.cs (2 of 3)
43
Outline
XMLWriter.cs (3 of 3)
44
Outline
Players.xml