<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

ADO.NET: Convert an ADO Application to ADO.NET

This is an example of an ADO application that has been ported to .NET without the original graphical interface. It also shows the usage of a forward-only read-only fast DataReader. It shows how to use a DataView class to take a table from a DataSet and operate on it in a fashion similar to the old ADO Recordset model. Remember an ADO Recordset only holds data for one table, but an ADO.NET DataSet can hold multiple tables and therefore is very flexible.

The original ADO sample is the employee sample in SQL Server 2000. The original sample uses an ADO Recordset to manage the resulting data returned from the SQL query. The new sample shows how to use the SqlDataAdapter to fill a DataSet in a model similar to the ADO Recordset. Also, the original sample uses a dialog window (.cpp) or a form (.frm) to display the output of the employee table of the Northwind database. However, this .NET example only uses the Console window to output the non-image type data in the sample.

The main topics covered in this sample are:

Connection to the database

Usage of a light-weight read-only, forward-only reader

Execution of the SQL query and resulting ADO Recordset or ADO.NET DataSet

Accessing individual records in the ADO Recordset or ADO.NET DataSet

Typically in an ADO application there is a connection to the database and execution of the SQL query and resulting ADO Recordset.

In the original code used in the Visual Basic version of Employee, a connection is opened to the SQL Server database 'Northwind' using an ADO connection object and a connection string "server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind;provider=SQLNCLI". A Recordset is then returned using the ADO Recordset.Open method with a SQL 'SELECT' query.

Then, the FillDataFields function is called to retrieve individual record values.

    ' Open the database.

    cn.Open("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind;provider=SQLNCLI")
    
    ' Open the Recordset.
    Set rs = New ADODB.Recordset
    rs.Open "select * from Employees", cn, adOpenKeyset, adLockPessimistic
    
    ' Move to the first record and display the data.
    rs.MoveFirst
    FillDataFields

In the .NET implementation the steps are very similar. A connection is opened to the SQL Server database 'Northwind' using a SqlConnection object and a connection string "server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind". A Reader is then used to cycle through the data returned on execution of the query. This reader read-only, forward-only DataReader is more performant than its native ADO counterpart. A DataSet is then populated using SqlDataAdapter with a SQL 'SELECT' query and the SqlDataAdapter Fill method.

SqlConnection mySqlConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind"); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from employees", mySqlConnection); DataSet myDataSet = new DataSet(); mySqlDataAdapter.Fill(myDataSet,"Employees"); Dim mySqlConnection as SqlConnection = new SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind") Dim mySqlDataAdapter as SqlDataAdapter = new SqlDataAdapter("select * from employees", mySqlConnection) Dim myDataSet as DataSet = new DataSet() mySqlDataAdapter.Fill(myDataSet,"Employees")

Then in an ADO application there is some function that moves among the Recordset getting data from each record and field combination. This would typically be accomplished using calls to the Recordset MoveFirst, MoveLast, MovePrevious, and MoveNext methods. For Example:

    If rs.EOF = False Then
        If rs.BOF = True Then
             rs.MoveFirst
        End If
        rs.MoveNext
    End If
    If rs.EOF = False Then
        FillDataFields
    End If

Then data would be extracted from each current record in a Recordset using individual field accessors. For Example:

    For Each fld In Flds
        FieldSize = fld.ActualSize
        If FieldSize > 0 Then
            Select Case fld.Name
                Case "EmployeeID"
                    txtEID.Text = Str(fld.Value)
                Case "LastName"
                    txtLastName.Text = fld.Value
                Case "FirstName"
                    txtFirstName.Text = fld.Value
                Case "Title"
                    txtTitle.Text = fld.Value
                ...
            End Select
        End If
    Next

In this example, the DataSet Table "Employee" is specifically assigned to a DataView and the resulting DataView iterated over to extract the data values. By using a DataView you can turn any table in a DataSet into an object that functions similarly to the old ADO Recordset. // Create a new dataview instance on the Employees table that was just created DataView myDataView = new DataView(myDataSet.Tables["Employees"]); // Sort the view based on the first column name. myDataView.Sort = "EmployeeID"; int iReportsTo; for (int i = 0; i < myDataView.Count; i++) { Console.Write("\n************************ Employee number " + (i+1).ToString() + " ************************\n"); Console.Write("EmployeeID:\t" + myDataView[i]["EmployeeID"].ToString() + "\n" + "FirstName:\t" + myDataView[i]["FirstName"].ToString() + "\n" + "LastName:\t" + myDataView[i]["LastName"].ToString() + "\n" + "Title:\t\t" + myDataView[i]["Title"].ToString() + "\n" + "TitleOfCourtesy:" + myDataView[i]["TitleOfCourtesy"].ToString() + "\n" + ... } ' Create a new dataview instance on the Employees table that was just created Dim myDataView as DataView = new DataView(myDataSet.Tables("Employees")) ' Sort the view based on the first column name. myDataView.Sort = "EmployeeID" Dim iReportsTo as integer Dim i as integer for i = 0 to myDataView.Count -1 Console.Write(Chr(10) & "************************ Employee number " & (i+1).ToString() + " ************************" & Chr(10)) Console.Write("EmployeeID:" & Chr(9) & myDataView(i)("EmployeeID").ToString() + Chr(10) & _ "FirstName:" & Chr(9) & myDataView(i)("FirstName").ToString() + Chr(10) & _ "LastName:" & Chr(9) & myDataView(i)("LastName").ToString() + Chr(10) & _ "Title:" & Chr(9) & Chr(9) & myDataView(i)("Title").ToString() + Chr(10) & _ "TitleOfCourtesy:" & myDataView(i)("TitleOfCourtesy").ToString() + Chr(10) & _ ... next