Data Access with ADO.NET

Monday Jul 15th 2002 by DatabaseJournal.com Staff
Share:

This article will first be a simple overview of what ADO.NET is, and second, will be a look at the specific objects and functionality that make up ADO.NET as a whole.

If you are anything like I was when you began your search for knowledge on Microsoft's .NET technology, you were quite confused when first confronted with ADO.NET and how to implement it within your ASP.NET pages. This series of articles will first be a simple overview of what ADO.NET is, and second, will be a look at the specific objects and functionality that make up ADO.NET as a whole. My hope is that this series of articles will be beneficial to intermediate as well as advanced developers who are moving to .NET. Any prior knowledge of ADO and ASP.NET will be particularly helpful. So let's get started!

We should begin by noting that ADO.NET is not an upgrade to ADO 2.6, it is a complete rewrite, similar to the Microsoft .NET Platform itself. The move toward .NET technologies involves many new concepts and thought processes behind web applications development, and ADO.NET is certainly no exception. With ADO 2.6, developers only had three objects to work with when accessing and manipulating data: the connection, command, and recordset objects. While these objects were innovative at the time of their release, they now pose inherent problems as we strive for a distributed model of systems development. One major problem, for example, is that a recordset is a large COM object that is not easily passed around a network. Other problems also exist. ADO recordset objects are difficult to share across computing platforms and cannot penetrate firewalls. Additionally, if a recordset is created as a result of a JOIN query, the original data sources can be difficult to update.

So you can see that ADO, while innovative, has areas that require improvement. Now we enter the .NET era of application development. ADO.NET has been created with a multitude of objects that are designed to carry out very specific functional tasks and solve the problems listed above. These objects come in two varieties: SQL Server optimized or Ole-DB. This is of particular significance to developers who are using a full suite of Microsoft tools to build their applications. If SQL Server is your database of choice, ADO.NET provides you with a set of objects that bypass the Ole-DB provider and directly access SQL Server's tabular data stream. This direct access to SQL Server's proprietary API provides noticeable performance gains. If you are accessing MS Access, Oracle, Sybase, etc., you have a completely different set of classes that encompass the same functionality as the SQL Server optimized objects, but through an Ole-DB provider.

To access the ADO.NET functionality, you must import namespaces, which house the data objects we need to carry out specific functions, into the pages you create. The following is a listing of the main namespaces required to manage data in the .NET environment and a brief explanation of the functionality they expose.

System.Data

Exposes objects used to access and store relational data. The DataSet, DataReader, and DataRelation objects are used to create relational data stores in virtual memory. These objects are independent of data source, meaning that relational SQL Server data, XML, or array data, for example, can be accessed using the same type of properties and methods. This namespace should most likely be imported into every .NET page that accesses data. Some objects of this namespace include:

  • DataSet
  • DataTable
  • DataRow
  • DataRelation

System.Data.OleDb

Contains the objects that allow us to access data through an Ole-DB provider. These objects, as mentioned earlier, have the same properties and methods as the SQLClient namespace that I will cover next. Objects include:

  • OleDbConnectin
  • OleDbCommand
  • OleDbDataSet
  • OleDbDataReader
  • OleDbDataAdapter

System.Data.SQLClient

Encapsulates all the functionality that we need to do any type of data reading or manipulation from Microsoft's SQL Server. Objects include:

  • SQLConnection
  • SQLCommand
  • SQLDataSet
  • SQLDataReader
  • SQLDataAdapter

As you can tell by comparing the names of each object in the OleDb and SQLClient namespaces, the objects are built exactly alike for different access methods. This is a distinction that you as a developer will not have to be concerned with once you choose your data source.

Just from the onset, it is very obvious that the expanded classes can provide us with much more functionality than we have ever had in the past. In my next article, I will be discussing the most basic of ADO.NET objects: SQLConnection, SQLCommand, and SQLDataReader for reading database information onto a web page with ASP.NET (remember that the "SQL" and "OleDb" prefixes to object names are completely interchangeable).

All right, so you're tired of hearing the theory and concepts behind ADO.NET. Me too. Let's look at and discuss some techniques and code that will allow us to read data into our ASP.NET web pages. I am going to be using the "pubs" database that ships with SQL Server to demonstrate the examples. I will also be introducing one ASP.NET control that we are going to use to display the data we retrieve: the ASP DataGrid. Although this control will not be covered at length, you will get a sense of its basic functionality and how ASP.NET strives to separate presentation and business logic.

First of all, I'm going to show you all of the code for a small data access program. Take a minute and look over all the code for this example. Don't worry if you don't understand what's going on, I will explain each line.

 1 <%@ Import Namespace="System.Data" %>
 2 <%@ Import Namespace="System.Data.SQLClient" %>
 3 <html>
 4   <script language="vb" runat="server">
 5     'Page load event of the web page.
         This is similar to the Form_Load event in VB.'
 6     Protected Sub Page_Load(ByVal sender As System.Object,
           ByVal e As System.EventArgs) Handles MyBase.Load
 7       Dim myConnection as New SQLConnection(
           "Server=localhost;Database=pubs;UID=sa;PWD=;")
 8       myConnection.Open
 9
10       Dim strSQL as String = "select * from authors order by au_lname"
11       Dim myCommand as New SQLCommand(strSQL,myConnection)
12
13       Dim myDataReader as SQLDataReader
14       myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
15
16       myDataGrid.DataSource = myDataReader
17       myDataGrid.DataBind()				
18
19       myConnection.Close()
20     End Sub
21   </script>
22 <head>
23   <title>Viewing Data with ADO.NET</title>
24 </head>
25 <body>
26   <p>
27   <asp:DataGrid ID="myDataGrid" Runat="server" />
28   </p>
29 </body>
30 </html>

The first thing we are doing in this page is importing the proper namespaces. On lines 1 & 2 we import the System.Data and System.Data.SQLClient namespaces, which give us access to the objects we need to access SQL Server Data. If you need to access other data, such as Microsoft Access, Oracle, etc., simply import the System.Data.OleDb namespace rather than System.Data.SQLClient. Line 3 simply opens up our HTML tag for the web page.

Lines 4 through 21 hold the Visual Basic code that will provide the functionality in our page and I will come back to the specifics of this section in just a moment.

Lines 22 through 30 are self-explanatory HTML with one exception, and that is line 27:

27  <asp:DataGrid ID="myDataGrid" Runat="server" />

This is the ASP.NET DataGrid server control. The tag is just like a normal HTML tag, except for the fact that it opens with the ‘asp' namespace (in bold). The ‘ID' attribute of the tag sets the name of the control, which is how we will access the object programmatically. The ‘Runat' attribute tells the server that we want this control produced on the server. There are a plethora of other properties that can be set on this control, but they will not be discussed in this article. Just know, that these properties can be set either programmatically or in the HTML declaration of the control (notice that logical and presentational layers are separated, and that there is no HTML embedded within the <script> tags).

Finally, we get to the real meat and potatoes of the program, the code between the <script> tags in lines 4 through 21. The script tag also has the Runat attribute where we tell the server we want the code processed on the server. Line 6 is our procedure declaration. Don't worry about the parameters between the parentheses (just know they need to be there), but notice that this sub procedure "handles" the load event of this page.

6  Protected Sub Page_Load(ByVal sender As System.Object,
     ByVal e As System.EventArgs) Handles MyBase.Load

Lines 7 & 8 are where we see our first ADO.NET objects come into the picture. The SQLConnection object is much like the ADO Connection object. A nice feature of the .NET platform is its object orientation compared to VBScript. We can simply declare the new object and pass the connection string into it with one line of code. Once we have the SQLConnection object created, we open it… it's that easy.

7  Dim myConnection as New SQLConnection("Server=localhost;Database=pubs;UID=sa;PWD=;")
8  myConnection.Open

Next we put our query into a string and pass both it and our SQLConnection object into a new SQLCommand object. Again, this object is like its ADO 2.6 counterpart, though you will soon see a new method associated with ADO.NET's command object (just one of many new methods and properties).

10  Dim strSQL as String = "select * from authors order by au_lname"
11  Dim myCommand as New SQLCommand(strSQL,myConnection)

Continuing on, the SQLDataReader object appears. This object is optimized for reading data into a web page one time. You can think of this object as a forward only cursor, read only lock type ADO recordset. If you are pulling data into a page simply for display or to reference other data, you will use this object. On the other hand, ADO.NET provides a robust object called SQLDataSet which can be used in countless other scenarios. The DataSet object will be an article (or two!) completely on its own.

13  Dim myDataReader as SQLDataReader
14  myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

To fill the DataReader, we call the "ExecuteReader" method of the command object we created earlier. This method call returns a filled DataReader object. The parameter of this method simply tells the DataReader that when the connection is closed, it, too, is to be closed. This is important because the DataReader object is "connected" in nature, and closing it as soon as it is used will free up valuable resources on the server.

Finally, the DataGrid control we declared in our HTML is data bound by setting its DataSource property equal to the DataReader object we just filled. The DataBind method is then invoked to create the table that results (pictured below).

16  myDataGrid.DataSource = myDataReader
17  myDataGrid.DataBind()				
18
19  myConnection.Close()

Make sure you close your connection object out, and that's it! It is that simple to create a robust, data bound table with ADO.NET. No more looping and writing as we did with ASP, and our content and logic tiers are completely separated.

Sample Screen Capture

Now that you have seen a few objects in action, try them out on your own. I'm going to pick up the pace with my next article and get through several more key ADO.NET objects, so be ready. Until then… Happy Programming!

We now know how to access data and put it into a simple, readable form in our ASP .Net web pages, but there's so much more! In this article I am going to explain several more key objects that allow us to efficiently access our data in .Net. I will be using the OleDb flavor of ADO .Net classes for those of you who are not working with SQL Server, but just remember that if you are a SQL Server user, the difference is only in the naming of the classes you use and the connection string (refer to Part I of this series if you're not sure what I'm talking about). So let's get to work.

The objects we are going to look at are the OleDbCommand (a little more in depth than last time), OleDbDataAdapter, and DataSet objects. As in my previous articles, I am going to give you all of the code (click here to download a zip file of the source code) for a small data program that I have written. This program allows us to add records to the Authors table of the pubs database. After we add a record, it is displayed on the screen in a DataGrid object along with the rest of the records in the table. Mull it over and then continue on where I'll enumerate its functionality. There are some ASP .Net issues that we are going to have to discuss en route to understanding what is going on here, so hopefully you will learn a lot from this example.

This is our program's interface:

Sample Screen Capture

To begin with, note that in the second line of the program source code, that I had to import the OleDb namespace. We are accessing an MS Access data file, so we are unable to use the SQLClient classes.

Within the <script> tags of the file, resides our VB .Net code and procedures. There are five sub procedures, the first of which is 'Page_Load'. This procedure is the same as in my previous article's example, with one main difference. You will see the IsPostBack property of the Page object is being checked. ASP .Net pages are designed to post forms to themselves, so this boolean property is used to determine whether our program (page) is being accessed for the first time, or if we are posting information back to this page on the server. This can be a quite difficult concept to grasp, and it has bitten me in the rear more than once since I started developing with ASP .Net. The functionality of the Page_Load event goes like this: If the page is being loaded for the first time, we are going to call our GetConnection() and FillDataGrid() functions, both of which are defined by me, the developer. If this is a post-back operation, we execute no code from within the Page_Load event.

(Above the page load procedure, you will find that I declared our connection object and its connection string. This simply makes the connection object global so that we can use it in multiple sub procedures.)

The GetConnection() sub procedure simply builds a new connection out of the global connection we created at the beginning of our VB .Net code and opens it. Now we can use it in our FillDataGrid() procedure (shown below) where we find two new ADO .Net objects: the OleDbDataAdapter and the DataSet. The OleDbDataAdapter is a member of the OleDb namespace and is the "gateway" for our data to get into the DataSet object. The DataSet is a member of the System.Data namespace and is similar to the ADO recordset, but with tons of new functionality that we have never seen in ADO data access classes before.

FillDataGrid()

 1 Sub FillDataGrid()
 2    Dim strSQL as String = "SELECT au_fname as [First Name], " & _ 
 3       "au_lname as [Last Name], address as Address, city as City, " & _
 4       "state as State, zip as ZIP FROM Authors ORDER BY au_lname"
 5
 6    Dim myDataAdapter as New OleDbDataAdapter(strSQL, myConnection)
 7
 8    Dim myDataSet as New DataSet()
 9    myDataAdapter.Fill(myDataSet, "Authors")
10
11    myDataGrid.DataSource = myDataSet.Tables("Authors")
12    myDataGrid.DataBind()
13 End Sub

Now when I say "gateway" for our data, I mean that the OleDbDataAdapter object fills our dataset for us. On line 5, we provide it the SQL query (from lines 2-4) and our connection object, which we opened in the Page_Load event. Next, we declare a new DataSet object on line 8, and finally we use the its Fill method on line 9 to populate the DataSet. We send in two parameters with the fill command, the first being the targeted DataSet, "myDataSet", and second, the name we wish to use to refer to the virtual table we are creating, "Authors". The last piece of code in lines 11-12 simply binds the DataSet to our DataGrid object. On line 11, the DataGrid's data source property is set to the "Authors" table in the tables collection of the DataSet object. Pretty nifty, eh?

In essence, the DataSet object is a virtual relational database. Not only can we define multiple tables within the object, but we can also enumerate relationships between tables. While we won't be going into that much depth in this article, be aware that this is possible and is a large advantage of the DataSet over ADO 2.6's recordset object. The OleDbDataAdapter's functionality looks like a trivial, extra step in this small program, but where its real power lies is in its data source update capabilities. After building a DataSet and making changes to it, the OleDbDataAdapter object can build appropriate update, delete and insert SQL statements to bring the data source up to date on changes made to the data within the DataSet object. This type of operation is much more difficult using ADO 2.6.

Okay now lets cover the AddAuthor() procedure which I have listed below. This procedure is called when a user clicks the 'Add Author' ASP:Button on the form. This is coded into the onClick property of the ASP server control (notice the Runat="server" property):

<asp:Button Text="Add Author" ID="btnAdd" OnClick="AddAuthor" Runat="server" />

The Page_Load event code is bypassed when this happens because IsPostBack is true. Then, within this procedure, I am using an OleDbCommand object to insert a new record, and then again calling the FillDataGrid() procedure followed by a ClearForm() procedure that I have created to clear the field values entered into the form by the user.

AddAuthor()

 1 Sub AddAuthor(ByVal sender As System.Object, ByVal e As System.EventArgs)
 2    Dim intRecordsAffected as Integer
 3    GetConnection()
 4
 5    Dim strSQL as String = "INSERT INTO Authors(au_id,au_fname,au_lname,
                             address,city,state,zip) values('" & _
 6       au_id.Text    & "','" & _
 7       au_fname.Text & "','" & _
 8       au_lname.Text & "','" & _
 9       address.Text  & "','" & _
10       city.Text     & "','" & _
11       state.Text    & "','" & _
12       zip.Text      & "')"
13
14    Dim myCommand as New OleDbCommand(strSQL, myConnection)
15
16    intRecordsAffected = myCommand.ExecuteNonQuery()
17
18    txtRecAff.Text = intRecordsAffected & " record added successfully."
19    FillDataGrid()
20    ClearForm()
21 End Sub

On line 14, "myCommand" is declared as an OleDbCommand object. I pass in a SQL INSERT string that I created in lines 5-12, (the values are simply pulled from the form input fields with the "Text" property, similar to a traditional VB windows form) and my connection object that was opened again on line 3 with the GetConnection() sub procedure. You may have noticed that I declared an integer variable, "intRecordsAffected" on line 2; this will hold the number of records affected by the execution of my OleDbCommand object with the ExecuteNonQuery() method on line 16. ExecuteNonQuery() simply executes a command that we know is not going to return any records, like an update, insert or delete.

After the insert command is complete, we bind the DataGrid to the Authors table in the FillDataGrid() procedure and clear all of the form fields with the ClearForm() call. All ClearForm() does is set each form field's text property equal to an empty string.

As I keep preaching, ADO .Net makes our lives a lot easier than traditional ADO does, and gives us more functionality to boot. The examples I am providing right now are farily simple, and are just to get you familiar with the classes and their associated methods and properties, but once we get into some more complex examples, the added-value of these data access methods will become increasingly more evident.

So now we have seen in action the following ADO .Net objects:

  • SQL/OleDbConnection
  • SQL/OleDbDataReader
  • SQL/OleDbDataAdapter
  • SQL/OleDbCommand
  • DataSet

Along with some ASP .Net objects, properties and controls:

  • Page.IsPostBack
  • ASP:DataGrid
  • ASP:TextBox
  • ASP:Button

Until next time, good luck and have fun!

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved