Using Stored Programs in ADO.NET

Stored programs have always been an integral and important part of application development within SQL Server, and SQL Server support is a primary focus of the ADO.NET interfaces. Unlike some implementations of stored programs (Oracle's for instance), SQL Server's stored programs can directly return multiple result sets, which results in the ADO.NET interfaces providing very natural support for the MySQL implementation.

17.2.1. Calling a Simple Stored Procedure

Let's start with a very simple stored procedure. Example 17-19 shows a simple stored procedure that takes no parameters and returns no result sets.

Example 17-19. A simple stored procedure

CREATE PROCEDURE sp_simple( )
BEGIN
 SET autocommit=0;
END;

Calling this stored procedure is only slightly more complex than calling a non-SELECT statement, as described in "Issuing a Non-SELECT Statement" earlier in this chapter. The procedure for calling this stored procedure differs in two small ways:

• The text for the SQL call contains only the stored procedurethe CALL statement is unnecessary, as are parentheses to represent the parameter list.
• The CommandType property of the MySqlCommand object should be set to CommandType.StoredProcedure.

Example 17-20 illustrates the process of calling the simple stored procedure from Example 17-19 in VB.NET. The name of the stored procedure is used to initialize the MySqlCommand object, and the CommandType for that object is set to CommandType.StoredProcedure. The stored procedure is then executed using the ExecuteNonQuery() method of the MySqlCommand object.

Example 17-20. Calling a simple stored procedure in VB.NET

 Dim SpSimple As MySqlCommand
 SpSimple = New MySqlCommand("sp_simple", myConnection)
 SpSimple.CommandType = CommandType.StoredProcedure
 SpSimple.ExecuteNonQuery( )

Example 17-21 shows the same logic implemented in C#.

Example 17-21. Calling a simple stored procedure in C#

 MySqlCommand SpSimple;
 SpSimple = new MySqlCommand("sp_simple", myConnection);
 SpSimple.CommandType = CommandType.StoredProcedure;
 SpSimple.ExecuteNonQuery( );

17.2.2. Supplying Input Parameters

Earlier in this chapter we saw how to use the Parameters collection of the MySqlCommand class to specify parameters to simple SQL statements. The Parameters collection can be used to manipulate stored procedure parameters as well. In this section we'll look at specifying input parameters . Example 17-22 shows a simple stored procedure that takes a single input parameter.

Example 17-22. Stored procedure with an input parameter

CREATE PROCEDURE sp_simple_parameter(in_autocommit INT)
BEGIN
 SET autocommit=in_autocommit;
END;

To specify a value for this parameter, we can create a parameter object using the Parameters.Add() method of the MySqlCommand object. We can then use the Values property of the resulting object to set a value for the parameter prior to executing the procedure. Example 17-23 shows us doing just that in C#.

Example 17-23. Calling a stored procedure with an input parameter in C#

1 MySqlCommand SpCmd;
2 SpCmd = new MySqlCommand("sp_Simple_Parameter", myConnection);
3 SpCmd.CommandType = CommandType.StoredProcedure;
4 MySqlParameter Parm1 = SpCmd.Parameters.Add(
5 "in_autocommit",MySqlDbType.Int32);
6
7 Parm1.Value = 0;
8
9 SpCmd.ExecuteNonQuery( );

In lines 13 we create the stored procedure definition. On line 4 we create a parameter object representing the first (and only) parameter to the stored procedure. On line 7 we assign a value to this parameter, and finallyon line 9we execute the stored procedure.

Note that once the stored procedure (including its parameters) is defined, we can change the parameter value and re-execute the procedure as many times as we like. We'll see an example of this technique at the end of this chapter.

Example 17-24 shows how we can set the stored procedure parameter and execute the stored procedure in VB.NET.

Example 17-24. Calling a stored procedure with an input parameter in VB.NET

 Dim SpCmd As MySqlCommand
 SpCmd = New MySqlCommand("sp_Simple_Parameter", myConnection)
 SpCmd.CommandType = CommandType.StoredProcedure
 Dim Parm1 As MySqlParameter
 Parm1 = SpCmd.Parameters.Add("in_autocommit", MySqlDbType.Int32)
 Parm1.Value = 0
 SpCmd.ExecuteNonQuery( )

17.2.3. Using a DataReader with a Stored Program

Retrieving a single result set from a stored procedure can be achieved by using pretty much the same coding as we would use to obtain the results of a SELECT statement. Consider a stored procedure that returns only a single result set, as shown in Example 17-25.

Example 17-25. Stored procedure with a single result set

CREATE PROCEDURE Sp_one_result_set( )
 SELECT department_id,department_name
 FROM departments;

To retrieve a result set from this stored procedure, we can use the ExecuteReader() method to return a DataReader object and then loop through the DataReader in the usual way. Example 17-26 shows how to do this in C#.

Example 17-26. Creating a DataReader from a stored procedure in C#

MySqlCommand SpCmd;
SpCmd = new MySqlCommand("sp_one_result_set", myConnection);
SpCmd.CommandType = CommandType.StoredProcedure;
MySqlDataReader MyReader=SpCmd.ExecuteReader( );
while (MyReader.Read( ))
{
 Console.Write(MyReader.GetInt32(0)+"	");
 Console.WriteLine(MyReader.GetString(1));
}

Example 17-27 shows how to create a DataReader from a stored procedure execution in VB.NET.

Example 17-27. Creating a DataReader from a stored procedure in VB.NET

Dim SpCmd As MySqlCommand
SpCmd = New MySqlCommand("sp_one_result_set", myConnection)
SpCmd.CommandType = CommandType.StoredProcedure
Dim MyReader As MySqlDataReader = SpCmd.ExecuteReader
While MyReader.Read
 Console.Write(MyReader.GetInt32(0).ToString + _
 "" & Microsoft.VisualBasic.Chr(9) & "")
 Console.WriteLine(MyReader.GetString(1))
End While
MyReader.Close( )

17.2.4. Processing Multiple Result Sets in a DataReader

The DataReader class provides a method for processing multiple result sets : the DataReader method NexTResult( ) will return true if there is an additional result set available from the SqlCommand and will move the DataReader to that result set.

To illustrate, let's retrieve the two result sets returned from the stored procedure in Example 17-28.

Example 17-28. Stored procedure returning two result sets

CREATE PROCEDURE sp_two_results( )
BEGIN
 SELECT location,address1,address2
 FROM locations;
 SELECT department_id,department_name
 FROM departments;
END;

We can process the second result set by calling the Nextresult( ) method after finishing with the first result set, then reading the rows from the second result set. Example 17-29 illustrates this technique in VB.NET.

Example 17-29. Processing two result sets using a DataReader in VB.NET

 Dim TabChr As Char = Microsoft.VisualBasic.Chr(9)
 Dim SpCmd As MySqlCommand
 SpCmd = New MySqlCommand("sp_two_results", myConnection)
 SpCmd.CommandType = CommandType.StoredProcedure
 Dim MyReader As MySqlDataReader = SpCmd.ExecuteReader
 While MyReader.Read
 Console.Write(MyReader.GetString(0) + TabChr)
 Console.Write(MyReader.GetString(1))
 Console.WriteLine(MyReader.GetString(2))
 End While
 MyReader.NextResult( )
 While MyReader.Read
 Console.Write(MyReader.GetInt32(0).ToString +TabChr)
 Console.WriteLine(MyReader.GetString(1))
 End While
 MyReader.Close( )

Using this technique is a bit cumbersome, especially if there is a large number of result sets. As we will see later on, writing code to dynamically process multiple result sets from a DataReader, or processing multiple result sets using the DataSet class, can often result in simpler and more robust code.

17.2.5. Dynamically Processing Result Sets

In the previous example, we knew exactly how many result sets to expect from the stored procedure and we knew in advance the number and types of columns to be returned from each. While this is a realistic scenario, we may often need to process a stored procedure where the number and types of result sets might change depending on the input parameters.

For instance, the stored procedure in Example 17-30 returns a different set of result sets depending on the characteristics of the employee whose identity is defined by the input employee_id parameter. If the employee is a sales representative, then three result sets are returned. Otherwise, only two result sets are returned. Furthermore, the structure of the second result set for a sales rep is different from the result set returned by a normal employee.

Example 17-30. Stored procedure that returns an unpredictable number of result sets

CREATE PROCEDURE sp_employee_report
 (in_emp_id decimal(8,0),
 OUT out_customer_count INT)
 READS SQL DATA

BEGIN

 SELECT employee_id,surname,firstname,date_of_birth,address1,address2,zipcode
 FROM employees
 WHERE employee_id=in_emp_id;

 SELECT department_id,department_name
 FROM departments
 WHERE department_id=
 (SELECT department_id
 FROM employees
 WHERE employee_id=in_emp_id);

 SELECT count(*)
 INTO out_customer_count
 FROM customers
 WHERE sales_rep_id=in_emp_id;

 IF out_customer_count=0 THEN
 SELECT 'Employee is not a current sales rep';
 ELSE
 SELECT customer_name,customer_status,contact_surname,contact_firstname
 FROM customers
 WHERE sales_rep_id=in_emp_id;

 SELECT customer_name,sum(sale_value) as "TOTAL SALES",
 max(sale_value) as "MAX SALE"
 FROM sales JOIN customers USING (customer_id)
 WHERE customers.sales_rep_id=in_emp_id
 GROUP BY customer_name;
 END IF;

END$$;

To process this stored procedure, our code needs to:

• Loop through all of the result sets with no assumption as to how many there may be.
• Loop through the columns in each result set without knowing at compile time how many columns exist in each result set.

We can easily achieve the first objective simply by iterating through the result sets of a DataReader as long as the Nextresult( ) call returns true.

We achieve the second objective by using the FieldCount property of the Reader and the GetName( ) and GetString( ) methods, which allow us to retrieve the name and value for each column, as shown in Example 17-31.

Example 17-31. Processing result sets dynamically with a DataReader

1 static void EmployeeReport(int EmployeeId)
2 {
3 MySqlCommand SpCmd = new MySqlCommand("sp_employee_report", myConnection);
4 SpCmd.CommandType = CommandType.StoredProcedure;
5 MySqlParameter Param_empid = SpCmd.Parameters.Add(
6 "in_emp_id", MySqlDbType.Int32);
7
8 Param_empid.Value = EmployeeId;
9 MySqlDataReader EmpReader=SpCmd.ExecuteReader( );
10
11 do
12 {
13 //Print Column Names
14 Console.WriteLine("-------------------------------------");
15 for (int i = 0; i < EmpReader.FieldCount; i++)
16 {
17 Console.Write(EmpReader.GetName(i)+"	");
18 }
19 Console.WriteLine("
-----------------------------------");
20 //Print out the row values
21 while (EmpReader.Read( ))
22 {
23 for (int i = 0; i < EmpReader.FieldCount; i++)
24 {
25 Console.Write(EmpReader.GetString(i)+"	");
26 }
27 Console.WriteLine( );
28 }
29 } while (EmpReader.NextResult( ));
30 EmpReader.Close( );
31 }

Let's step through this example:

Line(s)	Explanation
35	Define a MySqlCommand object to call the stored procedure. The object has a single parameter that corresponds to the EmployeeId argument passed to our routine on line 1.
89	Assign the value of the stored procedure parameter to the value of the input parameter and create a MySqlDataReader to process the result sets.
1129	This loop will continue until a call to NexTResult( ) returns false. In other words, it will continue until all of the result sets have been retrieved from the stored procedure.
1518	Print out the names of the columns of the result set. FieldCount returns the number of columns; GetName(i) returns the name of a particular column.
2128	Loop through each row in the result set.
2326	Loop through each column in the current row. We use GetString(i) to retrieve the value of the current column. GetString will successfully retrieve values for most MySQL data types (numbers, dates, etc.), but if we need to retrieve the values into a more appropriate variable (perhaps we want to perform some calculations on a float, for instance), then we can use GetType(i) to determine the appropriate method (GetFloat(i) for instance).
30	Close the DataReader having processed all of the rows in all of the result sets.

Example 17-32 shows Example 17-31 writen in VB.NET.

Example 17-32. Processing dynamic result sets using a DataReader in VB.NET

Sub EmployeeReport(ByVal EmployeeId As Integer)

 Dim i As Integer = 0
 Dim TabChr As Char = Microsoft.VisualBasic.Chr(9)
 Dim RetChr As Char = Microsoft.VisualBasic.Chr(10)
 Dim SpCmd As MySqlCommand
 SpCmd = New MySqlCommand("sp_employee_report", myConnection)
 SpCmd.CommandType = CommandType.StoredProcedure
 Dim Param_empid As MySqlParameter
 Param_empid = SpCmd.Parameters.Add("in_emp_id", MySqlDbType.Int32)
 Param_empid.Value = EmployeeId
 Dim EmpReader As MySqlDataReader = SpCmd.ExecuteReader
 Do
 Console.WriteLine("-------------------------------------")

 For i = 0 To EmpReader.FieldCount - 1
 Console.Write(EmpReader.GetName(i) + TabChr)
 Next

 Console.WriteLine(RetChr+ "-----------------------------------")
 While EmpReader.Read( )

 For i = 0 To EmpReader.FieldCount - 1
 Console.Write(EmpReader.GetString(i) + TabChr)
 Next
 Console.WriteLine( )
 End While
 Loop While EmpReader.NextResult( )
 EmpReader.Close( )
 End Sub

17.2.6. Using DataSets with Stored Programs

DataSets offer an alternative to the DataReader class for retrieving result sets from stored procedures. We can store more than one result set into a single DataSet object, which allows us to easily process the multiple result sets that might be returned by a stored procedure.

A DataReader may be more convenient than a DataSet for processing a single result set where we know the column names and types in advance. However, when we are processing more than one result set, or when we don't know the structure of the result sets in advance, we find the DataSet more convenient.

Example 17-33 shows us dynamically processing multiple result sets from a stored procedure using a DataSet. We've used this stored procedure before: see Example 17-28.

Example 17-33. Dynamically processing multiple result sets using a DataSet in VB.NET

1 Dim TabChr As Char = Microsoft.VisualBasic.Chr(9)
2 Dim SpCmd As MySqlCommand
3 SpCmd = New MySqlCommand("sp_two_results", myConnection)
4 SpCmd.CommandType = CommandType.StoredProcedure
5
6 Dim MyAdapter As MySqlDataAdapter = New MySqlDataAdapter(SpCmd)
7 Dim SpDataSet As DataSet = New DataSet
8 MyAdapter.Fill(SpDataSet)
9
10 For Each SpTable As DataTable In SpDataSet.Tables
11 For Each SpCol As DataColumn In SpTable.Columns
12 Console.Write(SpCol.ToString( ) + TabChr)
13 Next
14 Console.WriteLine( )
15
16 For Each SpRow As DataRow In SpTable.Rows
17 For Each SpCol As DataColumn In SpTable.Columns
18 Console.Write(SpRow(SpCol).ToString + TabChr)
19 Next
20 Console.WriteLine( )
21 Next
22 Next

You may want to review the section "DataSets " earlier in this chapter if you're not sure of the relationship between MySqlCommands, MySqlDataAdapters, and DataSets.

Let's look at how Example 17-33 works, line by line:

Line(s)	Explanation
24	Create a MySqlCommand object to represent our stored procedure call in the usual way.
6	Create a MySqlDataAdapter object and associate it with the MySqlCommand object.
7	Create a new DataSet object.
8	Populate the DataSet from our MySqlDataAdapter. Since MySqlDataApadapter is associated with the MySqlCommand for our stored procedure, this results in all of the results sets from the stored procedure being stored into the DataSet.
10	The DataSet will now contain one DataTable for each result set returned by the stored procedure. Here we iterate through these tables using the Tables collection of the DataSet object.
1113	Iterate through the columns in the current DataTable using the Columns collection and print the column name.
1621	Iterate through the DataRows in the current DataTable using the Rows collection of the DataTable object.
1719	Iterate through the columns in the current DataRow and print the appropriate column value. SpRow(SpCol) represents a specific column value for a specific row.

Example 17-34 shows this logic implemented in C#.

Example 17-34. Dynamically processing result sets using a DataSet in C#

 MySqlCommand SpCmd;
 SpCmd = new MySqlCommand("sp_two_results", myConnection);
 SpCmd.CommandType = CommandType.StoredProcedure;

 MySqlDataAdapter MyAdapter = new MySqlDataAdapter(SpCmd);
 MyAdapter.SelectCommand = SpCmd;
 DataSet SpDataSet = new DataSet( );
 MyAdapter.Fill(SpDataSet);

 foreach (DataTable SpTable in SpDataSet.Tables)
 {
 foreach (DataColumn SpCol in SpTable.Columns)
 {
 Console.Write(SpCol.ToString( ) + "	");
 }
 Console.WriteLine( );

 foreach (DataRow SpRow in SpTable.Rows)
 {
 foreach (DataColumn SpCol in SpTable.Columns)
 {
 Console.Write(SpRow[SpCol] + "	");
 }
 Console.WriteLine( );
 }
 }

17.2.7. Retrieving Output Parameters

We've left the processing of output parameters until almost the end of this chapter, because obtaining the value of an output parameter (OUT or INOUT) is the last thing we should do when processing a stored program. In particular, we should make sure that we have retrieved all result sets from the stored procedure before trying to access the value of the output parameter. Before all the result sets are processed, the value of the parameter will be NULL, which could lead to subtle bugsespecially if there is a variable number of output parameters.

To use an output parameter in Connector/Net, we define the parameter as we would for an input parameter, but set the ParameterDirection property of the parameter to either Output or InputOutput.

Example 17-35 is an example of a stored procedure that contains an OUT parameter.

Example 17-35. Stored procedure with an OUT parameter

CREATE PROCEDURE sp_custsales
 (in_customer_id INT,
 OUT out_sales_total FLOAT)
BEGIN
 SELECT customer_name
 FROM customers
 WHERE customer_id=in_customer_id;

 SELECT sum(sale_value)
 INTO out_sales_total
 FROM sales
 WHERE customer_id=in_customer_id;

END;

In Example 17-36 we execute this stored procedure and retrieve the value of the output parameter. Prior to executing the stored procedure, we set the value of the Parameter.Direction property to ParameterDirection.Output. After we have processed all of the rows from the result set returned by the stored procedure, we can examine the parameter's Value property to see the value placed by the stored procedure into the OUT parameter.

Example 17-36. Processing a stored procedure with an OUT parameter in C#

 static void CustomerSales(int CustomerId)
 {
 MySqlCommand SpCustSales;
 MySqlParameter PCustId,PSalesTotal;
 MySqlDataReader CustReader;

 SpCustSales = new MySqlCommand("sp_custsales", myConnection);
 SpCustSales.CommandType = CommandType.StoredProcedure;
 PCustId = SpCustSales.Parameters.Add(
 "in_customer_id", MySqlDbType.Int32);
 PSalesTotal = SpCustSales.Parameters.Add(
 "out_sales_total", MySqlDbType.Float);
 PSalesTotal.Direction = ParameterDirection.Output;

 PCustId.Value = CustomerId;
 CustReader=SpCustSales.ExecuteReader( );
 while (CustReader.Read( ))
 {
 Console.WriteLine(CustReader.GetString(0));
 }
 CustReader.Close( );
 Console.WriteLine(PSalesTotal.Value);

 Console.WriteLine("====");

 }

Example 17-37 shows this logic coded in VB.NET.

Example 17-37. Processing an output parameter in VB.NET

Sub CustomerSales(ByVal CustomerId As Integer)

 Dim SpCustSales As MySqlCommand
 Dim PCustId As MySqlParameter
 Dim PSalesTotal As MySqlParameter
 Dim CustReader As MySqlDataReader

 SpCustSales = New MySqlCommand("sp_custsales", myConnection)
 SpCustSales.CommandType = CommandType.StoredProcedure
 PCustId = SpCustSales.Parameters.Add("in_customer_id", MySqlDbType.Int32)
 PSalesTotal = SpCustSales.Parameters.Add("out_sales_total", MySqlDbType.Float)
 PSalesTotal.Direction = ParameterDirection.Output

 PCustId.Value = CustomerId
 CustReader = SpCustSales.ExecuteReader( )
 While CustReader.Read( )
 Console.WriteLine(CustReader.GetString(0))
 End While
 CustReader.Close( )
 Console.WriteLine(PSalesTotal.Value)

 End Sub

Make sure you have processed all of the result sets returned from a stored procedure before attempting to access any output parameters.

 

17.2.8. Calling Stored Functions

In languages such as Perl or PHP, if we want to get the results of a stored function call, we simply embed it into a SELECT statement and retrieve the result of the function call as a single-row SELECT.

This technique is available to us in ADO.NET , but we also have the option of retrieving the result of a function call in a more direct fashion. We can call a function as we would a stored procedure that has no result sets, and we can retrieve the results of the function execution by associating a parameter with ParameterDirection set to ReturnValue.

For instance, consider the very simple stored function in Example 17-38, which returns a date formatted just the way we like it.

Example 17-38. Simple MySQL stored function

CREATE FUNCTION my_date( )
 RETURNS VARCHAR(50)
BEGIN
 RETURN(DATE_FORMAT(NOW( ),'%W, %D of %M, %Y'));
END$$

To call this directly in ADO.NET, we call the function as we would a stored procedure, but we create a special parameter to retrieve the function return value with the Direction property set to ReturnValue. Example 17-39 shows us processing our simple date function in C#.

Example 17-39. Processing a stored function in C#

 MySqlCommand FCmd = new MySqlCommand("my_date", myConnection);
 FCmd.CommandType = CommandType.StoredProcedure;
 MySqlParameter rv = FCmd.Parameters.Add("rv", MySqlDbType.String);
 rv.Direction = ParameterDirection.ReturnValue;
 FCmd.ExecuteNonQuery( );
 Console.WriteLine("return value=" + rv.Value);

Example 17-40 shows the same logic in VB.NET.

Example 17-40. Processing a stored function in VB.NET

 Dim FCmd As MySqlCommand = New MySqlCommand("my_date", myConnection)
 FCmd.CommandType = CommandType.StoredProcedure
 Dim rv As MySqlParameter = FCmd.Parameters.Add("rv", MySqlDbType.String)
 rv.Direction = ParameterDirection.ReturnValue
 FCmd.ExecuteNonQuery( )
 Console.WriteLine("return value=" + rv.Value)