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         /// Summary description for MySqlHelper.
   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                 public static int ExecuteNonQuery( MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters )
  19                 {
  20                         //create a command and prepare it for execution
  21                         MySqlCommand cmd = new MySqlCommand();
  22                         cmd.Connection = connection;
  23                         cmd.CommandText = commandText;
  24                         cmd.CommandType = CommandType.Text;
  25
  26                         if (commandParameters != null)
  27                                 foreach (MySqlParameter p in commandParameters)
  28                                         cmd.Parameters.Add( p );
  29
  30                         int result = cmd.ExecuteNonQuery();
  31                         cmd.Parameters.Clear();
  32
  33                         return result;
  34                 }
  35
  36                 public static int ExecuteNonQuery( string connectionString, string commandText, params MySqlParameter[] parms )
  37                 {
  38                         //create & open a SqlConnection, and dispose of it after we are done.
  39                         using (MySqlConnection cn = new MySqlConnection(connectionString))
  40                         {
  41                                 cn.Open();
  42
  43                                 //call the overload that takes a connection in place of the connection string
  44                                 return ExecuteNonQuery(cn, commandText, parms );
  45                         }
  46                 }
  47                 #endregion
  48
  49                 #region ExecuteDataSet
  50                 public static DataRow ExecuteDatarow( string connectionString, string commandText, params MySqlParameter[] parms )
  51                 {
  52                         DataSet ds = ExecuteDataset( connectionString, commandText, parms );
  53                         if (ds == null) return null;
  54                         if (ds.Tables.Count == 0) return null;
  55                         if (ds.Tables[0].Rows.Count == 0) return null;
  56                         return ds.Tables[0].Rows[0];
  57                 }
  58
  59                 public static DataSet ExecuteDataset(string connectionString, string commandText)
  60                 {
  61                         //pass through the call providing null for the set of SqlParameters
  62                         return ExecuteDataset(connectionString, commandText, (MySqlParameter[])null);
  63                 }
  64
  65                 public static DataSet ExecuteDataset(string connectionString, string commandText, params MySqlParameter[] commandParameters)
  66                 {
  67                         //create & open a SqlConnection, and dispose of it after we are done.
  68                         using (MySqlConnection cn = new MySqlConnection(connectionString))
  69                         {
  70                                 cn.Open();
  71
  72                                 //call the overload that takes a connection in place of the connection string
  73                                 return ExecuteDataset(cn, commandText, commandParameters);
  74                         }
  75                 }
  76
  77                 public static DataSet ExecuteDataset(MySqlConnection connection, string commandText)
  78                 {
  79                         //pass through the call providing null for the set of SqlParameters
  80                         return ExecuteDataset(connection, commandText, (MySqlParameter[])null);
  81                 }
  82
  83
  84                 public static DataSet ExecuteDataset(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters)
  85                 {
  86                         //create a command and prepare it for execution
  87                         MySqlCommand cmd = new MySqlCommand();
  88                         cmd.Connection = connection;
  89                         cmd.CommandText = commandText;
  90                         cmd.CommandType = CommandType.Text;
  91
  92                         if (commandParameters != null)
  93                                 foreach (MySqlParameter p in commandParameters)
  94                                         cmd.Parameters.Add( p );
  95
  96                         //create the DataAdapter & DataSet
  97                         MySqlDataAdapter da = new MySqlDataAdapter(cmd);
  98                         DataSet ds = new DataSet();
  99
 100                         //fill the DataSet using default values for DataTable names, etc.
 101                         da.Fill(ds);
 102
 103                         // detach the MySqlParameters from the command object, so they can be used again.
 104                         cmd.Parameters.Clear();
 105
 106                         //return the dataset
 107                         return ds;
 108                 }
 109
 110                 public static void UpdateDataSet( string connectionString, string commandText, DataSet ds, string tablename )
 111                 {
 112                         MySqlConnection cn = new MySqlConnection( connectionString );
 113                         cn.Open();
 114                         MySqlDataAdapter da = new MySqlDataAdapter( commandText, cn );
 115                         MySqlCommandBuilder cb = new MySqlCommandBuilder( da );
 116                         da.Update( ds, tablename );
 117                         cn.Close();
 118                 }
 119
 120                 #endregion
 121
 122                 #region ExecuteDataReader
 123                 private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, string commandText, MySqlParameter[] commandParameters, bool ExternalConn )
 124                 {
 125                         //create a command and prepare it for execution
 126                         MySqlCommand cmd = new MySqlCommand();
 127                         cmd.Connection = connection;
 128                         cmd.Transaction = transaction;
 129                         cmd.CommandText = commandText;
 130                         cmd.CommandType = CommandType.Text;
 131
 132                         if (commandParameters != null)
 133                                 foreach (MySqlParameter p in commandParameters)
 134                                         cmd.Parameters.Add( p );
 135
 136                         //create a reader
 137                         MySqlDataReader dr;
 138
 139                         // call ExecuteReader with the appropriate CommandBehavior
 140                         if (ExternalConn)
 141                         {
 142                                 dr = cmd.ExecuteReader();
 143                         }
 144                         else
 145                         {
 146                                 dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 147                         }
 148
 149                         // detach the SqlParameters from the command object, so they can be used again.
 150                         cmd.Parameters.Clear();
 151
 152                         return dr;
 153                 }
 154
 155                 public static MySqlDataReader ExecuteReader(string connectionString, string commandText)
 156                 {
 157                         //pass through the call providing null for the set of SqlParameters
 158                         return ExecuteReader(connectionString, commandText, (MySqlParameter[])null);
 159                 }
 160
 161                 public static MySqlDataReader ExecuteReader(string connectionString, string commandText, params MySqlParameter[] commandParameters)
 162                 {
 163                         //create & open a SqlConnection
 164                         MySqlConnection cn = new MySqlConnection(connectionString);
 165                         cn.Open();
 166
 167                         try
 168                         {
 169                                 //call the private overload that takes an internally owned connection in place of the connection string
 170                                 return ExecuteReader(cn, null, commandText, commandParameters, false );
 171                         }
 172                         catch
 173                         {
 174                                 //if we fail to return the SqlDatReader, we need to close the connection ourselves
 175                                 cn.Close();
 176                                 throw;
 177                         }
 178                 }
 179                 #endregion
 180
 181                 #region ExecuteScalar
 182                 public static object ExecuteScalar(string connectionString, string commandText)
 183                 {
 184                         //pass through the call providing null for the set of MySqlParameters
 185                         return ExecuteScalar(connectionString, commandText, (MySqlParameter[])null);
 186                 }
 187
 188                 public static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] commandParameters)
 189                 {
 190                         //create & open a SqlConnection, and dispose of it after we are done.
 191                         using (MySqlConnection cn = new MySqlConnection(connectionString))
 192                         {
 193                                 cn.Open();
 194
 195                                 //call the overload that takes a connection in place of the connection string
 196                                 return ExecuteScalar(cn, commandText, commandParameters);
 197                         }
 198                 }
 199
 200                 public static object ExecuteScalar(MySqlConnection connection, string commandText)
 201                 {
 202                         //pass through the call providing null for the set of MySqlParameters
 203                         return ExecuteScalar(connection, commandText, (MySqlParameter[])null);
 204                 }
 205
 206                 public static object ExecuteScalar(MySqlConnection connection, string commandText, params MySqlParameter[] commandParameters)
 207                 {
 208                         //create a command and prepare it for execution
 209                         MySqlCommand cmd = new MySqlCommand();
 210                         cmd.Connection = connection;
 211                         cmd.CommandText = commandText;
 212                         cmd.CommandType = CommandType.Text;
 213
 214                         if (commandParameters != null)
 215                                 foreach (MySqlParameter p in commandParameters)
 216                                         cmd.Parameters.Add( p );
 217
 218                         //execute the command & return the results
 219                         object retval = cmd.ExecuteScalar();
 220
 221                         // detach the SqlParameters from the command object, so they can be used again.
 222                         cmd.Parameters.Clear();
 223                         return retval;
 224
 225                 }
 226
 227                 #endregion
 228         }
 229 }