C#封装数据库类

using System;
using System.Collections.Generic;
using System.Text;
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.Web.Configuration;
using System.Collections;

namespace JCY
{
    class DataModule
    {
        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter ada;
        private DataSet ds;
        private string connstr;
        private SqlTransaction st;

        #region 关闭数据库连接
        /**/
        /// <summary>
        /// <c>关闭数据库连接</c>
        /// </summary>
        private void closeDB()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        #endregion

        #region 初始化连接字符串
        /**/
        /// <summary>
        /// <c>『钙娃儿 封装』</c>
        /// <c>初始化连接字符串</c>
        /// </summary>
        public DataModule()
        {
            int x = WebConfigurationManager.AppSettings.Count;
            SqlConnection conn = null;
            try
            {

                for (int i = 0; i < x; i++)
                {
                    connstr = WebConfigurationManager.AppSettings[i].ToString();
                    conn = new SqlConnection(connstr);
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        break;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 返回DataSet的查询函数#region 返回DataSet的查询函数
        /**/
        /// <summary>
        /// 传入Hashtable，输出DataSet
        /// </summary>
        /// <param name="ht">如果是ｓｑｌ语句，ｋｅｙ为临时表名，ｖａｌｕｅ为ｓｑｌ语句；如果是存储过程，ｋｅｙ为过程名，ｖａｌｕｅ为过程的参数数组</param>
        /// <returns>返回ＤａｔａＳｅｔ</returns>
        public DataSet getData(Hashtable ht)
        {
            DataSet ds = null;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                ada = new SqlDataAdapter(cmd);
                ds = new DataSet();
                conn.Open();
                if (ht != null)
                {
                    IDictionaryEnumerator ide = ht.GetEnumerator();
                    while (ide.MoveNext())
                    {
                        if (ide.Value != null)//有参数的存储过程或者ｓｑｌ语句
                        {
                            if (ide.Value.GetType() == Type.GetType("System.String"))//ｓｑｌ语句
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = ide.Value.ToString();
                                ada.Fill(ds, ide.Key.ToString());
                            }
                            else//有参数的存储过程
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = ide.Key.ToString();
                                foreach (SqlParameter sp in (SqlParameter[])ide.Value)
                                {
                                    cmd.Parameters.Add(sp);
                                }
                                ada.Fill(ds, ide.Key.ToString());
                            }
                        }
                        else//没有参数的存储过程
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = ide.Key.ToString();
                            cmd.Parameters.Clear();
                            ada.Fill(ds, ide.Key.ToString());
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                closeDB();
            }
            return ds;
        }
        #endregion

        #region 进行增删改操作
        /**/
        /// <summary>
        /// 传入sql语句，执行增删改
        /// </summary>
        /// <param name="sql">增删改的sql</param>
        /// <returns>成功返回：1；失败返回：-1</returns>
        public int runSql(string sql)
        {
            int returnValue = -1;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand(sql, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                returnValue = 1;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                conn.Close();
            }
            return returnValue;
        }
        #endregion

        #region 进行增删改操作
        /**/
        /// <summary>
        /// 传入存储过程和参数，执行增删改
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="paras">参数数组</param>
        /// <returns>成功返回：1；失败返回：-1</returns>
        public int runSql(string procName, params SqlParameter[] paras)
        {
            int dt = -1;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Connection = conn;
                if (paras != null)
                {
                    foreach (SqlParameter sp in paras)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }
                conn.Open();
                cmd.ExecuteNonQuery();
                dt = 1;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                closeDB();
            }
            return dt;
        }
        #endregion

        #region 进行增删改操作
        /**/
        /// <summary>
        /// 传入sql语句或者存储过程，执行增删改
        /// </summary>
        /// <param name="ht">封装sql语句或者存储过程的Hashtable</param>
        /// <returns>成功返回：1；失败返回：-1</returns>
        public int runSql(Hashtable ht)
        {
            int ds = -1;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                st = conn.BeginTransaction();
                cmd.Transaction = st;
                if (ht != null)
                {
                    IDictionaryEnumerator ide = ht.GetEnumerator();
                    while (ide.MoveNext())
                    {
                        if (ide.Value != null)//有参数的存储过程或者ｓｑｌ语句
                        {
                            if (ide.Value.GetType() == Type.GetType("System.String"))//ｓｑｌ语句
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = ide.Value.ToString();
                                cmd.ExecuteNonQuery();
                            }
                            else//有参数的存储过程
                            {
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.CommandText = ide.Key.ToString();
                                foreach (SqlParameter sp in (SqlParameter[])ide.Value)
                                {
                                    cmd.Parameters.Add(sp);
                                }
                                cmd.ExecuteNonQuery();
                            }
                        }
                        else//没有参数的存储过程
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = ide.Key.ToString();
                            cmd.Parameters.Clear();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    st.Commit();
                    ds = 1;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                st.Rollback();
            }
            finally
            {
                closeDB();
            }
            return ds;
        }
        #endregion   

        #region 返回DataTable的查询函数#region 返回DataTable的查询函数
        /**/
        /// <summary>
        ///函数输入SQL语句和临时表名，输出一个结果集（DATATABLE）
        /// </summary>
        /// <param name="sql">传入的sql语句</param>
        /// <param name="tmpTab">临时表名</param>
        /// <returns>返回DataTable</returns>
        public DataTable getData(string sql, string tmpTab)
        {
            DataTable dt = null;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand(sql, conn);
                ada = new SqlDataAdapter(cmd);
                ds = new DataSet();
                conn.Open();
                ada.Fill(ds, tmpTab);
                dt = ds.Tables[tmpTab];
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                closeDB();
            }
            return dt;
        }
        #endregion

        #region  返回DataTable的查询函数#region 返回DataTable的查询函数
        /**/
        /// <summary>
        ///函数输入存储过程，输出一个结果集（DATATABLE）
        /// </summary>
        /// <param name="procName">传入的sql语句</param>
        /// <param name="paras">存储过程的参数数组</param>
        /// <returns>返回DataTable</returns>
        public DataTable getData(string procName, params SqlParameter[] paras)
        {
            DataTable dt = null;
            try
            {
                conn = new SqlConnection(connstr);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Connection = conn;
                ds = new DataSet();
                if (paras != null)
                {
                    foreach (SqlParameter sp in paras)
                    {
                        cmd.Parameters.Add(sp);
                    }
                }
                ada = new SqlDataAdapter(cmd);
                conn.Open();
                ada.Fill(ds, procName);
                dt = ds.Tables[procName];
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                closeDB();
            }
            return dt;
        }
        #endregion
    }
}