AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=128&pId=-1
A Quick Comparison of ADO and ADO.NET - Part I
page
by Devarticles.com
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30209/ 99

Introduction

Whether you develop for .NET not, you have to admit that Microsoft have come along way in the last 3 to 5 years. Back in the late nineties Bill Gates had a vision of using the Internet to facilitate distributed computing, and today web services and the .NET framework have made this vision an exciting reality.

In terms of the .NET framework, the way we access our data has also changed. Pretty much everything in .NET uses XML as its underlying data structure, and even records from database are represented internally as well formed XML, which is then converted to the data type required by the client application.

Since "classic" ASP's humble beginnings, ADO data access classes and methods have been re-written. When Microsoft decided to embark on its multi billion dollar .NET adventure, they also built a new version of ActiveX Data Objects (ADO) called ADO.NET.

Today we're going to take a look at some of the fundamental differences that exist between ADO and ADO.NET in a practical sense. We will be using ASP, ASP.NET and C# to both instantiate ADO and ADO.NET classes, seeing how the calling conventions to each of these classes differ.

To test the samples explained in this article, you should have the Microsoft .NET framework installed on your machine, which you can download here. We won't be using Visual Studio.NET to create web forms, so notepad or any other plain text editor is fine.

Old versus New: ADO versus ASP.NET

In ADO 2.x we used one common set of classes to perform database connections, retrieve records, update data, execute stored procedures, etc. They were the ADO command, connection, error, field, parameter, property, record, recordset and stream classes.

In ADO.NET we have two sets of classes that we can use to access our data, depending on how that data is stored. Each of these classes is stored in a separate namespaces that clearly identifies them. To access data stored in an SQL Server database, we would use the classes that exist in the System.Data.SqlClient namespace. To access data stored in any other OLEDB accessible repository (such as Excel, MySQL, Access, and even SQL Server) we use the classes available under the System.Data.OleDb namespace.

[Note] If you're wondering why you can use both SQL and OleDb providers to access an SQL Server database, it's because the classes under the SqlClient namespace have been optimised to do so, where as the classed under the OleDb namespace are more generic, and therefore their performance isn't as good when working with SQL server. [End Note]

Here's a list of the namespaces that we use to access our data with ADO.NET:

  1. System.Data: The root class of the entire ADO.NET hierarchy, this namespace stores the generic data access classes and variables that are used by both the SqlClient and OleDb classes.
  2. System.Data.Common: Contains classes that data providers can use as base classes when implementing their own data access routines.
  3. System.Data.SqlClient: Contains the classes that we instantiate to access data stored in SQL Server databases.
  4. System.Data.SqlTypes: Data types including enumerations and constants that can be used when calling certain functions from the SqlClient namespace.
  5. System.Data.OleDb: Contains the classes that we instantiate to access data stored in OleDb compatible data repositories.

ADO.Net also defines several shared classes that are common to both the SqlClient and OleDb namespaces (i.e. can be used when we're working with either SQL server or any other OleDb provider). In ADO 2.x we used the recordset, command and stream classes to access data, but with ADO.NET we now have access to several new data access classes. These classes can be used to store, access, manipulate and relate data from one source to another. We will take a look at these later.

Let's now look at some examples of how we can access and manipulate our data using ASP/ADO and ASP.NET/ADO.NET. In the examples that follow I will be working with SQL server only.

Connecting to a Database

With ADO 2.x we instantiate a connection object from which we then call its open method, passing in a connection string. In ADO.NET the same principles apply, instead we instantiate an SqlConnection object, which exists under the System.Data.SqlClient namespace.

Here's how we connect to an SQL Server database on the local machine using classic ASP and ADO 2.6:

<%
dim objConn
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; UId=sa; Pwd="
%>

Here's an example that does the same thing, only this time we're using ASP.NET and ADO.NET:

<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>

<script language="c#" runat="server">
public void Page_Load(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection("Server=(local); Database=Pubs; UId=sa; Pwd=");

objConn.Open();
}
</script

In our ADO.NET example above, we've used the SqlClient namespace. We could just have easily used the OleDb namespace and OleDb connection class to connect to our SQL Server database, because the SQLOLEDB provide is OleDb compatible.

The great thing about ADO.NET is that in 99% of the cases, you can simply copy and paste your old ADO connection strings into your .NET applications and they should work fine.

Sending Commands to a Database

As with ADO 2.x, it's extremely easy to execute a command against a database using ADO.NET. If we wanted to update a record in the authors' table of the pubs database using ADO 2.6, we would do something like this:

<%

dim objConn
dim objComm

set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")

objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=Pubs; UId=sa; Pwd="

objComm.ActiveConnection = objConn
objComm.CommandType = 1 'adCmdText
objComm.CommandText = "UPDATE authors SET au_lname = 'Smith' WHERE au_fname = 'Burt'"

objComm.Execute

%>

In ADO.NET we instantiate the SqlCommand class, which resides under the System.Data.SqlClient namespace. We pass the SQL command we want to execute along with a connection object to its constructor, like this:

<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>

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

public void Page_Load(object sender, EventArgs e)
{
SqlConnection objConn = new SqlConnection("Server=(local); Database=Pubs; UId=sa; Pwd=");
objConn.Open();

string strQuery = "UPDATE authors SET au_lname = 'Smith' WHERE au_fname = 'Burt'";

SqlCommand objCmd = new SqlCommand(strQuery, objConn);

Just like with ADO 2.x, we can specify the type of command that we're executing. We specify the value using the CommandType enumeration. The three possible choices are:

  1. CommandType.Text: Should be used when you're passing in a plain text TSQL statement, such as a select, insert, or update command.
  2. CommandType.TableDirect: Should be used when you want all records from a table returned. You would specify only the name of the table as the command. This type can only be used with the OLEDB command object, OleDbCommand.
  3. CommandType.StoredProcedure: Should be used when you're passing in the name of a stored procedure to execute. We will look at stored procedures later.

In our example we're executing a direct query command, so we specify the Text type, like this:

objCmd.CommandType = CommandType.Text;

One of the many things that are different with ADO.NET is that we can specify how we want our query executed. Both the SqlCommand and OleDbCommand classes expose the following methods:

  1. ExecuteNonQuery: Runs the query against the database, but returns no results or output from that query.
  2. ExecuteScalar: Runs the query against the database, and returns only one value. ExecuteScalar is good for when you perform select * queries.
  3. ExecuteReader: Runs the query against the database and returns an SqlDataReader object, which is similar to ASP's recordset object.

Because our command updates a record and doesn't return anything, we use the ExecuteNonQuery method, like this:

objCmd.ExecuteNonQuery();
}

</script>

In ADO 2.6, we could run a select * query and use the "fields" collection of the returned recordset to get the number returned:

dim objRS
dim intAuthorCount

set objRS = Server.CreateObject("ADODB.Recordset")

objRS.ActiveConnection = objConn
objRS.Open "SELECT COUNT(*) AS authCount FROM authors"

intAuthorCount = objRS.Fields("authCount").value

In ADO.NET, we would do exactly the same thing using the ExecuteScalar method to return a single value, like this:

string strQuery = "SELECT COUNT(*) As authCount FROM authors";

SqlCommand objCmd = new SqlCommand(strQuery, objConn);
objCmd.CommandType = CommandType.Text;

int intAuthorCount = (int)objCmd.ExecuteScalar();

Sometimes it's good to just return an entire table. In ASP using ADO 2.6, we can accomplish this by simply specifying the table name for the command object and changing the command type to adCmdTable, or 2:

objComm.ActiveConnection = objConn
objComm.CommandType = 2 'adCmdTable
objComm.CommandText = "authors"

objComm.Execute

If we want to retrieve a table in the same way using ADO.NET, then we have to use a data source that supports a managed OLEDB provider. The SqlCommand object doesn't support the CommandType.TableDirect command type, so we have to use the System.Data.OleDb namespace and classes. Remember that SQL Server supports the SQLOLEDB provider, so we add this to our connection string as well. Here's how we would do it in ASP.NET:

<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.OleDb" %>

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

public void Page_Load(object sender, EventArgs e)
{
OleDbConnection objConn = new OleDbConnection("Provider=SQLOLEDB; Server=(local); Database=Pubs; UId=sa; Pwd=");
objConn.Open();

string strQuery = "authors";

OleDbCommand objCmd = new OleDbCommand(strQuery, objConn);
objCmd.CommandType = CommandType.TableDirect;

OleDbDataReader objDR = objCmd.ExecuteReader();
}

</script>

As you can see in the example above, we use the ExecuteReader method to return an OleDbDataReader object, which can be used to display the contents of the table.

Credits

This article was contributed by the team at http://www.devarticles.com.devArticles provides ASP, PHP and .NET articles, tutorials, reviews, interviews and FREE eBooks. If you're after some serious programming tutorials then...

Visit
http://www.devarticles.com now...OR, for free eBooks
visit
http://www.devarticles.com/ebooks.php


Product Spotlight
Product Spotlight 

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-25 6:09:06 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search