3 using ByteFX
.Data
.MySqlClient
;
5 namespace ByteFX
.Data
.MySqlClient
8 /// Helper class that makes it easier to work with the provider.
10 public sealed class MySqlHelper
12 // this class provides only static methods
17 #region ExecuteNonQuery
20 /// Executes a single command against a MySQL database. The <see cref="MySqlConnection"/> is assumed to be
21 /// open when the method is called and remains open after the method completes.
23 /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
24 /// <param name="commandText">SQL command to be executed</param>
25 /// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command.</param>
26 /// <returns></returns>
27 public static int ExecuteNonQuery( MySqlConnection connection
, string commandText
, params MySqlParameter
[] commandParameters
)
29 //create a command and prepare it for execution
30 MySqlCommand cmd
= new MySqlCommand();
31 cmd
.Connection
= connection
;
32 cmd
.CommandText
= commandText
;
33 cmd
.CommandType
= CommandType
.Text
;
35 if (commandParameters
!= null)
36 foreach (MySqlParameter p
in commandParameters
)
37 cmd
.Parameters
.Add( p
);
39 int result
= cmd
.ExecuteNonQuery();
40 cmd
.Parameters
.Clear();
46 /// Executes a single command against a MySQL database. A new <see cref="MySqlConnection"/> is created
47 /// using the <see cref="MySqlConnection.ConnectionString"/> given.
49 /// <param name="connectionString"><see cref="MySqlConnection.ConnectionString"/> to use</param>
50 /// <param name="commandText">SQL command to be executed</param>
51 /// <param name="parms">Array of <see cref="MySqlParameter"/> objects to use with the command.</param>
52 /// <returns></returns>
53 public static int ExecuteNonQuery( string connectionString
, string commandText
, params MySqlParameter
[] parms
)
55 //create & open a SqlConnection, and dispose of it after we are done.
56 using (MySqlConnection cn
= new MySqlConnection(connectionString
))
60 //call the overload that takes a connection in place of the connection string
61 return ExecuteNonQuery(cn
, commandText
, parms
);
66 #region ExecuteDataSet
69 /// Executes a single SQL command and returns the first row of the resultset. A new MySqlConnection object
70 /// is created, opened, and closed during this method.
72 /// <param name="connectionString">Settings to be used for the connection</param>
73 /// <param name="commandText">Command to execute</param>
74 /// <param name="parms">Parameters to use for the command</param>
75 /// <returns>DataRow containing the first row of the resultset</returns>
76 public static DataRow
ExecuteDatarow( string connectionString
, string commandText
, params MySqlParameter
[] parms
)
78 DataSet ds
= ExecuteDataset( connectionString
, commandText
, parms
);
79 if (ds
== null) return null;
80 if (ds
.Tables
.Count
== 0) return null;
81 if (ds
.Tables
[0].Rows
.Count
== 0) return null;
82 return ds
.Tables
[0].Rows
[0];
86 /// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
87 /// A new MySqlConnection object is created, opened, and closed during this method.
89 /// <param name="connectionString">Settings to be used for the connection</param>
90 /// <param name="commandText">Command to execute</param>
91 /// <returns><see cref="DataSet"/> containing the resultset</returns>
92 public static DataSet
ExecuteDataset(string connectionString
, string commandText
)
94 //pass through the call providing null for the set of SqlParameters
95 return ExecuteDataset(connectionString
, commandText
, (MySqlParameter
[])null);
99 /// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
100 /// A new MySqlConnection object is created, opened, and closed during this method.
102 /// <param name="connectionString">Settings to be used for the connection</param>
103 /// <param name="commandText">Command to execute</param>
104 /// <param name="commandParameters">Parameters to use for the command</param>
105 /// <returns><see cref="DataSet"/> containing the resultset</returns>
106 public static DataSet
ExecuteDataset(string connectionString
, string commandText
, params MySqlParameter
[] commandParameters
)
108 //create & open a SqlConnection, and dispose of it after we are done.
109 using (MySqlConnection cn
= new MySqlConnection(connectionString
))
113 //call the overload that takes a connection in place of the connection string
114 return ExecuteDataset(cn
, commandText
, commandParameters
);
119 /// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
120 /// The state of the <see cref="MySqlConnection"/> object remains unchanged after execution
123 /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
124 /// <param name="commandText">Command to execute</param>
125 /// <returns><see cref="DataSet"/> containing the resultset</returns>
126 public static DataSet
ExecuteDataset(MySqlConnection connection
, string commandText
)
128 //pass through the call providing null for the set of SqlParameters
129 return ExecuteDataset(connection
, commandText
, (MySqlParameter
[])null);
133 /// Executes a single SQL command and returns the resultset in a <see cref="DataSet"/>.
134 /// The state of the <see cref="MySqlConnection"/> object remains unchanged after execution
137 /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
138 /// <param name="commandText">Command to execute</param>
139 /// <param name="commandParameters">Parameters to use for the command</param>
140 /// <returns><see cref="DataSet"/> containing the resultset</returns>
141 public static DataSet
ExecuteDataset(MySqlConnection connection
, string commandText
, params MySqlParameter
[] commandParameters
)
143 //create a command and prepare it for execution
144 MySqlCommand cmd
= new MySqlCommand();
145 cmd
.Connection
= connection
;
146 cmd
.CommandText
= commandText
;
147 cmd
.CommandType
= CommandType
.Text
;
149 if (commandParameters
!= null)
150 foreach (MySqlParameter p
in commandParameters
)
151 cmd
.Parameters
.Add( p
);
153 //create the DataAdapter & DataSet
154 MySqlDataAdapter da
= new MySqlDataAdapter(cmd
);
155 DataSet ds
= new DataSet();
157 //fill the DataSet using default values for DataTable names, etc.
160 // detach the MySqlParameters from the command object, so they can be used again.
161 cmd
.Parameters
.Clear();
168 /// Updates the given table with data from the given <see cref="DataSet"/>
170 /// <param name="connectionString">Settings to use for the update</param>
171 /// <param name="commandText">Command text to use for the update</param>
172 /// <param name="ds"><see cref="DataSet"/> containing the new data to use in the update</param>
173 /// <param name="tablename">Tablename in the dataset to update</param>
174 public static void UpdateDataSet( string connectionString
, string commandText
, DataSet ds
, string tablename
)
176 MySqlConnection cn
= new MySqlConnection( connectionString
);
178 MySqlDataAdapter da
= new MySqlDataAdapter( commandText
, cn
);
179 MySqlCommandBuilder cb
= new MySqlCommandBuilder( da
);
180 da
.Update( ds
, tablename
);
186 #region ExecuteDataReader
189 /// Executes a single command against a MySQL database, possibly inside an existing transaction.
191 /// <param name="connection"><see cref="MySqlConnection"/> object to use for the command</param>
192 /// <param name="transaction"><see cref="MySqlTransaction"/> object to use for the command</param>
193 /// <param name="commandText">Command text to use</param>
194 /// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
195 /// <param name="ExternalConn">True if the connection should be preserved, false if not</param>
196 /// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
197 private static MySqlDataReader
ExecuteReader(MySqlConnection connection
, MySqlTransaction transaction
, string commandText
, MySqlParameter
[] commandParameters
, bool ExternalConn
)
199 //create a command and prepare it for execution
200 MySqlCommand cmd
= new MySqlCommand();
201 cmd
.Connection
= connection
;
202 cmd
.Transaction
= transaction
;
203 cmd
.CommandText
= commandText
;
204 cmd
.CommandType
= CommandType
.Text
;
206 if (commandParameters
!= null)
207 foreach (MySqlParameter p
in commandParameters
)
208 cmd
.Parameters
.Add( p
);
213 // call ExecuteReader with the appropriate CommandBehavior
216 dr
= cmd
.ExecuteReader();
220 dr
= cmd
.ExecuteReader(CommandBehavior
.CloseConnection
);
223 // detach the SqlParameters from the command object, so they can be used again.
224 cmd
.Parameters
.Clear();
230 /// Executes a single command against a MySQL database.
232 /// <param name="connectionString">Settings to use for this command</param>
233 /// <param name="commandText">Command text to use</param>
234 /// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
235 public static MySqlDataReader
ExecuteReader(string connectionString
, string commandText
)
237 //pass through the call providing null for the set of SqlParameters
238 return ExecuteReader(connectionString
, commandText
, (MySqlParameter
[])null);
242 /// Executes a single command against a MySQL database.
244 /// <param name="connectionString">Settings to use for this command</param>
245 /// <param name="commandText">Command text to use</param>
246 /// <param name="commandParameters">Array of <see cref="MySqlParameter"/> objects to use with the command</param>
247 /// <returns><see cref="MySqlDataReader"/> object ready to read the results of the command</returns>
248 public static MySqlDataReader
ExecuteReader(string connectionString
, string commandText
, params MySqlParameter
[] commandParameters
)
250 //create & open a SqlConnection
251 MySqlConnection cn
= new MySqlConnection(connectionString
);
256 //call the private overload that takes an internally owned connection in place of the connection string
257 return ExecuteReader(cn
, null, commandText
, commandParameters
, false );
261 //if we fail to return the SqlDatReader, we need to close the connection ourselves
268 #region ExecuteScalar
271 /// Execute a single command against a MySQL database.
273 /// <param name="connectionString">Settings to use for the update</param>
274 /// <param name="commandText">Command text to use for the update</param>
275 /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
276 public static object ExecuteScalar(string connectionString
, string commandText
)
278 //pass through the call providing null for the set of MySqlParameters
279 return ExecuteScalar(connectionString
, commandText
, (MySqlParameter
[])null);
283 /// Execute a single command against a MySQL database.
285 /// <param name="connectionString">Settings to use for the command</param>
286 /// <param name="commandText">Command text to use for the command</param>
287 /// <param name="commandParameters">Parameters to use for the command</param>
288 /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
289 public static object ExecuteScalar(string connectionString
, string commandText
, params MySqlParameter
[] commandParameters
)
291 //create & open a SqlConnection, and dispose of it after we are done.
292 using (MySqlConnection cn
= new MySqlConnection(connectionString
))
296 //call the overload that takes a connection in place of the connection string
297 return ExecuteScalar(cn
, commandText
, commandParameters
);
302 /// Execute a single command against a MySQL database.
304 /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
305 /// <param name="commandText">Command text to use for the command</param>
306 /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
307 public static object ExecuteScalar(MySqlConnection connection
, string commandText
)
309 //pass through the call providing null for the set of MySqlParameters
310 return ExecuteScalar(connection
, commandText
, (MySqlParameter
[])null);
314 /// Execute a single command against a MySQL database.
316 /// <param name="connection"><see cref="MySqlConnection"/> object to use</param>
317 /// <param name="commandText">Command text to use for the command</param>
318 /// <param name="commandParameters">Parameters to use for the command</param>
319 /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
320 public static object ExecuteScalar(MySqlConnection connection
, string commandText
, params MySqlParameter
[] commandParameters
)
322 //create a command and prepare it for execution
323 MySqlCommand cmd
= new MySqlCommand();
324 cmd
.Connection
= connection
;
325 cmd
.CommandText
= commandText
;
326 cmd
.CommandType
= CommandType
.Text
;
328 if (commandParameters
!= null)
329 foreach (MySqlParameter p
in commandParameters
)
330 cmd
.Parameters
.Add( p
);
332 //execute the command & return the results
333 object retval
= cmd
.ExecuteScalar();
335 // detach the SqlParameters from the command object, so they can be used again.
336 cmd
.Parameters
.Clear();