Introduction To ADO

Document Sample
Introduction To ADO Powered By Docstoc
					Introduction to ADO.NET
Cathi Gero, Prenia Software & Consulting Services

Learn the key technologies involved in using ADO.NET with Visual Studio .NET to create Web Forms or
Windows Forms.

What is ADO.NET?
.NET Data Providers
Connecting To A Data Source
     Connection Samples
     Command Samples
Reading Data
     DataReader Sample
Executing Stored Procedures
     Stored Procedure Sample
DataAdapter – Working With Disconnected Data
     DataAdapter Sample
Command Builder
     CommandBuilder Sample
DataSet Object
     Populating A DataSet Sample
Strongly-Typed DataSets
DataTable Object
     Manually Filling A DataTable Sample
DataColumn Object
     Accessing Metadata Sample
DataRow Object
DataView Object
     DataView Sample
Using DataRelations
     Relating DataTables Sample
Manipulating Data
     Updating Data Sample
     Adding New Data Sample
Working With XML
     Working With XML Sample
Author’s Bio


“black box” technologies such as the ADO cursor engine.
through the ADO Recordset object.

What is ADO.NET?

                 VFP 7.0




    DataReader                DataSet

Figure 1. The ADO.NET Overview

.NET Data Providers
with SQL Server databases, version 7 and later.

Each .NET data provider implements the same base classes – Connection, Command, DataReader,
Parameter, and Transaction – although their actual names depend on the provider. For example, the SQL
OleDbConnection object. Regardless of which .NET data provider you use, the provider’s Connection
your data store, you create an instance of the provider’s connection object, set the object’s
ConnectionString property, and then call its Open method.


separate download at The namespace is Microsoft.Data.Odbc.
Data Provider.


            .SqlTypes                .SqlClient          .Common               .OleDb

Figure 2. The ADO.NET Related Namespaces

Connecting To A Data Source
System.Data.SQLClient.SqlConnection and for OLE DB you use the System.Data.OleDb.OleDbConnection.
queries and retrieve results.

Transaction object.

Connection Samples

' OLE DB Provider Sample
Dim connStr As String = "Provider=VFPOLEDB.1;Data Source=“ & _
Dim cnn As New OleDbConnection(connStr)
' Use the connection in here…
If cnn.State = ConnectionState.Open Then
End If

' SQL Server Provider Sample
Dim connStr As String = "data source=(local);” & _
       “initial catalog=Northwind;user id=sa"
Dim cnn As New SqlConnection(connStr)
' Use the connection in here…
If cnn.State = ConnectionState.Open Then
End If


namespace System.Data.OleDb.OleDbCommand.



Command Samples

' OLE DB Provider Sample
Dim strSQL As String = "SELECT * FROM customer”
Dim cmd As New OleDbCommand(strSQL ,cnn)

' SQL Server Provider Sample
Dim strSQL As String = "SELECT * FROM customers”
Dim cmd As New SqlCommand(strSQL ,cnn)

Reading Data

DataReader doesn’t support updating. The data returned by the DataReader is read-only. Because the
DataReader object supports such a minimal set of features, it’s extremely fast and lightweight. The
network activity.
database connection. Otherwise the connection won’t be closed until the Garbage Collector gets around to
This tells the Command object to automatically close the database connection when the DataReader’s
Close method is called.

DataReader Sample

Dim   cnn As New SqlConnection(connStr)
Dim   strSQL As String = "SELECT * FROM Authors"
Dim   cmd As New SqlCommand(strSQL, cnn)
Dim   dr As SqlDataReader

dr = cmd.ExecuteReader( _

Do While dr.Read()
        lstDemo.Items.Add(String.Format("{0}: {1}, {2}", _
                dr("Au_Id"), dr("Au_lname"), dr("Au_fname")))


Executing Stored Procedures

be executed. Thus compilation takes SQL Server time. That’s one of the benefits of using stored

object’s CommandType property. Set this property to any value in the CommandType enumeration:
StoredProcedure tells the Command that you’re calling a stored procedure. The Command object will
generate the syntax for calling your stored procedure.

then adding the same parameters to the Command object’s Parameters collection. By default the
are ParameterDirection.Output or ParameterDirection.Input. The SQL Server .NET data provider doesn’t
support the generic parameter marker “?”; instead it requires named parameters that use the “@” prefix.

Stored Procedure Sample

Dim dr As SqlDataReader

Dim cnn As New SqlConnection(connStr)
Dim cmd As New SqlCommand("CustOrderHist", cnn)

cmd.CommandType = CommandType.StoredProcedure
Dim prm As SqlParameter = _
        cmd.Parameters.Add("@CustomerID", "CACTU")
dr = cmd.ExecuteReader()

While dr.Read()
        lstDemo.Items.Add(String.Format("{0}: {1}", dr(0), dr(1)))
End While

DataAdapter – Working With Disconnected Data

the pending changes stored in your DataSet objects to your database.

instance, the SelectCommand property contains a Command object that represents the query that you’ll
the Update method on the DataAdapter object and ADO.NET will use the Command objects which you’ve

for mapping columns, called a ColumnMappings collection.



                      ecec Comman


                     Ins Co omman

                     De eteCmm an

Figure 3. The DataAdapter Object Model

DataAdapter Sample


Dim strSQL As String = "SELECT * FROM Products"

Dim oda As New OleDbDataAdapter(strSQL, connStr)

Dim cmdInsert As New OleDbCommand(“INSERT INTO Products” & _
        “(product_id, prod_name) VALUES (?,?)”)

oda.InsertCommand = cmdInsert

Command Builder

object’s SelectCommand.


       Your query returns data from only one table
       That table has a primary key
       The primary key is included in the results of your query

The disadvantages with using the CommandBuilder are:

       It doesn’t offer the best possible run-time performance.
       You can supply your own updating logic in code in less time than it takes the CommandBuilder to
        request and process the metadata required to generate similar updating logic.
       The CommandBuilder doesn’t offer options to let you control the updating logic that is generated.
       You can’t specify the type of optimistic concurrency you want to use.
       A CommandBuilder will not help you submit updates using stored procedures.

CommandBuilder Sample


Dim strSQL As String = "SELECT * FROM Products"

Dim oda As New OleDbDataAdapter(strSQL, connStr)

Dim cb As New OleDbCommandBuilder(oda)

oda.InsertCommand = cb.GetInsertCommand()
oda.UpdateCommand = cb.GetUpdateCommand()
oda.DeleteCommand = cb.GetDeleteCommand()

DataSet Object

DataTable objects (stored in the DataSet object’s Tables collection). ADO.NET was created to help
than act as a container for multiple DataTable objects.
data are simply cached in each DataRow. When it’s time to send these changes to your database, it might
processes or servers.

the DataSet class’s Merge method to combine the contents of two DataSet objects into a single DataSet.

communicate with a database. With ADO.NET, it is not necessary to communicate until you’re ready to
submit the new rows.








Figure 4. The DataSet Object Model

Populating A DataSet Sample


Dim strSQL As String = "SELECT * FROM Products"
Dim oda As New OleDbDataAdapter(strSQL, connStr)
Dim ds As New DataSet()
Dim dr As DataRow

oda.Fill(ds, "ProductInfo")

For Each dr In ds.Tables("ProductInfo").Rows
Next dr

' Want to bind a Windows Form grid? It's this easy:
dgdDemo.DataSource = ds.Tables("ProductInfo")

Strongly-Typed DataSets

only improves the readability of your code, but also enables Visual Studio .NET’s IntelliSense feature to
make context-sensitive suggestions as you type code.

once to populate its internal data.

DataTable Object


collection of tables in a DataSet object through the DataSet object’s Tables property.

DataTable object using DataColumn objects, as well as ForeignKeyConstraint and UniqueConstraint


using a DataRelation object, which may then be used to return a row’s related child or parent rows.

Manually Filling A DataTable Sample
Dim   dr As DataRow
Dim   fi As FileInfo
Dim   dir As New DirectoryInfo("C:\")
Dim   dt As New DataTable()

dt.Columns.Add("FileName", GetType(System.String))
dt.Columns.Add("Size", GetType(System.Int32))

For Each fi In dir.GetFiles()
  dr = dt.NewRow()
  dr(0) = fi.Name
  dr(1) = fi.Length

„ Bind the DataGrid to this DataTable.
Me.dgdDemo.DataSource = dt

DataColumn Object


Accessing Metadata Sample

Dim   strSQL As String = "SELECT * FROM Products"
Dim   sda As New SqlDataAdapter(strSQL, connStr)
Dim   ds As New DataSet()
Dim   dt As DataTable

sda.Fill(ds, "ProductInfo")

Dim dc As DataColumn
For Each dc In ds.Tables("ProductInfo").Columns
        lstDemo.Items.Add(String.Format("{0} ({1})", _
                dc.ColumnName, dc.DataType))
Next dc

DataRow Object

To access the actual values stored in a DataTable object, use the object’s Rows collection, which contains
collection of DataRows.

the EndEdit method to save the changes to that row. A DataRow object’s CancelEdit method allows you to
delete or remove an item from the DataTable object’s collection of DataRows.
DataRow object maintains that column’s original value as well as its current value in order to successfully
value of a column when the row has a pending change.

DataView Object

Once you’ve retrieved the results of a query into a DataTable object, you can use a DataView object to
column, simply set the DataView object’s Sort property to the name of that column. You can also use the

DataView Sample

Dim strSQL As String = "SELECT * FROM Products"
Dim sda As New SqlDataAdapter(strSQL, connStr)
Dim ds As New DataSet()

sda.Fill(ds, "ProductInfo")

Dim dv As New DataView(ds.Tables(“ProductInfo”))

dv.Sort = “Prod_Name ASC”

dv.RowFilter = “In_Stock > 100”

„ Bind the DataGrid to this DataView.
Me.dgdDemo.DataSource = dv

Using DataRelations

different DataTable objects in your DataSet.

defined by the relation - are automatically deleted as well.

the column in each table must contain identical data types – if the parent column is an integer, then the
child column must also be an integer.

Locating a row’s related child rows in another DataTable is rather straight-forward. Simply call the
object’s name. The GetChildRows method returns the related data as an array of DataRow objects.

Relating DataTables Sample

Dim ds As New DataSet()

Dim strSQL As String = "SELECT Cust_ID, Order_Id, “ & _
        “Order_Date FROM Orders WHERE Year(Order_Date) > 1997"
Dim oda As New OleDbDataAdapter(strSQL, connStr)
oda.Fill(ds, "OrderInfo")

strSQL = "SELECT CustomerID FROM Customers “ & _
        “ORDER BY CustomerID"
Dim sda As New SqlDataAdapter(strSQL, connStr)
sda.Fill(ds, "CustomerInfo")

Dim dc1 As DataColumn = _
Dim dc2 As DataColumn = _

Dim dr As New DataRelation("CustomersToOrders", dc1, dc2)

Dim drCustomer As DataRow
Dim drOrder As DataRow

For Each drCustomer In ds.Tables("CustomerInfo").Rows
        lstDemo.Items.Add("Customer: " & _
        ' Iterate through related rows.
        For Each drOrder In drCustomer.GetChildRows(dr)
                lstDemo.Items.Add( _
                        String.Format("   Order {0} placed on {1:d}", _
                        drOrder("Order_ID"), drOrder("Order_Date")))
        Next drOrder
Next drCustomer

Manipulating Data

object’s Update method submits changes stored in the DataSet into the database. Each DataAdapter

Update method.

When the DataAdapter object detects a modified row, it determines the type of change – insert, update,
or delete – and submits it to the database, based on this type. If the row has been modified, the
its DeleteCommand to delete rows.


Updating Data Sample
Dim strSQL As String = "SELECT * FROM Authors “ & _
        “Where au_lname='Green'"
Dim sda As New SqlDataAdapter(strSQL, connStr)
Dim scb As New SqlCommandBuilder(sda)
sda.UpdateCommand = scb.GetUpdateCommand()
Dim ds As New DataSet()
Dim dr As DataRow

sda.Fill(ds, "AuthorInfo")
dr = ds.Tables("AuthorInfo").Rows(0)
dr("au_fname") = "Lisa"

If sda.Update(ds, "AuthorInfo") = 1 Then
End If

To add a new row to a DataTable object, use the NewRow method. This method creates a new empty
DataRow object with the same schema as the DataTable. Keep in mind that this new row is not
associated with the DataTable object at all at this point, it simply has the same characteristics. Once you
have created a new DataRow object, you can assign the field values, and add the new object to your
DataTable object using the Add method of the DataTable’s Rows property, passing in the DataRow object
as a parameter.

Adding New Data Sample

Dim ds As New DataSet(), dr As DataRow, dt As DataTable
strSQL = "SELECT * FROM Authors Where 0=1"
Dim sda As New SqlDataAdapter(strSQL, cnn)
Dim scb As New SqlCommandBuilder(sda)
sda.InsertCommand = scb.GetInsertCommand()

sda.Fill(ds, "AuthorInfo")
dt = ds.Tables("AuthorInfo")
dr = dt.NewRow()
dr("au_id") = "111-11-1111": dr("au_lname") = "Randell"
dr("au_fname") = "Brian": dr("contract") = 0
If sda.Update(ds, "AuthorInfo") = 1 Then
End If

Working With XML

for a universal data container such as the DataSet.

2000 and VFP.

Working With XML Sample

Dim strCD As String = Environment.CurrentDirectory
If Not strCD.EndsWith("\") Then strCD &= "\"
Dim strFileXML As String = strCD & "demo.xml"
Dim strFileXSD As String = strCD & "demo.xsd"


Dim strSQL As String = _
        "SELECT * FROM Products WHERE Product_ID = 71"
Dim oda As New OleDbDataAdapter(strSQL, connStr)
Dim ds As New DataSet()

oda.Fill(ds, "ProductInfo")


Dim ds2 As New DataSet()

' Read XML Schema and data from separate
' files. Must read the schema first.

' Display DataSet in grid.
dgdDemo.DataSource = ds2.Tables("ProductInfo")


ADO.NET provides a rich API for working with all sorts of data. It has been designed to support legacy
architectures, as well as Internet-enabled, n-tier designs. In addition, the .NET Framework’s extensive
XML support means greater reach of all your data-enabled applications.

ADO.NET provides database connectivity between relational and non-relational systems through a
common set of components. It enables truly disconnected data access. Client-server applications have
traditionally had to maintain an open connection to the database while running, or provide their own
method of caching data locally. ADO.NET was built for a disconnected architecture. One of ADO.NET’s
key features is that the DataSet is a disconnected object held completely in memory. Another key feature
is that the ADO.NET DataSet uses the industry’s standard XML to transfer data.

Author’s Bio
Cathi Gero is a consultant, developer, and founder of Prenia Corporation, providing custom software
application solutions to businesses since 1987. Her expertise lies in developing .NET business
applications, developer consulting in .NET, and database solutions using Visual FoxPro, SQL Server and
Crystal Reports. She is a Microsoft MVP and a C.P.A. As a contractor for Microsoft in 2002, Cathi was a
member of the FoxPro team working on Visual FoxPro 8.0. She is the Contributing Technical Editor for the
book .NET for Visual FoxPro Developers by Hentzenwerke Publishing. Cathi has authored whitepapers for
Microsoft and is a speaker at many developer conferences and user groups. Her monthly column, “Cathi
Gero’s .NET Tips” appears in Universal Thread Magazine and is an example of her involvement in the .NET
community. Contact Cathi directly at