MySqlDataAccess.cs 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data.SqlClient;
  5. using System.Data;
  6. using System.Data.Common;
  7. using System.Xml;
  8. using System.Configuration;
  9. using Ant.Common;
  10. using MySql.Data.MySqlClient;
  11. namespace Ant.Data
  12. {
  13. public sealed class MySqlDataAccess : BaseDataAccess
  14. {
  15. private MySqlConnection DbConnection;
  16. private MySqlTransaction trans;
  17. private DataAccess da;
  18. /// <summary>
  19. /// 构造函数
  20. /// </summary>
  21. /// <param name="connString">数据库连接字符串</param>
  22. public MySqlDataAccess(string connString)
  23. {
  24. this.DbConnection = new MySqlConnection(connString);
  25. }
  26. #region 数据库连接
  27. /// <summary>
  28. /// 数据库连接
  29. /// </summary>
  30. public override string ConnectionString
  31. {
  32. get
  33. {
  34. if (this.DbConnection.IsNull())
  35. return "";
  36. else
  37. return this.DbConnection.ConnectionString;
  38. }
  39. set
  40. {
  41. this.DbConnection.ConnectionString = value;
  42. }
  43. }
  44. public override DatabaseType DatabaseType
  45. {
  46. get { return DatabaseType.MySQL; }
  47. }
  48. /// <summary>
  49. /// 获取事务
  50. /// </summary>
  51. public override DbTransaction Trans
  52. {
  53. get
  54. {
  55. if (this.trans != null)
  56. return trans;
  57. else
  58. return null;
  59. }
  60. }
  61. /// <summary>
  62. /// 数据库连接是否不可用
  63. /// </summary>
  64. public override bool IsUnEnable
  65. {
  66. get
  67. {
  68. if (this.DbConnection == null)
  69. {
  70. return true;
  71. }
  72. else
  73. {
  74. if (this.DbConnection.ConnectionString.IsEmpty())
  75. {
  76. return true;
  77. }
  78. else
  79. {
  80. return false;
  81. }
  82. }
  83. }
  84. }
  85. /// <summary>
  86. /// 数据库是否关闭
  87. /// </summary>
  88. public override bool IsClosed
  89. {
  90. get
  91. {
  92. if (this.DbConnection.IsNull())
  93. {
  94. return true;
  95. }
  96. if (this.DbConnection.State == ConnectionState.Closed)
  97. {
  98. return true;
  99. }
  100. else
  101. {
  102. return false;
  103. }
  104. }
  105. }
  106. /// <summary>
  107. /// 打开数据库
  108. /// </summary>
  109. public override void Open()
  110. {
  111. if (string.IsNullOrEmpty(DbConnection.ConnectionString))
  112. {
  113. da = DataAccessFactory.GetWriteDataDefault;
  114. this.DbConnection = new MySqlConnection(da.ConnectionString);
  115. }
  116. if (this.DbConnection.State != ConnectionState.Open)
  117. {
  118. this.DbConnection.Open();
  119. }
  120. }
  121. /// <summary>
  122. /// 关闭数据库
  123. /// </summary>
  124. public override void Close()
  125. {
  126. if (this.DbConnection != null && this.DbConnection.State != ConnectionState.Closed && trans == null)
  127. {
  128. this.DbConnection.Close();
  129. //this.DbConnection.Dispose();
  130. //this.DbConnection = null;
  131. }
  132. }
  133. /// <summary>
  134. /// 释放资源
  135. /// </summary>
  136. public override void Dispose()
  137. {
  138. if (this.DbConnection != null)
  139. {
  140. this.DbConnection.Dispose();
  141. this.DbConnection = null;
  142. }
  143. if (this.trans != null)
  144. {
  145. this.trans.Dispose();
  146. this.trans = null;
  147. }
  148. }
  149. /// <summary>
  150. /// 开户数据库事务
  151. /// </summary>
  152. public override void BeginTransaction()
  153. {
  154. this.Open();
  155. if (trans == null) trans = this.DbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
  156. }
  157. /// <summary>
  158. /// 提交数据库事务
  159. /// </summary>
  160. public override void Commit()
  161. {
  162. try
  163. {
  164. if (null != this.trans)
  165. {
  166. this.trans.Commit();
  167. this.trans.Dispose();
  168. this.trans = null;
  169. }
  170. }
  171. finally
  172. {
  173. this.Close();
  174. }
  175. }
  176. /// <summary>
  177. /// 回滚数据库事务
  178. /// </summary>
  179. public override void RollBack()
  180. {
  181. try
  182. {
  183. if (trans != null)
  184. {
  185. this.trans.Rollback();
  186. this.trans.Dispose();
  187. this.trans = null;
  188. }
  189. }
  190. finally
  191. {
  192. this.Close();
  193. }
  194. }
  195. #endregion
  196. #region 执行SQL语句和存储过程
  197. #region int ExecuteNonQuery
  198. /// <summary>
  199. /// 执行SQL命令,并返回受影响的行数
  200. /// </summary>
  201. /// <param name="commandText">SQL命令</param>
  202. /// <param name="commandType">SQL命令类型</param>
  203. /// <param name="Parameters">命令参数集合</param>
  204. /// <returns>返回受影响的行数</returns>
  205. public override int ExecuteNonQuery(string commandText, System.Data.CommandType commandType, QueryParameterCollection Parameters)
  206. {
  207. try
  208. {
  209. MySqlCommand cmd = new MySqlCommand();
  210. cmd.CommandText = commandText;
  211. cmd.CommandType = commandType;
  212. cmd.Connection = this.DbConnection;
  213. cmd.CommandTimeout = 60;
  214. if (trans != null)
  215. {
  216. cmd.Transaction = trans;
  217. }
  218. Open();
  219. if (Parameters != null)
  220. {
  221. foreach (QueryParameter q in Parameters)
  222. {
  223. cmd.Parameters.Add(ToSqlParameter(q));
  224. }
  225. }
  226. int tmpValue = cmd.ExecuteNonQuery();
  227. //Parameters.Clear();
  228. //foreach (SqlParameter p in cmd.Parameters)
  229. //{
  230. // Parameters[p.ParameterName] = ToQueryParameter(p);
  231. //}
  232. return tmpValue;
  233. }
  234. finally
  235. {
  236. Close();
  237. }
  238. }
  239. #endregion
  240. /// <summary>
  241. /// 执行SQL命令,并返回受影响的行数
  242. /// </summary>
  243. /// <param name="commandText">SQL命令</param>
  244. /// <param name="commandType">SQL命令类型</param>
  245. /// <param name="Parameters">命令参数集合</param>
  246. /// <returns>返回受影响的行数</returns>
  247. public override int ExecuteNonQuery(string commandText, DbParam[] parameters, System.Data.CommandType commandType)
  248. {
  249. //try
  250. //{
  251. // SqlCommand cmd = this.DbConnection.CreateCommand();
  252. // if (trans != null)
  253. // {
  254. // cmd.Transaction = trans;
  255. // }
  256. // Open();
  257. // this.PrepareCommand(cmd, commandText, parameters, commandType);
  258. // cmd.CommandTimeout = 60;
  259. // int tmpValue = cmd.ExecuteNonQuery();
  260. // return tmpValue;
  261. //}
  262. //finally
  263. //{
  264. // Close();
  265. //}
  266. //return 1;
  267. try
  268. {
  269. MySqlCommand cmd = this.DbConnection.CreateCommand();
  270. if (trans != null)
  271. {
  272. cmd.Transaction = trans;
  273. }
  274. Open();
  275. this.PrepareCommand(cmd, commandText, parameters, commandType);
  276. cmd.CommandTimeout = 60;
  277. int tmpValue = cmd.ExecuteNonQuery();
  278. return tmpValue;
  279. }
  280. finally
  281. {
  282. Close();
  283. }
  284. }
  285. /// <summary>
  286. /// 执行查询,并返回查询所返回的结果集中第一行的第一列
  287. /// </summary>
  288. /// <param name="commandText"></param>
  289. /// <param name="commandType"></param>
  290. /// <param name="parameters"></param>
  291. /// <returns></returns>
  292. public override object ExecuteScalar(string commandText, DbParam[] parameters, CommandType commandType)
  293. {
  294. try
  295. {
  296. MySqlCommand cmd = this.DbConnection.CreateCommand();
  297. if (trans != null)
  298. {
  299. cmd.Transaction = trans;
  300. }
  301. Open();
  302. this.PrepareCommand(cmd, commandText, parameters, commandType);
  303. cmd.CommandTimeout = 60;
  304. return cmd.ExecuteScalar();
  305. }
  306. finally
  307. {
  308. Close();
  309. }
  310. }
  311. public override DbDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandType cmdType)
  312. {
  313. try
  314. {
  315. Open();
  316. MySqlCommand cmd = this.DbConnection.CreateCommand();
  317. if (trans != null)
  318. {
  319. cmd.Transaction = trans;
  320. }
  321. this.PrepareCommand(cmd, cmdText, parameters, cmdType);
  322. cmd.CommandTimeout = 60;
  323. MySqlDataReader reader = cmd.ExecuteReader();
  324. return reader;
  325. }
  326. finally { }
  327. }
  328. #region ExecuteDataReader
  329. /// <summary>
  330. ///
  331. /// </summary>
  332. /// <param name="commandText"></param>
  333. /// <param name="commandType"></param>
  334. /// <param name="Parameters"></param>
  335. /// <returns></returns>
  336. public override DbDataReader ExecuteDataReader(string commandText, CommandType commandType, QueryParameterCollection Parameters)
  337. {
  338. try
  339. {
  340. Open();
  341. MySqlCommand cmd = this.DbConnection.CreateCommand();
  342. cmd.CommandText = commandText;
  343. cmd.CommandType = commandType;
  344. cmd.CommandTimeout = 60;
  345. if (trans != null)
  346. {
  347. cmd.Transaction = trans;
  348. }
  349. if (Parameters != null)
  350. {
  351. foreach (QueryParameter p in Parameters)
  352. {
  353. cmd.Parameters.Add(ToSqlParameter(p));
  354. }
  355. }
  356. MySqlDataReader reader = cmd.ExecuteReader();
  357. //foreach (SqlParameter p in cmd.Parameters)
  358. //{
  359. // Parameters[p.ParameterName] = ToQueryParameter(p);
  360. //}
  361. return reader;
  362. }
  363. finally
  364. {
  365. //Close();
  366. }
  367. }
  368. #endregion
  369. #region ExecuteDataTable
  370. /// <summary>
  371. /// 返回DataTable
  372. /// </summary>
  373. /// <param name="commandText"></param>
  374. /// <param name="commandType"></param>
  375. /// <param name="Parameters"></param>
  376. /// <param name="tableName"></param>
  377. /// <param name="startRecord"></param>
  378. /// <param name="maxRecords"></param>
  379. /// <returns></returns>
  380. public override DataTable ExecuteDataTable(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
  381. {
  382. try
  383. {
  384. DataTable dt = new DataTable(tableName);
  385. MySqlDataAdapter sda = new MySqlDataAdapter();
  386. Open();
  387. MySqlCommand cmd = this.DbConnection.CreateCommand();
  388. cmd.CommandText = commandText;
  389. cmd.CommandType = commandType;
  390. cmd.CommandTimeout = 60;
  391. if (trans != null)
  392. {
  393. cmd.Transaction = trans;
  394. }
  395. if (Parameters != null)
  396. {
  397. foreach (QueryParameter q in Parameters)
  398. {
  399. cmd.Parameters.Add(ToSqlParameter(q));
  400. }
  401. }
  402. sda.SelectCommand = cmd;
  403. //sda.Fill(startRecord, maxRecords, dt);
  404. sda.Fill(dt);
  405. //foreach (SqlParameter p in sda.SelectCommand.Parameters)
  406. //{
  407. // Parameters[p.ParameterName] = ToQueryParameter(p);
  408. //}
  409. return dt;
  410. }
  411. finally
  412. {
  413. Close();
  414. }
  415. }
  416. #endregion
  417. #region ExecuteDataSet
  418. /// <summary>
  419. /// 返回DataSet数据集
  420. /// </summary>
  421. /// <param name="commandText"></param>
  422. /// <param name="commandType"></param>
  423. /// <param name="Parameters"></param>
  424. /// <param name="tableName"></param>
  425. /// <param name="startRecord"></param>
  426. /// <param name="maxRecords"></param>
  427. /// <returns></returns>
  428. public override DataSet ExecuteDataSet(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
  429. {
  430. try
  431. {
  432. DataSet ds = new DataSet();
  433. MySqlDataAdapter sda = new MySqlDataAdapter();
  434. Open();
  435. MySqlCommand cmd = this.DbConnection.CreateCommand();
  436. cmd.CommandText = commandText;
  437. cmd.CommandType = commandType;
  438. cmd.CommandTimeout = 60;
  439. if (trans != null)
  440. {
  441. cmd.Transaction = trans;
  442. }
  443. if (Parameters != null)
  444. {
  445. foreach (QueryParameter q in Parameters)
  446. {
  447. cmd.Parameters.Add(ToSqlParameter(q));
  448. }
  449. }
  450. sda.SelectCommand = cmd;
  451. if (maxRecords > 0)
  452. {
  453. sda.Fill(ds, startRecord, maxRecords, tableName);
  454. }
  455. else
  456. {
  457. if (!string.IsNullOrEmpty(tableName))
  458. sda.Fill(ds, tableName);
  459. else
  460. sda.Fill(ds);
  461. }
  462. //foreach (SqlParameter p in sda.SelectCommand.Parameters)
  463. //{
  464. // Parameters[p.ParameterName] = ToQueryParameter(p);
  465. //}
  466. return ds;
  467. }
  468. finally
  469. {
  470. Close();
  471. }
  472. }
  473. /// <summary>
  474. /// 获取视图
  475. /// </summary>
  476. /// <param name="commandText"></param>
  477. /// <param name="commandType"></param>
  478. /// <param name="Parameters"></param>
  479. /// <param name="tableName"></param>
  480. /// <param name="startRecord"></param>
  481. /// <param name="maxRecords"></param>
  482. /// <returns></returns>
  483. public override DataView ExecuteDataView(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
  484. {
  485. try
  486. {
  487. DataSet ds = new DataSet();
  488. MySqlDataAdapter sda = new MySqlDataAdapter();
  489. Open();
  490. MySqlCommand cmd = this.DbConnection.CreateCommand();
  491. cmd.CommandText = commandText;
  492. cmd.CommandType = commandType;
  493. cmd.CommandTimeout = 60;
  494. if (trans != null)
  495. {
  496. cmd.Transaction = trans;
  497. }
  498. if (Parameters != null)
  499. {
  500. foreach (QueryParameter q in Parameters)
  501. {
  502. cmd.Parameters.Add(ToSqlParameter(q));
  503. }
  504. }
  505. sda.SelectCommand = cmd;
  506. if (maxRecords > 0)
  507. {
  508. sda.Fill(ds, startRecord, maxRecords, tableName);
  509. }
  510. else
  511. {
  512. if (!string.IsNullOrEmpty(tableName))
  513. sda.Fill(ds, tableName);
  514. else
  515. sda.Fill(ds);
  516. }
  517. //foreach (SqlParameter p in sda.SelectCommand.Parameters)
  518. //{
  519. // Parameters[p.ParameterName] = ToQueryParameter(p);
  520. //}
  521. return ds.Tables[0].DefaultView;
  522. }
  523. finally
  524. {
  525. Close();
  526. }
  527. }
  528. #endregion
  529. #region object ExecuteScalar
  530. /// <summary>
  531. /// 执行查询,并返回查询所返回的结果集中第一行的第一列
  532. /// </summary>
  533. /// <param name="commandText"></param>
  534. /// <param name="commandType"></param>
  535. /// <param name="parameters"></param>
  536. /// <returns></returns>
  537. public override object ExecuteScalar(string commandText, CommandType commandType, QueryParameterCollection parameters)
  538. {
  539. try
  540. {
  541. Open();
  542. MySqlCommand cmd = this.DbConnection.CreateCommand();
  543. cmd.CommandText = commandText;
  544. cmd.CommandType = commandType;
  545. cmd.CommandTimeout = 60;
  546. if (trans != null)
  547. {
  548. cmd.Transaction = trans;
  549. }
  550. if (!object.Equals(parameters, null))
  551. {
  552. foreach (QueryParameter p in parameters)
  553. {
  554. cmd.Parameters.Add(ToSqlParameter(p));
  555. }
  556. }
  557. return cmd.ExecuteScalar();
  558. }
  559. catch (Exception ex)
  560. {
  561. throw (ex);
  562. }
  563. finally
  564. {
  565. Close();
  566. }
  567. }
  568. #endregion
  569. /// <summary>
  570. /// C#代码验证sql语句是否正确(只验证不执行sql)的方法
  571. /// </summary>
  572. /// <param name="sql"></param>
  573. /// <returns></returns>
  574. public override bool ValidateSQL(string sql)
  575. {
  576. bool bResult;
  577. MySqlCommand cmd = this.DbConnection.CreateCommand();
  578. cmd.CommandText = "SET PARSEONLY ON";
  579. cmd.ExecuteNonQuery();
  580. try
  581. {
  582. cmd.CommandText = sql;
  583. cmd.ExecuteNonQuery();
  584. bResult = true;
  585. }
  586. catch (Exception ex)
  587. {
  588. bResult = false;
  589. }
  590. finally
  591. {
  592. cmd.CommandText = "SET PARSEONLY OFF";
  593. cmd.ExecuteNonQuery();
  594. }
  595. return bResult;
  596. }
  597. #endregion
  598. #region 参数类转换
  599. /// <summary>
  600. /// 获取参数
  601. /// </summary>
  602. /// <param name="cmd"></param>
  603. /// <param name="cmdText"></param>
  604. /// <param name="parameters"></param>
  605. /// <param name="cmdType"></param>
  606. void PrepareCommand(IDbCommand cmd, string cmdText, DbParam[] parameters, CommandType cmdType)
  607. {
  608. cmd.CommandText = cmdText;
  609. cmd.CommandType = cmdType;
  610. if (parameters != null)
  611. {
  612. for (int i = 0; i < parameters.Length; i++)
  613. {
  614. var param = parameters[i];
  615. if (param == null)
  616. continue;
  617. var parameter = cmd.CreateParameter();
  618. parameter.ParameterName = param.Name;
  619. Type parameterType;
  620. if (param.Value == null || param.Value == DBNull.Value)
  621. {
  622. parameter.Value = DBNull.Value;
  623. parameterType = param.Type;
  624. }
  625. else
  626. {
  627. parameter.Value = param.Value;
  628. parameterType = param.Value.GetType();
  629. }
  630. if (param.Precision != null)
  631. parameter.Precision = param.Precision.Value;
  632. if (param.Scale != null)
  633. parameter.Scale = param.Scale.Value;
  634. if (param.Size != null)
  635. parameter.Size = param.Size.Value;
  636. DbType? dbType = AntUtils.TryGetDbType(parameterType);
  637. if (dbType != null)
  638. parameter.DbType = dbType.Value;
  639. cmd.Parameters.Add(parameter);
  640. }
  641. }
  642. }
  643. /// <summary>
  644. /// 参数类转换
  645. /// </summary>
  646. /// <param name="parameter"></param>
  647. /// <returns></returns>
  648. private MySqlParameter ToSqlParameter(QueryParameter parameter)
  649. {
  650. MySqlParameter sqlParameter = new MySqlParameter();
  651. sqlParameter.DbType = parameter.DbType;
  652. sqlParameter.Direction = parameter.Direction;
  653. sqlParameter.IsNullable = parameter.IsNullable;
  654. sqlParameter.ParameterName = parameter.ParameterName;
  655. sqlParameter.Precision = parameter.Precision;
  656. sqlParameter.Scale = parameter.Scale;
  657. sqlParameter.Size = parameter.Size;
  658. sqlParameter.SourceColumn = parameter.SourceColumn;
  659. sqlParameter.SourceVersion = parameter.SourceVersion;
  660. sqlParameter.Value = parameter.Value;
  661. return sqlParameter;
  662. }
  663. /// <summary>
  664. /// 将SqlParameter转化为QueryParameter
  665. /// </summary>
  666. /// <param name="parameter"></param>
  667. /// <returns></returns>
  668. private QueryParameter ToQueryParameter(MySqlParameter parameter)
  669. {
  670. QueryParameter queryParameter = new QueryParameter();
  671. queryParameter.DbType = parameter.DbType;
  672. queryParameter.Direction = parameter.Direction;
  673. queryParameter.IsNullable = parameter.IsNullable;
  674. queryParameter.ParameterName = parameter.ParameterName;
  675. queryParameter.Precision = parameter.Precision;
  676. queryParameter.Scale = parameter.Scale;
  677. queryParameter.Size = parameter.Size;
  678. queryParameter.SourceColumn = parameter.SourceColumn;
  679. queryParameter.SourceVersion = parameter.SourceVersion;
  680. queryParameter.Value = parameter.Value;
  681. return queryParameter;
  682. }
  683. #endregion
  684. }
  685. }