我转载的文章《.NET中统一的存储过程调用方法 》具体实现代码

 

我转载的文章《.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;
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];
                            }
                            break;
                        case "NCHAR":
                            myParameter.SqlDbType = SqlDbType.NChar;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(string)parameters[i];
                            }
                            break;
                        case "NTEXT":
                            myParameter.SqlDbType = SqlDbType.NText;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(string)parameters[i];
                            }
                            break;
                        case "REAL":
                            myParameter.SqlDbType = SqlDbType.Real;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(Double)parameters[i];
                            }
                            break;
                        case "TEXT":
                            myParameter.SqlDbType = SqlDbType.Text;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(string)parameters[i];
                            }
                            break;
                            //                    case "VARBINARY":
                            //                        myParameter.SqlDbType = SqlDbType.VarBinary;
                            //                        break;
                        case "SMALLDATETIME":
                            myParameter.SqlDbType = SqlDbType.SmallDateTime;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(DateTime)parameters[i];
                            }
                            break;
                        case "SMALLINT":
                            myParameter.SqlDbType = SqlDbType.SmallInt;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(int)parameters[i];
                            }
                            break;
                        case "SMALLMONEY":
                            myParameter.SqlDbType = SqlDbType.SmallMoney;
                            if(myParameter.Direction == ParameterDirection.Input)
                            {
                                myParameter.Value=(int)parameters[i];
                            }
                            break;
                            //                    case "TIMESTAMP":
                            //                        myParameter.SqlDbType = SqlDbType.Timestamp;
                            //                        break;
                        default:
                            throw new Exception("不支持的数据类型！");
                    }
                    i++;
 
                    myCommand.Parameters.Add(myParameter);
                }
            }
            finally
            {
                reader.Close();
            }
 
        }
        #endregion

        获得返回值参数#region 获得返回值参数
        private void GetOutputValue(SqlResult result)
        {
            //遍历所有参数
            foreach (SqlParameter para in myCommand.Parameters)
            {
                //如果是返回参数或者输出参数
                if (para.Direction!=ParameterDirection.Input)
                {
                    result.ReturnVal.Add(para.ParameterName,para.Value);
                }
            }
        }
        
        #endregion
    }
}

调用的时候只需要把参数直接做方法的参数即可.

    try
            {
                SQLProcHelper helper=new SQLProcHelper("byroyalty","server=ddl;database=pubs;uid=sa;pwd=wd");
                SqlResult result=helper.Call(100);
                dataGrid1.DataSource=result.MyDataSet.Tables[0];
            }
            catch(Exception ex)
            {
                label1.Text=ex.Message;
            }

由于这段代码只是做个测试,所以还存在不少小问题.