Introduction to ADO.NET

Interested in .NET? Don’t miss SitePoint’s .NET Feature Guide — it’s an excellent resource!

The advent of ASP back in late 1996 when ADO 1.0 was released represented a new way of dynamically retrieving data from a database. Though ADO was at its very infancy then, and was something of an offshoot of DAO and RDO, nevertheless it represented a new bold direction. Each subsequent version of the technology leads us one step closer to ADO 2.6, when development came to an end. At this point, emerging from the shadows came the most revolutionary framework to date – .NET, with it the very powerful and mature ADO.NET.

This new data component, introduced with .NET, presented an exciting new approach to data access. Though the techniques, and logic used to connect to databases with ADO.NET weren’t startlingly different from those used with its predecessor, ADO.NET had a lot to offer. What was unique about this technology was the architecture beneath it all, its powerful approach to data management, and the flexibility in the next level of data-presenting devices.

ADO, for its time, was certainly successful. Although the re-releases of the technology hadn’t heralded dramatic change for ADO, it achieved what it had to within its own architecture. However, it was continually plagued by problems that surrounded its handling of disconnected data stores, and functioning properly and concisely with XML. This is where ADO.NET stepped in – these are the two things the new technology easily manages, as XML is the core component of the entire .NET Framework! ADO.NET was simply created to remedy all the deficiencies found in ADO, and provide developers with the power to achieve more with less.

In this article we’ll dive right into accessing data in .NET, and demonstrate the variety of options available to the ADO.NET developer for data access and presentation. This article is intended as a concise introduction to data access in .NET that’ll have you up to speed on the technology in no time.

Namespaces

With classic ASP (or other database accessing methods), you need to first connect to your data store before you can even begin to contemplate any data retrieval. However, in ADO.NET there are two core objects that allow developers to work with data initially: the DataReader and the DataSet. We’ll soon learn all about them.

In any .NET data access page, before you connect to a database, you first have to import all the necessary namespaces that will allow you to work with the objects required. As we’re going to work with SQL Server, we’ll first import the namespaces we need. Namespaces in .NET are simply a neat and orderly way of organizing objects, so that nothing becomes ambiguous.

<%@ Import Namespace="System" %> 

<%@ Import Namespace="System.Data" %> 

<%@ Import Namespace="System.Data.SqlClient" %>

Note: If we were using a database other than SQL, for instance, MS Access, we would then replace the SQLClient with OleDb. If we use Oracle, .NET v 1.1 provides the System.Data.OracleClient namespace, and for any ODBC data source it provides the System.Data.Odbc namespace. You’ll find detailed information on all the available methods and objects we’ll discuss in the .NET SDK Framework documentation.

The Connection

After we import all the necessary namespaces, we’re ready to connect to our database. Now, whether you implement the DataReader or Dataset, your initial database connection will still be as follows:

SqlConnection objConnect = new SqlConnection (Your Connection String); 

 

objConnect.Open();

Above, we set up our SQLConnection Connection object with our database connection information, and then we opened it. Listed below are the common connection object methods we could work with:

• Open – Opens the connection to our database
• Close – Closes the database connection
• Dispose – Releases the resources on the connection object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Close method as well.
• State – Tells you what type of connection state your object is in, often used to check whether your connection is still using any resources. Ex. if (ConnectionObject.State == ConnectionState.Open)

As far as opening a database connection goes, that’s really the extent of it. Now we have to decide which object to use in order to achieve the end results you wish to present. We now have to choose whether to work with a Datareader or the Dataset. Let’s begin by looking at the DataReader.

The DataReader

With classic ASP, when we needed a method for data retrieval, we’d use the appropriate data object and set its cursors to the task at hand. If we wanted a quick forward-only data read, we would set our Recordset’s CursorType to adOpenForwardOnly and its LockType to adLockReadOnly (often referred to as a “fire-hose” cursor). Well, with .NET, all you need is the DataReader, which offers many features by which you can further tweak the efficiency of its output.

Command Object Methods

Now that we know what the DataReader does, there are numerous methods that can be used with it to achieve your specific goals. Here are some methods the DataReader works with through its Command object. The four Execute methods all pertain to an action performed by the Command object, wile the remaining methods are used to enhance the Command object’s own functionality.

• ExecuteReader – Simply executes the SQL query against the database, using the Read() method to traverse through data, as illustrated below
• ExecuteNonQuery – Used whenever you work with SQL stored procedures with parameters, as illustrated in the Stored Procedures section below
• ExecuteScalar – Returns a lightning fast single value as an object from your database Ex. object val = Command.ExecuteScalar(); Then check if != null.
• ExecuteXmlReader – Executes the SQL query against SQL Server only, while returning an XmlReader object. See .NET documentation for more information
• Prepare – Equivalent to ADO’s Command.Prepared = True property. Useful in caching the SQL command so it runs faster when called more than once. Ex. Command.Prepare();
• Dispose – Releases the resources on the Command object. Used to force garbage collecting, ensuring no resources are being held after our connection is used. Incidentally, by using the Dispose method you automatically call the Connection object’s Close method as well.

Thus, after we establish our initial database connection all we need to do to retrieve data with a DataReader is to use the Command object to query the open database. The Command.ExecuteReader method used via the Command Object creates our DataReader.

SqlCommand objCommand = new SqlCommand(Sql String, objConnect);  

  

SqlDataReader objDataReader = objCommand.ExecuteReader();

If you hadn’t noticed, the connection object above is obviously set for SQL Server. For the others like Access, you’d use the OleDbConnection object, easily accommodated by replacing the SQL part in SqlCommand with OleDB, i.e. OleDbCommand objCommand. This applies to the SqlDataReader (which must be changed to OleDbDataReader) as well. This interchangeability works across the .NET framework, so keep it in mind.

Once we have our DataReader object, we set up our DataReader’s Read() method in a while loop and traverse and display our data results by index position, rather than objDataReader["Name"].ToString(). I prefer this method as it’s a little quicker, provided you can remember your column names!

while (objDataReader.Read() == true) {  

  

Response.Write (objDataReader[0].ToString() + "<BR>");  

  

}

DataReader Methods

Here are some common methods available to the Datareader:

• Read – Moves the record pointer to the first row, which allows the data to be read by column name or index position. Can check for data existence with conditional, if (DataReader.Read() = true)
• HasRows – New only with .NET v1.1. HasRows checks if any data exists, and is used instead of the Read method. Ex. if (DataReader.HasRows).
• IsClosed – A method that can determine if the DataReader is closed. Ex. if (DataReader.IsClosed == false)
• NextResult – Equivalent to ADO’s NextRecordset Method, where a batch of SQL statements are executed with this method before advancing to the next set of data results. As with the loop just listed, you can add DataReader.NextResult() after the first loop within multiple SQL statements, and then begin a new loop for the next set.
• Close – Closes the DataReader

After all’s said and done, it’s always good programming practice to close, release and clear all connections and resources:

objDataReader.Close();  

objDataReader = null;  

objCommand.Dispose();  

objCommand = null;   

objConnect.Close();  

objConnect= null;

DataReader Object Command Behaviors

But that’s not all! The DataReader object also has some cool optional Command Behaviors that you should use whenever you can for added scalability and the improved performance of your application. These can be implemented within the DataReader object. For example:

objCommand.ExecuteReader(CommandBehavior.CloseConnection);   

or objCommand.ExecuteReader(CommandBehavior.SingleResult);

The behaviours are:

• SingleResult – Similar to ExecuteScalar, returns a single value
• SingleRow – As it sounds, returns us a single row
• SequentialAccess – Is a very efficient method of in-order data access and useful in two ways. Firstly, it’s used with the DataReader’s Get Methods where you can return results in sequential order from the database using the columns’ ordinal positions, it’s superior (in terms of speed) to using the objDataReader["Column1"].ToString() or objDataReader[0].ToString() methods. And secondly, the Data Reader’s many available Get Methods, i.e. objDataReader.GetString(0) or objDataReader.GetInt32(0), allow us to bypass any initial data conversion that would take place. However, non-Null data must be present for it to work, and all data retrieval must be in sequential order, dependent on your SQL query.
• CloseConnection – Forces the DataReader and its Connection object to close, once it reads through the data

The Completed DataReader

Here is our Datareader code in its entirety. You could run this within any function, or on Page_Load. Furthermore, the tags that enclose the ADO code below are common to all code examples listed throughout this article:

<%@ Page Language="C#" Debug="False" Explicit="True"%>  

<%@ Import Namespace="System.Data" %>  

<%@ Import Namespace="System.Data.SqlClient" %>  

  

<html>  

<head>  

<title>ADO.NET</title>  

</head>  

<body>  

  

<script runat="server" language="C#">  

  

  void Page_Load (Object Source, EventArgs E) {  

  

    SqlConnection objConnect = new SqlConnection   

("server=(local);uid=sa;pwd=;database=Northwind;");  

        objConnect.Open();  

  

    SqlCommand objCommand = new SqlCommand("Select * from   

Suppliers Order by ContactName Asc", objConnect);  

    objCommand.Prepare(); SqlDataReader objDataReader =   

objCommand.ExecuteReader CommandBehavior.CloseConnection);  

  

    while (objDataReader.Read() == true) {  

        Response.Write (objDataReader  

[2].ToString() + "<BR>");  

    }  

  

    objDataReader.Close();  

        objDataReader = null;   

        objCommand.Dispose();  

        objCommand = null;  

        objConnect.Close();  

        objConnect= null;  

    

  }  

  

</script>  

</body>  

</html>

In the example above we set up our page, and import our necassary namespaces that allow us to work with our data (keep in mind SqlClient is case sensitive in C#). Next, we place our datareader code between the server side tags that connect to our SQL Database, and in turn, display our results. After this, we close all our objects.

Stored Procedures

The Datareader itself, as with the DataSet, is more than capable of dealing with Stored Procedures; they offer plenty of performance advantages and the ability to consolidate many operations in one location. However, the Dataset’s approach to Stored Procedures tends to become a little drawn-out, as it follows closely with the data manipulation model that the Dataset offers. When it comes to quick and straightforward Stored Procedure handling, the DataReader’s Command Object methods are more than sufficient, as we’ll see now.

We begin by creating a trivial stored procedure that accepts one parameter – @txt, which is passed into my query.

CREATE PROCEDURE ShowSuppliers (   

@txt varchar(50)   

)   

AS   

Select CompanyName, City, Country from Suppliers Where    

Country like "%" + @txt + "%" 

If you wanted to simply execute a Stored Procedure, pass it a value, and then read through that, how would you do it? Easy! Prior to calling the ExecuteReader method, replace the SQL String in the SqlCommand constructor with the name of the Stored Procedure, then specify to the Command Object’s CommandType Property that you’re working with a Stored Procedure. After you’ve done this, implement the Parameters collection (via the Param SqlParameter variable) to set up the value you wish to pass to your stored procedure, like so:

SqlCommand objCommand = new SqlCommand("ShowSuppliers",   

objConnect);   

objCommand.CommandType = CommandType.StoredProcedure;   

   

SqlDataReader objDataReader = objCommand.ExecuteReader   

(CommandBehavior.CloseConnection);   

   

SqlParameter Param = objCommand.Parameters.Add("@txt",    

SqlDbType.VarChar, 50);   

Param.Value = "US";   

   

// ... Get Data

Then display your data using the DataReader methods, like the while loop code example listed in the last section, or via the DataGrid example that’s coming up.

Stored Procedures – and Alternative Method

An alternative method of working with Stored Procedures within this context is the Command.ExecuteNonQuery() method. This is useful when working with more multifaceted Stored Procedures that have input, output and return values. Using them is not that much more complicated; simply implement the Parameters collection shown below, i.e. Param.Direction = ParameterDirection.Input or .OutPut or .ReturnValue; and apply the .Value =, whatever the value type:

CREATE PROCEDURE ShowSuppliers (   

@txt varchar(50),   

@Name varchar (50) output,   

@Company varchar (50) output,   

@Country varchar (50) output   

)   

AS   

Select @Name = ContactName, @Company = CompanyName,    

@Country = Country from Suppliers Where Country like "%" + @txt + "%"   

GO 

Here we’ve created a slightly more elaborate Stored Procedure to illustrate the ExecuteQuery method. As you can see, this not only contains our initial search word variable – @txt, but we could now obtain several output values.

// ... Database Connection / Command here like above   

   

SqlParameter Param = objCommand.Parameters.Add("@txt",    

SqlDbType.VarChar, 50);   

Param.Direction = ParameterDirection.Input;   

Param.Value = "US";   

   

Param = objCommand.Parameters.Add("@Name", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

Param = objCommand.Parameters.Add("@Company", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

Param = objCommand.Parameters.Add("@Country", SqlDbType.VarChar,50);   

Param.Direction = ParameterDirection.Output;   

   

objCommand.ExecuteNonQuery();   

   

Response.Write (objCommand.Parameters["@Name"]   

.Value.ToString() + "<BR>");   

Response.Write (objCommand.Parameters["@Company"]   

.Value.ToString() + "<BR>");   

Response.Write (objCommand.Parameters["@Country"]   

.Value.ToString() + "<BR>");

The only thing here that’s dissimilar from our last Sproc example is the fact that we’ve just added a new Parameter Direction to reflect the values we wish to retrieve. In turn, we Response.Write them in the way shown here. For added information on this and working with Stored Procedures with return values, refer to the .NET documentation.

But now let’s imagine you want to display data in a datagrid using the datareader, rather than using the while loop and formatting a table through HTML. But in this case, you don’t require any of the DataGrid’s common properties, like paging. How would this be accomplished? In a similar way to the DataReader example code at the end of the last section. Everything remains the same, except that after you run the ExecuteReader() method, you simply bind your DataGrid’s datasource to the Datareader as shown.

MyDataGrid.DataSource = objDataReader;   

   

MyDataGrid.DataBind();

Well, what do you think? A lot of cool methods, huh? All this is not that far removed from classic ASP in its techniques and order of usage. But it obvious how many more options you have to work with and quickly display your data.

Aside from binding our Datagrid, it’s just as easy to bind other server controls. For instance, to bind a DropDownList control, you obviously change your DataGrid Control to a DropDownList, though the Datasource and Binding techniques are the same. The only difference is dependent on the control you use, as they all have their own unique properties that can be assigned. How do we make our DropDownList display our query results? Right after your ExecuteReader method, specify its DataTextField – the value to be displayed, and the DataValueField – the value to be passed when an item is selected. Here goes:

// ... our Data Access was here. Then we assign    

our new control's properties   

   

MyDropList.DataSource = objDataReader;   

   

MyDropList.DataTextField = "Country";   

MyDropList.DataValueField = "Country";   

   

MyDropList.DataBind();   

   

//And our control placed in our runat="server"    

form tags on our page   

   

<ASP:DropDownList id="MyDropList" runat="server" />

Ultimately you should, at all times strive to utilize the least overhead when accessing data. In this instance, the DataReader is clearly the best choice. Be that as it may, in the next section we’ll explore some more involved data access methodologies with which you can present your data.

The DataSet

The DataSet can be thought of as an in-memory storage container for all your data. It allows us far more capability and power over the manipulation and presentation of our data, even extending to XML. Here, the DataAdapter acts as the intermediary object responsible for joining our database with our DataSet, as the Command Object is with the DataReader.

Unlike the DataReader object, the DataAdapter/DataSet not only gives you the ability to work with disconnected data stores, but can also be used for common editing functions such as inserting, deleting, and updating your data, and even setting transactional logic.

Working with DataSet

In this example, we’ll demonstrate a common end result when working with a DataAdapter and DataSet: the DataGrid server control for sole data presentation.

We already know how to open a connection to our database. However, as we’re going to deal with our DataSet, the order of events changes at this point. Recall us using the Command Object to query the database? When working with Datasets, we would use the DataAdapter to do the querying, and the Fill() method to populate our DataSet with results:

SqlDataAdapter objDataAdapter = new SqlDataAdapter ("Select     

CompanyName, ContactName, City, Country, Region from     

Suppliers", objConnect);    

    

DataSet objDS = new DataSet();    

    

objDataAdapter.Fill (objDS);

What’s going on here? Well, we use the SqlDataAdapter to execute the SQL statement, and upon this action it returns data to fill our DataSet. Once our DataSet object is created, it in turn exposes the Datatable, and within it a collection of rows and columns that we’ll examine later on. Incidentally, you could, if you so chose, name the newly create Datatable, like so: objDataAdapter.Fill (objDS,"MyTable"); Note: For an OleDB database use OleDbDataAdapter.

The most common method for exposing data upon the creation of a disconnected data store would be the Fill() method, which we’ve mentioned, and will demonstrate in just a moment. At this point you may be wondering why the DataReader has been explored in such detail. Well, here we’re focusing on data access and presentation, and the DataReader is more commonly used for these purposes. The DataSet, which has roughly an equivalent number of methods, is geared more towards data management. We momentarily dealt with its Fill() method, as this is what enabled us to retrieve data easily. Additional DataSet methodology would involve complex methods for data manipulation. I would refer you to the .NET documentation at this point, since we won’t cover this information here.

The DataGrid Control within .NET is probably the most commonly used control, as it offers paging, filtering and sorting. This is the reason for the common DataSet / Datagrid relationship. The Datareader, being a forward/read-only object, cannot support these conditions unless you cleverly program it to do so.

Displaying the Data

Now that we’ve discussed the chosen methods of data retrieval, we have to look at how we’ll display it. We can do this by binding our DataSet to the DataGrid. We assign the DataGrid’s DataSource property to our DataSet, and then bind it to view our results:

MyDataGrid.DataSource = objDS    

MyDataGrid.DataBind(); 

We’ve learned that the DataSet itself is capable of holding multiple tables of data, each being a DataTable. From this point you could further manipulate your data before you bind it with the DataTable object, as we’ll illustrate further on.

What’s more, by using a DataView we can create different views on our Dataset’s DataTable, which enables us to filter or sort the data. To do this, you’d assign your DataGrid’s Datasource property to the DataView, then bind it.

DataView objView = objDS.Tables[0].DefaultView;    

    

objView.RowFilter = "Country like '%US%'";

or

objView.Sort = "Country asc";    

    

MyDataGrid.DataSource = objView;    

    

MyDataGrid.DataBind();

The results would then reflect the filtering or sorting you’ve established. For instance, you could implement this by pulling in a value from a dropdown list box that specifies what you wish to filter out or sort by.

Also keep in mind that within your .NET page all server controls are placed within runat="server" form tags. Here’s the entire code:

<script runat="server" language="C#">    

    

void Page_Load(Object Source,EventArgs E) {    

    

SqlConnection objConnect = new SqlConnection     

("server=(local);uid=sa;pwd=;database=Northwind;");     

    

objConnect.Open();    

    

SqlDataAdapter objDataAdapter = new SqlDataAdapter     

("Select CompanyName, ContactName, City, Country, Region from     

Suppliers", objConnect);    

    

DataSet objDS = new DataSet();    

    

objDataAdapter.Fill (objDS);    

    

MyDataGrid.DataSource = objDS;    

    

//DataView objView = objDS.Tables[0].DefaultView;    

//objView.RowFilter = "Country like '%US%'";    

//MyDataGrid.DataSource = objView;    

    

MyDataGrid.DataBind();    

    

objDataAdapter.Dispose();     

objDataAdapter = null;     

objDS.Dispose();    

objDS = null;    

objConnect.Close();    

objConnect = null;    

    

}    

    

</script>    

    

<form runat="server">    

<ASP:DataGrid id="MyDataGrid" runat="server" />    

</form> 

Recall in the last section how I quickly illustrated binding to a DropDownList? Well the same process could be applied to the DataSet, except that in this instance it would occur after the Fill method has been executed (as opposed to the ExecuteReader), and each would be assigned a different datasource.

Before we conclude, let’s revisit our DataTable for a moment. If you wanted more control over your data you could set up the DataTable from your dataset, and loop through it just like you did with the DataReader. Therefore, after using the Fill() method explained above, you can loop through your DataTable DataRows with something like this:

DataTable dsReader = objDS.Tables[0];     

foreach(DataRow row in dsReader.Rows) {    

    

Response.Write (row[0] + "<BR>");    

    

}

Conclusion

In summary, this introductory tutorial should’ve whet your appetite, and has hopefully encouraged you to jump right in and create an application or two. In addition, be sure to read the .NET documentation and QuickStart tutorials for more information on everything we’ve examined, and more so on what we’ve referred to in passing (including reading XML with the DataReader, implementing transactions, and inserting, editing and updating your data with a DataSet).

You should now understand the ease of database access and data presentation that’s available within ADO.NET, and have a feel for the further directions these objects, notably the DataSet, can take. I hope you now have a firmer grasp on the power of .NET, with more than enough information to get you up and running.

Until next time, happy .NETing!

Frequently Asked Questions about ADO.NET