操作数据库的一个类

代码：

 

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

namespace chinafanbu.DataAccessLayer
{
    /// <summary>
    /// 数据库接口类
    /// </summary>
    public class DataBase
    {
        //私有变量，数据库连接
        protected SqlConnection Connection;
        protected string ConnectionString;

        //构造函数
        public DataBase()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }

        //保护方法，打开数据库连接 
        private void Open()
        {
            //判断数据库连接是否存在
            if (Connection == null)
            {
                //不存在，新建并打开
                Connection = new SqlConnection(ConnectionString);
                Connection.Open();
            }
            else
            {
                //存在，判断是否处于关闭状态  如果处于关闭状态，重新打开
                if (Connection.State.Equals(ConnectionState.Closed))
                    Connection.Open();     
            }
        }

        //公有方法，关闭数据库连接 
        public void Close()
        {
            if (Connection.State.Equals(ConnectionState.Open))
            {
                Connection.Close();  //连接处于打开状态，关闭连接
            }
        }

        /// <summary>
        /// 析构函数，释放非托管资源
        /// </summary>
        ~DataBase()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch
            {
 
            }
            try
            {
                Dispose();
            }
            catch 
            {

            }
        }

        /// <summary>
        /// 公有方法，释放资源
        /// </summary>
        public void Dispose()
        {
            if (Connection != null)   //确保连接被关闭
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        //公有方法，根据Sql语句，返回是否查询到记录
        public bool GetRecord(string SqlString)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection );
            DataSet dataset = new DataSet();
            adapter.Fill(dataset );
            Close();

            if (dataset.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataSet GetDataSetBySql(string SqlString)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString ,Connection );
            DataSet dataset = new DataSet();
            adapter.Fill(dataset );
            Close();

            return dataset;
        }

        /// <summary>
        /// 公有方法，返回Sql语句获得的数据值
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int GetRecordCount(string SqlString)
        {
            string SCount;

            Open();
            SqlCommand cmd = new SqlCommand(SqlString,Connection );
            cmd.CommandTimeout = 80000;
            SCount = cmd.ExecuteScalar().ToString().Trim();
            if (SCount == "")
            {
                SCount = "0";
            }
            Close();
            return Convert.ToInt32(SCount );
        }

        /// <summary>
        /// 公有方法，根据where更新数据表TableName中的某些记录
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="HT">哈希表，键为字段名，值为字段值</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet AdvancedSearch(string TableName, Hashtable HT)
        {
            int Count = 0;

            string Fields = "";
            foreach (DictionaryEntry Item in HT)
            {
                if (Count != 0)
                {
                    Fields += "and";
                }
                Fields += Item.Key.ToString();
                Fields += "like '%";
                Fields += Item.Value.ToString();
                Fields += "%'";
                Count++;
            }
            Fields += " ";

            string SqlString = "select * from" + TableName + "where" + Fields;
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection );
            DataSet dataset = new DataSet();
            adapter.Fill(dataset );
            Close();

            return dataset;
 
        }

        /// <summary>
        /// 私有方法，获取一个用来调用存储过程的SqlCommand对象
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="Prams">用来调用存储过程的参数表</param>
        /// <returns>返回SqlCommand对象</returns>
        private SqlCommand CreateCommand(string ProcName,SqlParameter[] Prams)
        {
            Open();
            SqlCommand cmd = new SqlCommand(ProcName,Connection );
            cmd.CommandTimeout = 80000;
            cmd.CommandType = CommandType.StoredProcedure;

            if (Prams != null)
            {
                foreach (SqlParameter Parameter in Prams)
                {
                    cmd.Parameters.Add(Parameter );
                }
            }

            return cmd;
        }

        /// <summary>
        /// 公有方法，实例化一个用于调用存储过程的参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">传递方向</param>
        /// <param name="Value">值</param>
        /// <returns>返回一个SqlParameter</returns>
        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter Param;

            if (Size > 0)
                Param = new SqlParameter(ParamName, DbType, Size);
            else
                Param = new SqlParameter(ParamName,DbType );

            Param.Direction = Direction;

            if (Value != null)
                Param.Value = Value;

            return Param;
        }

        /// <summary>
        /// 公有方法，实例化一个用于调用存储过程的输入参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns>返回SqlParameter</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam (ParamName,DbType,Size,ParameterDirection.Input,Value );
        }

        /// <summary>
        /// 公有方法，调用存储过程（不带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <returns>对Update,Insert</returns>
        public int RunProc(string ProcName)
        {
            int Count = -1;
            SqlCommand cmd = CreateCommand(ProcName,null );
            cmd.CommandTimeout = 80000;
            Count = cmd.ExecuteNonQuery();
            Close();

            return Count;
        }

        /// <summary>
        /// 公有方法，调用存储过程（带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="Params">用来调用存储过程的参数表</param>
        /// <returns>对Update,Inset,Delete 操作返回影响到的行数，其他情况为-1；</returns>
        public int RunProc(string ProcName, SqlParameter[] Params)
        {
            int Count = -1;

            SqlCommand cmd = CreateCommand(ProcName,Params );
            cmd.CommandTimeout = 80000;
            Count = cmd.ExecuteNonQuery();
            Close();
            return Count;
        }

        /// <summary>
        /// 公有方法，调用存储过程（不带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <returns>将执行结果以SqlDataReader返回。使用后注意调用SqlDataReader.Close()方法</returns>
        public SqlDataReader RunProcGetReader(string ProcName)
        {
            SqlCommand cmd = CreateCommand(ProcName,null );
            cmd.CommandTimeout = 80000;
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection );
        }

        /// <summary>
        /// 公有方法，调用存储过程（带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="Params">参数</param>
        /// <returns>将执行结果以SqlDataReader返回。使用后注意调用SqlDataReader.Close()方法</returns>
        public SqlDataReader RunProcGetReader(string ProcName, SqlParameter[] Params)
        {
            SqlCommand cmd = CreateCommand(ProcName,Params );
            cmd.CommandTimeout = 80000;
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection );
        }

        public SqlDataReader GetReaderBySql(string strsql)
        {
            Open();
            SqlCommand cmd = new SqlCommand(strsql,Connection );
            cmd.CommandTimeout = 80000;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        /// <summary>
        /// 公有方法，调用存储过程（带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="Params">存储过程需要的参数</param>
        /// <returns>返回查询第一行信息。注意使用后注意调用SqlDataReader.Close()方法</returns>
        public int RunProcGetCount(string ProcName, SqlParameter[] Params)
        {
            SqlCommand cmd = CreateCommand(ProcName,Params );
            cmd.CommandTimeout = 80000;
            string SCount;
            SCount = cmd.ExecuteScalar().ToString().Trim();
            if (SCount == "")
                SCount = "0";
            Close();
            return Convert.ToInt32(SCount );
        }

        /// <summary>
        /// 公有方法，调用存储过程（不带参数）
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <returns>将执行结果以DataSet返回</returns>
        public DataSet GetDataSet(string ProcName)
        {
            Open();
            SqlDataAdapter adapter = new SqlDataAdapter(ProcName,Connection );
            DataSet dataset = new DataSet();
            adapter.Fill(dataset );
            Close();
            return dataset;
        }

        /// <summary>
        /// 公有方法，调用存储过程(带参数)
        /// </summary>
        /// <param name="ProcName">存储过程名</param>
        /// <param name="Params">将执行结果以DataSet对象返回</param>
        /// <returns></returns>
        public DataSet GetDataSet(string ProcName, SqlParameter[] Params)
        {
            Open();
            SqlCommand cmd = CreateCommand(ProcName,Params );
            cmd.CommandTimeout = 80000;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset );
            Close();
            return dataset;
        }

    } 
}