SQLServer2000數據訪問基類

2019-11-18 19:02:02

字體：大中小

來源：轉載

供稿：網友

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