Statement: This article has been published in "Computer Applications" Volume 23 Issue 11
Abstract: In the development of various systems, using stored procedures is a good habit, which not only brings features such as temporary tables, functions, and cursors, but also Debugging, upgrading, and maintenance become convenient. However, almost all stored procedure calls are in the same mode, and the main difference is that the parameters of each stored procedure are different. So, is it possible to use a method to unify all stored procedure calls and reduce unnecessary programming? Based on the study of SQL Server database and ASP.NET, we implemented a unified calling method. This method only needs to provide the name of the stored procedure to be called, and provide specific parameter values when calling, so that any stored procedure can be called. .
Keywords: stored procedure, system table, information structure view, ADO.NET Document identification code: ② Practical technical achievement report (technology), summary of theoretical learning and social practice (social science)
Call stored procedures in a same way in .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
Summary: In the development of a project, you often call the stored procedures. However, almost all stored procedure calls are in the same pattern. The main difference lies in the type and value of each parameter created. So, is it possible to call all stored procedures through one function (or class)? This article implements a unified calling method based on the principle of using the system tables provided by the database. This method only needs to provide the name of the stored procedure to be called, and provide specific parameter values when calling, so that any stored procedure can be called.
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.
<DIV class=text4><B>1． Introduction</B></DIV>
In the development of various systems, using stored procedures is a good habit. It not only brings features such as temporary tables, functions, and cursors, but also makes debugging, upgrades, and maintenance easier. During the storage process, the data can be processed and then returned, which can provide more analysis and control of the data. In the stored procedure calls, we found that the stored procedure calls are almost in the following pattern:
1. Declare SqlConnection
2. Declare SqlCommand, and set its Connection property to the SqlConnection instance just declared, set CommandName to the stored procedure name, and CommandType to the stored procedure.
3． Add all the parameters required for stored procedure calls to the Parameters collection of the SqlCommand instance just declared 4. Call the ExecuteReader() method of SqlCommand to get the row set returned by the stored procedure.
4. Declare SqlDataAdapter and DataSet, set the SelectCommand property of SqlDataAdapter to the instance declared in 3, and then call its Fill method to fill the returned row set into the DataSet.
5. Close the SqlConnection object
6． Release each declared object instance (Note: 4 refers to the two data extraction methods). During this call process, we found that almost all stored procedure calls are in this mode. The difference lies in the storage in step 2. The different process names are different from the parameters used in each stored procedure call in step 3. They have differences in parameter names, directions, data types, lengths, etc. So, is there a way to implement all stored procedure calls? That is, you only need to provide the stored procedure name, and then pass the parameter values into the calling method to realize the stored procedure call, and then use certain data structures to save the returned row set, outgoing parameter values, and process return values. After studying the system tables of SQL Server, we found that this idea is feasible.
2. System tables and information structure views
Relational databases such as SQL Server store metadata in the database in some way. In SQL Server, it is the system database and system tables. After installing SQL Server, four system databases will be automatically generated: master, model, msdb and tempdb. The master database is the warehouse of all system-level information in SQL Server. Login accounts, configuration settings, system stored procedures, and the existence of other databases are recorded in the master database. The msdb database stores SQL Server Agent information. When jobs, operators and alerts are defined, they are stored in msdb. model is a template for all user-generated databases. When generating a new database, copy the model and create the required objects. tempdb saves temporary objects in SQL Server. Display generated temporary tables and temporary stored procedures as well as system generated temporary objects all utilize tempdb. [1] And each database has its own system tables. These system tables are used to store configuration and object information. From these system tables, we can get information about all parameters of each stored procedure. This information is stored in the syscolumns table. There are parameter names, types, lengths, directions, etc. that need to be used in our method. However, the fields in the system tables will change with the SQL Server version. For example, type and xtype in syscolumns are an example of such changes. They both store type information. To adapt our method to the version changes of SQL Server, we need to use the information structure view. ANSI-92 defines information structure views as a set of views that provide system data. By leveraging this view, you can hide the actual system tables from your application. Changes to system tables will not affect applications, so applications can be independent of database vendors and versions. [1] ANSI-92 and SQL Server support using a three-segment naming structure to reference objects on the local server. The ANSI-92 terminology is called catalog.schema.object, while SQL Server calls it database.owner.object. [1] For example, if we want to find all the parameter information of all stored procedures, we can use: select * from INFORMATION_SCHEMA.PARAMETERS. If we want to find all the parameter information of a certain stored procedure, it is: select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME ='Proc1 ' With the information structure view, more than half of our problems have been solved. Next we look at how to implement our method in .NET.
3． The focus of the implementation method is on how to obtain all the parameter information of the stored procedure based on its name, and then automatically create each parameter based on these parameter information. In order to automate these actions, the process of declaring SqlConnection, SqlCommand, and SqlParameter, and the process of creating each SqlParameter should be invisible to the user. The only thing the user needs to provide is the name of the stored procedure, and then provides various parameters when calling, and even their types do not need to be provided.
3.1 Obtaining and creating the parameters of the stored procedure How to obtain and create the parameters of the stored procedure to be called is a key point. We can automatically implement this step through the information structure view.
// Get and create parameters of 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 parameter
myParameter = new SqlParameter();
myParameter.ParameterName = "@Value";
myParameter. SqlDbType = SqlDbType.Int;
myParameter.Direction = ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myParameter);
int i = 0; // Create each parameter. Here you can automatically create the SqlParameter type, value, direction and other attributes
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; //...a lot of specific type processing is omitted
default : break; }
i++;
myCommand.Parameters.Add(myParameter);
}
}
3.2 Return result data set, return value, and outgoing parameter set. After creating the parameters of the stored procedure, we can call the stored procedure. Because in .NET, the commonly used classes that return result sets are SqlDataReader and DataSet, and SqlDataReader can only be used while maintaining a connection, but DataSet does not. In our implementation, the connection should be disconnected after the call, so a DataSet is used to save the returned result set.
public SqlResult Call(params object[] parameters){ // SqlResult is a self-defined class used to save result data sets, return values, and outgoing parameter sets. SqlResult result = new SqlResult(); // Define your own connection as needed String
myConnection = new SqlConnection(ConnectionString);
myCommand = new SqlCommand(this.ProcedureName, myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
myConnection.Open(); // Get and create Store the parameters of the procedure and set the values
GetProcedureParameter(parameters);
myAdapter.Fill(result.dataSet, "Table"); // Get the outgoing parameter value and name pair of the stored procedure and save it in a Hashtable GetOutputValue(result) ; // Release various resources here and disconnect
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
return result;}
4. Further work Although our implementation here is for the SQL Server database, this method can be used for any database that provides information structure views, complies with the ANSI-92 standard, or provides metadata. We encapsulate it into a SqlProcedure class, and the stored procedure can be easily called when needed, reducing a lot of basically repetitive code work. In order for the SqlProcedure class to support more data types, in the GetProcedureParameter() method, you need to analyze the type, direction, length, default value and other information of each parameter according to your own needs, and then create this parameter. Basically any type can be implemented, even image types can be created in this way. This way the class can be very general and useful in any project.
References
[1] Ray Rankins, Paul Jensen, Paul Bertucci, SQL Server 2000 Practical Book, Beijing: Electronic Industry Press, 2002
[2] MSDN Library January 2003, Microsoft Corporation.
About the author: Liu Zhibo (1979-), male, from Xinhua, Hunan, master's degree, main research directions: neural network and pattern recognition, office automation information system
email:[email protected]