Working with Data
Database access is the perfect example of source code reuse. The basic operations are always the same—add, retrieve, update, and delete records. The only thing different is the structure of the data. Yet, each database system has its own native programming library that is not compatible with other databases, making it difficult to port applications to other database systems. So, it is not surprising that numerous database libraries have been developed over the years to encapsulate the implementation details of database operations.
In this article, we talk about how to access a database using ADO.NET—the set of classes that .NET provides for database access.
Databases
Most business applications need to access some kind of a database. That database could be a flat file, a directory of files, or it could be a server program that handles data requests from client programs. The later case is most common because database servers are optimized for managing large amounts of data and many concurrent connections. In the case of Microsoft Access, the tables are contained within a file with the MDB extension and the "server" is the JET database engine that your program communicates with using the classes that ADO.NET provides.
A database consists of a collection of tables. Each table is defined as a set of columns and a collection of rows. Each row has a field of data for each column in its table.
A relational database is a database that defines relationships between the tables. Consider the logical relationship between a Customer
table and an Orders
table. A customer can have many orders, but each order can belong to only one customer. This type of relationship is called one-to-many.
The Customer
record can be thought of as the parent record of each Order
record belonging to that customer. If you delete the Customer
record while it still has orders in the Orders
table, then those orders would be orphaned records. A relational database gives us the ability make that logical relationship a physical one by putting constraints or rules on the table, such as not allowing a Customer
record to be deleted if it has any Order
records, preserving the integrity of the database.
A database table usually defines a primary key to uniquely identify every record in the table. The primary key is normally a combination of one or more fields in the table. If a table contains child records that are linked back to a parent record, then the value of that parent’s primary key is usually stored in the child record. It is that field that provides the link to the parent record. When a table contains a field that stores a primary key from some other table, that field is called a foreign key.
Consider two tables that store Customers
and Orders
. The Customer
table may be defined to hold customer information and a primary key. Table 15.1 shows what a Customer
table might look like.
Table 15.1 - Definition of the Customer Table
Field Name Data Type CustomerID
int, auto counter, primary key
Name
string
Address
string
Phone
string
The Customer
table is defined to store the Name
, Address
, and Phone
of a Customer
, as well as a CustomerID
field that will uniquely identify a record in the table. The CustomerID
field is the primary key.
The Customer
and Orders
table have a parent-child relationship, with Customers
being the parent table. Each Customer
can have one or more Orders
, giving the tables a one-to-many relationship. The definition of the Orders
table is below.
Table 15.2 - Definition of the Order Table
Field Name Data Type OrderID
int, auto counter, primary key
CustomerID
int, foreign key
OrderDate
DateTime
ShipDate
DateTime
The Orders
table definition stores the OrderID
as its primary key. It also stores the CustomerID
as the foreign key to link it back to the customer record of the customer who placed the order.
The Northwind Trader Database
An example of a database is the Northwind Trader database, which you can install with Microsoft Access. The Northwind Trader database is a sample database that contains tables for Customers
, Orders
, Employees
, and other data commonly used by businesses. We will use the Northwind Trader database in the examples that follow.
Figure 15.1
Introduction to SQL
Most databases use a language called SQL, Structured Query Language, to process their commands. While SQL (commonly pronounced "sequel") is a large topic, the basics are easily grasped by looking at a few statements. The basic operations for any database are to add, retrieve, update, and delete records. The SQL language supports these operations with the following four statements.
The
INSERT
StatementThe
INSERT
statement will add new records to a table. To insert a record, you must specify the name of the table and the values for the fields. The following statement inserts a new record into theCustomers
table of the Northwind Trader database.INSERT INTO Customers ( CustomerID, CompanyName )
VALUES ('SUD100', 'Jeff Suddeth')The
INSERT
statement takes the name of the table that you want to insert a record into. Then, in parentheses, you must specify the list of fields you are setting values to. After theVALUES
keyword, there is another set of parentheses that include the list of values to be assigned to the fields. The statement above creates a newCustomers
record and sets theCustomerID
field to"SUD100"
and theCompanyName
field to"Jeff Suddeth"
. All other fields will be null unless the field has a default value defined.The
SELECT
StatementUse the
SELECT
statement to retrieve a set of records based on the criteria you specify. TheSELECT
statement may return zero or more records depending on the conditions of your selection. The following statement does not specify any selection criteria. It will return theCustomerID
andCompanyName
fields from every row in the table.SELECT CustomerID, CompanyName
FROM CustomersTo add search criteria to the
SELECT
statement, you can use theWHERE
clause. TheWHERE
clause takes a comma separated list of conditions. The following select statement will select the record we added above.SELECT CustomerID, CompanyName
FROM Customers'''The ADO.NET Classes
ADO.NET is the portion of the .NET library that deals with databases. Table 15.3 lists some of the common ADO.NET classes.
Table 15.3 – Classes of ADO.NET
Class Description DataSet
A set of database tables that can be filled from a database and used as a disconnected database DataTable
A collection data rows and the columns that define their structure DataRow
A database Record DataView
A view of the data. Can be filtered for search criteria and sorted DataRowView
A single row from a DataView DataRelation
A parent-child relationship between tables
The classes in Table 15.3 organize the data into table structures. They are declared within theSystem.Data
namespace. TheDataSet
class is one of the most useful classes in .NET; because, it encapsulates a collection of tables and the relationships between those tables. It is essentially a mini database that you can use in memory. You can also fill theDataSet
with tables and data from some other database, giving you a local cache of data.Roll Your Own Database
In this section, we use the ADO.NET classes from Table 15.3 to define a database of customer information. We will define the structure of the database by adding columns to a table named
Customers
. Then, we will load some sample data into the table and persist the data to an XML file. When the program runs again, we will load the database from the XML file and print the data that it contains. The complete listing is shown in Example15_1.cs.Listing 15.1
// Example15_1.cs
using System;
using System.Text;
using System.Data;
using System.IO;
namespace csbook.ch15 {
class CustomerDatabase {
public void MakeFile() {
DataSet ds = new DataSet();
ds.DataSetName = "CustomerDatabase";
DataTable custTable = new DataTable();
custTable.TableName = "Customers";
// this will be the primary key
DataColumn custId = new DataColumn("CustID", typeof(int));
custId.AutoIncrement = true;
custId.AutoIncrementSeed = 101;
custId.AutoIncrementStep = 1;
custTable.Columns.Add(custId);
// make this the primary key
custTable.PrimaryKey = new DataColumn[] { custId };
// add some fields for customer data
custTable.Columns.Add(new DataColumn("Name", typeof(string)));
custTable.Columns.Add(new DataColumn("Phone", typeof(string)));
custTable.Columns.Add(new DataColumn("Email", typeof(string)));
// add the table to the DataSet
ds.Tables.Add(custTable);
// add some records to the table
DataRow row;
row = custTable.NewRow();
row["Name"] = "Evy";
row["Phone"] = "123-555-9876";
row["Email"] = "evy@evymail.com";
custTable.Rows.Add(row);
row = custTable.NewRow();
row["Name"] = "Jon";
row["Phone"] = "123-444-7384";
row["Email"] = "jon@jonsmail.com";
custTable.Rows.Add(row);
row = custTable.NewRow();
row["Name"] = "Eric";
row["Phone"] = "123-666-7398";
row["Email"] = "eric@ericsmail.com";
custTable.Rows.Add(row);
// dump to XML
ds.WriteXml("customers.xml");
Console.WriteLine("The file was created");
}
public void ReadFile() {
DataSet ds = new DataSet();
ds.ReadXml("customers.xml");
foreach (DataRow row in ds.Tables[0].Rows) {
// get the data
string name = (string)row["Name"];
string phone = (string)row["Phone"];
string email = (string)row["Email"];
// build the string
StringBuilder builder = new StringBuilder();
builder.AppendFormat
("Name: {0}\t Phone: {1}\t Email: {2}",
name, phone, email);
// write the string
Console.WriteLine(builder.ToString());
}
}
public void Test() {
if (File.Exists("customers.xml")) {
ReadFile();
}
else {
MakeFile();
}
}
static void Main(string[] args) {
CustomerDatabase db = new CustomerDatabase();
db.Test();
}
}
}Listing 15.1 defines the Customers table to have the structure shown below.
Table 15.4 – The Customers Table
Field Name Data Type CustID
int, auto increment, primary key
Name
string
Phone
string
string
To define the table, we create a
DataTable
object and set itsTableName
property to"Customers"
. TheDataTable
has a property namedColumns
, which is a collection ofDataColumn
objects that define the columns of the table. Another important property of theDataTable
is thePrimaryKey
property. ThePrimaryKey
property is an array ofDataColumn
objects. You can set the primary key by creating an array of one or moreDataColumns
from the table and assigning a reference to that array in thePrimaryKey
property.The
DataColumn
class has many properties, many of which you won’t care about for the examples here. However, two important properties are theName
andDataType
.The
Name
property is a string value that names the field. When you access a column of aDataRow
you can use this name as an index instead of an integer; so, you don’t have to remember the order of the columns.The
DataType
property stores an instance of theType
class containing the type of data the column will store. The data are stored in the fields as object references so you can use any C# data type. If theDataType
of a column isint
, then you can set the column’sAutoIncrement
property totrue
. If theAutoIncrement
property istrue
, then the column’s value will be set automatically from a counter variable that is incremented every time a new row is created. This guarantees that the field will hold a unique value for each row in the table and makes the field a good candidate to use as a primary key.Defining the Table Structure
The following code segment defines the
DataTable custTable = new DataTable();Customer
table structure. TheCustID
field is defined as anauto counter
field and used as the primary key.
custTable.TableName = "Customers";
// this will be the primary key DataColumn custId = new DataColumn("CustID", typeof(int)); custId.AutoIncrement = true;
custId.AutoIncrementSeed = 101;
custId.AutoIncrementStep = 1;
Columns.Add(custId);
// make this the primary key
custTable.PrimaryKey = new DataColumn[] { custId };
// add some fields for customer data custTable.Columns.Add(newDataColumn("Name", typeof(string)));
custTable.Columns.Add(new DataColumn("Phone", typeof(string))); custTable.Columns.Add(new DataColumn("Email", typeof(string)));Reading Rows of Data
The
DataTable
also has a property namedRows
that stores the rows of data. The rows are actually objects of theDataRow
class. TheDataRow
stores a collection of values that you can read or write to using an index. You can either use an integer—if you know the order of the columns—or you can use the name of the field. The following code segment loops through the table’s rows and obtains references to theName
,Phone
, andforeach (DataRow row in ds.Tables[0].Rows) {
// get the data
string name = (string)row["Name"];
string phone = (string)row["Phone"];
string email = (string)row["Email"];
}The
DataRow
stores the values as object references so the data must be cast to the appropriate type before we can use it. In this case, we used strings.Adding Rows of Data
We obviously need to add new rows to the table, too. To create a
DataRow
object, we call theTable
’sNewRow
method. That method will instantiate aDataRow
object and set its auto increment field for the primary key. Then, we can set the other values of the record and add the row into theTable
’sRows
collection.DataRow row = custTable.NewRow();
row["Name"] = "Evy";
row["Phone"] = "123-555-9876";
row["Email"] = "evy@evymail.com";
custTable.Rows.Add(row);Storing the Data as XML
The
DataSet
stores a collection ofDataTable
s in itsTables
property. To dump the entire DataSet to an XML file, we need to call theDataSet
’sWriteXml
method, passing the name of the file as a string. Reading the file back in is just a matter of calling theReadXml
method.ds.WriteXml("customers.xml"); // dump to XML
ds.ReadXml ("customers.xml"); // restore from XMLFiltering the Data with the DataView
The
DataView
class can be used to create a new view of the table that can be filtered or sorted without affecting the underlying table. TheDataView
is similar to a table in that it has a collection of rows but the objects in theDataView
’sRows
collection are of typeDataRowView
. You use theDataRowView
the same as you would use aDataRow
. You use the name of the field as an index to get the value from the column. Once you have aDataView
, you can filter or sort the data without affecting the actual table.You create a
// create a view that can be filtered and sorted DataView view = new DataView(ds.Tables[0]); foreach (DataRowView drv in view) { Console.WriteLine(drv["Name"].ToString()); } // find the record for Rick view.RowFilter = "Name = 'Evy'"; foreach (DataRowView drv in view) { Console.WriteLine(drv["Name"].ToString()); }DataView
by passing aDataTable
to theDataView
constructor.In the code segment, we create a
DataView
for the firstDataTable
in theDataSet
’sTables
collection. Then, we loop through theRows
collection, printing out theName
field as we go. Then, we filter the view so that view only contains records where theName
field is"Evy"
. Theforeach
loop iterates through each row in the view of"Evy"
records and prints out theName
field.Table 15.5 – OLEDB Database Classes
Class Description OleDbConnection
The physical database connection OleDbCommand
A command to execute on the database OleDbDataAdapter
Moves data between the DataSet and the physical Database using a connection and a command OleDbDataReader
Can iterate results returned from a command Now that you have been introduced to the ADO.NET classes, it is time to write some real code. The example in the next section connects to the Microsoft Access Northwind Traders database using the OleDb classes.
Connecting to Access using OLE DB
In this section, we will use the Northwind Trader database that installs (optionally) with Microsoft Access. The program is a Console Application that simply loads a
DataSet
from theCustomers
table of the database. Then, we loop through the records, printing theCustomerName
field to the screen.Listing 15.2
// Example15_2.cs
using System;
using System.Data;
using System.Data.OleDb;
namespace csbook.ch15 {
class Example15_2 {
static void Main(string[] args) {
string conString =
@"Provider=Microsoft.JET.OLEDB.4.0;"
+ @"data source=c:\data\Northwind.mdb";
// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
conn.Open();
// create the DataSet
DataSet ds = new DataSet();
// create the adapter and fill the DataSet
OleDbDataAdapter adapter =
new OleDbDataAdapter("Select * from Customers", conn);
adapter.Fill(ds);
// close the connection
conn.Close();
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows) {
Console.WriteLine(dr["CompanyName"].ToString());
}
}
}
}The listing first creates a connection string that provides information to the
OleDbConnection
class. Specifically, the connection string contains the provider for the database engine that we want to connect to and the data source, which in this case is a Microsoft Access file. If the file were password protected, we would also specify a User ID and Password in the connection string. After creating the connection string, the program creates anOleDbConnection
object, passing the connection string as the argument.Next, the listing opens the database connection by calling the connection’s
Open
method. It also creates an emptyDataSet
that will later be filled from the database.The
OleDbAdapter
class encapsulates the command to fill theDataSet
. We create the adapter by passing a SQL query string and the database connection to its constructor. This query string will select all fields of all rows from theCustomers
table. The adapter’sFill
method will execute the query through the connection and load the results into theDataSet
that we pass to it. The results include the data from the query as well as the metadata, defining its structure. When the call is complete, theDataSet
will contain a table with all theCustomer
records from the database. At that point, we can close the database connection so that it can be used by some other application.The tables within the
DataSet
are represented by theDataTable
class. EachDataTable
has a collection ofDataRow
objects storing the rows from the query. Theforeach
loop iterates through theDataRow
s, displaying theCompanyName
field. TheDataRow
object uses the string"CompanyName"
as an index to find the field of that name and retrieve its value. The value is returned as anobject
type. Calling the virtualToString
method will result in displaying the string value of the field.Connecting to a SQL Server Database
The SQL Server versions of the connection, command, and adapter classes are listed below in Table 15.6. They are defined in the
System.Data.SqlClient
namespace.Table 15.6 – SQL Server Database Classes
Class Description SqlConnection
The physical database connection SqlDataCommand
A command to execute on the database SqlDataAdapter
Moves data between the DataSet and the physical Database using a connection and a command SqlDataReader
Can iterate results returned from a command SQL Server also installs a copy of the Northwind Trader database. The next example is similar to the previous except that it uses the SQL Server versions of the connection and adapter.
While the previous listing used an adapter to execute a command internally and fill a
DataSet
, this example uses the command object directly. The program calls theSqlCommand
object’sExecuteReader
method, which returns aSqlReader
object. Then, it uses theSqlReader
to loop through the records that have been returned.Listing 15.3
// Example15_3.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace csbook.ch15 {
class Example15_3 {
static void Main(string[] args) {
// create an open the connection
SqlConnection conn =
new SqlConnection("Data Source=DESKTOP;"
+ "Initial Catalog=Northwind;"
+ "Persist Security Info=True;"
+ "User ID=jeff;Password=password");
conn.Open();
// create a SqlCommand object for this connection
SqlCommand command = conn.CreateCommand();
command.CommandText = "Select * from Customers";
command.CommandType = CommandType.Text;
// execute the command that returns a SqlDataReader
SqlDataReader reader = command.ExecuteReader();
// display the results
while (reader.Read()) {
string output = reader["CompanyName"].ToString();
Console.WriteLine(output);
}
// close the connection
reader.Close();
conn.Close();
}
}
}Data Binding
Window controls have the ability to bind their properties to the properties of other objects. When two properties are bound, a change in one is reflected in the other.
For example, consider the following listing for the
class Student { private string name; private double gpa; public string Name { get { return name; } set { name = value; } } public double Gpa { get { return gpa; } set { gpa = value; } } }Student
class. TheStudent
has two properties—the string valueName
and the double valueGpa
.If we declare an instance of the
// create the student student = new Student(); student.Name = "Jeff"; student.Gpa = 4.0; // bind the properties textBox1.DataBindings.Add(new Binding("Text", student, "Name")); textBox2.DataBindings.Add(new Binding("Text", student, "Gpa"));Student
as a member of a WindowsForm
, we can bind theName
andGpa
properties to theText
properties of a pair ofTextBox
es.We can also put a
private void testButton_Click(object sender, EventArgs e) { MessageBox.Show("Name: " + student.Name + " GPA: " + student.Gpa); }Button
on theForm
that displays the current values of the student’sName
andGpa
. This allows us to view the changes made to the object after we change the text in theTextBox
.Running the example, we can see that when the form first opens the initial values are displayed.
Figure 15.2
Then, we can change the text in the text boxes and click the
Test Button
. The message box displays the values of the student object, which have been changed to the new values in theTextBox
es.Figure 15.3
This type of data binding is called simple data binding. We can also bind a
public partial class Form1 : Form { string[] names = { "Jeff", "Rachel", "Katy", "Evy", "Ben" }; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { listBox1.DataSource = names; } }ListBox
control to an array or collection of data. Binding to a collection of data is called complex data binding. To bind theListBox
to a collection you set theListBox
’sDataSource
property. TheListBox
will display the first public property of each item in the collection in its list. The next example binds an array of strings to aListBox
.When the form is displayed, the names in the string array appear in the
ListBox
.Figure 15.4
Using the
DataGridView
The
DataGridView
is a control designed to be bound to database tables. You will find theDataGridView
in the Data group on your Toolbox. In this section, we will use theDataGridView
to build a SQL interface for the Northwind Trader database. This application is surprisingly simple using the classes provided by ADO.NET.Figure 15.5
To create the user interface, place a
TextBox
on the form and set itsMultiline
property totrue
. Then, add two buttons, setting theirText
properties toQuery
andUpdate
. Finally, place aDataGridView
component on the form. Your form might look something like the one in Figure 15.5.Since we are going to connect to a Microsoft Access database, we need to use the
OleDb
classes. At the top of the file, you should add the followingusing
statement to gain access to theSystem.Data.OleDb
namespace.using System.Data.OleDb;The
Form
’sLoad
event should create anOleDbConnection
object, anOleDbCommand
object, and aDataSet
object. Each of these objects should be stored as members of yourForm
class.private void Form1_Load(object sender, EventArgs e) {
// connect to the database
string conString =
@"Provider=Microsoft.JET.OLEDB.4.0;"
+ @"data source=c:\data\Northwind.mdb";
// create an open the connection
conn = new OleDbConnection(conString);
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
}Next, you need to implement the select button’s
Click
handler. The method should start by clearing theDataSource
property of theDataViewGrid
and creating a freshDataSet
. Then, it should reopen the connection. Set theCommand
object’sCommandText
property to theText
from theTextBox
control. Then, create an adapter with the command and fill the newDataSet
. Finally, set theDataGridView
’sDataSource
property to the first table in theDataSet
’sTable
collection.private void button1_Click(object sender, EventArgs e) {
// clear the grids data source
dataGridView1.DataSource = null;
// create a new DataSet
ds = new DataSet();
// open the connection
conn.Open();
// run the query
command.CommandText = textBox1.Text;
adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
// close the connection
conn.Close();
// set the grid's data source
dataGridView1.DataSource = ds.Tables[0];
}The last step is to implement the
Click
event handler for theUpdate
button.private void button2_Click(object sender, EventArgs e) {
// clear the grids data source
dataGridView1.DataSource = null;
// open the connection
conn.Open();
// run the query
command.CommandText = textBox1.Text;
int affected = command.ExecuteNonQuery();
MessageBox.Show ("There were " + affected + " rows affected");
// close the connection
conn.Close();
}The update button sets the
DataGridView
’sDataSource
property tonull
to clear the grid. Then, it reopens the connection and sets the command object’sCommandText
property to whatever text is in theTextBox
. This time the command is executed by theExecuteNonQuery
method. TheExecuteNonQuery
method runs a command that updates or deletes records. It returns the number of rows affected by the command. Finally, the method displays a message box showing the number of rows affected and then closes the connection. Figure 15.6 shows aSELECT
statement being executed on the database.Figure 15.6
Figure 15.7 shows the update button being used to change the
ContactTitle
fromGuru
toFool
.Figure 15.7
To verify that our update actually worked, we can run the query again.
Figure 15.8
No comments:
Post a Comment