当前位置：首页 > 软件开发 > dev
firefox

SQLServer2000数据访问基类

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;
            }
        }   

 ↓相关文章：
© 2006-2008 All Rights Reserved