An introduction to using the ADO.NET - SqlCommand Object

,

An

introduction to using the ADO.NET - SqlCommand Object

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.

Rate

4 (1)

Share

Share

Rate

4 (1)