An introduction to using the ADO.NET - SqlCommand Object

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.