Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

An introduction to using the ADO.NET - SqlCommand Object

By Jon Winer, (first published: 2001/12/11)

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);

//Open the connection

//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

> //Create second parameter
> SqlParameter parm2 = new SqlParameter("@nCustID", SqlDbType.Int, 4);

> //Add the parameter

> //Open the connection

> //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.

Total article views: 18402 | Views in the last 30 days: 7
Related Articles

Reusing SQLCommand

How to use 1 SQLCommand for many set of parameter values


Introduction to ADO - The Command Object

The third article in a four part series, this week Andy shows how to use the command object to work ...


SQLCommand Question

Multiple commands with one connection


Adding Parameters to Stored Procedure

I have the parameters as an ArrayList now I want to Add them to my sqlCommand


Command text was not set for the command object

Command text was not set for the command object

sql server 7    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones