Are you familiar with using the Command object in ADO? If so, migrating
to ADO.NET shouldn't be as much of a headache as you might
think. Here I discuss a couple of the methods available using ADO.NET's SqlDataReader and
SqlCommand objects that map well to coding practices commonly utilized in
ADO.
The basics for ADO's Command object are essentially the learning blocks for
ADO.NET. Here is a rough outline of the steps used in creating and
executing an ADO command object or an ADO.NET SqlCommand object:
1)
You must create and open a connection
2) You must instantiate the command object
3) You must specify the command type
4) You must specify the command text
5) You must append parameters to the command object (if necessary)
6)
You must execute the command
If you're not so familiar with the Command object of old, here is a piece of
code to refresh your mind. The example below depicts a Command object
executing a stored procedure which takes a single parameter.
ADO Command object - VB 6 example
' Set the CommandText as a parameterized SQL query.
objCmd.CommandText =
"sp_GetCustomerOrders"
objCmd.CommandType =
adCmdStoredProc
' Create new parameter for CustomerID. Initial value is ABCDE.
Set objParm1 = objCmd.CreateParameter("CustId", adChar, _
adParamInput, 5, "ABCDE")
objCmd.Parameters.Append objParm1
' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
' Execute
Set objRs = objCmd.Execute
' Process data and clean up
|
Of course, there are several ways to use the Command object, but the above
example is one that is widely familiar. So lets address some of the
similarities between the ADO Command object and ADO.NET. Just as you
append parameters to the ADO Command object, so too must you do this in
ADO.NET. The syntax is a bit different, but the idea is the same. Here is a
sample:
|
ADO.NET/C# - Adding parameters to the SqlCommand object
//Create a SqlDataReader - a read only, forward only cursor
SqlDataReader DR;
//Create the SqlCommand
SqlCommand cmd = new SqlCommand(spName,GetConnection());
//Set the Command Type
cmd.CommandType =
CommandType.StoredProcedure;
//parameters are the stored procedure parameter name(s) and the value(s)
SqlParameter parm1 =
new SqlParameter("@nCustID",nCustID);
cmd.Parameters.Add(parm1);
//Open the connection
cmd.Connection.Open();
//Execute the command and close the connection
DR =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
//Process data and clean up
|
As you
can see, the methods are not too different. And as the above code example shows,
you have a higher level of control over when certain events should occur
- i.e. (When the connection is actually opened).
Another common task in ADO programming is creating an output
parameter. In ADO, you must specify the direction of the
parameter. In ADO.NET it is very much the same. Take a look at the
code below:
|
ADO.NET/C# - Creating an output parameter
//Create a SqlDataReader - a read only, forward only cursor
SqlDataReader DR;
>
//Create the SqlCommand
SqlCommand cmd = new SqlCommand(spName,GetConnection());
//Set the Command Type
cmd.CommandType = CommandType.StoredProcedure;
//Create the output parameter
SqlParameter parm1 = new SqlParameter("RETURN_VALUE", SqlDbType.Int, 4);
//Specify the parameter's direction
parm1.Direction =
ParameterDirection.ReturnValue;
//Add the parameter
cmd.Parameters.Add(parm1);
>
//Create second parameter
>
SqlParameter parm2 = new SqlParameter("@nCustID", SqlDbType.Int, 4);
>
//Add the parameter
cmd.Parameters.Add(parm2);
>
//Open the connection
cmd.Connection.Open();
>
//Execute the command
> DR =
cmd.ExecuteReader();
//Get return value from output parameter
string sReturnValue =
parm1.Value.ToString();
>
//Process data and clean up
|
One thing to keep in mind, in .NET, a SqlDataReader uses a read
only, forward only cursor. If you desire more functionality with the
cursor type, a DataSet might be the next best option. Another
important tidbit, you may only have one open SqlDataReader at a time
per a single connection. Keep this in mind during the design
of your application.
I hope these code samples will help as an introductory
to moving forward in adopting ADO.NET. Please look for my
next article. I'll be looking at some additional new features
in
ADO.NET.
|
|