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.
