Home arrow ADO.NET arrow Page 3 - A Practical Comparison of ADO and ADO.NET
ADO.NET

A Practical Comparison of ADO and ADO.NET


Since "classic" ASP's humble beginnings, ADO data access classes and methods have been re-written to form ADO.NET. In this article Joe looks at the differences (in terms of classes, methods, and calling conventions) that exist between ADO and ADO.NET. He provides several practical examples that show us how to evolve from using ADO with ASP to using ADO.NET with C# and ASP.NET.

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 72
March 01, 2002
TABLE OF CONTENTS:
  1. · A Practical Comparison of ADO and ADO.NET
  2. · Old versus new: ADO versus ASP.NET
  3. · Connecting to a database
  4. · Working with recordsets/rowsets
  5. · Calling stored procedures
  6. · Retrieving records as XML
  7. · Conclusion

print this article
SEARCH DEVARTICLES

A Practical Comparison of ADO and ADO.NET - Connecting to a database
(Page 3 of 7 )

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:
  • CommandType.Text: Should be used when you're passing in a plain text TSQL statement, such as a select, insert, or update command.
  • 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.
  • 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:
  • ExecuteNonQuery: Runs the query against the database, but returns no results or output from that query.
  • ExecuteScalar: Runs the query against the database, and returns only one value. ExecuteScalar is good for when you perform select * queries.
  • 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.
blog comments powered by Disqus
ADO.NET ARTICLES

- Datasets in Microsoft.Net
- Latest Developments in the .Net World
- Introduction to .NET
- Automatic Generation of Single Table SQL Sta...
- Data Access in .NET using C#: Part 1
- All You Need To Know About ADO.NET: Part 2/2
- All You Need To Know About ADO.NET: Part 1/2
- Easing Transition From ASP and ADO to ASP.NE...
- A Practical Comparison of ADO and ADO.NET

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials