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.

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 = "

MySQL Server status and statistics

" + 36 "Host:"+tHost.Text+"
"+ 37 " Port: "+tPort.Text+"
"+ 38 "Version:"+OutMyVersion.Value+"
"; 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="

"+ SpTable.Rows[0][0]+"

"; 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.
1020	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.
2128	Set up the stored procedure for execution. Both input and output parameters are defined.
3031	Create a MySqlDataAdpator associated with the stored procedure.
2333	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.
3538	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.
4261	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.
4349	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.
5060	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 5557 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.