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: 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．引言
在各种系统开发中，使用存储过程是一个良好的习惯，不仅可以带来临时表、函数、游标等特性，而且调试、升级、维护都变得方便。在存储过程中能够把数据经过处理再返回，这样能够对数据提供更多的分析和控制。
在存储过程的调用中，我们发现存储过程的调用都几乎是如下的模式：
1．声明SqlConnection
2．声明SqlCommand，并且设置其Connection属性为刚声明的SqlConnection实例，设置CommandName为存储过程名，CommandType为存储过程。
3．往刚声明的SqlCommand实例的Parameters集合中添加所有的存储过程调用需要的参数
 
4．呼叫SqlCommand的ExecuteReader()方法来得到存储过程的返回行集

4．声明SqlDataAdapter和DataSet，设置SqlDataAdapter的SelectCommand属性为3中声明的实例，再调用其Fill方法来把返回的行集填充到DataSet中
 
5．关闭SqlConnection对象
6．释放声明的各对象实例
（说明：4指的是两种数据提取方法）
在这个调用过程中，我们发现几乎所有的存储过程调用都是这个模式，之间的区别就在第2步中的存储过程名不同和第3步中各个存储过程调用使用的参数是不一样的，他们有参数名字、方向、数据类型、长度等的区别。
那么，有没有一种方法可以实现所有的存储过程调用？即只需要提供存储过程名，然后把参数值传入调用方法即可实现存储过程的调用，再用某些数据结构来保存返回的行集、传出参数值、过程返回值。经过研究SQL Server的系统表，我们发现这个想法是切实可行的。
2．系统表与信息结构视图
SQL Server等关系型数据库都将元数据以某种方式保存在数据库中，在SQL Server中就是系统数据库和系统表。安装SQL Server后会自动生成四个系统数据库：master, model, msdb与tempdb。master数据库是SQL Server中所有系统级信息的仓库。登录帐号、配置设置、系统存储过程和其他数据库的存在性都记录在master数据库中。msdb数据库保存SQL Server Agent的信息。定义作业、操作员和警报时，他们存放在msdb中。model是个模框，用于所有用户生成的数据库。生成新数据库时，将model复制，建立所要的对象。tempdb保存SQL Server中的临时对象。显示生成的临时表和临时存储过程以及系统生成的临时对象都利用tempdb。[1]
 而且每个数据库中都有自己的系统表。这些系统表被用来保存配置和对象信息。从这些系统表中，我们就可以得到每个存储过程的所有参数的信息。syscolumns表中就保存了这些信息。其中有参数名、类型、长度、方向等需要用到我们方法中的信息。
 不过，系统表中的字段会随着SQL Server版本的变化而变化。比如syscolumns中的type和xtype就是这样的一个变化例子，他们都保存了类型的信息。要让我们的方法适应SQL Server的版本变化要求，就要用到信息结构视图。
 ANSI-92将信息结构视图定义为一组提供系统数据的视图。通过利用该视图，可以将实际系统表从应用程序中隐藏起来。系统表的改变就不会影响到应用程序，这样应用程序就可以独立于数据库厂家和版本。[1]
 ANSI-92和SQL Server支持用三段命名结构引用本地服务器上的对象。ANSI-92术语称为catalog.schema.object，而SQL Server称为database.owner.object。[1]比如我们要找到所有存储过程的所有参数信息，就可以用：
 select * from INFORMATION_SCHEMA.PARAMETERS
如果要找到某个存储过程的所有参数信息，就是：
 select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME =’Proc1’
有了信息结构视图，我们的问题就解决了一大半了。下面我们看如何在.NET中实现我们的方法。

3．实现方法
实现的重点就放在如何根据存储过程名来得到它的所有的参数信息，再根据这些参数信息自动的创建各个参数。为了让这些动作自动化，声明SqlConnection、SqlCommand、SqlParameter的过程，创建各个SqlParameter的过程对用户来说都应该不可见。用户唯一需要提供的就是存储过程的名字，然后就是在调用的时候提供各个参数，甚至连他们的类型都不需要提供。

3．1获得和创建存储过程的参数
如何获得并且创建要调用的存储过程的参数是一个重点，通过信息结构视图我们可以自动的实现这个步骤。
// 获得和创建存储过程的参数
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();

// 创建返回参数
myParameter = new SqlParameter();
myParameter.ParameterName = "@Value";
myParameter.SqlDbType = SqlDbType.Int;
myParameter.Direction = ParameterDirection.ReturnValue;

myCommand.Parameters.Add(myParameter);

int i = 0;
// 创建各个参数,在这个地方可以自动的创建SqlParameter的类型,值,方向等属性
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;
//...省略了很多具体的类型处理
default : break;
}
i++;

myCommand.Parameters.Add(myParameter);
}

}
3．2返回结果数据集、返回值、传出参数集
创建好存储过程的参数之后，我们就可以调用这个存储过程了。由于在.NET中，常用的返回结果集的类为SqlDataReader和DataSet，而SqlDataReader必须在保持连接的状态下才可以使用，DataSet却不需要。在我们的实现中，连接应该在调用之后就断开，因此采用DataSet来保存返回结果集。

public SqlResult Call(params object[] parameters)
{
	// SqlResult是自己定义的用于保存结果数据集、返回值、传出参数集的类
	SqlResult result = new SqlResult();

	// 根据需要定义自己的连接字符串
	myConnection  = new SqlConnection(ConnectionString);

	myCommand = new SqlCommand(this.ProcedureName, myConnection);
	myCommand.CommandType = CommandType.StoredProcedure;

	SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

	myConnection.Open();
	// 获得和创建存储过程的参数,并且设置好值
	GetProcedureParameter(parameters);

	myAdapter.Fill(result.dataSet, "Table");
	
	// 获得存储过程的传出参数值和名字对,保存在一个Hashtable中
	GetOutputValue(result);

	// 在这里释放各种资源,断开连接
	myAdapter.Dispose();
	myCommand.Dispose();
	myConnection.Close();
	myConnection.Dispose();


	return result;
}
4．进一步工作
虽然我们在这里的实现是针对SQL Server数据库，但是对于任何提供了信息结构视图，符合ANSI-92标准，
或者是提供了元数据的数据库都可以使用这种方法来实现。我们把它封装成一个SqlProcedure类，在需要的时候可以很简单的就调用了存储过程，减少了大量基本上是重复的代码工作。
 为了让SqlProcedure类支持更过的数据类型，在GetProcedureParameter()方法中需要根据自己的需要来分析各个参数的类型、方向、长度、默认值等信息，然后来创建这个参数。基本上任何类型都是能够实现的，甚至连image类型都可以采用这种方式创建。这样这个类就可以很通用，在任何项目中都可以发挥作用。