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.
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.
To read the rest of this article, and access thousands of other articles, we ask you to register on the site and subscribe to our newsletters.
We ask you to register on the site and subscribe to our newsletters. Subscribing to our newsletters gets you:
We ask that you give the newsletter a try for a week. Over 200,000 SQL Server Professionals a day find it entertaining and useful. If not, you are welcome to unsubscribe at anytime.
Steve Jones Editor, SQLServerCentral.com
