mysql和sqllite兼容_兼容SQLSERVER、Oracle、MYSQL、SQLITE的超级DBHelper

1 ///

2 ///超级数据库操作类3 /// 2015年12月21日

4 ///

5 public classDBHelper6 {7 #region 属性

8 privateDbProviderFactory _DbFactory;9 privateDBConfig mDBConfig;10

11 ///

12 ///数据库连接配置13 ///

14 publicDBConfig DBConfig15 {16 get { returnmDBConfig; }17 }18

19 ///

20 ///表示一组方法，这些方法用于创建提供程序对数据源类的实现的实例。21 ///

22 publicDbProviderFactory DbFactory23 {24 get { return_DbFactory; }25 set { _DbFactory =value; }26 }27 #endregion

28

29 #region 构造函数

30 publicDBHelper(DBConfig aORMConfig)31 {32 mDBConfig =aORMConfig;33 switch(mDBConfig.DBType)34 {35 caseORMType.DBTypes.SQLSERVER:36 _DbFactory =System.Data.SqlClient.SqlClientFactory.Instance;37 break;38 caseORMType.DBTypes.MYSQL:39 LoadDbProviderFactory("MySql.Data.dll", "MySql.Data.MySqlClient.MySqlClientFactory");40 break;41 caseORMType.DBTypes.SQLITE:42 LoadDbProviderFactory("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteFactory");43 break;44 }45 }46

47 ///

48 ///动态载入数据库封装库49 ///

50 /// 数据库封装库文件名称

51 /// 工厂路径名称

52 private void LoadDbProviderFactory(string aDLLName, stringaFactoryName)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 = newFileStream(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) asDbProviderFactory;85 return;86 }87 }88 }89 #endregion

90

91 #region 数据库操作

92 ///

93 ///执行一条计算查询结果语句，返回查询结果94 ///

95 /// SQL语句及参数

96 /// 查询结果(object)

97 public objectGetSingle(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 returnobj;114 }115 }116 }117 }118

119 ///

120 ///执行SQL语句，返回影响的记录数121 ///

122 /// SQL语句

123 /// 影响的记录数

124 public int ExecuteSql(stringaSQL)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 returnrows;135 }136 }137 }138

139 ///

140 ///执行SQL语句，返回影响的记录数141 ///

142 /// SQL语句及参数

143 ///

144 public intExecuteSql(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 returnrows;155 }156 }157 }158

159 ///

160 ///执行多条SQL语句，实现数据库事务。161 ///

162 /// 参数化的SQL语句结构体对象集合

163 public string ExecuteSqlTran(ListaSQLWithParameterList)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 fTranCmdList = new List();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 = newStringBuilder();181 foreach (SQLWithParameter fSQLWithParameter inaSQLWithParameterList)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

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 = newStringBuilder();210 foreach (SQLWithParameter fSQLWithParameter inaSQLWithParameterList)211 {212 fSQL.Append(fSQLWithParameter.SQL.ToString() + ";");213 }214 YFmk.Lib.LocalLog.WriteByDate(fSQL.ToString()+"错误："+ex.Message, "ORM");215 returnex.Message;216 }217 }218 }219

220 ///

221 ///执行查询语句，返回DataSet222 ///

223 /// 查询语句

224 /// DataSet

225 public DataSet Query(stringSQLString)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 = newDataSet();237 try

238 {239 da.Fill(ds, "ds");240 cmd.Parameters.Clear();241 }242 catch(Exception ex)243 {244

245 }246 returnds;247 }248 }249 }250 }251

252 ///

253 ///执行查询语句，返回DataSet254 ///

255 /// 查询语句

256 /// DataSet

257 publicDataSet 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 = newDataSet();269 da.Fill(ds, "ds");270 cmd.Parameters.Clear();271 returnds;272 }273 }274 }275 }276 #endregion

277

278 #region 私有函数

279 private void PrepareCommand(DbCommand cmd, DbConnection conn, stringcmdText)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, ListcmdParms)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