1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingSystem.Data;7 usingMySql.Data.MySqlClient;8

9

10 namespaceFly.Util.DataBase11 {12 ///

13 ///MySql數(shù)據(jù)庫(kù)操作類(lèi)14 ///

15 public static classMySqlHelper16 {17 ///

18 ///執(zhí)行數(shù)據(jù)庫(kù)非查詢操作,返回受影響的行數(shù)19 ///

20 /// 數(shù)據(jù)庫(kù)連接字符串

21 /// 命令的類(lèi)型

22 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

23 /// 命令參數(shù)集合

24 /// 當(dāng)前操作影響的數(shù)據(jù)行數(shù)

25 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)26 {27 MySqlCommand cmd = newMySqlCommand();28 using (MySqlConnection conn = newMySqlConnection(connectionString))29 {30 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);31 int val =cmd.ExecuteNonQuery();32 cmd.Parameters.Clear();33 returnval;34 }35 }36

37 ///

38 ///執(zhí)行數(shù)據(jù)庫(kù)事務(wù)非查詢操作,返回受影響的行數(shù)39 ///

40 /// 數(shù)據(jù)庫(kù)事務(wù)對(duì)象

41 /// Command類(lèi)型

42 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

43 /// 命令參數(shù)集合

44 /// 當(dāng)前事務(wù)操作影響的數(shù)據(jù)行數(shù)

45 public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)46 {47 MySqlCommand cmd = newMySqlCommand();48 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);49 int val =cmd.ExecuteNonQuery();50 cmd.Parameters.Clear();51 returnval;52 }53

54 ///

55 ///執(zhí)行數(shù)據(jù)庫(kù)非查詢操作,返回受影響的行數(shù)56 ///

57 /// MySql數(shù)據(jù)庫(kù)連接對(duì)象

58 /// Command類(lèi)型

59 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

60 /// 命令參數(shù)集合

61 /// 當(dāng)前操作影響的數(shù)據(jù)行數(shù)

62 public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)63 {64 if (connection == null)65 throw new ArgumentNullException("當(dāng)前數(shù)據(jù)庫(kù)連接不存在");66 MySqlCommand cmd = newMySqlCommand();67 PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);68 int val =cmd.ExecuteNonQuery();69 cmd.Parameters.Clear();70 returnval;71 }72

73 ///

74 ///執(zhí)行數(shù)據(jù)庫(kù)查詢操作,返回MySqlDataReader類(lèi)型的內(nèi)存結(jié)果集75 ///

76 /// 數(shù)據(jù)庫(kù)連接字符串

77 /// 命令的類(lèi)型

78 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

79 /// 命令參數(shù)集合

80 /// 當(dāng)前查詢操作返回的MySqlDataReader類(lèi)型的內(nèi)存結(jié)果集

81 public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)82 {83 MySqlCommand cmd = newMySqlCommand();84 MySqlConnection conn = newMySqlConnection(connectionString);85 try

86 {87 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);88 MySqlDataReader reader =cmd.ExecuteReader(CommandBehavior.CloseConnection);89 cmd.Parameters.Clear();90 returnreader;91 }92 catch

93 {94 cmd.Dispose();95 conn.Close();96 throw;97 }98 }99

100 ///

101 ///執(zhí)行數(shù)據(jù)庫(kù)查詢操作,返回DataSet類(lèi)型的結(jié)果集102 ///

103 /// 數(shù)據(jù)庫(kù)連接字符串

104 /// 命令的類(lèi)型

105 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

106 /// 命令參數(shù)集合

107 /// 當(dāng)前查詢操作返回的DataSet類(lèi)型的結(jié)果集

108 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)109 {110 MySqlCommand cmd = newMySqlCommand();111 MySqlConnection conn = newMySqlConnection(connectionString);112 DataSet ds = null;113 try

114 {115 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);116 MySqlDataAdapter adapter = newMySqlDataAdapter();117 adapter.SelectCommand =cmd;118 ds = newDataSet();119 adapter.Fill(ds);120 cmd.Parameters.Clear();121 }122 catch

123 {124 throw;125 }126 finally

127 {128 cmd.Dispose();129 conn.Close();130 conn.Dispose();131 }132

133 returnds;134 }135

136 ///

137 ///執(zhí)行數(shù)據(jù)庫(kù)查詢操作,返回DataTable類(lèi)型的結(jié)果集138 ///

139 /// 數(shù)據(jù)庫(kù)連接字符串

140 /// 命令的類(lèi)型

141 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

142 /// 命令參數(shù)集合

143 /// 當(dāng)前查詢操作返回的DataTable類(lèi)型的結(jié)果集

144 public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)145 {146 MySqlCommand cmd = newMySqlCommand();147 MySqlConnection conn = newMySqlConnection(connectionString);148 DataTable dt = null;149

150 try

151 {152 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);153 MySqlDataAdapter adapter = newMySqlDataAdapter();154 adapter.SelectCommand =cmd;155 dt = newDataTable();156 adapter.Fill(dt);157 cmd.Parameters.Clear();158 }159 catch

160 {161 throw;162 }163 finally

164 {165 cmd.Dispose();166 conn.Close();167 conn.Dispose();168 }169

170 returndt;171 }172

173 ///

174 ///執(zhí)行數(shù)據(jù)庫(kù)查詢操作,返回結(jié)果集中位于第一行第一列的Object類(lèi)型的值175 ///

176 /// 數(shù)據(jù)庫(kù)連接字符串

177 /// 命令的類(lèi)型

178 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

179 /// 命令參數(shù)集合

180 /// 當(dāng)前查詢操作返回的結(jié)果集中位于第一行第一列的Object類(lèi)型的值

181 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)182 {183 MySqlCommand cmd = newMySqlCommand();184 MySqlConnection conn = newMySqlConnection(connectionString);185 object result = null;186 try

187 {188 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);189 result =cmd.ExecuteScalar();190 cmd.Parameters.Clear();191 }192 catch

193 {194 throw;195 }196 finally

197 {198 cmd.Dispose();199 conn.Close();200 conn.Dispose();201 }202

203 returnresult;204 }205

206 ///

207 ///執(zhí)行數(shù)據(jù)庫(kù)事務(wù)查詢操作,返回結(jié)果集中位于第一行第一列的Object類(lèi)型的值208 ///

209 /// 一個(gè)已存在的數(shù)據(jù)庫(kù)事務(wù)對(duì)象

210 /// 命令類(lèi)型

211 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

212 /// 命令參數(shù)集合

213 /// 當(dāng)前事務(wù)查詢操作返回的結(jié)果集中位于第一行第一列的Object類(lèi)型的值

214 public static object ExecuteScalar(MySqlTransaction trans, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)215 {216 if (trans == null)217 throw new ArgumentNullException("當(dāng)前數(shù)據(jù)庫(kù)事務(wù)不存在");218 MySqlConnection conn =trans.Connection;219 if (conn == null)220 throw new ArgumentException("當(dāng)前事務(wù)所在的數(shù)據(jù)庫(kù)連接不存在");221

222 MySqlCommand cmd = newMySqlCommand();223 object result = null;224

225 try

226 {227 PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);228 result =cmd.ExecuteScalar();229 cmd.Parameters.Clear();230 }231 catch

232 {233 throw;234 }235 finally

236 {237 trans.Dispose();238 cmd.Dispose();239 conn.Close();240 conn.Dispose();241 }242

243 returnresult;244 }245

246 ///

247 ///執(zhí)行數(shù)據(jù)庫(kù)查詢操作,返回結(jié)果集中位于第一行第一列的Object類(lèi)型的值248 ///

249 /// 數(shù)據(jù)庫(kù)連接對(duì)象

250 /// Command類(lèi)型

251 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

252 /// 命令參數(shù)集合

253 /// 當(dāng)前查詢操作返回的結(jié)果集中位于第一行第一列的Object類(lèi)型的值

254 public static object ExecuteScalar(MySqlConnection conn, CommandType cmdType, string cmdText, paramsMySqlParameter[] cmdParms)255 {256 if (conn == null) throw new ArgumentException("當(dāng)前數(shù)據(jù)庫(kù)連接不存在");257 MySqlCommand cmd = newMySqlCommand();258 object result = null;259

260 try

261 {262 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);263 result =cmd.ExecuteScalar();264 cmd.Parameters.Clear();265 }266 catch

267 {268 throw;269 }270 finally

271 {272 cmd.Dispose();273 conn.Close();274 conn.Dispose();275 }276

277 returnresult;278 }279

280 ///

281 ///執(zhí)行存儲(chǔ)過(guò)程282 ///

283 /// MySql數(shù)據(jù)庫(kù)連接對(duì)象

284 /// 存儲(chǔ)過(guò)程名

285 /// 存儲(chǔ)過(guò)程參數(shù)

286 /// SqlDataReader對(duì)象

287 public static MySqlDataReader RunStoredProcedure(MySqlConnection connection, stringstoredProcName, IDataParameter[] parameters)288 {289 MySqlDataReader returnReader = null;290 connection.Open();291 MySqlCommand command =BuildSqlCommand(connection, storedProcName, parameters);292 returnReader =command.ExecuteReader(CommandBehavior.CloseConnection);293 returnreturnReader;294 }295

296 ///

297 ///執(zhí)行數(shù)據(jù)庫(kù)命令前的準(zhǔn)備工作298 ///

299 /// Command對(duì)象

300 /// 數(shù)據(jù)庫(kù)連接對(duì)象

301 /// 事務(wù)對(duì)象

302 /// Command類(lèi)型

303 /// MySql存儲(chǔ)過(guò)程名稱或PL/SQL命令

304 /// 命令參數(shù)集合

305 private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, stringcmdText, MySqlParameter[] cmdParms)306 {307 if (conn.State !=ConnectionState.Open)308 conn.Open();309

310 cmd.Connection =conn;311 cmd.CommandText =cmdText;312

313 if (trans != null)314 cmd.Transaction =trans;315

316 cmd.CommandType =cmdType;317

318 if (cmdParms != null)319 {320 foreach (MySqlParameter parm incmdParms)321 cmd.Parameters.Add(parm);322 }323 }324

325 ///

326 ///構(gòu)建SqlCommand對(duì)象327 ///

328 /// 數(shù)據(jù)庫(kù)連接

329 /// 存儲(chǔ)過(guò)程名

330 /// 存儲(chǔ)過(guò)程參數(shù)

331 /// SqlCommand

332 private static MySqlCommand BuildSqlCommand(MySqlConnection connection, stringstoredProcName, IDataParameter[] parameters)333 {334 MySqlCommand command = newMySqlCommand(storedProcName, connection);335 command.CommandType =CommandType.StoredProcedure;336 foreach (MySqlParameter parameter inparameters)337 {338 command.Parameters.Add(parameter);339 }340 returncommand;341 }342 }343 }

總結(jié)

以上是生活随笔為你收集整理的winform mysql 工具类_C#工具类MySqlHelper，基于MySql.Data.MySqlClient封装的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。