Creating Commands in Visual Studio .NET | Microsoft ADO.NET (Core Reference) (PRO-Developer)

Questions That Should Be Asked More Frequently

Q. I called a stored procedure that returns a set of rows. Everything seems to work except that the output and return parameters are empty. Why is that?

A. You can think of a stored procedure as a function in your code. The function doesn't return a value until it has executed all of its code. If the stored procedure returns results and you haven't finished processing these results, the stored procedure hasn't really finished executing. Until you've closed the DataReader, the return and output parameters of your Command won't contain the values returned by your stored procedure.

Let's say we have the stored procedure

CREATE PROCEDURE RowsAndOutput (@OutputParam int OUTPUT) AS     SELECT @OutputParam = COUNT(*) FROM Customers     SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers

and we call it with the following code:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "{CALL RowsAndOutput(?)}" Dim cmd As New OleDbCommand(strSQL, cn) Dim param As OleDbParameter param = cmd.Parameters.Add("@OutputParam", OleDbType.Integer) param.Direction = ParameterDirection.Output Dim rdrCustomers As OleDbDataReader = cmd.ExecuteReader Console.WriteLine("After execution - " & CStr(param.Value)) Do While rdrCustomers.Read Loop Console.WriteLine("After reading rows - " & CStr(param.Value)) Do While rdrCustomers.NextResult() Loop Console.WriteLine("After reading all results - " &                    CStr(param.Value)) rdrCustomers.Close() Console.WriteLine("After closing DataReader - " & CStr(param.Value))

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                   "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); string strSQL = "{CALL RowsAndOutput(?)}"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbParameter param; param = cmd.Parameters.Add("@OutputParam", OleDbType.Integer); param.Direction = ParameterDirection.Output; OleDbDataReader rdrCustomers = cmd.ExecuteReader(); Console.WriteLine("After execution - " + (string) param.Value); while (rdrCustomers.Read()) {} Console.WriteLine("After reading rows - " + (string) param.Value); while (rdrCustomers.NextResult()) {} Console.WriteLine("After reading all results - " +                    (string) param.Value); rdrCustomers.Close(); Console.WriteLine("After closing DataReader - " +                    (string) param.Value);

Even though the stored procedure sets the value of the output parameter before running the query that returns rows from the Customers table, the value of the output parameter is not available until after the DataReader is closed.

Q. I saw a snippet of sample code in the MSDN documentation that set the value of parameters without setting the DbType or the .NET data provider-specific data type property. How does that work? Is using that type of code safe?
A. This is a rather impressive ADO.NET feature, but I've been hesitant to discuss it in much depth because, well, frankly, it scares me. That's probably just due to my background in technical support, but I'd rather explicitly set the data types for my parameters.

In the vast majority of applications, you know the data types of the parameters you're calling. Ad-hoc query tools are probably the only types of applications in which you might need to call a parameterized query or stored procedure without knowing the data types of the parameters involved.

Getting back to the feature, if you leave the data type properties of a Parameter uninitialized but set the Value property, the Parameter will automatically choose the appropriate data type to use. The following code successfully retrieves all customers whose CustomerID column begins with the letter A.

Visual Basic .NET
```
Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "SELECT CustomerID, CompanyName FROM Customers " & _          "WHERE CustomerID LIKE ?" Dim cmd As New OleDbCommand(strSQL, cn) cmd.Parameters.Add("@CustomerID", "A%") Dim rdr As OleDbDataReader = cmd.ExecuteReader Do While rdr.Read     Console.WriteLine(rdr(0)) Loop rdr.Close() cn.Close()
```
Visual C# .NET
```
string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "SELECT CustomerID FROM Customers WHERE CustomerID LIKE ?"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cmd.Parameters.Add("@CustomerID", "A%"); OleDbDataReader rdr = cmd.ExecuteReader(); while (rdr.Read())     Console.WriteLine(rdr[0]); rdr.Close(); cn.Close();
```
This query retrieves the desired rows, but you won't see the DbType, OleDbType, or Size properties of the OleDbParameter set to the expected values. The Parameter determines the appropriate values for these properties when you execute the query and only sets these properties internally. If you use the SQL Profiler tracing tool in SQL Server 2000, you'll see that the Command executed the following query:
```
exec sp_executesql      N'SELECT CustomerID FROM Customers WHERE CustomerID LIKE @P1',       N'@P1 nvarchar(2)', N'A%'
```
The Parameter assumed that the parameter should be a variable-length Unicode string of length 2. I've successfully called parameterized queries against SQL Server, Oracle, and Access databases using string-based and numeric parameters (such as currency) without encountering a problem.

This fact that this feature works so well really impressed me. My technical support background still makes me uneasy when I see a feature like this, however. It's not that I don't have confidence in the feature, it's just that I'd rather see developers set the data type and size properties explicitly.
Q. Why can't I call a stored procedure and successfully retrieve the value of the return parameter? I've been able to use output parameters successfully, but not a return parameter.

A. First, if you used the Command to create a DataReader, make sure you've closed the DataReader before trying to examine the contents of the return parameter. (See the question on page 145 for more on this process.)

The other day, a co-worker stopped by my office and said he'd been trying to retrieve the value of the return parameter for his stored procedure. He knew to close the DataReader first, but that wasn't the problem. His stored procedure looked something like this:

CREATE PROCEDURE GetReturnParameter (@CustomerID nchar(5)) AS SELECT OrderID, CustomerID, EmployeeID, OrderDate         FROM Orders WHERE CustomerID = @CustomerID RETURN @@ROWCOUNT

And his Visual Basic .NET code looked like this:

Dim cmd As New OleDbCommand("GetReturnParameter", cn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5) cmd.Parameters("@CustomerID").Value = "ALFKI" cmd.Parameters.Add("@RetVal", OleDbType.Integer) cmd.Parameters("@RetVal").Direction = ParameterDirection.ReturnValue

To make a long story short, his parameters were in the wrong order. It was a simple mistake that anyone could have made. The fact that he was setting the CommandType to StoredProcedure made the problem more difficult to discover, which is part of why I've never been a big fan of the CommandType property in ADO.NET or in ADO. The ADO.NET development team meant well by adding this feature, but I think developers are better off learning how to build the real query.

The appropriate CommandText in this case is

{? = CALL GetReturnParameter(?)}

The syntax is similar to calling a function in Visual Basic or C#. You want a parameter to store the value returned by the stored procedure. Here's the full code for calling the stored procedure:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "{? = CALL GetReturnParameter(?)}" Dim cmd As New OleDbCommand(strSQL, cn) cmd.Parameters.Add("@RetVal", OleDbType.Integer) cmd.Parameters("@RetVal").Direction = ParameterDirection.ReturnValue cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5) cmd.Parameters("@CustomerID").Value = "ALFKI" Dim rdr As OleDbDataReader = cmd.ExecuteReader Do While rdr.Read     Console.WriteLine("OrderID = " & rdr(0).ToString) Loop rdr.Close() Console.WriteLine(cmd.Parameters(0).Value.ToString & " orders")

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "{? = CALL GetReturnParameter(?)}"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cmd.Parameters.Add("@RetVal", OleDbType.Integer); cmd.Parameters["@RetVal"].Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5); cmd.Parameters["@CustomerID"].Value = "ALFKI"; OleDbDataReader rdr = cmd.ExecuteReader(); while (rdr.Read())     Console.WriteLine("OrderID = " + rdr[0].ToString()); rdr.Close(); Console.WriteLine(cmd.Parameters[0].Value.ToString() + " orders"); cn.Close();

Q. I'm trying to migrate code from ADO to ADO.NET. My old program used the Refresh method on ADO Parameters collection. Doesn't the ADO.NET ParameterCollection object have a Refresh method?

A. There is no Refresh method on the ADO.NET ParameterCollection object, at least not yet. As of the initial release, there is no way to use the ADO.NET object model to "auto-magically" supply parameter information for a generic parameterized query.

However, if you're calling a stored procedure, there is a solution. The CommandBuilder object, which we'll discuss later in Chapter 10, exposes a DeriveParameters method that can populate a Command object's Parameters collection if the Command calls a stored procedure. The following code snippet demonstrates how to use this functionality.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;" & _                         Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;" & _                         Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As New OleDbCommand("SalesByCategory", cn) cmd.CommandType = CommandType.StoredProcedure OleDbCommandBuilder.DeriveParameters(cmd) Dim param As OleDbParameter For Each param In cmd.Parameters     Console.WriteLine(param.ParameterName)     Console.WriteLine(vbTab & param.Direction.ToString())     Console.WriteLine(vbTab & param.OleDbType.ToString())     Console.WriteLine() Next param cn.Close()

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = new OleDbCommand("SalesByCategory", cn); cmd.CommandType = CommandType.StoredProcedure; OleDbCommandBuilder.DeriveParameters(cmd); foreach (OleDbParameter param in cmd.Parameters) {     Console.WriteLine(param.ParameterName);     Console.WriteLine("\t" + param.Direction.ToString());     Console.WriteLine("\t" + param.OleDbType.ToString());     Console.WriteLine(); } cn.Close();

Q. The ADO object model supports asynchronous queries. How do I use the ADO.NET object model to execute a query asynchronously?

A. As of the initial release of ADO.NET, the object model does not provide such functionality.

The ADO Command object does allow you to execute queries asynchronously, but the major reason that this feature was added to the ADO object model was that writing threading code in Visual Basic "classic" was next to impossible for the majority of Visual Basic developers.

The .NET Framework greatly simplifies the process of working with threads. That's not to say that writing threading code is simple. Writing solid multi-threaded code is still a challenge because multi-threaded code exposes you to an entirely new level of possible problems. It's very easy to write multi-threaded code that can create problems that you might not discover until long after you've deployed your application. If you want to write multi-threaded code in either Visual Basic .NET or Visual C# .NET, I strongly recommend picking up a book that thoroughly discusses all the ins and outs of writing solid multi-threaded code.

The following code snippet uses multi-threading strictly as an example of how you can execute a query on another thread. The code also includes an example of using the Command object's Cancel method to cancel the execution of the query. I do not claim to be a threading guru. This code is intended as a very simple example and should not be used in a production application.

To use this code, create a new Console application in your language of choice. Then add a new Class to the project whose class name is clsSlowQuery. Paste the code into the code files as described in the comments. The code calls the CallSlowQuery method of the class, which starts a new thread and starts a query on that thread. The query continues to run on the background thread, and the method returns immediately after starting the query. When and if the query completes, the results will appear in the Console window. The code waits 2.5 seconds and then calls the CancelQuery method on the class to cancel the execution of the query.

Visual Basic .NET

'Use this code in Module1.vb Dim obj As New clsSlowQuery() obj.CallSlowQuery() Threading.Thread.Sleep(2500) obj.CancelQuery() 'Use this code in clsSlowQuery.vb Public Class clsSlowQuery     Dim cn As OleDbConnection     Dim cmd As OleDbCommand     Dim ts As Threading.ThreadStart     Dim th As Threading.Thread     Dim blnRunningQuery, blnCancelledQuery As Boolean     Public Sub CallSlowQuery()         If Not blnRunningQuery Then             SyncLock Me                 blnRunningQuery = True                 ts = New Threading.ThreadStart(AddressOf CallQueryAsync)                 th = New Threading.Thread(ts)                 th.Start()                 Console.WriteLine("Running query")             End SyncLock         Else             Dim strMessage As String             strMessage = "Can't execute CallSlowQuery method." & _                          vbCrLf & _                          vbTab & "I'm still waiting on the results " & _                          "of a previous query."             Throw New Exception(strMessage)         End If     End Sub     Public Sub CancelQuery()         If blnRunningQuery Then             SyncLock Me                 Try                     blnCancelledQuery = True                     cmd.Cancel()                 Catch ex As Exception                     Console.WriteLine(ex.Message)                 End Try             End SyncLock         Else             Dim strMessage As String             strMessage = "Can't execute CancelQuery method." & vbCrLf & _                          vbTab & "I'm not currently running a query."             Throw New Exception(strMessage)         End If     End Sub     Private Sub CallQueryAsync()         Dim strConn As String         strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                   "Initial Catalog=Northwind;Trusted_Connection=Yes;"         Dim strSQL As String = "WAITFOR DELAY '00:00:10';" & _                                "SELECT Count(*) FROM Customers"         cn = New OleDbConnection(strConn)         cn.Open()         cmd = New OleDbCommand(strSQL, cn)         Dim intNumCustomers As Integer         Try             intNumCustomers = CInt(cmd.ExecuteScalar)             Console.WriteLine(intNumCustomers)         Catch ex As Exception             If blnCancelledQuery = True Then                 Console.WriteLine("Query cancelled")             Else                 Console.WriteLine(ex.Message)             End If         End Try         cn.Close()         blnRunningQuery = False         blnCancelledQuery = False     End Sub End Class

Visual C# .NET

//use this code in Class1.cs clsSlowQuery obj = new clsSlowQuery(); obj.CallSlowQuery(); System.Threading.Thread.Sleep(2500); obj.CancelQuery(); //use this code in clsSlowQuery.cs public class clsSlowQuery {     OleDbConnection cn;     OleDbCommand cmd;     System.Threading.ThreadStart ts;     System.Threading.Thread th;     bool blnRunningQuery, blnCancelledQuery;     public void CallSlowQuery()     {         if (!blnRunningQuery)             lock (this)             {                 blnRunningQuery = true;                 ts = new System.Threading.ThreadStart(this.CallQueryAsync);                 th = new System.Threading.Thread(ts);                 th.Start();                 Console.WriteLine("Running query");             }         else         {             string strMessage;             strMessage = "Can't execute CallSlowQuery method.\n\t" +                          "I'm still waiting on the results " +                          "of a previous query.";             throw new Exception(strMessage);         }     }     public void CancelQuery()     {         if (blnRunningQuery)              lock(this)             {                 try                 {                     blnCancelledQuery = true;                     cmd.Cancel();                 }                 catch (Exception ex)                 {                     Console.WriteLine(ex.Message);                 }             }         else         {             string strMessage;             strMessage = "Can't execute CancelQuery method.\n\t" +                          "I'm not currently running a query.";             throw new Exception(strMessage);         }     }     private void CallQueryAsync()     {         string strConn;         strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                   "Initial Catalog=Northwind;Trusted_Connection=Yes;";         string strSQL = "WAITFOR DELAY '00:00:10';" +                         "SELECT Count(*) FROM Customers";         cn = new OleDbConnection(strConn);         cn.Open();         cmd = new OleDbCommand(strSQL, cn);         int intNumCustomers;         try         {             intNumCustomers = (int) cmd.ExecuteScalar();             Console.WriteLine(intNumCustomers);         }         catch (Exception ex)         {             if (blnCancelledQuery)                 Console.WriteLine("Query cancelled");             else                 Console.WriteLine(ex.Message);         }         cn.Close();         blnRunningQuery = false;         blnCancelledQuery = false;     } }