Ch.20 Abridged
Download
Report
Transcript Ch.20 Abridged
1
20
Database, SQL and
ADO.NET
2006 Pearson Education, Inc. All rights reserved.
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 30:8
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
2006 Pearson Education, Inc. All rights reserved.
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.
2006 Pearson Education, Inc. All rights reserved.
4
20.1
Introduction
20.2
Relational Databases
20.3
Relational Database Overview: Books Database
20.4
SQL
20.5
20.4.1
Basic SELECT Query
20.4.2
WHERE
20.4.3
ORDER BY
20.4.4
Merging Data from Multiple Tables: INNER
20.4.5
INSERT
Statement
20.4.6
UPDATE
Statement
20.4.7
DELETE
Statement
Clause
Clause
JOIN
ADO.NET Object Model
2006 Pearson Education, Inc. All rights reserved.
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
2006 Pearson Education, Inc. All rights reserved.
6
Data Sources window
Data menu
Fig. 20.23 | Adding a data source to a project.
2006 Pearson Education, Inc. All rights reserved.
7
Fig. 20.24 | Choosing the data source type in the Data Source Configuration Wizard.
2006 Pearson Education, Inc. All rights reserved.
8
Fig. 20.25 | Adding a new data connection.
2006 Pearson Education, Inc. All rights reserved.
9
Fig. 20.26 | Choosing the Books.mdf data connection.
2006 Pearson Education, Inc. All rights reserved.
10
Fig. 20.27 | Saving the connection string to the application configuration file.
2006 Pearson Education, Inc. All rights reserved.
11
Fig. 20.28 | Choosing the database objects to include in the DataSet.
2006 Pearson Education, Inc. All rights reserved.
12
Data Sources window
Fig. 20.29 | Viewing a data source listed in the Data Sources window.
2006 Pearson Education, Inc. All rights reserved.
13
Fig. 20.30 | Viewing a database listed in the Solution Explorer.
2006 Pearson Education, Inc. All rights reserved.
14
authorsBindingNavigator
authorsDataGridView
Component tray
Fig. 20.31 | Design view after dragging the Authors data source node to the Form.
2006 Pearson Education, Inc. All rights reserved.
15
Move to first row
Move to previous row
(a)
Move to next row
Delete the current row
Add a new row
Move to last row
Save changes
(b)
Currently selected row
Fig. 20.32 | Displaying the Authors table in a DataGridView.
2006 Pearson Education, Inc. All rights reserved.
16
Fig. 20.33 | Data binding architecture used to display the Authors table of the Books
database in a GUI.
2006 Pearson Education, Inc. All rights reserved.
1
2
// Fig. 20.34: DisplayTable.cs
// Displays data from a database table in a DataGridView.
3
4
using System;
using System.Windows.Forms;
5
6
namespace DisplayTable
7
8
9
10
public partial class DisplayTableForm : Form
(1 of 2)
{
public DisplayTableForm()
{
12
13
InitializeComponent();
} // end constructor
14
15
16
17
// Click event handler for the Save Button in the
// BindingNavigator saves the changes made to the data
private void authorsBindingNavigatorSaveItem_Click(
22
23
Outline
DisplayTable.cs
{
11
18
19
20
21
17
object sender, EventArgs e )
{
this.Validate();
this.authorsBindingSource.EndEdit();
this.authorsTableAdapter.Update( this.booksDataSet.Authors );
} // end method authorsBindingNavigatorSaveItem_Click
2006 Pearson Education,
Inc. All rights reserved.
24
25
// loads data into the booksDataSet.Authors table,
26
// which is then displayed in the DataGridView
27
28
private void DisplayTableForm_Load( object sender, EventArgs e )
{
18
29
// TODO: This line of code loads data into the
30
31
32
// 'booksDataSet.Authors' table. You can move, or remove it,
// as needed.
this.authorsTableAdapter.Fill( this.booksDataSet.Authors );
Outline
DisplayTable.cs
(2 of 2)
} // end method DisplayTableForm_Load
33
34
} // end class DisplayTableForm
35 } // end namespace DisplayTable
(a)
(b)
2006 Pearson Education,
Inc. All rights reserved.
19
Dataset Designer
TitlesTableAdapter
Fig. 20.35 | Viewing the BooksDataSet in the Dataset Designer.
2006 Pearson Education, Inc. All rights reserved.
20
Fig. 20.36 | TableAdapter Query Configuration Wizard to add a query to a TableAdapter.
2006 Pearson Education, Inc. All rights reserved.
21
Fig. 20.37 | Choosing the type of query to be generated for the TableAdapter.
2006 Pearson Education, Inc. All rights reserved.
22
Fig. 20.38 | Specifying a SELECT statement for the query.
2006 Pearson Education, Inc. All rights reserved.
23
(a)
Fig. 20.39 | Query Builder after adding a WHERE clause by entering a value in the Filter
column. (Part 1 of 2.)
2006 Pearson Education, Inc. All rights reserved.
24
(b)
Fig. 20.39 | Query Builder after adding a WHERE clause by entering a value in the Filter
column. (Part 2 of 2.)
2006 Pearson Education, Inc. All rights reserved.
25
Fig. 20.40 | The SELECT statement created by the Query Builder.
2006 Pearson Education, Inc. All rights reserved.
26
Fig. 20.41 | Specifying names for the methods to be added to the TitlesTableAdapter.
2006 Pearson Education, Inc. All rights reserved.
27
Fig. 20.42 | Dataset Designer after adding Fill and Get methods to the
TitlesTableAdapter.
2006 Pearson Education, Inc. All rights reserved.
1
// Fig. 20.43: DisplayQueryResult.cs
2
// Displays the result of a user-selected query in a DataGridView.
3
using System;
4
5
6
using System.Windows.Forms;
28
Outline
namespace DisplayQueryResult
7 {
8
9
10
11
12
public partial class DisplayQueryResultForm : Form
{
public DisplayQueryResultForm()
{
InitializeComponent();
13
14
} // end DisplayQueryResultForm constructor
15
16
17
// Click event handler for the Save Button in the
// BindingNavigator saves the changes made to the data
private void titlesBindingNavigatorSaveItem_Click(
DisplayQuery
Result.cs
(1 of 5)
object sender, EventArgs e )
18
19
20
21
22
{
23
} // end method titlesBindingNavigatorSaveItem_Click
this.Validate();
this.titlesBindingSource.EndEdit();
this.titlesTableAdapter.Update( this.booksDataSet.Titles );
2006 Pearson Education,
Inc. All rights reserved.
24
29
25
// loads data into the booksDataSet.Titles table,
26
27
// which is then displayed in the DataGridView
private void DisplayQueryResultForm_Load(
object sender, EventArgs e )
28
29
Outline
{
30
// TODO: This line of code loads data into the
31
// 'booksDataSet.Titles' table. You can move, or remove it,
DisplayQuery
Result.cs
32
33
34
// as needed.
this.titlesTableAdapter.Fill( this.booksDataSet.Titles );
(2 of 5)
35
36
37
// set the ComboBox to show the default query that
// selects all books from the Titles table
queriesComboBox.SelectedIndex = 0;
38
} // end method DisplayQueryResultForm_Load
2006 Pearson Education,
Inc. All rights reserved.
39
40
// loads data into the booksDataSet.Titles table based on
41
42
43
// user-selected query
private void queriesComboBox_SelectedIndexChanged(
object sender, EventArgs e )
44
45
46
{
47
48
49
50
30
// fill the Titles DataTable with
// the result of the selected query
switch ( queriesComboBox.SelectedIndex )
{
case 0: // all books
titlesTableAdapter.Fill( booksDataSet.Titles );
51
52
53
break;
case 1: // books with copyright year 2006
titlesTableAdapter.FillWithCopyright2006(
54
55
56
57
booksDataSet.Titles );
break;
case 2: // How to Program books, sorted by Title
titlesTableAdapter.FillWithHowToProgramBooks(
58
59
60
Outline
DisplayQuery
Result.cs
(3 of 5)
booksDataSet.Titles );
break;
} // end switch
61
} // end method queriesComboBox_SelectedIndexChanged
62
} // end class DisplayQueryResultForm
63 } // end namespace DisplayQueryResult
2006 Pearson Education,
Inc. All rights reserved.
31
(a)
Outline
DisplayQuery
Result.cs
(4 of 5)
(b)
2006 Pearson Education,
Inc. All rights reserved.
32
(c)
Outline
DisplayQuery
Result.cs
(5 of 5)
2006 Pearson Education,
Inc. All rights reserved.
1
// Fig. 20.44: AddressBook.cs
2
3
// Allows users to manipulate an address book.
using System;
4
5
using System.Windows.Forms;
6
namespace AddressBook
7
8
{
9
10
Outline
AddressBook.cs
public partial class AddressBookForm : Form
(1 of 3)
{
public AddressBookForm()
11
12
13
14
15
{
16
17
18
// BindingNavigator saves the changes made to the data
private void addressesBindingNavigatorSaveItem_Click(
object sender, EventArgs e )
19
{
InitializeComponent();
} // end AddressBookForm constructor
// Click event handler for the Save Button in the
20
this.Validate();
21
this.addressesBindingSource.EndEdit();
22
this.addressesTableAdapter.Update(
23
24
33
this.addressBookDataSet.Addresses );
} // end method bindingNavigatorSaveItem_Click
2006 Pearson Education,
Inc. All rights reserved.
25
26
// loads data into the addressBookDataSet.Addresses table
27
28
29
30
private void AddressBookForm_Load( object sender, EventArgs e )
{
// TODO: This line of code loads data into the
// 'addressBookDataSet.Addresses' table. You can move,
34
31
// or remove it, as needed.
32
this.addressesTableAdapter.Fill(
33
this.addressBookDataSet.Addresses );
34
} // end method AddressBookForm_Load
35
36
37
38
39
// loads data for the rows with the specified last name
// into the addressBookDataSet.Addresses table
private void findButton_Click( object sender, EventArgs e )
{
40
41
// fill the DataSet's DataTable with only rows
// containing the user-specified last name
42
43
44
addressesTableAdapter.FillByLastName(
addressBookDataSet.Addresses, findTextBox.Text );
} // end method findButton_Click
Outline
AddressBook.cs
(2 of 3)
45
46
// reloads addressBookDataSet.Addresses with all rows
47
48
private void browseAllButton_Click( object sender, EventArgs e )
{
49
50
// fill the DataSet's DataTable with all rows in the database
addressesTableAdapter.Fill( addressBookDataSet.Addresses );
2006 Pearson Education,
Inc. All rights reserved.
51
35
findTextBox.Text = ""; // clear Find TextBox
} // end method browseAllButton_Click
52
53
Outline
54
} // end class AddressBookForm
55 } // end namespace AddressBook
AddressBook.cs
(a)
(b)
(3 of 3)
(c)
2006 Pearson Education,
Inc. All rights reserved.
36
Fig. 20.45 | Selecting the control(s) to be created when dragging and dropping a data source
member onto the Form.
2006 Pearson Education, Inc. All rights reserved.
37
Fig. 20.46 | Displaying a table on a Form using a series of Labels and TextBoxes.
2006 Pearson Education, Inc. All rights reserved.
38
Fig. 20.47 | Dataset Designer for the AddressBookDataSet after adding a query to
AddressesTableAdapter.
2006 Pearson Education, Inc. All rights reserved.
39
Fig. 20.48 | Design view after adding controls to locate a last name in the address book.
2006 Pearson Education, Inc. All rights reserved.
40
Fig. 20.49 | Viewing the DataBindings.Text property of a TextBox in the Properties
window.
2006 Pearson Education, Inc. All rights reserved.
1
2
// Fig. 20.50: XMLWriter.cs
// Demonstrates generating XML from an ADO.NET DataSet.
3
using System;
4
5
6
using System.Windows.Forms;
7
8
9
{
XMLWriter.cs
public partial class XMLWriterForm : Form
{
public XMLWriterForm()
11
{
12
13
14
15
InitializeComponent();
} // end XMLWriterForm constructor
16
17
// BindingNavigator saves the changes made to the data
private void playersBindingNavigatorSaveItem_Click(
(1 of 3)
// Click event handler for the Save Button in the
object sender, EventArgs e )
{
20
this.Validate();
21
this.playersBindingSource.EndEdit();
22
23
Outline
namespace XMLWriter
10
18
19
41
this.playersTableAdapter.Update( this.baseballDataSet.Players );
} // end method bindingNavigatorSaveItem_Click
2006 Pearson Education,
Inc. All rights reserved.
24
25
// loads data into the baseballDataSet.Players table
26
27
private void XMLWriterForm_Load( object sender, EventArgs e )
{
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
42
// TODO: This line of code loads data into the
// 'baseballDataSet.Players' table. You can move,
// or remove it, as needed.
this.playersTableAdapter.Fill( this.baseballDataSet.Players );
Outline
XMLWriter.cs
(2 of 3)
}
// write XML representation of DataSet when Button clicked
private void writeButton_Click( object sender, EventArgs e )
{
// set the namespace for this DataSet
// and the resulting XML document
baseballDataSet.Namespace = "http://www.deitel.com/baseball";
// write XML representation of DataSet to a file
baseballDataSet.WriteXml( "Players.xml" );
2006 Pearson Education,
Inc. All rights reserved.
43
44
43
// display XML representation in TextBox
45
outputTextBox.Text += "Writing the following XML:\r\n" +
46
baseballDataSet.GetXml() + "\r\n";
47
} // end method writeButton_Click
48
} // end class XMLWriterForm
49 } // end namespace XMLWriter
Outline
XMLWriter.cs
(3 of 3)
2006 Pearson Education,
Inc. All rights reserved.
1
2
3
4
<?xml version="1.0" standalone="yes"?>
<BaseballDataSet xmlns="http://www.deitel.com/baseball">
<Players>
<PlayerID>1</PlayerID>
5
6
<FirstName>John</FirstName>
<LastName>Doe</LastName>
7
<BattingAverage>0.375</BattingAverage>
8
9
<PlayerID>2</PlayerID>
11
<FirstName>Jack</FirstName>
12
13
14
<LastName>Smith</LastName>
<BattingAverage>0.223</BattingAverage>
</Players>
15
16
<Players>
<PlayerID>3</PlayerID>
18
19
20
Outline
Players.xml
</Players>
<Players>
10
17
44
<FirstName>George</FirstName>
<LastName>O'Malley</LastName>
<BattingAverage>0.344</BattingAverage>
</Players>
21 </BaseballDataSet>
2006 Pearson Education,
Inc. All rights reserved.