Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

How Do I...? Common Tasks QuickStart Tutorial

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


  • VB employees.aspx
    Run Sample View Source


    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");
        
    C#


    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" +
          ...
        }
        
    C#





    Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
    Copyright � 2004 Microsoft Corporation. All rights reserved.