C Realizes Practical SQLhelper

When I wrote a blog for the first time, I couldn't think of what to write b (￣ ￣) d. After half a day's consideration, I decided to start with sqlhelper. sqlhelper is just like helloworld1 for programmers, which is very simple but very important. helloworld represents the first time for programmers to write code, while sqlhelper is the first time to contact the database (I don't know if this statement is correct).

Ok, stop talking nonsense, and go directly to the code below (there is nothing to say):

public class SQLHelper
  {
    //  Timeout time 
    private static int Timeout = 1000;
    //  Database name 
    public const String BestNet = "BestNet";
    // Stored procedure name 
    public const String UserInfoCURD = "UserInfoCURD";
    //  Database connection string 
    private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();

    /// <summary>
    /// SQLServer Operation class ( Static constructor )
    /// </summary>
    static SQLHelper()
    {
      ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
      foreach (ConnectionStringSettings config in configs)
      {
        ConnStrs.Add(config.Name, config.ConnectionString);
      }
    }

    /// <summary>
    ///  Get a database connection 
    /// </summary>
    /// <param name="database"> Database ( Within the configuration file connectionStrings Adj. name)</param>
    /// <returns> Database connection </returns>
    private static SqlConnection GetConnection(string database)
    {
      if (string.IsNullOrEmpty(database))
      {
        throw new Exception(" Parameters are not set: database");
      }
      if (!ConnStrs.ContainsKey(database))
      {
        throw new Exception(" Database not found: " + database);
      }
      return new SqlConnection(ConnStrs[database]);
    }

    /// <summary>
    ///  Get 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 Array </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>
    ///  Query data and return DataTable
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="parms"> Parameter </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <returns>DataTable</returns>
    public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)
    {
      if (string.IsNullOrEmpty(database))
      {
        throw new Exception(" Parameters are not set: database");
      }
      if (string.IsNullOrEmpty(sql))
      {
        throw new Exception(" Parameters are not set: 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(" Error querying data: ");
        log.Append(ex);
        throw new Exception(log.ToString());
      }
    }

    /// <summary>
    ///  Query data and return DataSet
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="parms"> Parameter </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <returns>DataSet</returns>
    public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)
    {
      if (string.IsNullOrEmpty(database))
      {
        throw new Exception(" Parameters are not set: database");
      }
      if (string.IsNullOrEmpty(sql))
      {
        throw new Exception(" Parameters are not set: 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(" Error querying data: ");
        log.Append(ex);
        throw new Exception(log.ToString());
      }
    }

    /// <summary>
    ///  Execute command to get only 1 Value ( No. 1 1 Line number 1 Column )
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="parms"> Parameter </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <returns> Get a value </returns>
    public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)
    {
      if (string.IsNullOrEmpty(database))
      {
        throw new Exception(" Parameters are not set: database");
      }
      if (string.IsNullOrEmpty(sql))
      {
        throw new Exception(" Parameters are not set: 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(" Processing error: ");
        log.Append(ex);
        throw new Exception(log.ToString());
      }
    }

    /// <summary>
    ///  Execute commands to update data 
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="parms"> Parameter </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <returns> Number of rows updated </returns>
    public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)
    {
      if (string.IsNullOrEmpty(database))
      {
        throw new Exception(" Parameters are not set: database");
      }
      if (string.IsNullOrEmpty(sql))
      {
        throw new Exception(" Parameters are not set: sql");
      }

      // Return ( Addition, deletion and modification ) Number of updated rows for 
      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(" Processing error: ");
        log.Append(ex);
        throw new Exception(log.ToString());
      }
      return count;
    }

    /// <summary>
    ///  Query data and return DataTable
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <param name="values"> Parameter </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>
    ///  Executes a stored procedure to query data and returns DataSet
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <param name="values"> Parameter 
    /// <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>
    ///  Execute command to get only 1 Value ( No. 1 1 Line number 1 Column )
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <param name="values"> Parameter </param>
    /// <returns> Only 1 Value </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>
    ///  Execute commands to update data 
    /// </summary>
    /// <param name="database"> Database </param>
    /// <param name="sql">SQL Statement or stored procedure name </param>
    /// <param name="cmdType"> Query type (SQL Statement / Stored procedure name )</param>
    /// <param name="values"> Parameter </param>
    /// <returns> Number of rows updated </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>
    ///  Create Parameters 
    /// </summary>
    /// <param name="name"> Parameter name </param>
    /// <param name="type"> Parameter type </param>
    /// <param name="size"> Parameter size </param>
    /// <param name="direction"> Parameter direction ( Input / Output )</param>
    /// <param name="value"> Parameter value </param>
    /// <returns> New parameter object </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 Type conversion to Sql Type 
    /// </summary>
    /// <param name="t">.net Type </param>
    /// <returns>Sql Type </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;
    }

  }

You can call this directly:

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);