. Net

Abstract: using stored procedures is a good habit in developing projects. it provides temporary table, functions and cursors, and debugging, upgrading, maintainence can benefit from it too. however, almost all calling to a stored procedure is a same pattern, the main difference between them is the parameters of every stored procedure. then, can we call stored procedure in a same way in spite of their differences and reduce the programming code. we did it after studying SQL Server and. net. only information you provide is the stored procedure name and the values of its parameters, you needn't to create the parameters yourself.

Key word: stord procedure, system table, information schema, ADO. net

Abstract: stored procedures in databases are often called during project development. However, almost all stored procedures are called in the same mode. The main difference is that each parameter type and value are different. So can we call all stored procedures through a function (or class? This article uses the system table principle provided by the database to implement a unified call method. This method only needs to provide the name of the stored procedure to be called, you can call any stored procedure by providing specific parameter values during the call.

Abstract: we have to call stored procedures of database systems during a development of a project. however, calling a stored procedures are almost the same, the main difference is the difference between parameters 'Type or value etc. can we call any stored procedures through a function (or a class )? Based on the system tables provided by database systems, we wrote a class to call any stored procedures in this article. to call a stored procedure, the only parameters you provide are the name of the stored procedure and the value of all parameters of the stored procedure.

1. Introduction
In various system development, using stored procedures is a good habit. It not only brings temporary tables, functions, cursors, and other features, but also facilitates debugging, upgrade, and maintenance. Data can be processed and then returned in the stored procedure, which provides more data analysis and control.
In the calling of stored procedures, we found that the calling of stored procedures is almost the following mode:
1. Declare sqlconnection
2. Declare sqlcommand, set its connection attribute to the declared sqlconnection instance, set commandname to the stored procedure name, And commandtype to the stored procedure.
3. Add all parameters required for calling stored procedure to the parameters set of the declared sqlcommand instance
 
4. Call the executereader () method of sqlcommand to obtain the returned row set of the stored procedure.

4. Declare sqldataadapter and dataset, set the selectcommand attribute of sqldataadapter to the Instance declared in 3, and then call its fill method to fill the returned row set in Dataset

5. Close the sqlconnection object
6. Release declared object instances
(Note: 4 refers to two data extraction methods)
In this call process, we found that almost all stored procedure calls are in this mode, the difference is that the Stored Procedure names in step 1 are different, and the parameters used for calling each stored procedure in step 2 are different, they differ in parameter names, directions, data types, and lengths.
Is there a way to call all stored procedures? That is, you only need to provide the stored procedure name, and then pass the parameter value into the calling method to call the stored procedure, and then use some data structures to save the returned row set, outgoing parameter value, and process return value. After studying the SQL server system table, we found that this idea is feasible.
2. system table and Information Structure View
Relational databases such as SQL Server store metadata in a database in a certain way. in SQL Server, metadata is a system database and a system table. After SQL Server is installed, four system databases are automatically generated: Master, model, MSDB, and tempdb. The master database is the repository of all system-level information in SQL Server. Logon accounts, configuration settings, system stored procedures, and other databases are recorded in the master database. The MSDB database stores information about the SQL Server Agent. When defining jobs, operators, and alarms, they are stored in MSDB. A model is a model box used for databases generated by all users. When a new database is generated, copy the model to create the desired object. Tempdb saves temporary objects in SQL Server. Displays that the generated temporary tables, temporary stored procedures, and temporary objects all use tempdb. [1]
In addition, each database has its own system table. These system tables are used to save configuration and object information. From these system tables, we can obtain information about all the parameters of each stored procedure. This information is saved in the syscolumns table. The information in our method is required for parameters, type, length, and direction.
However, fields in the system table change with the SQL Server version. For example, type and xtype in syscolumns are such a change example. They both save the type information. To adapt our methods to SQL Server version changes, we need to use the information structure view.
The ANSI-92 defines an information structure view as a set of views that provide system data. By using this view, you can extract the actual system table from the application Program . Changes to the system table will not affect the application, so that the application can be independent from the database manufacturer and version. [1]
ANSI-92 and SQL server support referencing objects on the local server with a three-segment naming structure. ANSI-92 terminology is called catalog. schema. object, while SQL Server is called database. Owner. object. [1] For example, to find all the parameters of all stored procedures, you can use:
Select * From information_schema.parameters
If you want to find all the parameter information of a stored procedure, it is:
Select * From information_schema.parameters where specific_name = 'proc1'
With the information structure view, we have solved more than half of the problems. The following describes how to implement our method in. net.

3. Implementation Method
The focus of implementation is on how to obtain all its parameter information based on the stored procedure name, and then automatically create each parameter based on the parameter information. To automate these actions, the process of declaring sqlconnection, sqlcommand, and sqlparameter should be invisible to users when creating sqlparameter. The only thing users need to provide is the name of the stored procedure, and then each parameter is provided during the call, even their type is not required.
3.1 obtain and create stored procedure parameters
How to obtain and create parameters for the stored procedure to be called is a key point. We can automatically implement this step through the information structure view.
// Obtain and create parameters for the stored procedure
Private void getprocedureparameter (Params object [] parameters)
{
Sqlcommand mycommand2 = new sqlcommand ();

Mycommand2.connection = This. myconnection;
Mycommand2.commandtext = "select * From information_schema.parameters where specific_name = '" + this. procedurename + "'order by ordinal_position ";

Sqldatareader reader = NULL;
Reader = mycommand2.executereader ();

// Create return parameters
Myparameter = new sqlparameter ();
Myparameter. parametername = "@ value ";
Myparameter. sqldbtype = sqldbtype. Int;
Myparameter. Direction = parameterdirection. returnvalue;

Mycommand. Parameters. Add (myparameter );

Int I = 0;
// Create parameters. The sqlparameter type, value, direction, and other attributes can be automatically created here.
While (reader. Read ())
{
Myparameter = new sqlparameter ();

Myparameter. parametername = reader ["parameter_name"]. tostring ();
Myparameter. Direction = reader ["parameter_mode"]. tostring () = "in "? Parameterdirection. Input: parameterdirection. output;

Switch (Reader ["data_type"]. tostring ())
{
Case "int ":
If (myparameter. Direction = parameterdirection. Input)
Myparameter. value = (INT) parameters [I];
Myparameter. sqldbtype = sqldbtype. Int;

Break;
//... Omitted many specific types for processing
Default: break;
}
I ++;

Mycommand. Parameters. Add (myparameter );
}

}
3.2 return result dataset, return value, and outgoing parameter set
After the stored procedure parameters are created, we can call the stored procedure. In. net, the classes of commonly used returned result sets are sqldatareader and dataset, while sqldatareader can be used only when the connection is maintained, but dataset does not. In our implementation, the connection should be closed after the call, so dataset is used to save the returned result set.

Public sqlresult call (Params object [] parameters) {// sqlresult is a class defined by myself for saving the result dataset, return value, and outgoing parameter set sqlresult result = new sqlresult (); // define your own connection string myconnection = new sqlconnection (connectionstring); mycommand = new sqlcommand (this. procedurename, myconnection); mycommand. commandtype = commandtype. storedprocedure; sqldataadapter myadapter = new sqldataadapter (mycommand); myconnection. open (); // obtain and create the parameters of the stored procedure, and set the value getprocedureparameter (parameters); myadapter. fill (result. dataset, "table"); // obtain the outgoing parameter value and name pair of the stored procedure and save it in a hashtable getoutputvalue (result); // release various resources here, disconnect myadapter. dispose (); mycommand. dispose (); myconnection. close (); myconnection. dispose (); return result;} 4. further work
Although our implementation here is for the SQL Server database, but for any provided information structure view, compliant with ANSI-92 standards,
Or databases that provide metadata can use this method. We encapsulate it into a sqlprocedure class. When needed, we can easily call the stored procedure, which reduces the number of duplicates. Code Work.
To enable the sqlprocedure class to support more data types, the getprocedureparameter () method needs to analyze the type, direction, length, default value, and other information of each parameter as needed, then create this parameter. Basically, any type can be implemented, and even the image type can be created in this way. In this way, this class can be very common and can play a role in any project.