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:

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.

Категории