The Microsoft .Net Framework has been a major step forward for developers. Its rich class library and object-oriented structure have simplified many aspects of programming — a good thing, given the increasing complexity of today's applications. Database programmers have not been forgotten. The database part of .NET, called ADO.NET, offers significant improvements in interoperability, ease of programming and maintenance, and performance. It is an evolutionary advance from Microsoft's earlier database technology, called simply ADO (for ActiveX Data Objects).

Connections

You use one of two ADO.NET classes to create a connection to a database. The SqlConnection class is used for connections to Microsoft SQL Server databases (version 7 and later), and the OleDbConnection class is used for connections to databases that support the OLE DB technology (for example, Access, Oracle, SQL Server versions 6.5 and earlier, and third party SQL products).

The process of making a connection is much the same as you are probably used to: you need to create a connection string that contains all the needed information, such as the data source, used name, and password. For example:

ConStr="data source=DATA-SERVER;initial catalog=Northwind;user id=pga;password=redwine;"
Dim sqlCon As New SqlConnection(ConStr)
sqlCon.Open()

DataSets

If you have worked with earlier Microsoft database technologies, you are familiar with the concept of a recordset, an in-memory copy of information that has been extracted from the data source. In ADO.NET, the recordset is replaced with the dataset, represented by the DataSet class. The name was changed to reflect the fact that a dataset is a different animal than a recordset. They are similar in concept, but a dataset is much more powerful than a recordset. Let's look at the details.

A recordset can hold, at most, a single table. While you can use a JOIN command to combine data from two or more tables in a recordset, the result is still limited to a single results table that contains the joined data. A dataset, on the other hand, is a collection of one or more tables. Each table in a dataset is represented by a DataTable object. Typically, each DataTable object represents a table in the underlying database. As a result, the dataset can mimic the actual structure of the database.

In addition, a dataset can contain relationships. A dataset relationship is the same as a foreign key relationship in the database itself. For example, a database may contain a table of suppliers and a table of inventory items, with a relationship defined that links each part to its supplier. A dataset could contain not only the two tables, but also this relationship between them. As a result, a dataset can hold much richer data structures, including self-relating tables and many-to-many relationships.

Data Adapters

In the old ADO, recordsets were created directly from a database connection. In ADO.NET, the link is not direct; you must use a data adapter to create a dataset. Looked at another way, a data adapter serves as the link between a data source (represented by an SqlConnection or OleDbConnection object) and a dataset. There are two data adapter classes that function essentially the same: SqlDataAdapter, for use with SqlConnection objects, and OleDbDataAdapter, for use with OleDbConnection objects.

To use a data adapter, you must associate it with an SQL command that is encapsulated in a SqlCommand or OleDbCommand object, depending on the type of connection you are using. A SqlCommand or OleDbCommand object is also associated with the data connection to use. This might seem counterintuitive. Wouldn't the data adapter itself be associated with the data connection? Nope — that's not how ADO.NET does things. You create a command object as follows:

Dim myCmd As New SqlCommand(sql, conn)

Here, sql is a string containing your SQL statement and conn is a reference to the SqlConnection object. Syntax for the OleDbCommand object is the same. Once the adapter object is created, you use its Fill() method to transfer data from the data source to a dataset.

Putting it All Together

Now we have all the pieces needed to create a dataset and populate it with data. Let's look at the code that is required. This assumes a connection to an SQL Server database. If you are using an OleDb connection the syntax will be essentially the same. This code also assumes that the variable ConString contains the connection string required for the targeted data source and the variable SqlString contains your SQL statement.

Dim sqlCon As New SqlConnection(ConString)
sqlCon.Open()<
Dim myCmd As New SqlCommand(SqlString, sqlCon)
Dim sqlAdapter As New SqlDataAdapter>
sqlAdapter.SelectCommand = myCmd
Dim myDataSet As New DataSet("Dataset title of your choice")
sqlAdapter.Fill(myDataSet, "Title for results table")

By using Fill() multiple times (with different data adapters), you can put multiple tables in a dataset.

Using a Dataset

The previous section showed you how you can populate a dataset with one or more tables from an existing data source. While that's a common way to use datasets, it is certainly not the only way. You can add new tables to a dataset and then add columns (fields) to the tables, define a primary key, and so on. Once a table is defined, you can add data to it in various ways, such as from an XML source, and use the dataset's tools to work with it. Here's an example:

Dim ContactsDS As New DataSet("Contacts")
Dim ContactsTable As DataTable = ContactsDS.Tables.Add("Contacts Table")
ContactsTables.Columns.Add("CustomerID", Type.GetType("System.Int32")
ContactsTables.Columns.Add("FirstName", Type.GetType("System.String")
ContactsTables.Columns.Add("LastName", Type.GetType("System.String")
ContactsTables.Columns.Add("LastContact", Type.GetType("System.DateTime")
Dim ColumnArray(1) As DataColumn
ColumnArray(0) = ContactsTable.Columns("CustomerID")
ContactsTable.PrimaryKey = ColumnArray

At this point, you have a new, empty table with four defined columns (fields). The CustomerID field is defined as the primary key. Now you can add a row (record) to the table and populate it with data, like this:

Dim NewRow As DataRow = ContactsTable.NewRow()
NewRow("CustomerID") = 13423
NewRow("FirstName") = "Jason"
NewRow("LastName") = "Baker"
NewRow("LastContact") = DateTime.Parse("May3, 2004")

You can also create relationships between tables. A relationship links two tables by a column they have in common. For example, an Orders table can be linked to a Customers table via the CustomerID column:

Dim parentCol As DataColumn
Dim childCol As DataColumn
parentCol = MyDataSet.Tables("Customers").Columns("CustomerID")
childCol = MyDataSet.Tables("Orders").Columns("CustomerID")
Dim relCustOrder As DataRelation
relCustOrder = New DataRelation("CustomersOrders", parentCol, childCol)
MyDataSet.Relations.Add(relCustOrder)

You can also define constraints for a dataset. A foreign key constraint specifies what, if anything, happens to related tables when data is deleted or updated. There are four possibilities:

• Cascade: Delete or update actions are propagated to related tables/rows.
• SetNull: Values in related rows are set to DBNull
• SetDefault: Values in related rows are set to their default value.
• None: No action is taken on related rows.

Using the Customer/Order tables again for an example, what happens if you delete a customer from the Customers table? If a Cascade constraint is defined, then that customer's orders will be deleted also. On the other hand, if a SetNull constraint is in effect, then the CustID field (the one that links the tables) is set to DBNull for all that customer's orders. You could also define a constraint that would prevent the user from deleting any customer for whom there are orders in the Order table.

Summing Up

ADO.NET brings new power and flexibility to database programming. The DataSet class is central to ADO.NET, encapsulating in one class much of the functionality, such as relationships and constraints, that are more traditionally thought of as features of the database proper and not of a remote, memory-resident representation of the data. This is important for efficient programming of the distributed, disconnected data scenarios that programmers commonly face today.