C#实现较为实用的SQLhelper

第一次写博客，想不到写什么好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 ConnStrs = new Dictionary();

    /// 

    /// SQLServer操作类(静态构造函数)
    /// 
    static SQLHelper()
    {
      ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
      foreach (ConnectionStringSettings config in configs)
      {
 ConnStrs.Add(config.Name, config.ConnectionString);
      }
    }

    /// 

    /// 获取数据库连接
    /// 
    /// 数据库(配置文件内connectionStrings的name)
    /// 数据库连接
    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]);
    }

    /// 

    /// 获取SqlCommand
    /// 
    /// SqlConnection
    /// SqlTransaction
    /// CommandType
    /// SQL
    /// SqlParameter数组
    /// 
    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;
    }

    /// 

    /// 查询数据，返回DataTable
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 参数
    /// 查询类型(SQL语句/存储过程名)
    /// DataTable
    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());
      }
    }

    /// 

    /// 查询数据，返回DataSet
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 参数
    /// 查询类型(SQL语句/存储过程名)
    /// DataSet
    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());
      }
    }

    /// 

    /// 执行命令获取唯一值(第一行第一列)
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 参数
    /// 查询类型(SQL语句/存储过程名)
    /// 获取值
    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());
      }
    }

    /// 

    /// 执行命令更新数据
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 参数
    /// 查询类型(SQL语句/存储过程名)
    /// 更新的行数
    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;
    }

    /// 

    /// 查询数据，返回DataTable
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 查询类型(SQL语句/存储过程名)
    /// 参数
    /// DataTable
    public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary values)
    {
      SqlParameter[] parms = DicToParams(values);
      return QueryDataTable(database, sql, parms, cmdType);
    }

    /// 

    /// 执行存储过程查询数据，返回DataSet
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 查询类型(SQL语句/存储过程名)
    /// 参数
    /// DataSet
    public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary values)
    {
      SqlParameter[] parms = DicToParams(values);
      return QueryDataSet(database, sql, parms, cmdType);
    }

    /// 

    /// 执行命令获取唯一值(第一行第一列)
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 查询类型(SQL语句/存储过程名)
    /// 参数
    /// 唯一值
    public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary values)
    {
      SqlParameter[] parms = DicToParams(values);
      return QueryScalar(database, sql, parms, cmdType);
    }

    /// 

    /// 执行命令更新数据
    /// 
    /// 数据库
    /// SQL语句或存储过程名
    /// 查询类型(SQL语句/存储过程名)
    /// 参数
    /// 更新的行数
    public static int Execute(string database, string sql, CommandType cmdType, IDictionary values)
    {
      SqlParameter[] parms = DicToParams(values);
      return Execute(database, sql, parms, cmdType);
    }

    /// 

    /// 创建参数
    /// 
    /// 参数名
    /// 参数类型
    /// 参数大小
    /// 参数方向(输入/输出)
    /// 参数值
    /// 新参数对象
    public static SqlParameter[] DicToParams(IDictionary values)
    {
      if (values == null) return null;

      SqlParameter[] parms = new SqlParameter[values.Count];
      int index = 0;
      foreach (KeyValuePair 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;
    }


    /// 

    /// .net类型转换为Sql类型
    /// 
    /// .net类型
    /// Sql类型
    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 values = new Dictionary();
 values.Add("@UserName", UserName);      
 values.Add("@PassWord", passWord);
 object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);  

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持考高分网。