This article introduces SQL, shows how to use ADO.NET Datasets, and connect to SQL Server and Microsoft Access databases. It introduces data binding and shows how to build your own SQL Query program that works with Access databases.
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.
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.
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 Statement
The 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 the Customers 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 the VALUES keyword, there is another set of parentheses that include the list of values to be assigned to the fields. The statement above creates a new Customers record and sets the CustomerID field to "SUD100" and the CompanyName field to "Jeff Suddeth". All other fields will be null unless the field has a default value defined.
The SELECT Statement
Use the SELECT statement to retrieve a set of records based on the criteria you specify. The SELECT 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 the CustomerID and CompanyName fields from every row in the table.
SELECT CustomerID, CompanyName
FROM Customers
To add search criteria to the SELECT statement, you can use the WHERE clause. The WHERE 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 the System.Data namespace. The DataSet 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 the DataSet 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 |
Email |
| string |
To define the table, we create a DataTable object and set its TableName property to "Customers". The DataTable has a property named Columns, which is a collection of DataColumn objects that define the columns of the table. Another important property of the DataTable is the PrimaryKey property. The PrimaryKey property is an array of DataColumn objects. You can set the primary key by creating an array of one or more DataColumns from the table and assigning a reference to that array in the PrimaryKey property.
The DataColumn class has many properties, many of which you won’t care about for the examples here. However, two important properties are the Name and DataType.
The Name property is a string value that names the field. When you access a column of a DataRow 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 the Type 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 the DataType of a column is int, then you can set the column’s AutoIncrement property to true. If the AutoIncrement property is true, 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 Customer table structure. The CustID field is defined as an auto counter field and used as the primary key.
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;
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 named Rows that stores the rows of data. The rows are actually objects of the DataRow class. The DataRow 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 the Name, Phone, and Email fields.
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"];
} 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 the Table’s NewRow method. That method will instantiate a DataRow 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 the Table’s Rows 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 of DataTables in its Tables property. To dump the entire DataSet to an XML file, we need to call the DataSet’s WriteXml method, passing the name of the file as a string. Reading the file back in is just a matter of calling the ReadXml method.
ds.WriteXml("customers.xml"); // dump to XML
ds.ReadXml ("customers.xml"); // restore from XML
Filtering 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. The DataView is similar to a table in that it has a collection of rows but the objects in the DataView’s Rows collection are of type DataRowView. You use the DataRowView the same as you would use a DataRow. You use the name of the field as an index to get the value from the column. Once you have a DataView, you can filter or sort the data without affecting the actual table.
You create a DataView by passing a DataTable to the DataView constructor.
// 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()); } In the code segment, we create a DataView for the first DataTable in the DataSet’s Tables collection. Then, we loop through the Rows collection, printing out the Name field as we go. Then, we filter the view so that view only contains records where the Name field is "Evy". The foreach loop iterates through each row in the view of "Evy" records and prints out the Name 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 the Customers table of the database. Then, we loop through the records, printing the CustomerName 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 an OleDbConnection 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 empty DataSet that will later be filled from the database.
The OleDbAdapter class encapsulates the command to fill the DataSet. 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 the Customers table. The adapter’s Fill method will execute the query through the connection and load the results into the DataSet 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, the DataSet will contain a table with all the Customer 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 the DataTable class. Each DataTable has a collection of DataRow objects storing the rows from the query. The foreach loop iterates through the DataRows, displaying the CompanyName field. The DataRow object uses the string "CompanyName" as an index to find the field of that name and retrieve its value. The value is returned as an object type. Calling the virtual ToString 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 the SqlCommand object’s ExecuteReader method, which returns a SqlReader object. Then, it uses the SqlReader 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 Student class. The Student has two properties—the string value Name and the double value Gpa.
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; } } } If we declare an instance of the Student as a member of a Windows Form, we can bind the Name and Gpa properties to the Text properties of a pair of TextBoxes.
// 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")); We can also put a Button on the Form that displays the current values of the student’s Name and Gpa. This allows us to view the changes made to the object after we change the text in the TextBox.
private void testButton_Click(object sender, EventArgs e) { MessageBox.Show("Name: " + student.Name + " GPA: " + student.Gpa); } 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 the TextBoxes.
Figure 15.3
This type of data binding is called simple data binding. We can also bind a ListBox control to an array or collection of data. Binding to a collection of data is called complex data binding. To bind the ListBox to a collection you set the ListBox’s DataSource property. The ListBox will display the first public property of each item in the collection in its list. The next example binds an array of strings to a ListBox.
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; } } 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 the DataGridView in the Data group on your Toolbox. In this section, we will use the DataGridView 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 its Multiline property to true. Then, add two buttons, setting their Text properties to Query and Update. Finally, place a DataGridView 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 following using statement to gain access to the System.Data.OleDb namespace.
using System.Data.OleDb;
The Form’s Load event should create an OleDbConnection object, an OleDbCommand object, and a DataSet object. Each of these objects should be stored as members of your Form 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 the DataSource property of the DataViewGrid and creating a fresh DataSet. Then, it should reopen the connection. Set the Command object’s CommandText property to the Text from the TextBox control. Then, create an adapter with the command and fill the new DataSet. Finally, set the DataGridView’s DataSource property to the first table in the DataSet’s Table 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 the Update 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’s DataSource property to null to clear the grid. Then, it reopens the connection and sets the command object’s CommandText property to whatever text is in the TextBox. This time the command is executed by the ExecuteNonQuery method. The ExecuteNonQuery 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 a SELECT statement being executed on the database.
Figure 15.6
Figure 15.7 shows the update button being used to change the ContactTitle from Guru to Fool.
Figure 15.7
To verify that our update actually worked, we can run the query again.
Figure 15.8
