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:
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)\NetSDK;Integrated Security=SSPI;database=northwind;provider=sqloledb". 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)\NetSDK;Integrated Security=SSPI;database=northwind;provider=sqloledb")
' 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
Dim mySqlConnection as SqlConnection = new SqlConnection("server=(local)\NetSDK;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
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