使用ado.net打造通用的数据库操作类
最近在项目中使用中碰到了这样一种情况，查询的数据是从Oracle中获取的，但是记录下来的数据是存在Sql Server中（企业Oracle数据库管理太严，没办法操作）。而且我在之前的工作中也碰到过使用SQLite数据库，就借这次机会写一个通用的类库。
我在网上搜到了一篇利用工厂模式实现的通用类库，我在参考该文章基础上，做了一些修改。本文最好有一些Ado.net的基础。
感谢埋头前进的码农 https://www.cnblogs.com/lifeil/archive/2013/04/16/3024161.html的文章。
1.首先创建数据库的枚举类型
1 public enum DbProviderType : byte 2 { 3 // <summary> 4 /// 微软 SqlServer 数据库 5 /// </summary> 6 SqlServer, 7 8 /// <summary> 9 /// 开源 MySql数据库 10 /// </summary> 11 MySql, 12 13 /// <summary> 14 /// 嵌入式轻型数据库 SQLite 15 /// </summary> 16 SQLite, 17 18 /// <summary> 19 /// 甲骨文 Oracle 20 /// </summary> 21 Oracle, 22 23 /// <summary> 24 /// 旧版微软的oracle组件，目前已经废弃 25 /// </summary> 26 OracleClient, 27 28 /// <summary> 29 /// 开放数据库互连 30 /// </summary> 31 ODBC, 32 33 /// <summary> 34 /// 面向不同的数据源的低级应用程序接口 35 /// </summary> 36 OleDb, 37 38 /// <summary> 39 /// 跨平台的关系数据库系统 Firebird 40 /// </summary> 41 Firebird, 42 43 /// <summary> 44 ///加州大学伯克利分校计算机系开发的关系型数据库 PostgreSql 45 /// </summary> 46 PostgreSql, 47 48 /// <summary> 49 /// IBM出口的一系列关系型数据库管理系统 DB2 50 /// </summary> 51 DB2, 52 53 /// <summary> 54 /// IBM公司出品的关系数据库管理系统（RDBMS）家族 Informix 55 /// </summary> 56 Informix, 57 58 /// <summary> 59 /// 微软推出的一个适用于嵌入到移动应用的精简数据库产品 SqlServerCe 60 /// </summary> 61 SqlServerCe 62 }
2.创建好后，我们再设计一个ProviderFactory类，该类可以根据传过来的DbProviderType，创建不同的DbProviderFactory 对象，该对象用于一般的数据库操作。
1 public class ProviderFactory 2 { 3 /// <summary> 4 /// 保存每种数据访问方式对应的程序集名称 5 /// </summary> 6 private static Dictionary<MyHelper.Define.DbProviderType,string> providerInvariantNames =new Dictionary<MyHelper.Define.DbProviderType,string>(); 7 8 /// <summary> 9 /// 保存已经生成的DbProviderFactory对象 10 /// </summary> 11 private readonly static Dictionary<MyHelper.Define.DbProviderType, DbProviderFactory> providerFactoies = new Dictionary<MyHelper.Define.DbProviderType, DbProviderFactory>(20); 12 13 /// <summary> 14 /// 加载已知的访问类型与名称 15 /// </summary> 16 static ProviderFactory() 17 { 18 providerInvariantNames.Add(MyHelper.Define.DbProviderType.SqlServer, "System.Data.SqlClient"); 19 providerInvariantNames.Add(MyHelper.Define.DbProviderType.OleDb, "System.Data.OleDb"); 20 providerInvariantNames.Add(MyHelper.Define.DbProviderType.ODBC, "System.Data.ODBC"); 21 providerInvariantNames.Add(MyHelper.Define.DbProviderType.OracleClient, "System.Data.OracleClient");//已经废弃 22 providerInvariantNames.Add(MyHelper.Define.DbProviderType.Oracle, "Oracle.ManagedDataAccess.Client"); //需要安装Oracle.ManagedDataAccess插件，否则无效 23 providerInvariantNames.Add(MyHelper.Define.DbProviderType.MySql, "MySql.Data.MySqlClient"); 24 providerInvariantNames.Add(MyHelper.Define.DbProviderType.SQLite, "System.Data.SQLite"); //注意在Nuget加入SQLite程序集 25 //出现错误的话，参考https://www.cnblogs.com/leleroyn/archive/2011/03/24/1993627.html 26 //<remove invariant="System.Data.SQLite"/> 27 //<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /> 28 providerInvariantNames.Add(MyHelper.Define.DbProviderType.Firebird, "FirebirdSql.Data.Firebird"); 29 providerInvariantNames.Add(MyHelper.Define.DbProviderType.PostgreSql, "Npgsql"); 30 providerInvariantNames.Add(MyHelper.Define.DbProviderType.DB2, "IBM.Data.DB2.iSeries"); 31 providerInvariantNames.Add(MyHelper.Define.DbProviderType.Informix, "IBM.Data.Informix"); 32 providerInvariantNames.Add(MyHelper.Define.DbProviderType.SqlServerCe, "System.Data.SqlServerCe"); 33 } 34 35 public static DbProviderFactory GetDbProviderFactory(MyHelper.Define.DbProviderType providerType) 36 { 37 if (!providerFactoies.ContainsKey(providerType)) 38 { 39 var factory = BuildDbProviderFactory(providerType); //生成DbProviderFactory对象 40 providerFactoies.Add(providerType, factory); //加入到字典中 41 } 42 return providerFactoies[providerType]; 43 } 44 45 /// <summary> 46 /// 生成DbProviderFactory对象 47 /// </summary> 48 /// <param name="providerType"></param> 49 /// <returns></returns> 50 private static DbProviderFactory BuildDbProviderFactory(MyHelper.Define.DbProviderType providerType) 51 { 52 string provideName = providerInvariantNames[providerType]; //程序集名称； 53 DbProviderFactory factory = null; 54 try 55 { 56 factory = DbProviderFactories.GetFactory(provideName); 57 } 58 catch (Exception ex) 59 { 60 factory = null; 61 } 62 return factory; 63 }
3.创建DbHelper类。
1 public class DbHelper 2 { 3 private DbProviderFactory _providerFactory; 4 private string _connectionString = null; //保存连接字符串 5 6 /// <summary> 7 /// 生成DbProviderFactory对象 8 /// </summary> 9 /// <param name="connectionString">连接字符串</param> 10 /// <param name="providerType">需要生成的DbProviderFactory对象</param> 11 public DbHelper(string connectionString,MyHelper.Define.DbProviderType providerType) 12 { 13 if (string.IsNullOrEmpty(connectionString)) //连接字符串不能为空 14 { 15 throw new ArgumentException("connectionString is not null"); 16 } 17 _providerFactory = ProviderFactory.GetDbProviderFactory(providerType); //获取到生成的DbProviderFactory对象 18 _connectionString = connectionString; 19 if (_providerFactory == null) 20 { 21 throw new ArgumentException("can't build DbProviderFactory, please check DbProviderType "); 22 } 23 } 24 25 /// <summary> 26 /// 判断数据库是否可以打开 27 /// </summary> 28 /// <returns></returns> 29 public bool CanOpen() 30 { 31 var dbConnection= _providerFactory.CreateConnection(); 32 dbConnection.ConnectionString = _connectionString; 33 try 34 { 35 dbConnection.Open(); //打开连接 36 return true; 37 38 } 39 catch (Exception ex) 40 { 41 return false; 42 } 43 finally 44 { 45 dbConnection.Close(); //关闭连接 46 } 47 } 48 49 /// <summary> 50 /// 51 /// </summary> 52 /// <returns></returns> 53 public DbConnection GetDbConnection() 54 { 55 var connection= _providerFactory.CreateConnection(); 56 connection.ConnectionString = _connectionString; 57 return connection; 58 } 59 60 /// <summary> 61 /// 执行增删改的方法 62 /// </summary> 63 /// <param name="sqlString">sql语句</param> 64 /// <param name="dbParameters">参数</param> 65 /// <param name="commandType">执行方式，是Sql语句还是存储过程</param> 66 /// <returns></returns> 67 public int ExecuteNonQuery(string sqlString ,DbParameter[] dbParameters,CommandType commandType) 68 { 69 int result; 70 using (DbConnection connection = GetDbConnection()) 71 { 72 using (DbCommand command = CreateDbQueryCommand(connection, sqlString, dbParameters, commandType)) 73 { 74 connection.Open(); 75 result= command.ExecuteNonQuery(); 76 connection.Close(); 77 } 78 } 79 80 return result; 81 } 82 83 84 /// <summary> 85 /// 执行查询语句，返回DataSet 86 /// </summary> 87 /// <param name="sqlString"></param> 88 /// <param name="dbParameters"></param> 89 /// <param name="commandType"></param> 90 /// <returns></returns> 91 public DataSet ExecuteQuery(string sqlString, DbParameter[] dbParameters, CommandType commandType) 92 { 93 DataSet dataSet=new DataSet(); 94 using (DbConnection connection = GetDbConnection()) 95 { 96 using(DbDataAdapter adapter=_providerFactory.CreateDataAdapter()) 97 { 98 adapter.SelectCommand=CreateDbQueryCommand(connection,sqlString,dbParameters,commandType); 99 connection.Open(); 100 adapter.Fill(dataSet); 101 connection.Close(); 102 } 103 } 104 return dataSet; 105 } 106 107 private DbCommand CreateDbQueryCommand(DbConnection connection, string sqlString, IDbDataParameter[] dbParameters, CommandType commandType) 108 { 109 return CreateDbCommand(connection, sqlString, dbParameters, commandType, null); 110 } 111 112 /// <summary> 113 /// 创建DbCommand对象 114 /// </summary> 115 /// <param name="connection">DbConnection对象</param> 116 /// <param name="sqlString">查询语句</param> 117 /// <param name="dbParameters">参数</param> 118 /// <param name="commandType">类型</param> 119 /// <param name="dbTransaction">事务</param> 120 /// <returns></returns> 121 public DbCommand CreateDbCommand(DbConnection connection, string sqlString, IDbDataParameter[] dbParameters, CommandType commandType,DbTransaction dbTransaction) 122 { 123 DbCommand command = _providerFactory.CreateCommand(); 124 if (dbTransaction != null) 125 { 126 command.Transaction = dbTransaction; 127 } 128 command.CommandText = sqlString; 129 command.CommandType = commandType; 130 command.Connection = connection; 131 if (dbParameters != null) 132 { 133 command.Parameters.AddRange(dbParameters); 134 } 135 return command; 136 } 137 138 139 /// <summary> 140 /// 返回第一行第一列的数据，一般用于执行聚合操作的语句 141 /// </summary> 142 /// <param name="sqlString">查询语句</param> 143 /// <param name="dbParameters">参数</param> 144 /// <param name="commandType">Sql语句还是存储过程</param> 145 /// <returns></returns> 146 public object ExecuteScalar(string sqlString, DbParameter[] dbParameters, CommandType commandType) 147 { 148 object result; 149 using (DbConnection connection = GetDbConnection()) 150 { 151 using (DbCommand command = CreateDbQueryCommand(connection, sqlString, dbParameters, commandType)) 152 { 153 connection.Open(); 154 result = command.ExecuteScalar(); 155 connection.Close(); 156 } 157 } 158 return result; 159 }
方法的说明已经的很清楚了，有点Ado.net基础的同学应该都能看的懂。
下面进入我们的测试阶段，在这儿我们重点以Sql Server做测试，也会测试一下Oracle数据库和SQLite数据库。
首先我们创建一个TestDb的数据库，再创建一张Users表。
我们在创建一个存储过程，该存过过程根据传进来的ID值，返回Name字段。
下面是存储过程的代码：
1 Create procedure [dbo].[GetUser] 2 @id varchar(30), 3 @name varchar(30) output 4 as 5 begin 6 select @name=name from Users where id=@id 7 end
现在开始测试。
首先我们先执行ExecuteQuery方法，该方法返回DataSet对象。
1 var connectionString = @"Server=.;Initial Catalog=TestDB;User ID=sa;Password=123456;"; 2 DbHelper dbHelper = new DbHelper(connectionString, MyHelper.Define.DbProviderType.SqlServer); 3 string text = "select * from users "; 4 5 var usersDataSet = dbHelper.ExecuteQuery(text, null, CommandType.Text); 6 7 for (int i = 0; i < usersDataSet.Tables[0].Rows.Count; i++) 8 { 9 Console.WriteLine("ID:{0},名字：{1}", usersDataSet.Tables[0].Rows[i][0], usersDataSet.Tables[0].Rows[i][1]); 10 } 11 12 13 Console.ReadKey();
结果：
执行ExecuteScalar方法，这儿我们查询出Users这张表的总数量。
1 var connectionString = @"Server=.;Initial Catalog=TestDB;User ID=sa;Password=123456;"; 2 DbHelper dbHelper = new DbHelper(connectionString, MyHelper.Define.DbProviderType.SqlServer); 3 string text = "select count(*) from users "; 4 5 var count = (int)dbHelper.ExecuteScalar(text, null, CommandType.Text); 6 Console.WriteLine("总数量：" + count.ToString()); 7 8 Console.ReadKey();
结果
执行ExecuteNonQuery方法，在这儿我们执行带有返回参数的存储过程。
1 var connectionString = @"Server=.;Initial Catalog=TestDB;User ID=sa;Password=123456;"; 2 DbHelper dbHelper = new DbHelper(connectionString, MyHelper.Define.DbProviderType.SqlServer); 3 string text = "GetUser "; 4 DbParameter[] parameters = new DbParameter[] 5 { 6 new SqlParameter("@id", SqlDbType.VarChar){ Value="1"}, 7 new SqlParameter("@name", SqlDbType.VarChar){ Direction=ParameterDirection.Output,Size=30} 8 }; 9 dbHelper.ExecuteNonQuery(text, parameters, CommandType.StoredProcedure); 10 Console.WriteLine("名字：" + parameters[1].Value); 11 12 Console.ReadKey();
上面的三种方法，我们都已经测试完成，没有任何问题。
下面我们来替换使用其他数据库继续测试。
Oracle数据库：
在测试前需要在Nuget中加入Oracle.ManagedDataAccess程序集，否则会出现错误。
1 var connectionString = @"Password=lisi;User ID=lisi;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.229.138) 2 (PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MLDN)))"; 3 DbHelper dbHelper = new DbHelper(connectionString, MyHelper.Define.DbProviderType.Oracle); 4 string text = "select * from users "; 5 6 var usersDataSet = dbHelper.ExecuteQuery(text, null, CommandType.Text); 7 8 for (int i = 0; i < usersDataSet.Tables[0].Rows.Count; i++) 9 { 10 Console.WriteLine("ID:{0},名字：{1}", usersDataSet.Tables[0].Rows[i][0], usersDataSet.Tables[0].Rows[i][1]); 11 } 12 13 14 Console.ReadKey();
结果
OK，Oracle也没问题。
下面试试看SQLite数据库。
该方法也需要在Nuget中加入SQLite的的插件：
如果不加入的话，会出现以下错误：
在装好后，不一定能使用起来，还是出现以上错误
则在Config文件DbProviderFactories节点中加入一段代码就可以了。
<remove invariant="System.Data.SQLite"/> <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
代码：
1 var connectionString = @"data source=mydb.db"; 2 DbHelper dbHelper = new DbHelper(connectionString, MyHelper.Define.DbProviderType.SQLite); 3 4 //string text = "create table users(id varchar(20),name varchar(20)); "; 5 //dbHelper.ExecuteNonQuery(text, null, CommandType.Text); //创建表 6 7 //string sql1 = "insert into users values('1','zhangsan')"; 8 //string sql2 = "insert into users values('2','lisi')"; 9 //dbHelper.ExecuteNonQuery(sql1, null, CommandType.Text); //创建表 10 //dbHelper.ExecuteNonQuery(sql2, null, CommandType.Text); //创建表 11 12 string selectSql = "select * from users"; 13 var usersDataSet = dbHelper.ExecuteQuery(selectSql, null, CommandType.Text); 14 for (int i = 0; i < usersDataSet.Tables[0].Rows.Count; i++) 15 { 16 Console.WriteLine("ID:{0},名字：{1}", usersDataSet.Tables[0].Rows[i][0], usersDataSet.Tables[0].Rows[i][1]); 17 } 18 19 Console.ReadKey();
在代码中我加入了创建表和插入数据的语句，该段代码被我注释了，如果想要测试的话，执行一次就可以了。
以上初步测试了SQL Server，Oracle和SQLite三种数据库。
参考文章：
https://www.cnblogs.com/leleroyn/archive/2011/03/24/1993627.html
https://www.cnblogs.com/gguozhenqian/p/4262813.html
https://www.cnblogs.com/lifeil/archive/2013/04/16/3024161.html
