連接配接SQL資料庫類-c#2.0

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;

/// <summary>

/// RasDatabase 的摘要說明

/// </summary>

//namespace RasDatabase

//{

 /// <summary>

 /// DataAccess 的摘要說明。

 /// <description>資料處理基類,調用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>

 /// </summary>

    public class RasDatabase

    {

       // #region  屬性

        protected static SqlConnection conn = new SqlConnection();

        protected static SqlCommand comm = new SqlCommand();

       // #endregion

        public RasDatabase()

        {

            //

            // TODO: 在此處添加構造函數邏輯

            //

        }

       // #region 内部函數  靜态方法中不會執行DataAccess()構造函數

        /// <summary>

        /// 打開資料庫連接配接

        /// </summary>

        private static void openConnection()

        {

            if (conn.State == ConnectionState.Closed)

            {

                //SysConfig.ConnectionString 為系統配置類中連接配接字元串，如："server=localhost;database=databasename;uid=sa;pwd=;"

                //conn.ConnectionString = SysConfig.ConnectionString;

                conn.ConnectionString=ConfigurationManager.ConnectionStrings["RasCon"].ConnectionString;

                comm.Connection = conn;

                try

                {

                    conn.Open();

                }

                catch (Exception e)

                {

                    throw new Exception(e.Message);

                }

            }

        }

        /// <summary>

        /// 關閉目前資料庫連接配接

        /// </summary>

        private static void closeConnection()

        {

            if (conn.State == ConnectionState.Open)

                conn.Close();

            conn.Dispose();

            comm.Dispose();

        }

     //   #endregion

        /// <summary>

        /// 執行Sql查詢語句

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        public static void ExecuteSql(string sqlstr)

        {

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                comm.ExecuteNonQuery();

                closeConnection();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 執行存儲過程

        /// </summary>

        /// <param name="procName">存儲過程名</param>

        /// <param name="coll">SqlParameters 集合</param>

        public static void ExecutePorcedure(string procName, SqlParameter[] coll)

        {

            try

            {

                openConnection();

                for (int i = 0; i < coll.Length; i++)

                {

                    comm.Parameters.Add(coll[i]);

                }

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = procName;

                comm.ExecuteNonQuery();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                comm.Parameters.Clear();

                closeConnection();

            }

        }

        /// <summary>

        /// 執行存儲過程并傳回資料集

        /// </summary>

        /// <param name="procName">存儲過程名稱</param>

        /// <param name="coll">SqlParameter集合</param>

        /// <param name="ds">DataSet </param>

        public static void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)

        {

            try

            {

                SqlDataAdapter da = new SqlDataAdapter();

                openConnection();

                for (int i = 0; i < coll.Length; i++)

                {

                    comm.Parameters.Add(coll[i]);

                }

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = procName;

                da.SelectCommand = comm;

                da.Fill(ds);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                comm.Parameters.Clear();

                closeConnection();

            }

        }

        /// <summary>

        /// 執行Sql查詢語句并傳回第一行的第一條記錄,傳回值為object 使用時需要拆箱操作 -> Unbox

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <returns>object 傳回值 </returns>

        public static object ExecuteScalar(string sqlstr)

        {

            object obj = new object();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                obj = comm.ExecuteScalar();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return obj;

        }

        /// <summary>

        /// 執行Sql查詢語句,同時進行事務處理

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        public static void ExecuteSqlWithTransaction(string sqlstr)

        {

            SqlTransaction trans;

            trans = conn.BeginTransaction();

            comm.Transaction = trans;

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                comm.ExecuteNonQuery();

                trans.Commit();

            }

            catch

            {

                trans.Rollback();

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 傳回指定Sql語句的SqlDataReader，請注意，在使用後請關閉本對象，同時将自動調用closeConnection()來關閉資料庫連接配接

        /// 方法關閉資料庫連接配接

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <returns>SqlDataReader對象</returns>

        public static SqlDataReader dataReader(string sqlstr)

        {

            SqlDataReader dr = null;

            try

            {

                openConnection();

                comm.CommandText = sqlstr;

                comm.CommandType = CommandType.Text;

                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            }

            catch

            {

                try

                {

                    dr.Close();

                    closeConnection();

                }

                catch

                {

                }

            }

            return dr;

        }

        /// <summary>

        /// 傳回指定Sql語句的SqlDataReader，請注意，在使用後請關閉本對象，同時将自動調用closeConnection()來關閉資料庫連接配接

        /// 方法關閉資料庫連接配接

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <param name="dr">傳入的ref DataReader 對象</param>

        public static void dataReader(string sqlstr, ref SqlDataReader dr)

        {

            try

            {

                openConnection();

                comm.CommandText = sqlstr;

                comm.CommandType = CommandType.Text;

                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            }

            catch

            {

                try

                {

                    if (dr != null && !dr.IsClosed)

                        dr.Close();

                }

                catch

                {

                }

                finally

                {

                    closeConnection();

                }

            }

        }

        /// <summary>

        /// 傳回指定Sql語句的DataSet

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <returns>DataSet</returns>

        public static DataSet dataSet(string sqlstr)

        {

            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

                closeConnection();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return ds;

        }

        /// <summary>

        /// 傳回指定Sql語句的DataSet

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <param name="ds">傳入的引用DataSet對象</param>

        public static void dataSet(string sqlstr, ref DataSet ds)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 傳回指定Sql語句的DataTable

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <returns>DataTable</returns>

        public static DataTable dataTable(string sqlstr)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            DataTable datatable = new DataTable();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(datatable);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return datatable;

        }

        /// <summary>

        /// 執行指定Sql語句,同時給傳入DataTable進行指派

        /// </summary>

        /// <param name="sqlstr">傳入的Sql語句</param>

        /// <param name="dt">ref DataTable dt </param>

        public static void dataTable(string sqlstr, ref DataTable dt)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(dt);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 執行帶參數存儲過程并傳回資料集合

        /// </summary>

        /// <param name="procName">存儲過程名稱</param>

        /// <param name="parameters">SqlParameterCollection 輸入參數</param>

        /// <returns></returns>

        public static DataTable dataTable(string procName, SqlParameterCollection parameters)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            DataTable datatable = new DataTable();

            try

            {

                openConnection();

                comm.Parameters.Clear();

                comm.CommandType = CommandType.StoredProcedure;

                comm.CommandText = procName;

                foreach (SqlParameter para in parameters)

                {

                    SqlParameter p = (SqlParameter)para;

                    comm.Parameters.Add(p);

                }

                da.SelectCommand = comm;

                da.Fill(datatable);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return datatable;

        }

        public static DataView dataView(string sqlstr)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            DataView dv = new DataView();

            DataSet ds = new DataSet();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

                dv = ds.Tables[0].DefaultView;

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return dv;

        }

    }

 //}