Using MySQL Stored Programs with .NET - Using MySQL Stored Programs in Applications - MySQL Stored Procedure Programming (2009)

MySQL Stored Procedure Programming (2009)

Part III. Using MySQL Stored Programs in Applications

Chapter 17. Using MySQL Stored Programs with .NET

ADO.NET is Microsoft's database-independent, language-neutral data access interface included within the .NET framework. ADO.NET allows .NET languages such as C# and VB.NET to communicate with various data sources, primarily relational databases such as SQL Server, Oracle, and, of course, MySQL. MySQL provides an ADO-compliant driver—Connector/Net—that allows us to work with MySQL databases using the ADO.NET interfaces.

First, we'll start with a quick review of how we can use ADO.NET to process standard SQL statements against a MySQL database. Next, we'll examine the ADO.NET syntax for invoking stored programs, including handling input and output parameters and processing multiple result sets. Finally, we'll show how we can use a MySQL stored procedure as the basis for an ASP.NET web application.

Review of ADO.NET Basics

Before looking at how to invoke stored programs using ADO.NET, let's review how we perform operations in ADO.NET involving simple SQL statements. These operations form the foundation of stored program interactions. If you are already familiar with using ADO.NET with MySQL, you might want to skip forward to "Using Stored Programs in ADO.NET," later in this chapter.

Installing the Connector/Net Driver and Configuring Your IDE

To connect to MySQL from ADO.NET, we first need to download and install the Connector/Net provider from MySQL. We can download the Connector/Net driver from the MySQL web site at http://dev.mysql.com/downloads/connector/net/.

Once we have installed the Connector/Net driver, we are ready to write .NET programs to connect to MySQL. However, we must add a reference to the Connector/Net driver in our .NET application.

To do this in Visual Studio, select Project → Add Reference from the main menu, then select the Browse tab. We find the MySQL.Data.dll file on our system, usually located in a directory such as C:\Program Files\MySQL\MySQL Connector Net <x.x.x>\bin\.NET <y.y>; where "x.x.x"corresponds to the version of the Connector/Net driver (currently 1.0.7) and "y.y" corresponds to the version of .NET that we are using (usually 1.1 or 2.0). Figure 17-1 shows how we can configure Visual C# Visual Studio Express Edition to use the Connector/Net driver.

Adding a reference to the Connector/Net driver in Visual C# Express

Figure 17-1. Adding a reference to the Connector/Net driver in Visual C# Express

Registering the Driver and Connecting to MySQL

To use the MySQL driver in your program code, we will normally first import the MySQL.Data.MySqlClient namespace so we don't have to fully qualify every reference to Connector/Net classes. In VB.NET, this means we would include Imports MySql.Data.MySqlClient as the first line of our VB.NET module. In C#, we would include a using MySql.Data.MySqlClient; statement within the Using directives region, as shown in Figure 17-2.

Adding the "using" clause in Visual C# Express

Figure 17-2. Adding the "using" clause in Visual C# Express

To establish a connection to MySQL we need to create a MySQLConnection object. The Constructer method for the MySQLConnection object accepts a string that defines the server, database, and connection credentials. This string consists of a set of name-value pairs separated by semicolons. For instance, the following string defines a connection to a server on the localhost at port 3306 and connects to database prod using the account fred and the password freddy:

Server=localhost;Port=3306;Database=prod;Username=fred;Password=freddy

Table 17-1 lists the most important keywords that you can provide for the MySQLConnection object; you can find a complete list in the Connector/Net documentation that ships with the driver.

Table 17-1. Some of the keyword values for the MySQLConnection

Keyword

Description

Host

Name of the host on which the MySQL server is located. This could be an IP address, hostname, or localhost.

Port

Port number upon which the MySQL server is listening.

Database

Name of the database for initial connection.

Username

MySQL username to use for the connection.

Password

Password for the MySQL account.

It would be unusual—and probably bad practice—to hardcode the MySQLConnection details in your program. More often, you will retrieve the keywords from command-line arguments or from a login dialog box.

Once the MySQLConnection object is initialized, we can establish the connection using the open( ) method. If the connection fails, a MySQLException will be thrown, so we need to enclose this call in a try block if we don't want to throw a non-handled exception (see "Handling Errors," later in this chapter). Example 17-1 shows us connecting to MySQL from within a VB.NET program, with the connection details specified as command-line arguments.

Example 17-1. Connecting to MySQL in VB.NET

Sub Main(ByVal CmdArgs( ) As String)

Dim myHost As String = CmdArgs(0)

Dim myUserId As String = CmdArgs(1)

Dim myPassword As String = CmdArgs(2)

Dim myDatabase As String = CmdArgs(3)

Dim myConnectionString As String = "Database=" & myDatabase & _

" ;Data Source=" & myHost & _

";User Id=" & myUserId & ";Password=" & myPassword

Dim myConnection As New MySqlConnection(myConnectionString)

Try

myConnection.Open( )

Console.WriteLine("Connection succeeded")

Catch MyException As MySqlException

Console.WriteLine("Connection error: MySQL code: " _

& MyException.Number & " " & MyException.Message)

End Try

Example 17-2 implements the same logic in C#.

Example 17-2. Connecting to MySQL in C#

static void Main(string[] args)

{

String myHost=args[0];

String myUserId=args[1];

String myPassword=args[2];

String myDatabase=args[3];

String myConnectionString = "Database=" + myDatabase +

" ;Host=" + myHost +

";UserName=" + myUserId + ";Password=" + myPassword;

MySqlConnection myConnection;

myConnection = new MySqlConnection( );

myConnection.ConnectionString = myConnectionString;

try {

myConnection.Open( );

Console.WriteLine("Connection succeded");

}

catch (MySqlException MyException) {

Console.WriteLine("Connection error: MySQL code: "+MyException.Number

+" "+ MyException.Message);

}

Issuing a Non-SELECT Statement

It is fairly straightforward to execute a non-SELECT statement—such as UPDATE, INSERT, DELETE, or SET—in .NET. First, we create a new MySQLCommand object, passing it the SQL statement to be executed and the name of the active connection (these can also be specified using the properties of the MySqlCommand object at a later time).

The ExecuteNonQuery() method of the MySqlCommand executes a statement that returns no result sets. It returns the number of rows affected by the statement. Example 17-3 shows an example of this in C#.

Example 17-3. Executing a non-SELECT SQL statement in C#

MySqlCommand NonSelect = new MySqlCommand(

"DELETE FROM employees WHERE employee_id=2001", myConnection);

int RowsAffected = NonSelect.ExecuteNonQuery( );

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

Example 17-4. Executing a non-SELECT statement in VB.NET

Dim NonSelect As MySqlCommand

NonSelect = New MySqlCommand( _

"DELETE FROM employees WHERE employee_id=2001", myConnection)

Dim RowsAffected As Int16

RowsAffected = NonSelect.ExecuteNonQuery( )

Reusing a Statement Object

We don't have to create a new statement object for every SQL statement we execute. By changing the CommandText property of the MySqlCommand object, we associate the object with a new SQL statement text, which we can submit to the database by calling the ExecuteNonQuery()method. Example 17-5 provides an example of this technique in C#.

Example 17-5. Reusing a MySqlCommand object in C#

MySqlCommand NonSelect = new MySqlCommand("set autocommit=0",myConnection);

int RowsAffected=NonSelect.ExecuteNonQuery( );

NonSelect.CommandText = "update departments "+

"set location=location "+

"where department_id=1";

RowsAffected = NonSelect.ExecuteNonQuery( );

Console.WriteLine(RowsAffected + " rows affected");

Using Parameters

A lot of the time we execute the same logical SQL statement with different values for the WHERE clause or some other variable part of the statement. It might seem simple to do this by manipulating the CommandText and "pasting it" in the variable portions. For instance, in Example 17-6 we generate a new unique SQL statement to update employees' salaries based on some values in arrays.

Example 17-6. "Paste" method of changing SQL parameters (not recommended)

For i = 1 To N

NonSelect.CommandText = "UPDATE employees " + _

" SET salary= " + EmployeeSal(i).ToString + _

" WHERE employee_id=" + EmployeeID(i).ToString

NonSelect.ExecuteNonQuery( )

Next

While this method will work—and is, in fact, a common technique—it is neither efficient nor safe. In particular, this style of coding cannot take advantage of MySQL server-side prepared statements, and it is vulnerable to SQL injection (a form of attack in which SQL syntax is inserted into parameters, leading to unintended SQL syntax being executed).

A far better way of performing this kind of iterative processing is to use the Parameters collection of the MySqlCommand object. Parameters are prefixed in the SQL text with the "?" character. You then use the Parameter methods of the MySqlCommand object to define the parameters and set their values, as shown in Example 17-7.

Example 17-7. Using parameters in VB.NET

1 Dim ParameterSQL As MySqlCommand

2 Dim SQLText As String

3 SQLText = "UPDATE employees " + _

4 " SET salary= ?NewSal" + _

5 " WHERE employee_id= ?EmpID"

6 ParameterSQL = New MySqlCommand(SQLText, myConnection)

7

8 Dim EmpSal As MySqlParameter

9 EmpSal = ParameterSQL.Parameters.Add("?NewSal", MySqlDbType.Float)

10 Dim EmpId As MySqlParameter

11 EmpId = ParameterSQL.Parameters.Add("?EmpID", MySqlDbType.Int16)

12 Dim RowCount As Int16

13

14 For i = 1 To N

15 EmpSal.Value = EmployeeSal(i)

16 EmpId.Value = EmployeeID(i)

17 RowCount = ParameterSQL.ExecuteNonQuery( )

18 Console.WriteLine(RowCount.ToString)

19 Next

Let's step through this example:

Line(s)

Explanation

3

Create the text for our SQL. The parameters in the SQL (?NewSal and ?EmpID) are prefixed by ? characters to distinguish them from normal MySQL identifiers.

6

Create the MySqlCommand object and associate it with our SQL text.

8–9

Declare a MySqlParameter object for the NewSal parameter on line 8, and on line 9, associate it with the MySqlCommand object. The name of the parameter provided to the Add() method should match exactly the name of the parameter in your SQL text. The second argument to Add() specifies the data type of the parameter.

10–11

Create a second parameter to represent the EmpID parameter.

14–19

Iterate through the EmployeeSal and EmployeeID arrays, which contain new salaries for specific employees.

15–16

Assign the appropriate values to the parameter objects. The values are taken from the EmployeeSal and EmployeeID arrays.

17

The ExecuteNonQuery() method executes the SQL with the parameters supplied.

Using parameters rather than hardcoded literals is highly recommended, especially since—as we will see later—we really must use parameters if we are going to invoke stored programs in .NET.

Example 17-8 shows the logic of Example 17-7 expressed in C# .NET.

Example 17-8. Using parameters in C#

String SQLText = "UPDATE employees " +

" SET salary= ?NewSal" +

" WHERE employee_id= ?EmpID";

MySqlCommand ParameterSQL = new MySqlCommand(SQLText,myConnection);

MySqlParameter EmpSal = ParameterSQL.Parameters.Add(

"?NewSal", MySqlDbType.Float);

MySqlParameter EmpId = ParameterSQL.Parameters.Add(

"?EmpID", MySqlDbType.Int16);

for(i=1;i<=N;i++)

{

EmpSal.Value = EmployeeSal[i];

EmpId.Value = EmployeeID[i];

RowCount = ParameterSQL.ExecuteNonQuery( );

}

Issuing a SELECT and Using a DataReader

MySQL supports a wide variety of methods of dealing with the output from a query. In this section, we will first review what is arguably the most straightforward of these methods: the DataReader.

A DataReader allows us to fetch rows from a result set in a manner similar to the fetching of rows from a stored program cursor. To create a MySqlDataReader object, we use the ExecuteReader( ) method of the MySqlCommand object. We iterate through the MySqlDataReaderusing the Read( ) method, and retrieve data values using GetInt32( ), GetString( ), and other data type-specific Get methods.

Example 17-9 is an example of using a MySqlDataReader in C#.

Example 17-9. Using a MySqlDataReader in C#

1 String SelectText = "SELECT department_id, department_name FROM departments";

2 MySqlCommand SelectStatement = new MySqlCommand(SelectText, myConnection);

3 MySqlDataReader SelectReader = SelectStatement.ExecuteReader( );

4 while (SelectReader.Read( ))

5 {

6 Console.WriteLine(SelectReader.GetInt32(0) + "\t" +

7 SelectReader.GetString(1));

8 }

9 SelectReader.Close( );

Let us step through this example:

Line(s)

Explanation

2

Create a MySqlCommand object for a SELECT statement.

3

Use the ExecuteReader( ) method to create a MySqlDataReader object.

4-8

Loop through the rows returned by the SELECT statement using the Read( ) method of the MySqlDataReader.

6

Use the GetInt32() and GetString() methods to retrieve the current values for the department_id and department_name columns. The argument for these methods is the numeric position of the column in the result set—starting with "0" as the first column.

9

Close the Reader. We should always do this since it releases database resources and is also a prerequisite for retrieving OUT parameters from stored procedures.

Example 17-10 shows the logic in Example 17-9 implemented in VB.NET.

Example 17-10. Using a MySqlDataReader in VB.NET

Dim SelectText As String

Dim SelectStatement As MySqlCommand

Dim SelectReader As MySqlDataReader

SelectText = "SELECT department_id, department_name FROM departments"

SelectStatement = New MySqlCommand(SelectText, myConnection)

SelectReader = SelectStatement.ExecuteReader( )

While (SelectReader.Read( ))

Console.WriteLine(SelectReader.GetInt32(0).ToString + _

" " + SelectReader.GetString(1))

End While

SelectReader.Close( )

Getting DataReader Metadata

The DataReader provides methods for retrieving information about the columns that will be returned in the Reader. This information is essential if we are going to process dynamic SQL—for instance, SQL that is entered at a terminal by an end user or generated on-the-fly by some other module in our program.

The FieldCount() method returns the number of columns in the DataReader's result set. GetFieldType() and GetName() return the name and data type of a column within the result set, where GetName(0) would return the name of the first column. Example 17-11 uses these methods to retrieve the names and data types of a query from within VB.NET and displays those to the console.

Example 17-11. Accessing DataReader metadata

SelectText = "SELECT * FROM departments"

SelectStatement = New MySqlCommand(SelectText, myConnection)

SelectReader = SelectStatement.ExecuteReader( )

For i = 0 To SelectReader.FieldCount( ) - 1

Console.WriteLine(SelectReader.GetName(i) + " " + _

SelectReader.GetFieldType(i).ToString)

Next

DataSets

While DataReaders offer a convenient way to access query result sets, the ADO.NET DataSet class provides an alternative that is a little more complex, but that offers increased flexibility and functionality. In particular, because we can only ever have a single DataReader open simultaneously for a given connection, we are likely to use DataSets in most complex applications.

DataSets provide an in-memory, datasource-independent representation of data that can persist even when a connection is closed. DataSets offer a number of methods for handling data modification, including a mechanism for resynchronizing data when a closed connection is reopened.

In this section we will provide a simple example of using a DataSet to retrieve the outputs only from a simple SQL query.

A DataSet object contains a collection of tables, each of which includes a collection of columns and rows. We can access and manipulate the tables, columns, and rows in the DataSet using the DataTable, DataColumn, and DataRow objects.

A DataSet is associated with its data source through a DataAdapter object. In our case, we have to create a MySqlDataAdapator object to associate a DataSet with a MySqlCommand.

The general steps for processing a SQL query through a DataSet are as follows:

1. Create the MySqlCommand object.

2. Create a MySqlDataAdpator object and associate it with the MySQLCommand.

3. Create a DataSet object.

4. Use the MySqlDataAdapter object to populate the DataSet.

5. Use the DataTable, DataColumn, and DataRow objects to retrieve the contents of the DataSet.

Example 17-12 shows an example of populating and examining a DataSet object in C# .

Example 17-12. Populating a DataSet from a simple SQL statement in C#

1 String SqlText = "SELECT * FROM departments";

2 MySqlCommand SqlCmd = new MySqlCommand(SqlText, myConnection);

3

4 MySqlDataAdapter MyAdapter=new MySqlDataAdapter(SqlCmd);

5 DataSet MyDataSet=new DataSet( );

6 int rows = MyAdapter.Fill(MyDataSet);

7

8 DataTable MyTable=MyDataSet.Tables[0];

9

10 //Write column headings

11 foreach(DataColumn MyColumn in MyTable.Columns)

12 {

13 Console.Write(MyColumn.Caption+"\t");

14 }

15 Console.WriteLine( );

16

17 //Write Column Rows

18 foreach(DataRow MyRow in MyTable.Rows)

19 {

20 foreach(DataColumn MyColumn in MyTable.Columns)

21 {

22 Console.Write(MyRow[MyColumn]+"\t");

23 }

24 Console.WriteLine( );

25 }

Let's step through this example:

Line(s)

Explanation

1–2

Define a MySqlCommand object (SqlCmd) that will issue our query.

4

Create a new MySQLDataAdapator object and associate it with SqlCmd (our MySqlCommand object).

5

Create a new DataSet and, in line 6, we populate this data set with the output of the SELECT statement (via the MySqlDataAdapter).

8

Declare a DataTable (MyTable) that references the first table (index "0") in the DataSet MyDataSet. Remember that a DataSet can contain multiple tables, but in this case we know that we need only concern ourselves with the first and only DataTable in the DataSet.

11–15

Print the names of the columns in the DataTable. We do this by iterating through the Columns collection in the DataTable and printing the Caption property for each column.

18–25

Print out the data rows. We do this by iterating through the Rows collection in the DataTable. For each Row, we iterate through the Columns collection to print an individual column value. MyRow[MyColumn] represents the value of a specific column within a specific row.

Example 17-13 shows this logic in VB.NET .

Example 17-13. Populating a DataSet from a SELECT statement in VB.NET

Dim TabChr As Char = Microsoft.VisualBasic.Chr(9)

Dim SqlText As String = "SELECT * FROM departments"

Dim SqlCmd As MySqlCommand = New MySqlCommand(SqlText, myConnection)

Dim MyAdapter As MySqlDataAdapter = New MySqlDataAdapter(SqlCmd)

Dim MyDataSet As DataSet = New DataSet

Dim rows As Integer = MyAdapter.Fill(MyDataSet)

Dim MyTable As DataTable = MyDataSet.Tables(0)

For Each MyColumn As DataColumn In MyTable.Columns

Console.Write(MyColumn.Caption + "" & TabChr & "")

Next

Console.WriteLine( )

For Each MyRow As DataRow In MyTable.Rows

For Each MyColumn As DataColumn In MyTable.Columns

Console.Write(MyRow(MyColumn).ToString + "" & TabChr & "")

Next

Console.WriteLine( )

Next

End Sub

As we will see later, using a DataSet is a good technique for stored procedures, which might return multiple result sets. However, for a single result set, we can populate the DataTable directly from the MySqlDataAdaptor() method, as shown in Example 17-14.

Example 17-14. Populating a DataTable directly from a MySqlDataAdapter( ) method

String SqlText = "SELECT * FROM departments";

MySqlCommand SqlCmd = new MySqlCommand(SqlText, myConnection);

MySqlDataAdapter MyAdapter = new MySqlDataAdapter(SqlCmd);

DataTable MyTable = new DataTable( );

MyAdapter.Fill(MyTable);

Handling Errors

The Connector/Net methods will throw a MySqlException exception if the database returns an error with respect to any of our ADO.NET calls. Therefore, we will usually want to enclose our ADO.NET sections in a try/catch block to ensure that we do not generate an unhandled exception condition at runtime. Example 17-15 shows a simple example of using an exception handler in VB.NET .

Example 17-15. Error handling in VB.NET

Sub CreateDemoTables( )

Dim MySqlText As String

MySqlText = "CREATE TABLE DEMO" & _

" (MyInt INT," & _

" MyString VARCHAR(30)) "

Dim CrDemoSQL As MySqlCommand

Try

CrDemoSQL = New MySqlCommand(MySqlText, myConnection)

CrDemoSQL.ExecuteNonQuery( )

Catch MyException As MySqlException

Console.WriteLine("Error creating demo tables:")

Console.WriteLine(MyException.Number.ToString & ": " & _

MyException.Message)

Console.WriteLine(MyException.StackTrace)

End Try

End Sub

In this example, the SQL statement is executed within a Try block. If an error occurs, control is passed to the Catch block that creates a MySqlException object call "MyException". The Number property returns the MySQL error code; the Message property contains the MySQL error message. StackTrace generates a familiar .NET stack trace that can be useful during debugging (though not so useful for Auntie Edna or other end users).

Example 17-16 demonstrates the same exception handling in C# .

Example 17-16. Exception handling in C#

static void CreateDemoTables( )

{

String MySqlText= "CREATE TABLE DEMO" +

" (MyInt INT," +

" MyString VARCHAR(30)) ";

try

{

MySqlCommand CrDemoSQL=new MySqlCommand(MySqlText,myConnection);

CrDemoSQL.ExecuteNonQuery( );

}

catch(MySqlException MyException)

{

Console.WriteLine("Error creating demo tables:");

Console.WriteLine(MyException.Number +

": " + MyException.Message);

Console.WriteLine(MyException.StackTrace);

}

}

Managing Transactions

You can execute the usual MySQL statements to manage your transactions in .NET programs, such as BEGIN TRANSACTION, COMMIT, and ROLLBACK. However, instead of using these statements, you may want to take advantage of the built-in transaction object to manage your transactions. Doing so may help make your code more readable and maintainable.

Connector/Net allows us to create a MySqlTransaction object that represents a transaction. Methods to the MySqlTransaction object allow us to commit and roll back our transaction, or to set the transaction isolation levels.

Example 17-17 shows an example of using these facilities in C#.

Example 17-17. Transaction management in C#

1 static void TferFunds(int FromAccount, int ToAccount, float TferAmount)

2 {

3 String TransSQL = "UPDATE account_balance " +

4 " SET balance=balance+?tfer_amount " +

5 "WHERE account_id=?account_id";

6 MySqlCommand TransCmd = new MySqlCommand(TransSQL, myConnection);

7 MySqlParameter P_tfer_amount = TransCmd.Parameters.Add("?tfer_amount",

8 MySqlDbType.Float);

9 MySqlParameter P_account_id = TransCmd.Parameters.Add("?account_id",

10 MySqlDbType.Int32);

11

12 MySqlTransaction myTransaction = myConnection.BeginTransaction( );

13 try

14 {

15 //Remove amount from from_account

16 P_tfer_amount.Value = TferAmount * -1;

17 P_account_id.Value = FromAccount;

18 TransCmd.ExecuteNonQuery( );

19 //Add amount to to_account;

20 P_tfer_amount.Value = TferAmount;

21 P_account_id.Value = ToAccount;

22 TransCmd.ExecuteNonQuery( );

23

24 myTransaction.Commit( );

25 Console.WriteLine("Transaction Succeeded");

26 }

27 catch (MySqlException TransException)

28 {

29 Console.WriteLine("Error in transaction: ");

30 Console.WriteLine(TransException.Message);

31 try

32 {

33 myTransaction.Rollback( );

34 Console.WriteLine("Transaction rollback");

35 }

36 catch (MySqlException RollbackException)

37 {

38 Console.WriteLine("Failed to rollback transaction:");

39 Console.WriteLine(RollbackException.Message);

40 }

41 }

42 }

The function is designed to transfer some money from one account to another. It is absolutely essential that both operations succeed or fail as a unit, and therefore they are enclosed within a transaction.

This is a relatively long example and ties in the use of parameters and exception handlers, so let us step through it line by line:

Line(s)

Explanation

3–9

Create a SQL UPDATE statement to adjust the account balance for a specific account. The statement includes parameters for the account ids and amounts, so we can reuse the statement to do both parts of the transfer and could also reuse it for subsequent transactions.

12

The BeginTransaction( ) method of the connection indicates the commencement of the transaction.

13

Declare a try/catch block that will handle any errors that occur within our transaction.

15–22

Execute the transfer by placing the appropriate values into the account and amount parameters, and then executing the UPDATE statement twice— once to reduce the balance in the "from" account and once to increase the balance in the "to" account.

24

Commit the transaction. Note that this statement would be reached only if all of the previous statements succeed. If any of the previous ADO.NET statements raised an exception, control would be assumed by the code in the catch block.

27–41

This is the catch block that will be invoked if a SQL error occurs. It executes a ROLLBACK statement (line 33) to undo any parts of the transaction that may have successfully executed.

31–41

We've nested another catch block without the main error handler to catch any problems that occur when we execute the rollback. This might seem a bit paranoid, but it is possible that the errors that caused the statements to fail will also cause us to fail to execute a rollback (the server may have crashed, for instance).

Example 17-18 implements the same transaction logic in VB.NET .

Example 17-18. Transaction handling in VB.NET

Sub TferFunds(ByVal FromAccount As Integer, _

ByVal ToAccount As Integer, _

ByVal TferAmount As Single)

Dim TransSQL As String = "UPDATE account_balance " + _

" SET balance=balance+?tfer_amount " + _

"WHERE account_id=?account_id"

Dim TransCmd As MySqlCommand = New MySqlCommand(TransSQL, myConnection)

Dim P_tfer_amount As MySqlParameter = _

TransCmd.Parameters.Add("?tfer_amount", MySqlDbType.Float)

Dim P_account_id As MySqlParameter = _

TransCmd.Parameters.Add("?account_id", MySqlDbType.Int32)

Dim myTransaction As MySqlTransaction = myConnection.BeginTransaction

Try

'Remove amount from FromAccount

P_tfer_amount.Value = TferAmount * -1

P_account_id.Value = FromAccount

TransCmd.ExecuteNonQuery( )

'Add amount to ToAccount

P_tfer_amount.Value = TferAmount

P_account_id.Value = ToAccount

TransCmd.ExecuteNonQuery( )

myTransaction.Commit( )

Console.WriteLine("Transaction Succeded")

Catch TransException As MySqlException

Console.WriteLine("Error in transaction: ")

Console.WriteLine(TransException.Message)

Try

myTransaction.Rollback( )

Console.WriteLine("Transaction rollback")

Catch RollbackException As MySqlException

Console.WriteLine("Failed to rollback transaction:")

Console.WriteLine(RollbackException.Message)

End Try

End Try

End Sub

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.

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 procedure—the 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( );

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 1–3 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 finally—on line 9—we 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( )

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)+"\t");

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( )

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.

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)+"\t");

18 }

19 Console.WriteLine("\n-----------------------------------");

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)+"\t");

26 }

27 Console.WriteLine( );

28 }

29 } while (EmpReader.NextResult( ));

30 EmpReader.Close( );

31 }

Let's step through this example:

Line(s)

Explanation

3–5

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.

8–9

Assign the value of the stored procedure parameter to the value of the input parameter and create a MySqlDataReader to process the result sets.

11–29

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.

15–18

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.

21–28

Loop through each row in the result set.

23–26

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 Get Type (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

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

2–4

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.

11–13

Iterate through the columns in the current DataTable using the Columns collection and print the column name.

16–21

Iterate through the DataRows in the current DataTable using the Rows collection of the DataTable object.

17–19

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( ) + "\t");

}

Console.WriteLine( );

foreach (DataRow SpRow in SpTable.Rows)

{

foreach (DataColumn SpCol in SpTable.Columns)

{

Console.Write(SpRow[SpCol] + "\t");

}

Console.WriteLine( );

}

}

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 bugs—especially 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

Tip

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

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)

Using Stored Programs in ASP.NET

In the final section of this chapter, let's put our newly acquired Connector/Net and stored program skills to work to create a simple ASP.NET application.

The stored procedure we will use is shown in Example 17-41. It takes as an (optional) argument a database name, and it reports on the objects within that database, along with a list of users currently connected to the server, server status variables, server configuration variables, and a list of databases contained within the server. It contains one OUT parameter that reports the server version.

Example 17-41. Stored procedure for our ASP.NET example

CREATE PROCEDURE sp_mysql_info

(in_database VARCHAR(60),

OUT server_version VARCHAR(100))

READS SQL DATA

BEGIN

DECLARE db_count INT;

SELECT @@version

INTO server_version;

SELECT 'Current processes active in server' as table_header;

SHOW full processlist;

SELECT 'Databases in server' as table_header;

SHOW databases;

SELECT 'Configuration variables set in server' as table_header;

SHOW global variables;

SELECT 'Status variables in server' as table_header;

SHOW global status;

SELECT COUNT(*)

INTO db_count

FROM information_schema.schemata s

WHERE schema_name=in_database;

IF (db_count=1) THEN

SELECT CONCAT('Tables in database ',in_database) as table_header;

SELECT table_name

FROM information_schema.tables

WHERE table_schema=in_database;

END IF;

END$$

The number and type of result sets is unpredictable, since a list of database objects is generated only if a database matching the stored procedure's first parameter is found on the server.

Prior to every major result set, the stored procedure generates a one-row "title" as a result set. This "title" result set is identified by the column title table_header.

First, we need to create an ASP.NET form to retrieve the information we need to connect to the MySQL server and to obtain the parameters we need to call the stored procedure.

Creating the input form in Visual Studio is fairly straightforward. We create TextBox controls to retrieve our input parameters, as shown in Figure 17-3.

ASP.NET form

Figure 17-3. ASP.NET form

Notice that in addition to the standard TextBox controls, we also added Literal and PlaceHolder controls. These controls allow us to insert dynamic content when the stored procedure is executed.

Next, we add the code that controls the database interaction. All of our database interaction logic is contained within the method associated with the Submit button. This logic is shown in Example 17-42.

Example 17-42. Database access logic for our ASP.NET page

1 void FindButton_Click(object sender, EventArgs e)

2 {

3 //Arrays of grids and literals for our output.

4 System.Web.UI.WebControls.DataGrid[] DataGrids;

5 DataGrids = new System.Web.UI.WebControls.DataGrid[20];

6 System.Web.UI.WebControls.Literal[] Literals;

7 Literals = new System.Web.UI.WebControls.Literal[20];

8

9

10 String myConnectionString = "Database=" + tDatabase.Text +

11 " ;Host=" + tHost.Text +

12 ";UserName=" + tUsername.Text+ ";Password=" + tPassword.Text;

13

14

15 MySqlConnection myConnection = new MySqlConnection( );

16 myConnection.ConnectionString = myConnectionString;

17

18 try

19 {

20 myConnection.Open( );

21 MySqlCommand SpCmd = new MySqlCommand("sp_mysql_info", myConnection);

22 SpCmd.CommandType = CommandType.StoredProcedure;

23 MySqlParameter InDbParm = SpCmd.Parameters.Add(

24 "in_database",MySqlDbType.String);

25 InDbParm.Value = tDatabase.Text;

26 MySqlParameter OutMyVersion = SpCmd.Parameters.Add(

27 "server_version", MySqlDbType.String);

28 OutMyVersion.Direction = ParameterDirection.Output;

29

30 MySqlDataAdapter MyAdapter = new MySqlDataAdapter(SpCmd);

31 MyAdapter.SelectCommand = SpCmd;

32 DataSet SpDataSet = new DataSet( );

33 MyAdapter.Fill(SpDataSet);

34

35 ReportHeaderl.Text = "<h1>MySQL Server status and statistics</h1>" +

36 "<b>Host:</b>"+tHost.Text+"<br>"+

37 " <b>Port:</b> "+tPort.Text+"<br>"+

38 "<b>Version:</b>"+OutMyVersion.Value+"<br>";

39

40 int grid_no = 0;

41 int heading_no=0;

42 foreach (DataTable SpTable in SpDataSet.Tables) {

43 if (SpTable.Columns[0].ColumnName == "table_header")

44 {

45 Literals[heading_no]=new Literal( );

46 Literals[heading_no].Text="<h2>"+ SpTable.Rows[0][0]+"</h2>";

47 PlaceHolder.Controls.Add(Literals[heading_no]);

48 heading_no++;

49 }

50 else

51 {

52 DataGrids[grid_no] = new DataGrid( );

53 DataGrids[grid_no].DataSource = SpTable;

54 DataGrids[grid_no].DataBind( );

55 DataGrids[grid_no].BorderWidth = 1;

56 DataGrids[grid_no].HeaderStyle.BackColor =

57 System.Drawing.Color.Silver;

58 PlaceHolder.Controls.Add(DataGrids[grid_no]);

59 grid_no++;

60 }

61 }

62

63

64

65 }

66 catch (MySqlException MyException)

67 {

68 Response.Write("Connection error: MySQL code: " + MyException.Number

69 + " " + MyException.Message);

70 }

71

72

73 }

There is quite a bit of code in this example, but the basic principles are fairly simple:

§ We connect to MySQL using the connection information given.

§ We call the stored procedure, passing the database name as an input parameter.

§ We cycle through the result sets in the stored procedure. If the result set is a one-line, one-column "title" for a subsequent result set, we store an HTML header into a literal control and add this to the Placeholder control we placed on the HTML form earlier.

§ If the result set is not a "title" result set, we bind the result set to a DataGrid control and add that to the Placeholder.

§ When all of the result sets have been processed, we retrieve the output parameter (MySQL version) and display this and other information in the Literal control we placed on the ASP.NET form earlier.

Let's examine this code in a bit more detail:

Line(s)

Explanation

4-7

Create an array of DataGrid and Literal controls. DataGrids are data-bound controls similar to HTML tables. Literals are controls in which we can insert regular HTML arguments. Later in the code, we will populate the controls in these arrays with data from the stored procedure output and insert the resulting controls into the Placeholder control on the ASPX page.

10–20

Construct a MySqlConnection string using the parameters provided in the input form and then establish a connection. The final connection call is embedded within a try/catch block so that we will handle any errors that might occur when attempting to connect.

21–28

Set up the stored procedure for execution. Both input and output parameters are defined.

30–31

Create a MySqlDataAdpator associated with the stored procedure.

23–33

Create a DataSet, and use the MySqlDataAdapter to populate the DataSet. This effectively executes the stored procedure and populates the DataSet with all the result sets from that stored procedure call.

35–38

Now that we have retrieved all of the result sets, we can access the value of the output parameter. Consequently, we can populate the Literal control with HTML to generate the first part of our report, which provides identity information for the MySQL server.

42–61

Generate the bulk of the report, which is based on the result sets generated from the stored procedure. This loop iterates through the DataTables contained within the DataSet.

43–49

If the first column within the table is called table_header, then this is a heading row, so we create a Literal containing an H2 HTML header containing the text of the row.

50–60

If the result set is not a heading, then we need to create an HTML table to represent the output. We use the ASP.NET DataGrid control, which is a data-bound table control. Line 53 attaches the DataGrid to the current DataTable. Lines 55–57 format the DataGrid. Finally on line 58 we add the DataGrid to the PlaceHolder control on the ASP.NET page.

Figure 17-4 shows some of the output generated by our ASP.NET application. The ASP.NET code can render virtually any output that might be returned by the stored procedure, so if we want to add a new set of output to the procedure, we do not need to modify the ASP.NET code.

Conclusion

In this chapter we looked at calling stored programs from within .NET code written in both C# and VB.NET.

Because of Microsoft's long history of stored procedures with SQL Server, support for stored programs in the ADO.NET interfaces is robust and feels very natural. There is no reason to avoid the use of stored programs in .NET applications, and no reason to avoid calling stored programs directly from .NET code.

ASP.NET form in action

Figure 17-4. ASP.NET form in action