What Is ADO.NET?
I live in New York state about an hour north of Manhattan. For this I consider myself lucky. Within a short drive I am able to enjoy all the wonders and experiences that New York City has to offer. Some of the greatest attractions in the world are in NYC, including some of the best restaurants, museums, and Broadway musicals and plays.
More often than not, when I want to enjoy the city, my wife and I will go to Mulberry Street in Little Italy to the world famous La Mela restaurant. They have gnocchi and tortellini to die for there. When I get a hankering for that delicious cuisine, we jump in the car and hit the highway.
We get down to the Hudson River and, thanks to the wonders of modern engineering, simply pay a toll and cross the George Washington Bridge to enter Manhattan and proceed down the island to our destination.
Could you imagine what I'd need to go through to get to La Mela if we had no bridge to Manhattan Island? It would make that trip a nightmare, and I would probably never go and enjoy the La Mela or anything else in NYC. It would take all that dynamic activity out of my life.
ADO.NET is like the George Washington Bridge in these previous paragraphs. It provides a method to achieve all kinds of dynamic productivity at a new level. It is the unsung hero of the .NET Framework, which makes all the rich information and data in our databases accessible to ASP.NET pages.
Just like the George Washington Bridge has different components the towers, trusses, cables, anchors, and roadway decks that make crossing the gap between New Jersey and New York possible ADO.NET has components and players.
ADO.NET has the Connection, which is like the towers that support the entire bridge, and the Command, which is like the giant cables that support the bridge from the towers. The DataAdapter connects the cables to the roadway deck sections. The DataReader, DataTable, and DataView are like roadway decking, and the DataSet is the collection of DataTable roadway deck sections that make up the entire span.
All these different things may seem confusing to you now, but as you go through them, you will see how they all play their little parts to help you cross the gap, river, or chasm between your ASP.NET pages and your database.
The first thing to note about ADO.NET and how it deals with databases is that it splits them into two categories: Microsoft SQL Server and everything else. Pretty clear and easy-to-understand delineation, huh? The reason for this is that this allows ADO.NET to deal with SQL Server through a different mechanism than the OLE DB or ODBC providers that have been used in the past. This provides a highly optimized bridge for Microsoft SQL Server. The other databases, such as Microsoft Access and Oracle, use the OLE DB provider.
Table 10.1 lists namespaces and their necessary objects.
| Namespace | Description |
|---|---|
| System.Data.SqlClient | This is the Microsoft SQL Server data provider namespace. You need to import this namespace if you will be communicating with Microsoft SQL Server. |
| System.Data.OleDb | This is the OLE DB .NET data provider. You need to import this namespace if you will be communicating with an OLE DB database. |
| System.Data | This namespace contains the classes that build the ADO.NET architecture. These objects allow you to manage data from multiple data sources with the centerpiece being the ADO.NET DataSet. |
When using ADO.NET to connect and communicate with a database, you will generally use two out of the three namespaces in Table 10.1 that contain the necessary objects. You will always use the System.Data namespace, but you will use either one or the other of the remaining namespaces, depending on which type of database you are using: SQL Server or the others.
Table 10.2 describes each namespace and the important objects that they contain for connecting and manipulating data in databases.
| Class | Description |
|---|---|
| SQLConnection | This represents an open connection to your SQL Server database. This is the equivalent of a network connection to a server. |
| SQLCommand | This represents a SQL statement or stored procedure that you want to execute against your SQL Server database. |
| SQLDataReader | Provides a means for reading rows of data from your SQL Server in a "forward-only" situation. This means you can iterate through the rows of the DataReader only forward, and you cannot return to the beginning of the Reader. It is the equivalent of the previous ADO version's Recordset object, with a cursor type of forward-only. |
| SQLDataAdapter | Represents a set of data commands and a data connection that are used to manipulate DataSets and update your SQL Server database. |
The objects provide all the towers, cables, anchors, and trusses for a bridge to your SQL Server database. Parallel objects are also available for OLE DB data sources as well. Table 10.3 gives you a look at them.
| Class | Description |
|---|---|
| OleDbConnection | This represents an open connection to your OLE DB data source. This is the equivalent of a network connection to a server. |
| OleDbCommand | This represents a SQL statement or stored procedure that you want to execute against your OLE DB data source. |
| OleDbDataReader | Provides a means for reading rows of data from your OLE DB data source in a "forward-only" situation. This means you can iterate through the rows of the DataReader only forward, and you cannot return to the beginning of the Reader. It is the equivalent of the previous ADO version's Recordset object, with a cursor type of forward-only. |
| OleDbDataAdapter | Represents a set of data commands and a data connection that are used to manipulate DataSets and update your OLE DB data source. |
As you can see, these hardly differ from the SQL Server version of these objects, but they are necessary for connecting to databases other than Microsoft SQL Server.
Throughout the rest of this chapter I will show you the objects and examples for the SQL Server only. But, never fear. These two groups of objects are exactly the same with one exception: their names. So any place I use the SQL object, you would just use the OLE DB equivalent. I have also included OLE DB versions of all the examples for download on the New Riders Publications web site located at http://www.newriders.com.
One thing I will give you is a few examples of connection strings for the SQLConnection and OleDbConnection objects.
SQLConnection String Example
server=servername;user id=userid;password=password;database=databasename OleDbConnection String Example
Microsoft Access Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\accessdatabase.mdb; Note |
| There might be times when you don't know the full path to your Access database for instance, when you are hosting a website on a web server where you don't have administrative access. There is a property of the ASP.NET Page object called Server that has a method called MapPath() that enables you to programmatically map the path to your database. Imagine that the page you're working on is located in one directory level from the web application's root, and the access database is located in a directory called Database in the application's root. The MapPath() method takes a single string parameter, which is the path to the file or database from that current page's location. To bump out one directory level to the root directory, you use dot, dot, forward slash (../). This puts you in the root directory. Then you append the path to the database from there. So, the connection string would look like the following: Microsoft Access with Server.MapPath "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../_database/northwind.mdb"); Oracle 8i Provider=MSDAORA; Data Source=ORACLE8i7; User ID=OLEDB; Password=OLEDB |
This should provide you with enough basic information to connect to the most common databases. If you need additional information, I recommend referring to the .NET Framework SDK, because there are many ways to create connection strings, and the SDK gives many examples, especially with regard to the SQLConnection object.
Now look at the System.Data namespace and its pertinent objects in Table 10.4. You need to import this namespace whether you are using a SQL Server database or an OLE DB database.
| Class | Description |
|---|---|
| DataTable | This represents one table of in-memory data. |
| DataRelation | This object represents a parent/child relationship between two DataTable objects. |
| DataSet | Represents an in-memory cache of data. It consists of a collection of DataTable objects that can be related to each other with the DataRelation object. |
| DataView | This represents a databindable view of a DataTable that can be sorted, filtered, search, edited, and navigated. |
So in plain English, this is how the System.Data namespace works:
-
SQLConnnection or OleDBConnection connects to the database.
-
SQLCommand or OleDbCommand tells the database what data you're working with.
-
DataReader holds the data.
-
DataSet creates a framework for holding DataTables of data.
-
SQLDataAdapter or OleDbDataAdapter passes data in and out of the DataTables and databases.
-
DataView manipulates snapshots of data in our dataset.
As I said, you will use two out of the three namespaces mentioned, depending on what type of data source you are using. The following is the code necessary to make these namespaces available, and it should be placed immediately after the @page directive of your ASP.NET page. This code is identical for both Visual Basic.NET and C#.
SQL Server Databases
<%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.SQLClient"%>
Ole Db Databases
<%@ Import Namespace="System.Data"%> <%@ Import Namespace="System.Data.OleDb"%>
Now let's investigate the different ways you can use ADO.NET to manipulate and query databases.
| Top |
