mcs/class/ByteFX.Data/mysqlclient/MySqlHelper.cs

   1 using System;
   2 using System.Data;
   3 using ByteFX.Data.MySqlClient;
   4
   5 namespace ByteFX.Data.MySqlClient
   6 {
   7         /// <summary>
   8         /// Helper class that makes it easier to work with the provider.
   9         /// </summary>
  10         public sealed class MySqlHelper
  11         {
  12                 // this class provides only static methods
  13                 private MySqlHelper()
  14                 {
  15                 }
  16
  17                 #region ExecuteNonQuery
  18
  19                 /// <summary>
  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.
  22                 /// </summary>
  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 )
  28                 {
  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;
  34
  35                         if (commandParameters != null)
  36                                 foreach (MySqlParameter p in commandParameters)
  37                                         cmd.Parameters.Add( p );
  38
  39                         int result = cmd.ExecuteNonQuery();
  40                         cmd.Parameters.Clear();
  41
  42                         return result;
  43                 }
  44
  45                 /// <summary>
  46                 /// Executes a single command against a MySQL database.  A new <see cref="MySqlConnection"/> is created
  47                 /// using the <see cref="MySqlConnection.ConnectionString"/> given.
  48                 /// </summary>
  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 )
  54                 {
  55                         //create & open a SqlConnection, and dispose of it after we are done.
  56                         using (MySqlConnection cn = new MySqlConnection(connectionString))
  57                         {
  58                                 cn.Open();
  59
  60                                 //call the overload that takes a connection in place of the connection string
  61                                 return ExecuteNonQuery(cn, commandText, parms );
  62                         }
  63                 }
  64                 #endregion
  65
  66                 #region ExecuteDataSet
  67
  68                 /// <summary>
  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.
  71                 /// </summary>
  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 )
  77                 {
  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];
  83                 }
  84
  85                 /// <summary>
  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.
  88                 /// </summary>
  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)
  93                 {
  94                         //pass through the call providing null for the set of SqlParameters
  95                         return ExecuteDataset(connectionString, commandText, (MySqlParameter[])null);
  96                 }
  97
  98                 /// <summary>
  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.
 101                 /// </summary>
 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)
 107                 {
 108                         //create & open a SqlConnection, and dispose of it after we are done.
 109                         using (MySqlConnection cn = new MySqlConnection(connectionString))
 110                         {
 111                                 cn.Open();
 112
 113                                 //call the overload that takes a connection in place of the connection string
 114                                 return ExecuteDataset(cn, commandText, commandParameters);
 115                         }
 116                 }
 117
 118                 /// <summary>
 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
 121                 /// of this method.
 122                 /// </summary>
 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)
 127                 {
 128                         //pass through the call providing null for the set of SqlParameters
 129                         return ExecuteDataset(connection, commandText, (MySqlParameter[])null);
 130                 }
 131
 132                 /// <summary>
 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
 135                 /// of this method.
 136                 /// </summary>
 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)
 142                 {
 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;
 148
 149                         if (commandParameters != null)
 150                                 foreach (MySqlParameter p in commandParameters)
 151                                         cmd.Parameters.Add( p );
 152
 153                         //create the DataAdapter & DataSet
 154                         MySqlDataAdapter da = new MySqlDataAdapter(cmd);
 155                         DataSet ds = new DataSet();
 156
 157                         //fill the DataSet using default values for DataTable names, etc.
 158                         da.Fill(ds);
 159
 160                         // detach the MySqlParameters from the command object, so they can be used again.
 161                         cmd.Parameters.Clear();
 162
 163                         //return the dataset
 164                         return ds;
 165                 }
 166
 167                 /// <summary>
 168                 /// Updates the given table with data from the given <see cref="DataSet"/>
 169                 /// </summary>
 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 )
 175                 {
 176                         MySqlConnection cn = new MySqlConnection( connectionString );
 177                         cn.Open();
 178                         MySqlDataAdapter da = new MySqlDataAdapter( commandText, cn );
 179                         MySqlCommandBuilder cb = new MySqlCommandBuilder( da );
 180                         da.Update( ds, tablename );
 181                         cn.Close();
 182                 }
 183
 184                 #endregion
 185
 186                 #region ExecuteDataReader
 187
 188                 /// <summary>
 189                 /// Executes a single command against a MySQL database, possibly inside an existing transaction.
 190                 /// </summary>
 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 )
 198                 {
 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;
 205
 206                         if (commandParameters != null)
 207                                 foreach (MySqlParameter p in commandParameters)
 208                                         cmd.Parameters.Add( p );
 209
 210                         //create a reader
 211                         MySqlDataReader dr;
 212
 213                         // call ExecuteReader with the appropriate CommandBehavior
 214                         if (ExternalConn)
 215                         {
 216                                 dr = cmd.ExecuteReader();
 217                         }
 218                         else
 219                         {
 220                                 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 221                         }
 222
 223                         // detach the SqlParameters from the command object, so they can be used again.
 224                         cmd.Parameters.Clear();
 225
 226                         return dr;
 227                 }
 228
 229                 /// <summary>
 230                 /// Executes a single command against a MySQL database.
 231                 /// </summary>
 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)
 236                 {
 237                         //pass through the call providing null for the set of SqlParameters
 238                         return ExecuteReader(connectionString, commandText, (MySqlParameter[])null);
 239                 }
 240
 241                 /// <summary>
 242                 /// Executes a single command against a MySQL database.
 243                 /// </summary>
 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)
 249                 {
 250                         //create & open a SqlConnection
 251                         MySqlConnection cn = new MySqlConnection(connectionString);
 252                         cn.Open();
 253
 254                         try
 255                         {
 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 );
 258                         }
 259                         catch
 260                         {
 261                                 //if we fail to return the SqlDatReader, we need to close the connection ourselves
 262                                 cn.Close();
 263                                 throw;
 264                         }
 265                 }
 266                 #endregion
 267
 268                 #region ExecuteScalar
 269
 270                 /// <summary>
 271                 /// Execute a single command against a MySQL database.
 272                 /// </summary>
 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)
 277                 {
 278                         //pass through the call providing null for the set of MySqlParameters
 279                         return ExecuteScalar(connectionString, commandText, (MySqlParameter[])null);
 280                 }
 281
 282                 /// <summary>
 283                 /// Execute a single command against a MySQL database.
 284                 /// </summary>
 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)
 290                 {
 291                         //create & open a SqlConnection, and dispose of it after we are done.
 292                         using (MySqlConnection cn = new MySqlConnection(connectionString))
 293                         {
 294                                 cn.Open();
 295
 296                                 //call the overload that takes a connection in place of the connection string
 297                                 return ExecuteScalar(cn, commandText, commandParameters);
 298                         }
 299                 }
 300
 301                 /// <summary>
 302                 /// Execute a single command against a MySQL database.
 303                 /// </summary>
 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)
 308                 {
 309                         //pass through the call providing null for the set of MySqlParameters
 310                         return ExecuteScalar(connection, commandText, (MySqlParameter[])null);
 311                 }
 312
 313                 /// <summary>
 314                 /// Execute a single command against a MySQL database.
 315                 /// </summary>
 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)
 321                 {
 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;
 327
 328                         if (commandParameters != null)
 329                                 foreach (MySqlParameter p in commandParameters)
 330                                         cmd.Parameters.Add( p );
 331
 332                         //execute the command & return the results
 333                         object retval = cmd.ExecuteScalar();
 334
 335                         // detach the SqlParameters from the command object, so they can be used again.
 336                         cmd.Parameters.Clear();
 337                         return retval;
 338
 339                 }
 340
 341                 #endregion
 342         }
 343 }