CSharp and Working with Data

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.

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