ADO.NET Overview

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Most applications handle data, whether in the form of a database, text file,spreadsheet, whatever — the majority of modern day programs need access to aninformation store.

Now when Visual Basic 3.0 came along with its new and groovy data accesscapabilities, people were amazed. Suddenly a mass of Access desktop applicationssprung up and the world was at peace.

Today, people are taking advantage of the latest data access technologies toimprove scalability and boost interoperability. But it all needs speeding up -faster, faster!

And there’s a chance that the tools we use today just won’t be able tokeep up with this demand. That’s why with the .NET move, Microsoft haveunveiled ADO.NET — its “evolutionary” step in data access technologiesthat promises the world and infinitely more.

But what exactly does it deliver?

ADO.NET brings along with it four, much-publicised advantages:

Interoperability – All data in ADO.NET is transported in XML format,meaning it’s simply a structured text document that can be read by anyone onany platform.

Scalability – The client/server model is out. ADO.NET promotes theuse of disconnected datasets, with automatic connection pooling bundled as partof the package.

Productivity – You can’t just pick it up and run, but ADO.NET cancertainly improve your overall development time. For example, “Typed DataSets”help you work quicker and produce more bug-free code.

Performance – Because ADO.NET is mainly about disconnected datasets,the database server is no longer a bottleneck and hence applications shouldincur a performance boost.

In ADO.NET, functionality is split into two key class groups — contentcomponents and managed-provider components.

The content components essentially hold actual data and include the DataSet,DataTable, DataView, DataRow, DataColumn and DataRelation classes.

We also have the managed-provider components, which actually talk to thedatabase to assist in data retrievals and updates. Such objects include theconnection, command and data reader.

Also, the managed-provider components are split into two key groups — onedesigned for regular data sources, with another finely tuned specifically forSQL Server.

ADO.NET bundles with a bunch of content components. The most important are:

DataSet — This is a lot like the old Recordset object, except thatit can hold multiple “tables” of data. You can also setup internal dataconstraints and relationships.

DataView — The DataView is similar to a regular database view. Youcan essentially use this object to filter tables inside the DataSet object.

Currently, there are two key sets of managed provider components — onedesigned for general data access (in System.Data.OleDb)and one fine-tuned for SQL Server (in System.Data.SqlClient).

Both of these comply with the standard data implementations defined in theSystem.Data.Common namespace.

So, what are the key managed-provider components?

Connection — OleDbConnection + SQLConnection — Like classicADO, this object implements properties such as the connection string and state.We also have the typical .Open and .Close,plus .BeginTransaction returning anobject to control a database transaction. Note that you no longer have a .Executemethod on the Connection object.

Command — OleDbCommand + SqlCommand — This is the pipelineto the backend data. You can use the command to either .ExecuteNonQuery, whichwill action an SQL statement (such as a DELETE command) upon the data — or .ExecuteReader,which links straight in to the Data Reader object.

Data Reader — OleDbDataReader + SqlDataReader — This objectessentially takes a stream of data from the Command object and allows you toread it. It’s like a forward-only Recordset and is very efficient. Howeverthis uses a server-side cursor, so you should avoid it too much as it naturallyrequires an open connection.

Data Adapter — OleDbDataAdapter + SqlDataAdapter — The DataAdapter object provides an all-in-one stop for your data. It essentially servesas a middle man, going through your connection to retrieve data, then passingthat into a DataSet. You can then pass the DataSet back to the Data Adapter,which will go and update the database. The SQL statements for each command arespecified in the InsertCommand, UpdateCommand, InsertCommand and DeleteCommandproperties.

Sample code using each of these objects will be given later.

As more and more companies move toward XML as the standard for cross-platformcommunication, Microsoft is making an active effort to use this structure topower many of its new technologies.

One application of this is in ADO.NET — where literally all data isintrinsically stored in such a format.

And you can easily tap into this store by getting your information into aDataSet — then accessing the.ReadXml,.WriteXml and .GetXmlfunctions.

In addition, you can parse XML programmatically using the System.Xml.XmlDocumentobject.

Sample access using Data Reader:

       ‘ Setup connection

       Dim myConnection AsNew OleDb.OleDbConnection( _

           "Provider=Microsoft.Jet.OLEDB.4.0;" & _

           "Data Source=C:Program FilesMicrosoft Visual " & _

           "Studio.NETCommon7ToolsBinnwind.Mdb")

        ‘ Setup command object, specifying SELECT

       ‘ and connection to use

       Dim myCommand AsNew OleDb.OleDbCommand( _
         "Select * from Customers", myConnection)

       Dim myReader AsOleDb.OleDbDataReader 

       ‘ Open connection

       myConnection.Open()

       ‘ Execute and put results into reader

       myReader = myCommand.ExecuteReader

       ‘ Read through all the records

       Do UntilmyReader.Read = False

           MessageBox.Show(myReader("CompanyName"))

       Loop

       ‘ Close reader connection before continuing

       myReader.Close()

       myConnection.Close()

Sample access using Data Adapter:

       ‘ Setup connection

       Dim myConnection AsNew OleDb.OleDbConnection( _

           "Provider=Microsoft.Jet.OLEDB.4.0;" & _

           "Data Source=C:Program FilesMicrosoft Visual " & _

           "Studio.NETCommon7ToolsBinnwind.Mdb")

       ‘ We’re just specifying the SELECT here.

       ‘ If done visually using the Server Explorer,

       ‘ the INSERT/UPDATE/DELETE + Parameters

       ‘ collection would be auto-specified for us

       Dim myDataAdapter AsNew OleDb.OleDbDataAdapter( _
         "Select * from Customers", myConnection) 

       Dim myDataSet AsNew DataSet() 

       ‘ Fill DataSet with table, call it ‘Customers’

       myDataAdapter.Fill(myDataSet, "Customers") 

       ‘ Display first CompanyName field of first

       ‘ row in Customers table

       MessageBox.Show(myDataSet.Tables("Customers").Rows(0)("CompanyName")) 

       ‘ If we had specified INSERT/UPDATE/DELETE

       ‘ commands + Parameters collection, we could

       ‘ also now edit the data, then run somethinglike:

          myDataAdapter.Update(myDataSet) 

 

Sample access using XML:

       Dim myDataSet AsNew DataSet()

       myDataSet.ReadXml("c:books.xml")

       ‘ Books.xml is file bundled with VS.NET,

       ‘ typically located at:

       ‘ C:Program FilesMicrosoft.NETFrameworkSDK

       ‘ … Samplesquickstarthowtosamplesxml

       ‘ … xmldocumenteventvbbooks.xml

        ‘ Counts the number of book nodes

       MessageBox.Show(myDataSet.Tables("book").Rows.Count)

        ‘ Retrieves the last name of the first author

       MessageBox.Show(myDataSet.Tables( _
         "author").Rows(0)("last-name"))

        ‘ Updates the last name

       myDataSet.Tables("author").Rows(0)( _
         "last-name") = "Flandadenham"

        ‘ Rewrites the XML file

       myDataSet.WriteXml("c:books.xml")

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories