最近有个网友问我转载的文章《.NET中统一的存储过程调用方法(收藏) 》具体该如何实现.
于是我写了代码完成了此功能.代码中有两个类
一个是用来做返回值的载体的用一个DataSet返回查询出的数据,用一个Hashtable返回存储过程的返回值和输出参数.
using System;
using System.Data;
using System.Collections;
namespace DDLLY
{
/**//// <summary>
/// SqlResult 的摘要说明。
/// </summary>
public class SqlResult
{
public DataSet MyDataSet=new DataSet();
public Hashtable ReturnVal=new Hashtable();
}
}
using System.Data;
using System.Collections;
namespace DDLLY
{
/**//// <summary>
/// SqlResult 的摘要说明。
/// </summary>
public class SqlResult
{
public DataSet MyDataSet=new DataSet();
public Hashtable ReturnVal=new Hashtable();
}
}
这个类是具体的实现,代码并不复杂,这里我也就不罗嗦了.只需要注意方法的参数是可变参数.
using System;
using System.Data;
using System.Data.SqlClient;
namespace DDLLY
{
/**//// <summary>
/// SQLProcHelper 的摘要说明。
/// </summary>
public class SQLProcHelper
{
//存储过程名
private String procedureName=null;
//连接字符串
private String connectionString=null;
private SqlConnection myConnection=new SqlConnection();
private SqlCommand myCommand=new SqlCommand();
private SqlParameter myParameter=new SqlParameter();
存储过程名#region 存储过程名
public String ProcedureName
{
get
{
return procedureName;
}
set
{
procedureName=value;
}
}
#endregion
连接字符串#region 连接字符串
public String ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString=value;
}
}
#endregion
构造函数#region 构造函数
public SQLProcHelper(){}
public SQLProcHelper(String ProcedureName,String ConnectionString)
{
procedureName=ProcedureName;
connectionString=ConnectionString;
}
#endregion
调用存储过程#region 调用存储过程
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.MyDataSet, "Table");
// 获得存储过程的传出参数值和名字对,保存在一个Hashtable中
GetOutputValue(result);
// 在这里释放各种资源,断开连接
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
return result;
}
#endregion
获得存储过程的参数#region 获得存储过程的参数
private void GetProcedureParameter(params object[] parameters)
{
SqlDataReader reader = null;
try
{
SqlCommand myCommand2 = new SqlCommand();
myCommand2.Connection = this.myConnection;
myCommand2.CommandText = "select PARAMETER_NAME,PARAMETER_MODE,DATA_TYPE from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
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().ToUpper())
{
case "NVARCHAR":
myParameter.SqlDbType =SqlDbType.NVarChar;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "VARCHAR":
myParameter.SqlDbType = SqlDbType.VarChar;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "BIT":
myParameter.SqlDbType = SqlDbType.Bit;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(bool)parameters[i];
}
break;
case "BIGINT":
myParameter.SqlDbType = SqlDbType.BigInt;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(int)parameters[i];
}
break;
case "CHAR":
myParameter.SqlDbType = SqlDbType.Char;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "DATETIME":
myParameter.SqlDbType = SqlDbType.DateTime;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(DateTime)parameters[i];
}
break;
case "DECIMAL":
myParameter.SqlDbType = SqlDbType.Decimal;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(Decimal)parameters[i];
}
break;
case "FLOAT":
myParameter.SqlDbType = SqlDbType.Float;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(float)parameters[i];
}
break;
// case "IMAGE":
// myParameter.SqlDbType = SqlDbType.Image;
// break;
case "INT":
myParameter.SqlDbType = SqlDbType.Int;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(int)parameters[i];
}
break;
case "MONEY":
myParameter.SqlDbType = SqlDbType.Money;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(Decimal)parameters[i];
using System.Data;
using System.Data.SqlClient;
namespace DDLLY
{
/**//// <summary>
/// SQLProcHelper 的摘要说明。
/// </summary>
public class SQLProcHelper
{
//存储过程名
private String procedureName=null;
//连接字符串
private String connectionString=null;
private SqlConnection myConnection=new SqlConnection();
private SqlCommand myCommand=new SqlCommand();
private SqlParameter myParameter=new SqlParameter();
存储过程名#region 存储过程名
public String ProcedureName
{
get
{
return procedureName;
}
set
{
procedureName=value;
}
}
#endregion
连接字符串#region 连接字符串
public String ConnectionString
{
get
{
return connectionString;
}
set
{
connectionString=value;
}
}
#endregion
构造函数#region 构造函数
public SQLProcHelper(){}
public SQLProcHelper(String ProcedureName,String ConnectionString)
{
procedureName=ProcedureName;
connectionString=ConnectionString;
}
#endregion
调用存储过程#region 调用存储过程
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.MyDataSet, "Table");
// 获得存储过程的传出参数值和名字对,保存在一个Hashtable中
GetOutputValue(result);
// 在这里释放各种资源,断开连接
myAdapter.Dispose();
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
return result;
}
#endregion
获得存储过程的参数#region 获得存储过程的参数
private void GetProcedureParameter(params object[] parameters)
{
SqlDataReader reader = null;
try
{
SqlCommand myCommand2 = new SqlCommand();
myCommand2.Connection = this.myConnection;
myCommand2.CommandText = "select PARAMETER_NAME,PARAMETER_MODE,DATA_TYPE from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" +this.ProcedureName+ "' order by ORDINAL_POSITION";
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().ToUpper())
{
case "NVARCHAR":
myParameter.SqlDbType =SqlDbType.NVarChar;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "VARCHAR":
myParameter.SqlDbType = SqlDbType.VarChar;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "BIT":
myParameter.SqlDbType = SqlDbType.Bit;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(bool)parameters[i];
}
break;
case "BIGINT":
myParameter.SqlDbType = SqlDbType.BigInt;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(int)parameters[i];
}
break;
case "CHAR":
myParameter.SqlDbType = SqlDbType.Char;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(string)parameters[i];
}
break;
case "DATETIME":
myParameter.SqlDbType = SqlDbType.DateTime;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(DateTime)parameters[i];
}
break;
case "DECIMAL":
myParameter.SqlDbType = SqlDbType.Decimal;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(Decimal)parameters[i];
}
break;
case "FLOAT":
myParameter.SqlDbType = SqlDbType.Float;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(float)parameters[i];
}
break;
// case "IMAGE":
// myParameter.SqlDbType = SqlDbType.Image;
// break;
case "INT":
myParameter.SqlDbType = SqlDbType.Int;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(int)parameters[i];
}
break;
case "MONEY":
myParameter.SqlDbType = SqlDbType.Money;
if(myParameter.Direction == ParameterDirection.Input)
{
myParameter.Value=(Decimal)parameters[i];