using system;
using system.io;
using system.data;
using system.data.sqlclient;
using system.configuration;
using system.collections;

namespace sqlserverbase
{
    /// <summary>
    ///內部類:存儲過程的返回值記錄類
    /// </summary>
    public class sqlresult
    {       
        public bool succeed;            //存儲過程是否執行成功.
        public hashtable outputvalues;    // 存儲過程output值,放在(hashtable)表outputvalues里.           
        public datatable datatable;        //存儲過程返回的結果集,放在(datatable)表datatable里.
        public dataset dataset;            //存儲過程返回的結果集,放在dataset表中
        public string errormessage;        //訪問數據庫失敗
        public int inflecntnum;
        public sqlresult()
        {
            succeed = false;
            outputvalues = new hashtable();
            datatable=new datatable();
            dataset=new dataset();
            errormessage = "";
        }
    }
    /// <summary>
    /// ====================***調用存儲過程和sql的基類***============================
    /// abstract:該類不能被實例化,只能通過派生子類來使用它
    /// </summary>
    public abstract class spsql_base : idisposable
    {
        public spsql_base() : this("","")
        {
        }
        //重載
        public spsql_base(string sp_name,string sql_name)
        {
            this.procedurename = sp_name;
            this.sqlname = sql_name;
        }
        //私有成員變量
        private string sp_name;
        private string sql_name;
        private sqlconnection myconnection;
        private sqlcommand mycommand;
        private sqlparameter myparameter;//存儲過程參數
       
        //公共屬性
        public string procedurename//獲取和設置存儲過程名
        {
            get
            {
                return this.sp_name;
            }
            set
            {
                this.sp_name = value;
            }
        }
        //公共屬性
        public string sqlname//獲取和設置存儲過程名
        {
            get
            {
                return this.sql_name;
            }
            set
            {
                this.sql_name = value;
            }
        }
        /// <summary>
        /// 調用存儲過程
        /// </summary>
        /// <param name="parameters">參數集合</param>
        /// <returns></returns>
        public  sqlresult call_sp(params object[] parameters)
        {
            string strconn=configurationsettings.appsettings["connectionstring"];
            //存儲過程的返回值記錄類
            sqlresult result = new sqlresult();
            myconnection  = new sqlconnection(strconn);           
            mycommand = new sqlcommand(this.procedurename, myconnection);
            mycommand.commandtype = commandtype.storedprocedure;           
            sqldataadapter myadapter = new sqldataadapter(mycommand);   
            myconnection.open();
            //將參數添加到存儲過程的參數集合
            getprocedureparameter(result,parameters);
            //開始事物
            using(sqltransaction trans = myconnection.begintransaction())
            {
                try
                {                   
                    if(trans!=null)
                    {
                        mycommand.transaction = trans;
                    }
                    //填充數據,將結果填充到sqlresult集中
                    myadapter.fill(result.dataset);
                    if(result.dataset.tables.count>0)
                        result.datatable=result.dataset.tables[0].copy();
                    //將輸出參數的值添加到result的outputvalues
                    getoutputvalue(result);
                    //提交事物
                    trans.commit();
                }
                catch(exception e)
                {
                    result.errormessage = e.message;
                    //事物回滾
                    trans.rollback();
                }
                //如果捕捉了異常，但仍會執行包括在 finally 塊中的輸出語句
                finally
                {
                    myadapter.dispose();
                    mycommand.dispose();
                    myconnection.close();
                    myconnection.dispose();
                }
            }
            return result;
        }
        /// <summary>
        /// 將參數添加到存儲過程的參數集合
        /// </summary>
        /// <param name="parameters"></param>
        private void getprocedureparameter(sqlresult result,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;
            try
            {
                reader = mycommand2.executereader();
                int i = 0;
                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())
                    {
                            //bigint
                        case "bigint":
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.toint64(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.bigint;
                            break;
                            //binary

                            //bit
                        case "bit" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.toboolean(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.bit;
                            break;
                            //char
                        case "char" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (string)parameters[i];
                            myparameter.size = convert.toint32(reader["character_maximum_length"]);
                            myparameter.sqldbtype = sqldbtype.char;
                            break;
                            //datetime
                        case "datetime" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.todatetime(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.datetime;
                            break;
                            //decimal
                        case "decimal" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (decimal)parameters[i];
                            myparameter.sqldbtype = sqldbtype.decimal;
                            myparameter.precision = (byte)reader["numeric_precision"];
                            myparameter.scale = byte.parse(reader["numeric_scale"].tostring());
                            break;
                            //float
                        case "float" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (float)parameters[i];
                            myparameter.sqldbtype = sqldbtype.float;
                            break;
                            //image
                        case "image" :
                            if(myparameter.direction == parameterdirection.input)
                            {
                                myparameter.value=(byte[])parameters[i];                           
                            }
                            myparameter.sqldbtype = sqldbtype.image;
                            break;
                            //int
                        case "int" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.toint32(parameters[i].tostring());
                            myparameter.sqldbtype = sqldbtype.int;
                            break;
                            //money
                        case "money":
                            if(myparameter.direction==parameterdirection.input)
                                myparameter.value=convert.todecimal(parameters[i]);
                            myparameter.sqldbtype=sqldbtype.money;
                            break;
                            //nchar
                        case "nchar" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (string)parameters[i];
                            myparameter.size = convert.toint32(reader["character_maximum_length"]);
                            myparameter.sqldbtype = sqldbtype.nchar;
                            break;
                            //ntext
                        case "ntext" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (string)parameters[i];
                            myparameter.sqldbtype = sqldbtype.ntext;
                            break;
                            //numeric
                        case "numeric" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (decimal)parameters[i];
                            myparameter.sqldbtype = sqldbtype.decimal;
                            myparameter.precision = (byte)reader["numeric_precision"];
                            myparameter.scale = byte.parse(reader["numeric_scale"].tostring());
                            break;
                            //nvarchar
                        case "nvarchar" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.tostring(parameters[i]);
                            myparameter.size = convert.toint32(reader["character_maximum_length"]);
                            myparameter.sqldbtype = sqldbtype.nvarchar;
                            break;
                            //real
                        case "real":
                            if(myparameter.direction==parameterdirection.input)
                                myparameter.value=convert.tosingle(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.real;
                            break;   
                            //smalldatetime
                        case "smalldatetime" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.todatetime(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.datetime;
                            break; 
                            //smallint
                        case "smallint" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.toint16(parameters[i].tostring());
                            myparameter.sqldbtype = sqldbtype.smallint;
                            break;
                            //smallmoney
                        case "smallmoney":
                            if(myparameter.direction==parameterdirection.input)
                                myparameter.value=convert.todecimal(parameters[i]);
                            myparameter.sqldbtype=sqldbtype.smallmoney;
                            break;
                            //sql_variant

                            //text
                        case "text" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (string)parameters[i];
                            myparameter.sqldbtype = sqldbtype.text;
                            break;
                            //timestamp

                            //tinyint
                        case "tinyint":
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = convert.tobyte(parameters[i]);
                            myparameter.sqldbtype = sqldbtype.tinyint;
                            break;
                            //uniqueidentifier

                            //varbinary
                        case "varbinary":
                            if(myparameter.direction==parameterdirection.input)
                                myparameter.value=(byte[])parameters[i];
                            myparameter.sqldbtype = sqldbtype.varbinary;
                            break;
                            //varchar
                        case "varchar" :
                            if(myparameter.direction == parameterdirection.input)
                                myparameter.value = (string)parameters[i];
                            myparameter.size = convert.toint32(reader["character_maximum_length"]);
                            myparameter.sqldbtype = sqldbtype.varchar;
                            break;
                        default :
                            break;
                    }
                    i++;
                    mycommand.parameters.add(myparameter);
                }
            }
            catch(exception e)
            {
                result.errormessage = e.message;
            }
            finally
            {
                if(reader!=null)
                {
                    reader.close();
                }
                mycommand2.dispose();
            }
        }
        /// <summary>
        /// 將輸出的值添加到result的outputvalues
        /// </summary>
        /// <param name="result"></param>
        private void getoutputvalue(sqlresult result)
        {
            if(result.succeed==false)
            {
                result.succeed=true;
            }
            foreach(sqlparameter parameter in mycommand.parameters)
            {
                if(parameter.direction == parameterdirection.output)
                {
                    //hashtab表是一個鍵值對
                    result.outputvalues.add(parameter.parametername, parameter.value);
                }
            }
        }
        public void dispose()
        {
            dispose(true);
            gc.suppressfinalize(true);
        }
        protected virtual void dispose(bool disposing)
        {
            if (! disposing)
                return;
            if(myconnection != null)
            {
                myconnection.dispose();
            }
        }
        //=======end======

        //=======begin====
        /// <summary>
        /// 調用sql的基類
        /// </summary>
        /// <param name="parameters">參數集合</param>
        /// <returns></returns>
        public sqlresult call_sql()
        {
            string strconn=configurationsettings.appsettings["connectionstring"];
            //存儲過程的返回值記錄類
            sqlresult result = new sqlresult();
            myconnection = new sqlconnection(strconn);
            mycommand = new sqlcommand(this.sql_name, myconnection);
            mycommand.commandtype = commandtype.text;
            sqldataadapter myadapter = new sqldataadapter(mycommand);       
            myconnection.open();
            using(sqltransaction trans  = myconnection.begintransaction())
            {
                try
                {
                    if(trans!=null)
                    {
                        mycommand.transaction = trans;
                    }           
                    //填充數據,將結果填充到sqlresult集中
                    myadapter.fill(result.datatable);
                    result.succeed = true;
                    //提交事物
                    trans.commit();
                }
                catch(exception e)
                {
                    result.succeed = false;
                    result.errormessage = e.message;
                }
                //如果捕捉了異常，但仍會執行包括在 finally 塊中的輸出語句
                finally
                {                   
                    myadapter.dispose();
                    mycommand.dispose();
                    myconnection.close();
                    myconnection.dispose();               
                }
            }
            return result;
        }
        //=======end=========
    }
}

繼承此類后直接調用，如下：db：northwind
public class datest : spsql_base
    {
        public datest()
        {}
        public sqlresult sqltest()
        {
            base.sqlname="select employeeid,lastname from dbo.employees";
            return base.call_sql();
        }
        public sqlresult sptest()
        {
            base.procedurename="custorderhist";
            return base.call_sp("alfki");
        }
    }配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appsettings>
    <add key="connectionstring" value="server=bim-7c67612053c;database=northwind;uid=sa;pwd=;" />
</appsettings>
</configuration>顯示查詢結果：
private void datashow_load(object sender, system.eventargs e)
        {
            datest da=new datest();
            if(da.sptest().succeed && da.sqltest().succeed)
            {
                this.datasp.datasource=da.sptest().datatable;
               
                this.datasql.datasource=da.sqltest().datatable;
            }
        }   

此類還有待完善，諸如存儲過程參數為//binary、//sql_variant、//timestamp、//uniqueidentifier這些類型時還不能執行查詢，其余bug請大家多多指正～～

感謝以前一起在二炮工作過的師兄們提供源代碼，小弟只是做簡單修改，謝謝各位師兄！

出處：shanvenleo blog