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