A Quick Comparison of ADO and ADO.NET - Part I
page 4 of 5
by Devarticles.com
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30126/ 127

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.


View Entire Article

User Comments

Title: dasds   
Name: adsad
Date: 2012-05-17 1:23:37 AM
Comment:
asdsadsada
Title: Comparison of ADO ADO.NET   
Name: Anand Mehta
Date: 2012-04-08 12:44:36 AM
Comment:
ADO works with connected data. This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. This is barring, of course, you programming special routines to pull all your data into temporary tables.

ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for Web applications. It's also decent for desktop applications.

ADO has one main object that is used to reference data, called the Recordset object. This object basically gives you a single table view of your data, although you can join tables to create a new set of records. With ADO.NET, you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders, accessing/updating the data in each related table individually.

ADO allows you to create client-side cursors only, whereas ADO.NET gives you the choice of either using client-side or server-side cursors. In ADO.NET, classes actually handle the work of cursors. This allows the developer to decide which is best. For Internet development, this is crucial in creating efficient applications.

Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.
Title: Comment on this article   
Name: Bharat Bhushan
Date: 2010-05-07 6:57:02 AM
Comment:
Thank You for this article on Differences between ado and ado.net.

Thanks Dude

Bharat Bhushan Sharma (Delhi)
Title: Excellent   
Name: Abraham Mathew
Date: 2007-06-04 5:33:52 AM
Comment:
Excellent article . It will gives us a short description of all the aspects of ADO .Net

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-05-22 7:05:49 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search