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 }