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

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