Tutorials » A Practical Comparison of ADO and ADO.NET

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 a 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" %>

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" %>

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" %>

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.


	1 2 3 4 5 6 7

Print this Page

E-mail this Page

DevGuru

Tutorials » A Practical Comparison of ADO and ADO.NET