DBHelper類代碼(個人查閱用)

using System;using System.Data;using System.Data.SqlClient;namespace SP.DBUtility{    /// <summary>    /// SqlServer資料層基類    /// </summary>    public abstract class SqlServerHelper    {        //SQL語句執行逾時(秒)        private const int COMMAND_TIMEOUT = 3600;        #region PrepareCommand        /// <summary>        /// Command預設定        /// </summary>        /// <param name="conn">MySqlConnection對象</param>        /// <param name="trans">MySqlTransaction對象，可為null</param>        /// <param name="cmd">MySqlCommand對象</param>        /// <param name="cmdType">CommandType，預存程序或命令列</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組，可為null</param>        private static void PrepareCommand(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)        {            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            cmd.CommandTimeout = COMMAND_TIMEOUT;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = cmdType;            if (cmdParms != null)            {                foreach (SqlParameter parm in cmdParms)                    cmd.Parameters.Add(parm);            }        }        #endregion        #region ExecuteNonQuery        /// <summary>        /// 執行命令        /// </summary>        /// <param name="connectionString">資料庫連接字串</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns>返回受引響的記錄行數</returns>        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            using (SqlConnection conn = new SqlConnection(connectionString))            {                PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);                int val = cmd.ExecuteNonQuery();                cmd.Parameters.Clear();                return val;            }        }        /// <summary>        /// 執行命令        /// </summary>        /// <param name="conn">Connection對象</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns>返回受引響的記錄行數</returns>        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            return val;        }        /// <summary>        /// 執行事務        /// </summary>        /// <param name="trans">MySqlTransaction對象</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns>返回受引響的記錄行數</returns>        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            PrepareCommand(trans.Connection, trans, cmd, cmdType, cmdText, cmdParms);            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            return val;        }        #endregion        #region ExecuteScalar        /// <summary>        /// 執行命令，返回第一行第一列的值        /// </summary>        /// <param name="connectionString">資料庫連接字串</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns>返回Object對象</returns>        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            using (SqlConnection connection = new SqlConnection(connectionString))            {                PrepareCommand(connection, null, cmd, cmdType, cmdText, cmdParms);                object val = cmd.ExecuteScalar();                cmd.Parameters.Clear();                return val;            }        }        /// <summary>        /// 執行命令，返回第一行第一列的值        /// </summary>        /// <param name="connectionString">資料庫連接字串</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns>返回Object對象</returns>        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);            object val = cmd.ExecuteScalar();            cmd.Parameters.Clear();            return val;        }        #endregion        #region ExecuteReader        /// <summary>        /// 執行命令或預存程序，返回MySqlDataReader對象        /// 注意MySqlDataReader對象使用完後必須Close以釋放MySqlConnection資源        /// </summary>        /// <param name="connectionString">資料庫連接字串</param>        /// <param name="cmdType">命令類型（預存程序或SQL語句）</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組</param>        /// <returns></returns>        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            SqlConnection conn = new SqlConnection(connectionString);            try            {                PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);                cmd.Parameters.Clear();                return dr;            }            catch            {                conn.Close();                throw;            }        }        #endregion        #region ExecuteDataSet        /// <summary>        /// 執行命令或預存程序，返回DataSet對象        /// </summary>        /// <param name="connectionString">資料庫連接字串</param>        /// <param name="cmdType">命令類型(預存程序或SQL語句)</param>        /// <param name="cmdText">SQL語句或預存程序名</param>        /// <param name="cmdParms">MySqlCommand參數數組(可為null值)</param>        /// <returns></returns>        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)        {            SqlCommand cmd = new SqlCommand();            using (SqlConnection conn = new SqlConnection(connectionString))            {                PrepareCommand(conn, null, cmd, cmdType, cmdText, cmdParms);                SqlDataAdapter da = new SqlDataAdapter(cmd);                DataSet ds = new DataSet();                da.Fill(ds);                conn.Close();                cmd.Parameters.Clear();                return ds;            }        }        #endregion    }}

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Configuration;using System.Data;using System.Data.Common;namespace DAL{     public static class DBHelpher    {         public static string sql = string.Empty;//這裡有問題 ,當並發訪問網站時 SQL語句會出現問題,可以用session來解決         public static string connectionString = ConfigurationManager.ConnectionStrings["ImitateConnStr"].ConnectionString;        //Database Connection Properties, it's already open the 'connection'         /*        private static SqlConnection connection;        public static SqlConnection Connection        {            get            {                string connectionString = ConfigurationManager.ConnectionStrings["ImitateConnStr"].ConnectionString;                if (connection == null)                {                    connection = new SqlConnection(connectionString);                    connection.Open();                }                else if (connection.State == System.Data.ConnectionState.Closed)                {                    connection.Open();                }                else if (connection.State == System.Data.ConnectionState.Broken)                {                    connection.Close();                    connection.Open();                }                return connection;            }        }          */         /// <summary>        /// Implementation of non-parametric SQL statements        /// </summary>        public static int ExecuteCommand(string safeSql)        {            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))                {                    int result = cmd.ExecuteNonQuery();                    return result;                }            }        }        /// <summary>        /// Implementation parameters to SQL statements        /// </summary>        public static int ExecuteCommand(string sql, params SqlParameter[] parameters)        {            //SqlCommand cmd = new SqlCommand(sql, Connection);            //cmd.Parameters.AddRange(parameters);            //return cmd.ExecuteNonQuery();            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = Connection.CreateCommand())                {                    cmd.CommandText = sql;                    //Traverse the parameter array of the number of variables, and add to the Sql statement                    foreach (SqlParameter parameter in parameters)                    {                        cmd.Parameters.Add(parameter);                    }                    return cmd.ExecuteNonQuery();                }            }        }        /// <summary>        /// Implementation of non-parametric SQL statements, and return to the implementation of the number of rows        /// </summary>        public static object GetScalar(string safeSql)        {            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))                {                    return cmd.ExecuteScalar();                }            }        }        /// <summary>        /// Implementation parameters to SQL statements,And return to the implementation of the number of rows        /// </summary>        public static object GetScalar(string sql, params SqlParameter[] parameters)        {            //using (SqlCommand cmd = new SqlCommand(sql, Connection))            //{            //    cmd.Parameters.AddRange(parameters);            //    int result = Convert.ToInt32(cmd.ExecuteScalar());            //    return result;            //}            //the same as below            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = Connection.CreateCommand())                {                    cmd.CommandText = sql;                    cmd.Parameters.AddRange(parameters);//AddRange() is adding a parameter array                    return cmd.ExecuteScalar();                }            }        }                 /// <summary>        /// Using StoredProcedure for Scalar        /// </summary>        /*        public static object GetScalar(string Storeprocedure, params SqlParameter[] parameters)        {            using (SqlCommand cmd = connection.CreateCommand())            {                cmd.CommandType = CommandType.StoredProcedure;                cmd.Parameters.AddRange(parameters);                return cmd.ExecuteScalar();            }        }         */         /// <summary>        /// Implementation of non-parametric SQL statements and return SqlDataReader        /// </summary>        public static SqlDataReader GetReader(string safeSql)        {            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))                {                    SqlDataReader reader = cmd.ExecuteReader();                    return reader;                }            }        }        /// <summary>        ///  Implementation parameters to SQL statements and return SqlDataReader        /// </summary>        public static SqlDataReader GetReader(string sql,params SqlParameter[] parameters)        {            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = new SqlCommand(sql, Connection))                {                    cmd.Parameters.AddRange(parameters);                    SqlDataReader reader = cmd.ExecuteReader();                    return reader;                }            }        }         /// <summary>        /// U can use this function to get user's Information. (the effect the same as SqlDataReader)         /// </summary>        public static DataTable GetDataSet(string sql, params SqlParameter[] parameters)        {            using (SqlConnection Connection = new SqlConnection(connectionString))            {                Connection.Open();                using (SqlCommand cmd = new SqlCommand(sql, Connection))                {                    cmd.Parameters.AddRange(parameters);                    DataSet dataset = new DataSet();                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);                    //adapter.Fill(dataset, "UserTable");                    //return dataset.Tables["UserTable"];                    adapter.Fill(dataset);                    return dataset.Tables[0];                }            }        }    }}

 

using System;using System.Data;using System.Configuration;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Data.SqlClient;namespace SRC{    public class DBhelper    {        public static string GetSqlConn()        {            string sConn;            sConn = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;            return sConn;        }        public static DataTable GetSqlDataTable(string strSQL)        {            DataTable dt = new DataTable();            string sConn = GetSqlConn();            try            {                SqlDataAdapter da = new SqlDataAdapter(strSQL, sConn);                da.Fill(dt);            }            catch            {                throw;            }            return dt;        }        public static string GetSqlDataScalar(string strSQL)        {            string strResult = "";            SqlConnection conn = new SqlConnection(GetSqlConn());            if (conn.State == ConnectionState.Closed)            {                conn.Open();            }            try            {                SqlCommand comm = new SqlCommand(strSQL, conn);                strResult = comm.ExecuteScalar().ToString();                return strResult;            }            catch            {                return strResult = "0";            }        }        public static int ExcuteNonQuery(string sql, params SqlParameter[] parameters)        {            int result;            SqlConnection conn = new SqlConnection(GetSqlConn());            SqlCommand comm = new SqlCommand(sql, conn);            foreach (SqlParameter p in parameters)            {                comm.Parameters.Add(p);            }            conn.Open();            try            {                result = comm.ExecuteNonQuery();                conn.Close();                return result;            }            catch (Exception)            {                return 0;            }        }        public static bool ChkExists(string strSQL)        {            DataTable dt = new DataTable();            try            {                SqlDataAdapter da = new SqlDataAdapter(strSQL, GetSqlConn());                da.Fill(dt);            }            catch            {                throw;            }            if (dt.Rows.Count > 0)            {                return true;            }            else            {                return false;            }        }    }}