C# MySQL 数据操作类

1using System;

2using System.Collections.Generic;

3using System.Text;

4using System.Data;

5using MySql.Data.MySqlClient;

6

7namespace DevSDK.Data

8 {

9publicclass MySQL

10 {

11privatestring _ConnectionString = "";

12

13privatestring _CommandText = "";

14

15private MySqlConnection Conn = null;

16

17private DataSet _DSAFDataSet = null;

18///

19///获取或设置数据库连接字串

20///

21publicstring ConnectionString { get{ returnthis._ConnectionString; } set { this._ConnectionString = value; } }

22///

23///获取或设置SQL语句

24///

25publicstring CommandText { get{ returnthis._CommandText; } set{ this._CommandText = value; } }

26

27public MySQL()

28 {

29this._DSAFDataSet = new DataSet();

30 }

31///

32///执行SQL 语句，并把执行结果集填充到一个DataSet容器中

33///

34///

35public DataSet Execute()

36 {

37if(string.IsNullOrEmpty(_ConnectionString)) { throw(new Exception("无传入数据连接字串")); }

38

39if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); }

40

41if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); }

42

43try

44 {

45 Conn = new MySqlConnection();

Conn.ConnectionString = _ConnectionString;

46

47 MySqlDataAdapter MySqlDa = new MySqlDataAdapter();

48

49 https://www.360docs.net/doc/fc4375400.html,mandText = _CommandText;

MySqlDa.SelectCommand.Connection = Conn;

50

51 MySqlDa.Fill(_DSAFDataSet); MySqlDa.Dispose();

52 }

53catch (MySqlException MySqlEx) { throw MySqlEx; }

54catch (Exception ex) { throw ex; }

55

56return _DSAFDataSet;

57 }

58///

59///执行SQL 语句，并返回受影响的行数

60///

61///

62publicint ExecuteNonQuery()

63 {

64if(string.IsNullOrEmpty(_ConnectionString)) { throw(new Exception("无传入数据连接字串")); }

65

66if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); }

67

68if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); }

69

70int iQuery = 0;

MySqlTransaction Transaction = null;

71

72try

73 {

74 MySqlCommand Comm = new MySqlCommand();

Conn = new MySqlConnection();

75

76 Conn.ConnectionString = _ConnectionString;

Comm.Connection = Conn;

https://www.360docs.net/doc/fc4375400.html,mandText = _CommandText;

77

78 Transaction = Conn.BeginTransaction();

Comm.Transaction = Transaction;

79

80 iQuery = Comm.ExecuteNonQuery();

https://www.360docs.net/doc/fc4375400.html,mit();

return iQuery;

81 }

82catch (MySqlException MySqlEx) { Transaction.Rollback(); throw MySqlEx; }

83catch (Exception ex) { throw ex; }

84finally { Transaction.Dispose(); Conn.Close(); Conn.Dispose(); }

85 }

86///

87///执行SQL 语句，并返回结果集中的第一行第一列。忽略其它行或列

88///

89///

90publicobject ExecuteScalar()

91 {

92if(string.IsNullOrEmpty(_ConnectionString)) { throw(new Exception("无传入数据连接字串")); }

93

94if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); }

95

96if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); }

97

98object iQuery = null; MySqlTransaction Transaction = null;

99

100try

101 {

102 MySqlCommand Comm = new MySqlCommand();

Conn = new MySqlConnection();

103

104 Conn.ConnectionString = _ConnectionString;

Comm.Connection = Conn;

https://www.360docs.net/doc/fc4375400.html,mandText = _CommandText;

105

106 Transaction = Conn.BeginTransaction();

Comm.Transaction = Transaction;

107

108 iQuery = Comm.ExecuteScalar();

https://www.360docs.net/doc/fc4375400.html,mit();

return iQuery;

109 }

110catch (MySqlException MySqlEx) { Transaction.Rollback(); throw MySqlEx; }

111catch (Exception ex) { throw ex; }

112finally { Transaction.Dispose(); Conn.Close(); Conn.Dispose(); } 113 }

114///

115///测试数据库连接

116///

117///

118privatebool DSAFConnectionTest()

119 {

120try

121 {

122 Conn = new MySqlConnection();

Conn.ConnectionString = _ConnectionString;

123

124 Conn.Open(); Conn.Close();

125 }

126catch { return (false); }

127

128return (true);

129 }

130

131///

132///释放由DevSDK.Data.MySql 使用的所有资源

133///

134publicvoid Dispose()

135 {

136if (Conn != null) { this.Conn.Dispose(); }

137

138if (_DSAFDataSet != null) { this._DSAFDataSet = null; }

139 }

140 }

141 }