以前只用一种数据库，倒也无所谓，但是再数据库切换的时候，发现代码差不多呀。
最初，两种数据库，大不了写两个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(); } mit(); } 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; mandText = cmdText; if (trans != null) cmd.Transaction = trans; mandType = 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再单例模式下是有问题的哦，这个请大家提出下好的建议。
