Unified stored procedure calling method in .NET
Liu Zhibo
Disclaimer: This article has been published in "Computer Application" Volume 23 No. 11 Summary: In various system development, the use of stored procedures is a good habit, not only can brought a temporal table, function, cursor and other characteristics, but also Commissioning, upgrade, and maintenance are convenient. However, almost all of the storage procedures are all modes, the main difference is the parameters of each stored procedure. So, can you use a method to unify all stored procedures calls, reduce unnecessary programming? Based on the SQL Server database and ASP.NET, we implemented a unified call method, which only needs to provide the stored procedure name to be called, and provide a specific parameter value when it is called. . Keywords: stored procedure, system table, information structure view, ADO.NET document identification code: 2 practical technical results report (technology), theoretical learning and social practice summary (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 value 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, the stored procedure in the database is often called. However, almost all of the storage procedures are the same mode, the main difference is that each parameter type, value, etc. of the created are different. So, can you invoke all stored procedures through a function (or class)? In this paper, in principle of system table provided by the database, the method of unified call is implemented. This method only needs to provide a stored procedure name to be called, as well as the specific parameter value, which provides a specific parameter value, can implement any storage procedure.
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, the use of stored procedures is a good habit, which can not only bring characteristics, functions, cursors, but also debugging, upgrading, and maintenance. The data can be returned during the stored procedure, which provides more analysis and control over the data. In the call to the stored procedure, we found that the calling process is almost as follows:
1. Declare SqlConnection
2. Declare SQLCOMMAND, and set its connection attribute as a SQLConnection instance for the just declared, set commandName to store procedure names, CommandType is a stored procedure.
3. Add all stored procedure calls to the parameters required to add all stored procedures to the parameters collection of SQLCommand instances.
4. Call SQLCOMMANDECUTEREADER () method to get the return row of stored procedures
4. Declare the SqlDataAdapter and DataSet, set the instance of the SQLDataAdapter's selectcommand attribute, and then call its Fill method to fill the returned row to DataSet.
5. Close SqlConnection object
6. Example of each object of the declaration
(Description: 4 Refers to two data extraction methods) During this call, we found that almost all stored procedures calls are this mode, and the difference between the stored procedure names in step 2 is different and the third The parameters used by each stored procedure call in the steps are different. They have the difference between parameter name, direction, data type, length, etc. So, is there a way to implement all stored procedures calls? That is, it is only necessary to provide a stored procedure name, and then the parameter value can be transferred to the calling method to implement the calling process, and then save the returned rowset, the parameter value, and the process return value. After studying the system table of SQL Server, we found this idea is practical.
2. System Table and Information Structure View
All relational databases such as SQL Server use metadata in some way in the database, in SQL Server is the system database and system table. Four system databases are automatically generated after installing SQL Server: Master, Model, MSDB and Tempdb. The Master database is a warehouse for all system-level information in SQL Server. Log in to account, configuration settings, existence of the system stored procedures, and other databases are recorded in the Master database. The MSDB database saves the information of the SQL Server Agent. When you define jobs, operators, and alerts, they are stored in MSDB. Model is a model frame for all users generated databases. When you generate a new database, copy Model to create the object you want. Tempdb saves temporary objects in SQL Server. Displaying the generated temporary table and temporary stored procedures and temporary objects generated by the system utilize TEMPDB. [1] and have its own system table in each database. These system tables are used to save configuration and object information. From these system tables, we can get information about all parameters of each stored procedure. This information is saved in the syscolumns table. Among them, there are parameter names, types, lengths, directions, etc. need to use information in our way. However, the fields in the system table changes with the changes of the SQL Server version. For example, Type and Xtype in syscolumn is such a variation example, they all saved the type of information. To make our way to adapt to the SQL Server version change request, you should use the information structure view. ANSI-92 defines the information structure view as a view that provides system data. By using this view, the actual system table can be hidden from the application. The system table changes will not affect the application, so the application can be independent of the database manufacturers and versions. [1] ANSI-92 and SQL Server supports the objects on the local server with a three-segment name structure. ANSI-92 Terminology is called catalog.schema.object, and SQL Server is called Database.Owner.Object. [1] For example, we have to find all parameter information for all stored procedures, you can use: select * from information_schema.parameters If you want to find all parameter information for a stored procedure, it is: select * from information_schema.Parameters where specific_name = 'proc1 'With information structure view, our problem solves more than half. Let's see how to implement our way in .NET. 3. Implementation
The focus of implementation is placed on how to get all the parameter information according to the stored procedure, and then automatically create each parameter according to these parameter information. In order to automate these movements, declare the process of SQLConnection, SqlCommand, SQLParameter, and create each SQLParameter process should be invisible to users. The only user needs to provide the name of the stored procedure, and then provide each parameter when calling, and even their types do not need to be provided.
3.1 Access to and create parameters for stored procedures
How to get and create the parameters of the stored procedures to be called are a key point, and we can automate this step by information structure view.
// Get and create parameters for stored procedures
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"; sqldataareader reader = null;
Reader = mycommand2.executeRead ();
// Create a 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, you can automatically create the type, value, direction of the SQLParameter, etc. in this place.
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;
// ... omit a lot of specific types of processing
DEFAULT: BREAK;
}
i ;
Mycommand.Parameters.Add (MyParameter);
}
}
3.2 Return the result data set, return value, outgoing parameter set
After creating the parameters of the stored procedure, we can call this stored procedure. Due to the Class of the Common Return Result Sets in .NET, SqlDataReader must be used in the state of keeping the connection, but DataSet is not required. In our implementation, the connection should be disconnected after the call, so use DataSet to save the return result set.
Public SqlResult Call (params object [] parameters)
{
// SQLRESULT is a class defined for the saving result dataset, return value, and out parameter set.
SQLRESULT RESULT = New SqlResult ();
/ / Define your own connection string as needed
MyConnection = New SqlConnection (Connectionstring);
MyCommand = new sqlcommand (this.procedurename, myconnection);
Mycommand.commandtype = commandType.StoredProcedure; sqldataadapter myadapter = new sqldataadapter (MyCommand);
MyConnection.open ();
// Get and create parameters for stored procedures, and set a value
GetProcedureParameter (parameters);
Myadapter.fill (Result.DataSet, "Table");
// Get the outgoing parameter value and name pair of stored procedures, save it in a HashTable
GetOutputValue (Result);
/ / Release a variety of resources here, disconnect
myadapter.dispose ();
mycommand.dispose ();
MyConnection.Close ();
MyConnection.dispose ();
Return Result;
}
4. Further work
Although we are here to achieve the SQL Server database, this method can be used for any information structure view that provides an information structure, in line with the ANSI-92 standard, or providing metadata can be implemented. We package it into a SQLProcedure class. When you need it, you can call the stored procedure when you need it, and reduce the work of a large amount of code that is basically repeated. In order to support the SqlProcedure class support more data types, you need to analyze the types, directions, lengths, default values of each parameter according to your own needs, and then create this parameter. Basically any type is achievable, even the image type can be created in this way. This kind of this class can be very common and can play in any project. Reference [1] ray Rankins, Paul Jensen, Paul Bertucci, SQL Server 2000 Practical Book, Beijing: Electronic Industry Press, 2002 [2] MSDN Library January 2003, Microsoft Corporation. , Hunan New People, Master, Main Research Direction: Neural Network and Model Recognition, Office Automation Information System Email: Jasper_Liu@msn.com
