SQLServer2000数据访问基类

80酷酷网    80kuku.com

　　server|server2000|sql|sqlserver|访问|数据

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

分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间

上一篇如何在调用线程的时候传递参数

下一篇C# FAQ: const和static readonly有什么区别？

相关文章

点击：