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
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.
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")
VB
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
VB
Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2005 Microsoft Corporation. All rights reserved.
|