兼容SQLSERVER、Oracle、MYSQL、SQLITE的超级DBHelper

  1     /// <summary>  
  2     /// 超级数据库操作类  
  3     /// <para>2015年12月21日</para>  
  4     /// </summary>  
  5     public class DBHelper  
  6     {  
  7         #region 属性  
  8         private DbProviderFactory _DbFactory;  
  9         private DBConfig mDBConfig;  
 10           
 11         /// <summary>  
 12         /// 数据库连接配置  
 13         /// </summary>  
 14         public DBConfig DBConfig  
 15         {  
 16             get { return mDBConfig; }  
 17         }  
 18   
 19         /// <summary>  
 20         /// 表示一组方法，这些方法用于创建提供程序对数据源类的实现的实例。  
 21         /// </summary>  
 22         public DbProviderFactory DbFactory  
 23         {  
 24             get { return _DbFactory; }  
 25             set { _DbFactory = value; }  
 26         }  
 27         #endregion  
 28  
 29         #region 构造函数  
 30         public DBHelper(DBConfig aORMConfig)  
 31         {  
 32             mDBConfig = aORMConfig;  
 33             switch (mDBConfig.DBType)  
 34             {  
 35                 case ORMType.DBTypes.SQLSERVER:  
 36                     _DbFactory = System.Data.SqlClient.SqlClientFactory.Instance;  
 37                     break;  
 38                 case ORMType.DBTypes.MYSQL:  
 39                     LoadDbProviderFactory("MySql.Data.dll", "MySql.Data.MySqlClient.MySqlClientFactory");  
 40                     break;  
 41                 case ORMType.DBTypes.SQLITE:  
 42                     LoadDbProviderFactory("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteFactory");  
 43                     break;  
 44             }  
 45         }  
 46   
 47         /// <summary>  
 48         /// 动态载入数据库封装库  
 49         /// </summary>  
 50         /// <param name="aDLLName">数据库封装库文件名称</param>  
 51         /// <param name="aFactoryName">工厂路径名称</param>  
 52         private void LoadDbProviderFactory(string aDLLName, string aFactoryName)  
 53         {  
 54             string dllPath = string.Empty;  
 55             if (System.AppDomain.CurrentDomain.RelativeSearchPath != null)  
 56             {  
 57                 dllPath = System.AppDomain.CurrentDomain.RelativeSearchPath+"\\"+ aDLLName;  
 58             }  
 59             else  
 60             {  
 61                 dllPath = System.AppDomain.CurrentDomain.BaseDirectory + aDLLName;  
 62             }  
 63             if (!File.Exists(dllPath))  
 64             {//文件不存在，从库资源中复制输出到基目录下  
 65                 FileStream fdllFile = new FileStream(dllPath,FileMode.Create);  
 66                 byte[] dllData = null;  
 67                 if (aDLLName == "System.Data.SQLite.dll")  
 68                 {  
 69                     dllData = YFmk.ORM.Properties.Resources.System_Data_SQLite;  
 70                 }  
 71                 else if (aDLLName == "MySql.Data.dll")  
 72                 {  
 73                     dllData = YFmk.ORM.Properties.Resources.MySql_Data;  
 74                 }  
 75                 fdllFile.Write(dllData, 0, dllData.Length);  
 76                 fdllFile.Close();  
 77             }  
 78             Assembly libAssembly = Assembly.LoadFile(dllPath);  
 79             Type type = libAssembly.GetType(aFactoryName);  
 80             foreach (FieldInfo fi in type.GetFields(BindingFlags.Static | BindingFlags.Public))  
 81             {  
 82                 if (fi.Name == "Instance")  
 83                 {  
 84                     _DbFactory = fi.GetValue(null) as DbProviderFactory;  
 85                     return;  
 86                 }  
 87             }  
 88         }  
 89         #endregion  
 90  
 91         #region 数据库操作  
 92         /// <summary>  
 93         /// 执行一条计算查询结果语句，返回查询结果  
 94         /// </summary>  
 95         /// <param name="aSQLWithParameter">SQL语句及参数</param>  
 96         /// <returns>查询结果（object）</returns>  
 97         public object GetSingle(SQLWithParameter aSQLWithParameter)  
 98         {  
 99             using (DbConnection conn = _DbFactory.CreateConnection())  
100             {  
101                 conn.ConnectionString = mDBConfig.ConnString;  
102                 using (DbCommand cmd = _DbFactory.CreateCommand())  
103                 {  
104                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
105                     object obj = cmd.ExecuteScalar();  
106                     cmd.Parameters.Clear();  
107                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))  
108                     {  
109                         return null;  
110                     }  
111                     else  
112                     {  
113                         return obj;  
114                     }  
115                 }  
116             }  
117         }  
118   
119         /// <summary>  
120         /// 执行SQL语句，返回影响的记录数  
121         /// </summary>  
122         /// <param name="aSQL">SQL语句</param>  
123         /// <returns>影响的记录数</returns>  
124         public int ExecuteSql(string aSQL)  
125         {  
126             using (DbConnection conn = _DbFactory.CreateConnection())  
127             {  
128                 conn.ConnectionString = mDBConfig.ConnString;  
129                 using (DbCommand cmd = _DbFactory.CreateCommand())  
130                 {  
131                     PrepareCommand(cmd, conn, aSQL);  
132                     int rows = cmd.ExecuteNonQuery();  
133                     cmd.Parameters.Clear();  
134                     return rows;  
135                 }  
136             }  
137         }  
138   
139         /// <summary>  
140         /// 执行SQL语句，返回影响的记录数  
141         /// </summary>  
142         /// <param name="aSQLWithParameter">SQL语句及参数</param>  
143         /// <returns></returns>  
144         public int ExecuteSql(SQLWithParameter aSQLWithParameter)  
145         {  
146             using (DbConnection conn = _DbFactory.CreateConnection())  
147             {  
148                 conn.ConnectionString = mDBConfig.ConnString;  
149                 using (DbCommand cmd = _DbFactory.CreateCommand())  
150                 {  
151                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
152                     int rows = cmd.ExecuteNonQuery();  
153                     cmd.Parameters.Clear();  
154                     return rows;  
155                 }  
156             }  
157         }  
158   
159         /// <summary>  
160         /// 执行多条SQL语句，实现数据库事务。  
161         /// </summary>  
162         /// <param name="aSQLWithParameterList">参数化的SQL语句结构体对象集合</param>          
163         public string ExecuteSqlTran(List<SQLWithParameter> aSQLWithParameterList)  
164         {  
165             using (DbConnection conn = _DbFactory.CreateConnection())  
166             {  
167                 conn.ConnectionString = mDBConfig.ConnString;  
168                 conn.Open();  
169                 DbTransaction fSqlTransaction = conn.BeginTransaction();  
170                 try  
171                 {  
172                     List<DbCommand> fTranCmdList = new List<DbCommand>();  
173                     //创建新的CMD  
174                     DbCommand fFirstCMD = _DbFactory.CreateCommand();  
175                     fFirstCMD.Connection = conn;  
176                     fFirstCMD.Transaction = fSqlTransaction;  
177                     fTranCmdList.Add(fFirstCMD);  
178                     int NowCmdIndex = 0;//当前执行的CMD索引值  
179                     int ExecuteCount = 0;//已经执行的CMD次数  
180                     StringBuilder fSQL = new StringBuilder();  
181                     foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)  
182                     {  
183                         fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");  
184                         fTranCmdList[NowCmdIndex].Parameters.AddRange(fSQLWithParameter.Parameters.ToArray());  
185                         if (fTranCmdList[NowCmdIndex].Parameters.Count > 2000)  
186                         { //参数达到2000个，执行一次CMD  
187                             fTranCmdList[NowCmdIndex].CommandText = fSQL.ToString();  
188                             fTranCmdList[NowCmdIndex].ExecuteNonQuery();  
189                             DbCommand fNewCMD = _DbFactory.CreateCommand();  
190                             fNewCMD.Connection = conn;  
191                             fNewCMD.Transaction = fSqlTransaction;  
192                             fTranCmdList.Add(fNewCMD);  
193                             NowCmdIndex++;  
194                             ExecuteCount++;  
195                             fSQL.Clear();//清空SQL  
196                         }  
197                     }  
198                     if (ExecuteCount < fTranCmdList.Count)  
199                     {//已执行CMD次数小于总CMD数，执行最后一条CMD  
200                         fTranCmdList[fTranCmdList.Count - 1].CommandText = fSQL.ToString();  
201                         fTranCmdList[fTranCmdList.Count - 1].ExecuteNonQuery();  
202                     }  
203                     fSqlTransaction.Commit();  
204                     return null;  
205                 }  
206                 catch (Exception ex)  
207                 {  
208                     fSqlTransaction.Rollback();  
209                     StringBuilder fSQL = new StringBuilder();  
210                     foreach (SQLWithParameter fSQLWithParameter in aSQLWithParameterList)  
211                     {  
212                         fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");  
213                     }  
214                     YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+" 错误："+ex.Message, "ORM");  
215                     return ex.Message;  
216                 }  
217             }  
218         }  
219   
220         /// <summary>  
221         /// 执行查询语句，返回DataSet  
222         /// </summary>  
223         /// <param name="SQLString">查询语句</param>  
224         /// <returns>DataSet</returns>  
225         public DataSet Query(string SQLString)  
226         {  
227             using (DbConnection conn = _DbFactory.CreateConnection())  
228             {  
229                 conn.ConnectionString = mDBConfig.ConnString;  
230                 using (DbCommand cmd = _DbFactory.CreateCommand())  
231                 {  
232                     PrepareCommand(cmd, conn, SQLString);  
233                     using (DbDataAdapter da = _DbFactory.CreateDataAdapter())  
234                     {  
235                         da.SelectCommand = cmd;  
236                         DataSet ds = new DataSet();  
237                         try  
238                         {  
239                             da.Fill(ds, "ds");  
240                             cmd.Parameters.Clear();  
241                         }  
242                         catch (Exception ex)  
243                         {  
244                               
245                         }  
246                         return ds;  
247                     }  
248                 }  
249             }  
250         }  
251   
252         /// <summary>  
253         /// 执行查询语句，返回DataSet  
254         /// </summary>  
255         /// <param name="aSQLWithParameter">查询语句</param>  
256         /// <returns>DataSet</returns>  
257         public DataSet Query(SQLWithParameter aSQLWithParameter)  
258         {  
259             using (DbConnection conn = _DbFactory.CreateConnection())  
260             {  
261                 conn.ConnectionString = mDBConfig.ConnString;  
262                 using (DbCommand cmd = _DbFactory.CreateCommand())  
263                 {  
264                     PrepareCommand(cmd, conn, aSQLWithParameter.SQL.ToString(), aSQLWithParameter.Parameters);  
265                     using (DbDataAdapter da = _DbFactory.CreateDataAdapter())  
266                     {  
267                         da.SelectCommand = cmd;  
268                         DataSet ds = new DataSet();  
269                         da.Fill(ds, "ds");  
270                         cmd.Parameters.Clear();  
271                         return ds;  
272                     }  
273                 }  
274             }  
275         }  
276         #endregion  
277  
278         #region 私有函数  
279         private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText)  
280         {  
281             if (conn.State != ConnectionState.Open)  
282                 conn.Open();  
283             cmd.Connection = conn;  
284             cmd.CommandText = cmdText;  
285         }  
286   
287         private void PrepareCommand(DbCommand cmd, DbConnection conn, string cmdText, List<DbParameter> cmdParms)  
288         {  
289             if (conn.State != ConnectionState.Open)  
290                 conn.Open();  
291             cmd.Connection = conn;  
292             cmd.CommandText = cmdText;  
293             if (cmdParms != null && cmdParms.Count>0)  
294             {  
295                 cmd.Parameters.AddRange(cmdParms.ToArray());  
296             }  
297         }  
298         #endregion