你的位置:首页 > ASP.net教程

[ASP.net教程]万能的SqlHelper,麻麻再也不用担心用什么数据库了


以前只用一种数据库,倒也无所谓,但是再数据库切换的时候,发现代码差不多呀。

最初,两种数据库,大不了写两个SqlHelper,但是多了也就发现代码重用率太低了吧。

因此,下面的SqlHelper诞生了。

using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;namespace WangSql.DBUtility{  public static class SqlHelperExt  {    public static int AddRange(this IDataParameterCollection coll, IDataParameter[] par)    {      int i = 0;      foreach (var item in par)      {        coll.Add(item);        i++;      }      return i;    }  }  #region SqlHelper  public class SqlHelper  {    private IDbConnection conn = null;    private IDbCommand cmd = null;    private IDataReader dr = null;    private DbType type = DbType.NONE;    #region 创建数据库连接    /// <summary>    /// 创建数据库连接    /// </summary>    public SqlHelper(string connectionString)    {      conn = DBFactory.CreateDbConnection(type, connectionString);    }    #endregion    #region 判断并打开conn    /// <summary>    /// 判断并打开conn    /// </summary>    /// <returns></returns>    public IDbConnection CreatConn()    {      if (conn.State == ConnectionState.Closed)      {        conn.Open();      }      return conn;    }    #endregion    #region 执行查询sql语句    /// <summary>    /// 执行查询sql语句    /// </summary>    /// <param name="sql">查询sql语句</param>    /// <returns>返回一个表</returns>    public DataTable ExecuteReader(string sql)    {      DataTable dt = new DataTable();      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        using (dr = cmd.ExecuteReader())        {          dt.Load(dr);        }      }      conn.Close();      return dt;    }    #endregion    #region 执行查询带参的sql语句    /// <summary>    /// 执行查询带参的sql语句    /// </summary>    /// <param name="sql">查询sql语句</param>    /// <param name="par">sql语句中的参数</param>    /// <returns>返回一个表</returns>    public DataTable ExecuteReader(string sql, IDataParameter[] par)    {      DataTable dt = new DataTable();      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        cmd.Parameters.AddRange(par);        using (dr = cmd.ExecuteReader())        {          dt.Load(dr);        }      }      conn.Close();      return dt;    }    public DataTable ExecuteReader(string sql, IDataParameter par)    {      DataTable dt = new DataTable();      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        cmd.Parameters.Add(par);        using (dr = cmd.ExecuteReader())        {          dt.Load(dr);        }      }      conn.Close();      return dt;    }    #endregion    #region 执行增,删,改sql语句    /// <summary>    /// 执行无参的增,删,改sql语句    /// </summary>    /// <param name="sql">增,删,改的sql语句</param>    /// <param name="par">sql语句中的参数</param>    /// <returns>返回所影响的行数</returns>    public int ExecuteNonQuery(string sql)    {      int result = 0;      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        result = cmd.ExecuteNonQuery();      }      conn.Close();      return result;    }    #endregion    #region 执行带参的增,删,改sql语句    /// <summary>    /// 执行带参的增,删,改sql语句    /// </summary>    /// <param name="sql">增,删,改的sql语句</param>    /// <param name="par">sql语句中的参数</param>    /// <returns>返回所影响的行数</returns>    public int ExecuteNonQuery(string sql, IDbDataParameter[] par)    {      int result = 0;      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        cmd.Parameters.AddRange(par);        result = cmd.ExecuteNonQuery();      }      conn.Close();      return result;    }    public int ExecuteNonQuery(string sql, IDbDataParameter par)    {      int result = 0;      using (cmd = DBFactory.CreateDbCommand(sql, CreatConn()))      {        cmd.Parameters.Add(par);        result = cmd.ExecuteNonQuery();      }      conn.Close();      return result;    }    #endregion    #region 事务    /// <summary>    /// 执行多条SQL语句,实现数据库事务。    /// </summary>    /// <param name="SQLList">SQL语句的哈希表(key为sql语句,value是该语句的OleDbParameter[])</param>    public bool ExecuteTransaction(Hashtable SqlList)    {      CreatConn();      using (IDbTransaction trans = conn.BeginTransaction())      {        IDbCommand cmd = DBFactory.CreateDbCommand(type);        try        {          //循环          foreach (DictionaryEntry myDE in SqlList)          {            string cmdText = myDE.Key.ToString();            IDbDataParameter[] cmdParms = (IDbDataParameter[])myDE.Value;            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);            int val = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();          }          trans.Commit();        }        catch        {          trans.Rollback();          return false;        }        finally        {          conn.Close();        }      }      return true;    }    private void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, string cmdText, IDataParameter[] cmdParms)    {      CreatConn();      cmd.Connection = conn;      cmd.CommandText = cmdText;      if (trans != null)        cmd.Transaction = trans;      cmd.CommandType = CommandType.Text;//cmdType;      if (cmdParms != null)        cmd.Parameters.AddRange(cmdParms);    }    #endregion  }  #endregion}

上面是核心代码,上面有个扩展。主要是是由于抽象类里面不包含AddRange方法。楼主也是懒得改原来的方法,也是为了和原来的SqlHelper保持一致,干脆就直接扩展了一个AddRange。

好了,既然是全是抽象参数,实际中,还是需要实例化具体某种数据库的实例的,所以还需要一个创建各个数据库实例的工厂了。

using MySql.Data.MySqlClient;using Oracle.DataAccess.Client;using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Data.SQLite;using System.Linq;using System.Text;namespace WangSql{  public enum DbType  {    //Oracle,SqlServer,MySql,Access,SqlLite    NONE,    ORACLE,    SQLSERVER,    MYSQL,    ACCESS,    SQLLITE  }  public class DBFactory  {    public static IDbConnection CreateDbConnection(DbType type, string connectionString)    {      IDbConnection conn = null;      switch (type)      {        case DbType.ORACLE:          conn = new OracleConnection(connectionString);          break;        case DbType.SQLSERVER:          conn = new SqlConnection(connectionString);          break;        case DbType.MYSQL:          conn = new MySqlConnection(connectionString);          break;        case DbType.ACCESS:          conn = new OleDbConnection(connectionString);          break;        case DbType.SQLLITE:          conn = new SQLiteConnection(connectionString);          break;        case DbType.NONE:          throw new Exception("未设置数据库类型");        default:          throw new Exception("不支持该数据库类型");      }      return conn;    }    public static IDbCommand CreateDbCommand(DbType type)    {      IDbCommand cmd = null;      switch (type)      {        case DbType.ORACLE:          cmd = new OracleCommand();          break;        case DbType.SQLSERVER:          cmd = new SqlCommand();          break;        case DbType.MYSQL:          cmd = new MySqlCommand();          break;        case DbType.ACCESS:          cmd = new OleDbCommand();          break;        case DbType.SQLLITE:          cmd = new SQLiteCommand();          break;        case DbType.NONE:          throw new Exception("未设置数据库类型");        default:          throw new Exception("不支持该数据库类型");      }      return cmd;    }    public static IDbCommand CreateDbCommand(string sql, IDbConnection conn)    {      DbType type = DbType.NONE;      if (conn is OracleConnection)        type = DbType.ORACLE;      else if (conn is SqlConnection)        type = DbType.SQLSERVER;      else if (conn is MySqlConnection)        type = DbType.MYSQL;      else if (conn is OleDbConnection)        type = DbType.ACCESS;      else if (conn is SQLiteConnection)        type = DbType.SQLLITE;      IDbCommand cmd = null;      switch (type)      {        case DbType.ORACLE:          cmd = new OracleCommand(sql, (OracleConnection)conn);          break;        case DbType.SQLSERVER:          cmd = new SqlCommand(sql, (SqlConnection)conn);          break;        case DbType.MYSQL:          cmd = new MySqlCommand(sql, (MySqlConnection)conn);          break;        case DbType.ACCESS:          cmd = new OleDbCommand(sql, (OleDbConnection)conn);          break;        case DbType.SQLLITE:          cmd = new SQLiteCommand(sql, (SQLiteConnection)conn);          break;        case DbType.NONE:          throw new Exception("未设置数据库类型");        default:          throw new Exception("不支持该数据库类型");      }      return cmd;    }  }}

 

哈哈,即使再来一个数据库,你试试看,是不是很简单呢。

对了,上面的SqlHelper再单例模式下是有问题的哦,这个请大家提出下好的建议。