C#工具类MySqlHelper，基于MySql.Data.MySqlClient封装

  1 using system;
  2 using system.collections.generic;
  3 using system.linq;
  4 using system.text;
  5 using system.threading.tasks;
  6 using system.data;
  7 using mysql.data.mysqlclient;
  8 
  9 
 10 namespace fly.util.database
 11 {
 12     /// <summary>
 13     /// mysql数据库操作类
 14     /// </summary>
 15     public static class mysqlhelper
 16     {
 17         /// <summary>  
 18         /// 执行数据库非查询操作,返回受影响的行数  
 19         /// </summary>  
 20         /// <param name="connectionstring">数据库连接字符串</param>
 21         /// <param name="cmdtype">命令的类型</param>
 22         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
 23         /// <param name="cmdparms">命令参数集合</param>  
 24         /// <returns>当前操作影响的数据行数</returns>  
 25         public static int executenonquery(string connectionstring, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
 26         {
 27             mysqlcommand cmd = new mysqlcommand();
 28             using (mysqlconnection conn = new mysqlconnection(connectionstring))
 29             {
 30                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
 31                 int val = cmd.executenonquery();
 32                 cmd.parameters.clear();
 33                 return val;
 34             }
 35         }
 36 
 37         /// <summary>  
 38         /// 执行数据库事务非查询操作,返回受影响的行数  
 39         /// </summary>  
 40         /// <param name="transaction">数据库事务对象</param>  
 41         /// <param name="cmdtype">command类型</param>  
 42         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
 43         /// <param name="cmdparms">命令参数集合</param>  
 44         /// <returns>当前事务操作影响的数据行数</returns>  
 45         public static int executenonquery(mysqltransaction trans, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
 46         {
 47             mysqlcommand cmd = new mysqlcommand();
 48             preparecommand(cmd, trans.connection, trans, cmdtype, cmdtext, cmdparms);
 49             int val = cmd.executenonquery();
 50             cmd.parameters.clear();
 51             return val;
 52         }
 53 
 54         /// <summary>  
 55         /// 执行数据库非查询操作,返回受影响的行数  
 56         /// </summary>  
 57         /// <param name="connection">mysql数据库连接对象</param>  
 58         /// <param name="cmdtype">command类型</param>  
 59         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
 60         /// <param name="cmdparms">命令参数集合</param>  
 61         /// <returns>当前操作影响的数据行数</returns>  
 62         public static int executenonquery(mysqlconnection connection, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
 63         {
 64             if (connection == null)
 65                 throw new argumentnullexception("当前数据库连接不存在");
 66             mysqlcommand cmd = new mysqlcommand();
 67             preparecommand(cmd, connection, null, cmdtype, cmdtext, cmdparms);
 68             int val = cmd.executenonquery();
 69             cmd.parameters.clear();
 70             return val;
 71         }
 72 
 73         /// <summary>  
 74         /// 执行数据库查询操作,返回mysqldatareader类型的内存结果集  
 75         /// </summary>  
 76         /// <param name="connectionstring">数据库连接字符串</param>
 77         /// <param name="cmdtype">命令的类型</param>
 78         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
 79         /// <param name="cmdparms">命令参数集合</param>  
 80         /// <returns>当前查询操作返回的mysqldatareader类型的内存结果集</returns>  
 81         public static mysqldatareader executereader(string connectionstring, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
 82         {
 83             mysqlcommand cmd = new mysqlcommand();
 84             mysqlconnection conn = new mysqlconnection(connectionstring);
 85             try
 86             {
 87                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
 88                 mysqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
 89                 cmd.parameters.clear();
 90                 return reader;
 91             }
 92             catch
 93             {
 94                 cmd.dispose();
 95                 conn.close();
 96                 throw;
 97             }
 98         }
 99 
100         /// <summary>  
101         /// 执行数据库查询操作,返回dataset类型的结果集  
102         /// </summary>  
103         /// <param name="connectionstring">数据库连接字符串</param>
104         /// <param name="cmdtype">命令的类型</param>
105         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
106         /// <param name="cmdparms">命令参数集合</param>  
107         /// <returns>当前查询操作返回的dataset类型的结果集</returns>  
108         public static dataset executedataset(string connectionstring, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
109         {
110             mysqlcommand cmd = new mysqlcommand();
111             mysqlconnection conn = new mysqlconnection(connectionstring);
112             dataset ds = null;
113             try
114             {
115                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
116                 mysqldataadapter adapter = new mysqldataadapter();
117                 adapter.selectcommand = cmd;
118                 ds = new dataset();
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             return ds;
134         }
135 
136         /// <summary>  
137         /// 执行数据库查询操作,返回datatable类型的结果集  
138         /// </summary>  
139         /// <param name="connectionstring">数据库连接字符串</param>
140         /// <param name="cmdtype">命令的类型</param>
141         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
142         /// <param name="cmdparms">命令参数集合</param>  
143         /// <returns>当前查询操作返回的datatable类型的结果集</returns>  
144         public static datatable executedatatable(string connectionstring, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
145         {
146             mysqlcommand cmd = new mysqlcommand();
147             mysqlconnection conn = new mysqlconnection(connectionstring);
148             datatable dt = null;
149 
150             try
151             {
152                 preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
153                 mysqldataadapter adapter = new mysqldataadapter();
154                 adapter.selectcommand = cmd;
155                 dt = new datatable();
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             return dt;
171         }
172 
173         /// <summary>  
174         /// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值  
175         /// </summary>  
176         /// <param name="connectionstring">数据库连接字符串</param>
177         /// <param name="cmdtype">命令的类型</param>
178         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
179         /// <param name="cmdparms">命令参数集合</param>  
180         /// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
181         public static object executescalar(string connectionstring, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
182         {
183             mysqlcommand cmd = new mysqlcommand();
184             mysqlconnection conn = new mysqlconnection(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             return result;
204         }
205 
206         /// <summary>  
207         /// 执行数据库事务查询操作,返回结果集中位于第一行第一列的object类型的值  
208         /// </summary>  
209         /// <param name="trans">一个已存在的数据库事务对象</param>  
210         /// <param name="commandtype">命令类型</param>  
211         /// <param name="commandtext">mysql存储过程名称或pl/sql命令</param>  
212         /// <param name="cmdparms">命令参数集合</param>  
213         /// <returns>当前事务查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
214         public static object executescalar(mysqltransaction trans, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
215         {
216             if (trans == null)
217                 throw new argumentnullexception("当前数据库事务不存在");
218             mysqlconnection conn = trans.connection;
219             if (conn == null)
220                 throw new argumentexception("当前事务所在的数据库连接不存在");
221 
222             mysqlcommand cmd = new mysqlcommand();
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             return result;
244         }
245 
246         /// <summary>  
247         /// 执行数据库查询操作,返回结果集中位于第一行第一列的object类型的值  
248         /// </summary>  
249         /// <param name="conn">数据库连接对象</param>  
250         /// <param name="cmdtype">command类型</param>  
251         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
252         /// <param name="cmdparms">命令参数集合</param>  
253         /// <returns>当前查询操作返回的结果集中位于第一行第一列的object类型的值</returns>  
254         public static object executescalar(mysqlconnection conn, commandtype cmdtype, string cmdtext, params mysqlparameter[] cmdparms)
255         {
256             if (conn == null) throw new argumentexception("当前数据库连接不存在");
257             mysqlcommand cmd = new mysqlcommand();
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             return result;
278         }
279 
280         /// <summary>
281         /// 执行存储过程
282         /// </summary>
283         /// <param name="connection">mysql数据库连接对象</param>  
284         /// <param name="storedprocname">存储过程名</param>
285         /// <param name="parameters">存储过程参数</param>
286         /// <returns>sqldatareader对象</returns>
287         public static mysqldatareader runstoredprocedure(mysqlconnection connection, string storedprocname, idataparameter[] parameters)
288         {
289             mysqldatareader returnreader = null;
290             connection.open();
291             mysqlcommand command = buildsqlcommand(connection, storedprocname, parameters);
292             returnreader = command.executereader(commandbehavior.closeconnection);
293             return returnreader;
294         }
295 
296         /// <summary>  
297         /// 执行数据库命令前的准备工作  
298         /// </summary>  
299         /// <param name="cmd">command对象</param>  
300         /// <param name="conn">数据库连接对象</param>  
301         /// <param name="trans">事务对象</param>  
302         /// <param name="cmdtype">command类型</param>  
303         /// <param name="cmdtext">mysql存储过程名称或pl/sql命令</param>  
304         /// <param name="cmdparms">命令参数集合</param>  
305         private static void preparecommand(mysqlcommand cmd, mysqlconnection conn, mysqltransaction trans, commandtype cmdtype, string cmdtext, 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 in cmdparms)
321                     cmd.parameters.add(parm);
322             }
323         }
324 
325         /// <summary>
326         /// 构建sqlcommand对象
327         /// </summary>
328         /// <param name="connection">数据库连接</param>
329         /// <param name="storedprocname">存储过程名</param>
330         /// <param name="parameters">存储过程参数</param>
331         /// <returns>sqlcommand</returns>
332         private static mysqlcommand buildsqlcommand(mysqlconnection connection, string storedprocname, idataparameter[] parameters)
333         {
334             mysqlcommand command = new mysqlcommand(storedprocname, connection);
335             command.commandtype = commandtype.storedprocedure;
336             foreach (mysqlparameter parameter in parameters)
337             {
338                 command.parameters.add(parameter);
339             }
340             return command;
341         }
342     }
343 }