欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#实现较为实用的SQLhelper

程序员文章站 2023-12-31 22:12:04
第一次写博客,想不到写什么好b( ̄▽ ̄)d ,考虑的半天决定从sqlhelper开始,sqlhelper对程序员来说就像helloworld一样,很简单却又很重要,hell...

第一次写博客,想不到写什么好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);  

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

上一篇:

下一篇: