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