C#實現較為實用的SQLhelper_C#教程

第一次寫部落格，想不到寫什麼好b（￣▽￣）d　，考慮的半天決定從sqlhelper開始，sqlhelper對程式員來說就像helloworld一樣，很簡單卻又很重要，helloworld代表著程式員萌新第一次寫代碼，而sqlhelper則是初次接觸資料庫（不知道這種說法對不對）。

好了不廢話了，下面直接上代碼（無話可說了）：

public class SQLHelper  {    // 逾時時間    private static int Timeout = 1000;    // 資料庫名稱    public const String BestNet = "BestNet";    //預存程序名稱    public const String UserInfoCURD = "UserInfoCURD";    // 資料庫連接字串    private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();    /// <summary>    /// SQLServer操作類(靜態建構函式)    /// </summary>    static SQLHelper()    {      ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;      foreach (ConnectionStringSettings config in configs)      {        ConnStrs.Add(config.Name, config.ConnectionString);      }    }    /// <summary>    /// 擷取資料庫連接    /// </summary>    /// <param name="database">資料庫(設定檔內connectionStrings的name)</param>    /// <returns>資料庫連接</returns>    private static SqlConnection GetConnection(string database)    {      if (string.IsNullOrEmpty(database))      {        throw new Exception("未設定參數：database");      }      if (!ConnStrs.ContainsKey(database))      {        throw new Exception("未找到資料庫：" + database);      }      return new SqlConnection(ConnStrs[database]);    }    /// <summary>    /// 擷取SqlCommand    /// </summary>    /// <param name="conn">SqlConnection</param>    /// <param name="transaction">SqlTransaction</param>    /// <param name="cmdType">CommandType</param>    /// <param name="sql">SQL</param>    /// <param name="parms">SqlParameter數組</param>    /// <returns></returns>    private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms)    {      SqlCommand cmd = new SqlCommand(sql, conn);      cmd.CommandType = cmdType;      cmd.CommandTimeout = Timeout;      if (transaction != null)        cmd.Transaction = transaction;      if (parms != null && parms.Length != 0)        cmd.Parameters.AddRange(parms);      return cmd;    }    /// <summary>    /// 查詢資料，返回DataTable    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="parms">參數</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <returns>DataTable</returns>    public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)    {      if (string.IsNullOrEmpty(database))      {        throw new Exception("未設定參數：database");      }      if (string.IsNullOrEmpty(sql))      {        throw new Exception("未設定參數：sql");      }      try      {        using (SqlConnection conn = GetConnection(database))        {          conn.Open();          using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))          {            using (SqlDataAdapter da = new SqlDataAdapter(cmd))            {              DataTable dt = new DataTable();              da.Fill(dt);              return dt;            }          }        }      }      catch (SqlException ex)      {        System.Text.StringBuilder log = new System.Text.StringBuilder();        log.Append("查詢資料出錯：");        log.Append(ex);        throw new Exception(log.ToString());      }    }    /// <summary>    /// 查詢資料，返回DataSet    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="parms">參數</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <returns>DataSet</returns>    public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)    {      if (string.IsNullOrEmpty(database))      {        throw new Exception("未設定參數：database");      }      if (string.IsNullOrEmpty(sql))      {        throw new Exception("未設定參數：sql");      }      try      {        using (SqlConnection conn = GetConnection(database))        {          conn.Open();          using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))          {            using (SqlDataAdapter da = new SqlDataAdapter(cmd))            {              DataSet ds = new DataSet();              da.Fill(ds);              return ds;            }          }        }      }      catch (SqlException ex)      {        System.Text.StringBuilder log = new System.Text.StringBuilder();        log.Append("查詢資料出錯：");        log.Append(ex);        throw new Exception(log.ToString());      }    }    /// <summary>    /// 執行命令擷取唯一值(第一行第一列)    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="parms">參數</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <returns>擷取值</returns>    public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)    {      if (string.IsNullOrEmpty(database))      {        throw new Exception("未設定參數：database");      }      if (string.IsNullOrEmpty(sql))      {        throw new Exception("未設定參數：sql");      }      try      {        using (SqlConnection conn = GetConnection(database))        {          conn.Open();          using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))          {            return cmd.ExecuteScalar();          }        }      }      catch (SqlException ex)      {        System.Text.StringBuilder log = new System.Text.StringBuilder();        log.Append("處理出錯：");        log.Append(ex);        throw new Exception(log.ToString());      }    }    /// <summary>    /// 執行命令更新資料    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="parms">參數</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <returns>更新的行數</returns>    public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)    {      if (string.IsNullOrEmpty(database))      {        throw new Exception("未設定參數：database");      }      if (string.IsNullOrEmpty(sql))      {        throw new Exception("未設定參數：sql");      }      //返回(增刪改)的更新行數      int count = 0;      try      {        using (SqlConnection conn = GetConnection(database))        {          conn.Open();          using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))          {            if (cmdType == CommandType.StoredProcedure)              cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;            count = cmd.ExecuteNonQuery();            if (count <= 0)              if (cmdType == CommandType.StoredProcedure)                count = (int)cmd.Parameters["@RETURN_VALUE"].Value;          }        }      }      catch (SqlException ex)      {        System.Text.StringBuilder log = new System.Text.StringBuilder();        log.Append("處理出錯：");        log.Append(ex);        throw new Exception(log.ToString());      }      return count;    }    /// <summary>    /// 查詢資料，返回DataTable    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <param name="values">參數</param>    /// <returns>DataTable</returns>    public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values)    {      SqlParameter[] parms = DicToParams(values);      return QueryDataTable(database, sql, parms, cmdType);    }    /// <summary>    /// 執行預存程序查詢資料，返回DataSet    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <param name="values">參數    /// <returns>DataSet</returns>    public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values)    {      SqlParameter[] parms = DicToParams(values);      return QueryDataSet(database, sql, parms, cmdType);    }    /// <summary>    /// 執行命令擷取唯一值(第一行第一列)    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <param name="values">參數</param>    /// <returns>唯一值</returns>    public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values)    {      SqlParameter[] parms = DicToParams(values);      return QueryScalar(database, sql, parms, cmdType);    }    /// <summary>    /// 執行命令更新資料    /// </summary>    /// <param name="database">資料庫</param>    /// <param name="sql">SQL語句或預存程序名</param>    /// <param name="cmdType">查詢類型(SQL語句/預存程序名)</param>    /// <param name="values">參數</param>    /// <returns>更新的行數</returns>    public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values)    {      SqlParameter[] parms = DicToParams(values);      return Execute(database, sql, parms, cmdType);    }    /// <summary>    /// 建立參數    /// </summary>    /// <param name="name">參數名</param>    /// <param name="type">參數類型</param>    /// <param name="size">參數大小</param>    /// <param name="direction">參數方向(輸入/輸出)</param>    /// <param name="value">參數值</param>    /// <returns>新參數對象</returns>    public static SqlParameter[] DicToParams(IDictionary<string, object> values)    {      if (values == null) return null;      SqlParameter[] parms = new SqlParameter[values.Count];      int index = 0;      foreach (KeyValuePair<string, object> kv in values)      {        SqlParameter parm = null;        if (kv.Value == null)        {          parm = new SqlParameter(kv.Key, DBNull.Value);        }        else        {          Type t = kv.Value.GetType();          parm = new SqlParameter(kv.Key, NetToSql(kv.Value.GetType()));          parm.Value = kv.Value;        }        parms[index++] = parm;      }      return parms;    }    /// <summary>    /// .net類型轉換為Sql類型    /// </summary>    /// <param name="t">.net類型</param>    /// <returns>Sql類型</returns>    public static SqlDbType NetToSql(Type t)    {      SqlDbType dbType = SqlDbType.Variant;      switch (t.Name)      {        case "Int16":          dbType = SqlDbType.SmallInt;          break;        case "Int32":          dbType = SqlDbType.Int;          break;        case "Int64":          dbType = SqlDbType.BigInt;          break;        case "Single":          dbType = SqlDbType.Real;          break;        case "Decimal":          dbType = SqlDbType.Decimal;          break;        case "Byte[]":          dbType = SqlDbType.VarBinary;          break;        case "Boolean":          dbType = SqlDbType.Bit;          break;        case "String":          dbType = SqlDbType.NVarChar;          break;        case "Char[]":          dbType = SqlDbType.Char;          break;        case "DateTime":          dbType = SqlDbType.DateTime;          break;        case "DateTime2":          dbType = SqlDbType.DateTime2;          break;        case "DateTimeOffset":          dbType = SqlDbType.DateTimeOffset;          break;        case "TimeSpan":          dbType = SqlDbType.Time;          break;        case "Guid":          dbType = SqlDbType.UniqueIdentifier;          break;        case "Xml":          dbType = SqlDbType.Xml;          break;        case "Object":          dbType = SqlDbType.Variant;          break;      }      return dbType;    }  }

可以直接這樣調用： 

IDictionary<string, object> values = new Dictionary<string, object>(); values.Add("@UserName", UserName);       values.Add("@PassWord", passWord); object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);  

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援雲棲社區。