使用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