ADO.NET database access made easy

ADO.NET and ADO are quite different in many areas, such as connection classes, commands, and data readers. But don't get them confused. This introduction to ADO.NET should help set you off on the right foot.

Although ADO.NET and ADO share some common concepts, they are different enough in many areas to give you fits when you first start working with the new .NET database access libraries. The first step in understanding how to use ADO.NET lies in understanding what you’ll find in each of its constituent namespaces:
  • System.Data.SqlClient contains the classes needed to connect to a SQL Server data source.
  • System.Data.OleDb is used for connecting to OLE-DB data sources.
  • System.Data.Common holds common classes that serve as bases for provider-specific classes.
  • System.Data contains a large set of provider-independent classes useful for high-level access to data.
  • System.Data.SqlTypes provides classes useful for converting .NET data types to and from SQL Server native data types.

Which connection class should you use?
As you just saw, there are two sets of connection classes in .NET, those found in the SqlClient and OleDb namespaces. What’s the difference? The SqlClient classes use the native SQL Server drivers to access a database, while the OleDb classes use the generic OLE-DB interface. When using SQL Server as a back end, the SqlClient classes will have a performance advantage over their more generalized OLE-DB cousins, so you should use the former for connecting to SQL Server.

For simplicity’s sake, I’ll assume you will be using SQL Server for the remainder of this article. However, the classes I’ll be discussing will also apply for an OLE-DB back end, so you’re welcome to stick around if you have a different database back end. You’ll need to substitute equivalent OleDb classes for the SqlClient classes, and you’ll likely need to tinker with the connection string, but the sample code should work for you as well.

Using connections in ADO.NET
Like ADO before it, ADO.NET depends on connection objects to represent open data sources. However, connections in ADO.NET are different from those you’ll find in ADO. I’ve already touched on one significant difference: There are specialized connection classes for certain data sources, which allow vendors to create specialized connection classes that use their databases' native interfaces to provide extra functionality or a speed boost over the OleDb classes. I don’t have space here to cover all the differences, but check out the MSDN Web site for more information.

The .NET Framework’s specialized SQL Server connection class uses System.Data.SqlClient.SqlConnection, so to connect to a SQL Server database, you’d create an instance of SqlConnection. When creating a connection object, you have the option of specifying the connection string when you call the constructor, or you can stick to the old methods of setting the connection string via the ConnectionString property. You cannot pass the string to the Open method, however, so don’t go trying that.

Getting the connection string right
Getting a connection string put together for your data provider can sometimes be the most challenging aspect of opening a connection. If you get into trouble, a good starting point is to use the Server Explorer in VS.NET to build the connection string, and then copy and paste it into your code.

To connect to the NWIND database on a SQL Server named SomeServer, I’d use the following code:
SqlConnection cn = new SqlConnection("Data Source=SomeServer; Initial Catalog=NWIND; Integrated Security=true;");


Note that if your SQL Server does not use domain security, you’ll need to specify user ID and password elements, and remove the Integrated Security statement from the connection string.

Working with commands
After opening a connection, you can use one of the provider-specific command objects to execute commands on the connection. Commands are abstract representations of the SQL statements you use to work with data in a database. I don’t have space here for an introduction to SQL, but I will recommend the SQL basics series if you’re new to SQL.

SqlCommand is the SQL Server specific command object, and you can specify a T-SQL statement, or the name of a stored procedure, for the command to represent by either passing it as a constructor parameter or using the CommandText property. Extending my above example, if you wanted to retrieve the names of all the employees living in the US from the NWIND database’s Employees table, you’d use the following code to create your command:
SqlCommand cmd = new SqlCommand("SELECT * FROM Employees WHERE Country = 'USA';", cn);
cmd.CommandType = CommandType.Text;


What kind of command is this?
A good tip from ADO that still applies to ADO.NET is to always specify the type of the command, using the CommandType property, assuming that you know it. The System.Data.CommandType enumeration provides a list of all available commands.

You can also execute action query statements using a command object. Simply specify the SQL statement in the constructor as you would a SELECT statement, and then invoke the ExecuteNonQuery method. For example, to remove the employee with an EmployeeID of 9, you’d use the following code:
SqlCommand del = new SqlCommand(“DELETE * FROM Employees WHERE EmployeeID = 9”);


To execute a scalar command—a command that returns a single value rather than a row of data—you’d use the ExecuteScalar method instead. If you wanted to know the number of records left in Employees after deleting the record in the last example, you could use the following code:
SqlCommand count = new SqlCommand(“SELECT count(*) as EmpCount FROM Employees;”);
int icount = (int) count.ExecuteScalar();

Retrieving data: Data readers
You have connections and commands, but here’s where the similarities with ADO largely end. There is no RecordSet object in ADO.NET. Instead, you have several ways of obtaining data. The simplest is to use a data reader object.

Data readers provide forward-only, stream-based access to the rows returned by a SQL statement or a stored procedure. Both SqlClient and OleDbClient have their own, private, implementations of data reader objects: SqlDataReader and OleDbDataReader, respectively. These two reader classes do not have a common class ancestor above System.Object, so they are not compatible with one another.

Data readers are very fast, but they aren’t very structured. You retrieve data from them using the ordinal number of the column in the data returned by the command object. They aren’t type aware either, so you need to specify the type of data you are retrieving by calling a particular GetXXX method. For example, to retrieve the name (a string) and date of birth (DateTime) from all employees returned in the above example, you’d write the following:
SqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine("Name: {0}, {1} DOB: {2}",
rdr.GetString(1), rdr.GetString(2), rdr.GetDateTime(5));

Getting fancy: Retrieving XML data
A rather neat feature of ADO.NET is the ability to work with data from a database as if it were an XML stream. The ExecuteXmlReader method returns a System.Xml.XmlReader object you can use to read the data returned from the command as if it were an XML document. I won’t go into the details of using XmlReader here, but I will suggest that you read “Learn to read and write XML with .NET’s XML classes” if you are unfamiliar with XmlReader.

More to come…
From my explanations, you should now have a working understanding of ADO.NET’s low-level data access classes and how to use them to manipulate data from a data source. In a future article, I’ll introduce the higher-level, provider-agnostic denizens of System.Data, and show you how to use them in a connected application.


Editor's Picks