Migrating an ASP.NET application from MS SQL Server To MySQL

During last week I was given a short term assignment: Migration of an ASP.NET 3.5 based application from MS SQL Server To MySQL. In fact it was very urgent requirement and one of my managers was little pushed by the client.

I was wrapping up the Reporting Services project, It was monday evening when he called and shared his worries. Anyways that's too specific and I know you are not interested to listen. Ok so what I had to do:

1. MySQL setup/ DB creation and data migration.


2. DAL layer changes.


3. Integration & Testing

Yes I can accept It's simple job but only if you are kidding ;)

Ok what I did:-

Database's Objects Migration

1. I installed MySQL on my development box. Please remember McAfee is not a friend of yours to install and configure MySQL DB. I disabled McAfee's access protection during the setup. But It is not always a case and otherwise it's not so tough.


2. I created a database for the project.


3. I downloaded MySQL Migration Toolkit(mysql-gui-tools-5.0-r17-win32.msi) from http://dev.mysql.com/downloads/gui-tools/5.0.html to export objects from MS SQL to MySQL. and I tried the export. But It didn't help enough to achieve the goals. It copied few tables and shown errors for others. I tried to fix with heuristics, I'll point out later.


4. I also searched on net for migration tools. I got certain tools but not free of cost. And I wasn't relying on automations and other thing was cost factor, so I decided to apply some tricks.


5. I got generated sql script from MS SQL and convert the whole script in MySQL specific syntaxes ;) and I ran those scripts on MySQL.
   

…..And my life was 20% happy ;)

DAL Migration:

Now I had to change DAL layer to set for MySQL. It was priorily using System.Data.SqlClient in DAL. So I was told to change and optimize it for MySQL. I did following things to achieve the goal.

1. I got MySql.Data.MySqlClient (Connector/Net 5.2) for my need from MySQL website: http://dev.mysql.com/downloads/connector/net/5.2.html.


2. The existing solution's DAL was having a sort of SQL Helper class based on System.Data.SqlClient. Here I replaced this by MySql.Data.MySqlClient.


3. I read user manual from here : http://dev.mysql.com/doc/refman/5.1/en/connector-net-ref-mysqlclient.html.


4. I found a clue and changed all SqlClient based objects in SQLHelper class just prefixing "My" and indeed after a succeessful build of Data Access Layer, my life seemed cool and I was all set to test the application.


5. But grrrrrrrr!! My SPs were not working... errorrrrrrrs. I re-analyzed my lacuna. and again some interesting changes I had to make in order to fix the problem for example I had to replace the sign '@' by '?' while calling a stored procedure from DAL.. and finally I could run the application smoothly :-).
   

What I learnt from this exercise:

• Don't expect MySQL to have all the same features as MS SQL: If You are using xp_cmdshell in your MSSQL Stored procedures, do not expect MySQL is also able to do the same.


• Table Variables are not available in MySQL.


• Stored procedure call from DAL in MySQL is prefixed by a question mark i.e. '?' not by a '@' as compared to MS SQL Server.


• Couple of data types such as MONEY, IMAGE are not supported in MySQL.


• You cannot make an identity(autonumber/autoincrement) field on MySQL table without making it a primary key or certain other column as a primary key.


• MySQL does not support Full Joins.


• Error Handling in MySQL is different from MSSQL and If you are using Try/Catch in your T-SQL code supported by CLR, then you are going to face a really hard time. Find alternatives in terms of output parameters etc.
• Last but not the least, Please learn basics of MySQL programming before going to take responsibility. Varible declarations, control /looping structures, string handling, system functions are pretty different.

Well, this project was not very complex, I wrapped in 2 days. Cool start in MySQL : )

I got thanks mail from the manager finally...; )

Sudhir

Updates: 10-dec-2009

MySqlHelper.cs is added:

// ===============================================================================
//This class is based on MySQL .NET Provider and similar to MySslClient
// It is revised to meet common needs of an application built in .NET and tested for ASP.NET 2.0
// For more you can send a mail to sudhir@sahpathi.com
// ==============================================================================
using System;
using System.Data;
using System.Xml;
using MySql.Data.MySqlClient;
using System.Collections;
　
///

/// The MySqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient
///

public sealed class MySqlHelper
{
///

/// Kalyan (23-01-2007): The DB user name.
///

public const string DB_USER_NAME = "";
#region private utility methods & constructors
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new MySqlHelper()"
private MySqlHelper() { }
///

/// This method is used to attach array of MySqlParameters to a MySqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
///

/// The command to which the parameters will be added
/// An array of MySqlParameters to be added to command
private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (MySqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
///

/// This method assigns dataRow column values to an array of MySqlParameters
///

/// Array of MySqlParameters to be assigned values
/// The dataRow used to hold the stored procedure's parameter values
private static void AssignParameterValues(MySqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) (dataRow == null))
{
// Do nothing if we get no data
return;
}
int i = 0;
// Set the parameters values
foreach (MySqlParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///

/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
///

/// The MySqlCommand to be prepared
/// A valid MySqlConnection, on which to execute this command
/// A valid MySqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of MySqlParameters to be associated with the command or 'null' if no parameters are required
/// true if the connection was opened by the method, otherwose is false.
private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = DB_USER_NAME + commandText; // Kalyan (23-01-2007): Use Inline Sql at your own risk !!
　
// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion private utility methods & constructors
#region ExecuteNonQuery
///

/// Execute a MySqlCommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string
///

///
/// e.g.:
/// int result = ExecuteNonQuery(connString, "PublishOrders");
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteNonQuery(connectionString, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters
///

///
/// e.g.:
/// int result = ExecuteNonQuery(connString, "PublishOrders", new MySqlParameter("@prodid", 24));
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a MySqlConnection, and dispose of it after we are done
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
///

/// Execute a MySqlCommand (that returns no resultset and takes no parameters) against the provided MySqlConnection.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders");
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteNonQuery(connection, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns no resultset) against the specified MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
///

/// Execute a MySqlCommand (that returns no resultset and takes no parameters) against the provided MySqlTransaction.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(trans, "PublishOrders");
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteNonQuery(transaction, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns no resultset) against the specified MySqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// int result = ExecuteNonQuery(trans, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
#endregion ExecuteNonQuery
#region ExecuteDataset
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders");
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a MySqlConnection, and dispose of it after we are done
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlConnection.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders");
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteDataset(connection, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// Return the dataset
return ds;
}
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlTransaction.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders");
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(MySqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteDataset(transaction, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDataset(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
// Return the dataset
return ds;
}
}
#endregion ExecuteDataset
#region ExecuteReader
///

/// This enum is used to indicate whether the connection was provided by the caller, or created by MySqlHelper, so that
/// we can set the appropriate CommandBehavior when calling ExecuteReader()
///

private enum MySqlConnectionOwnership
{
///

Connection is owned and managed by MySqlHelper

Internal,
///

Connection is owned and managed by the caller

External
}
///

/// Create and prepare a MySqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
///

///
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
///
/// A valid MySqlConnection, on which to execute this command
/// A valid MySqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of MySqlParameters to be associated with the command or 'null' if no parameters are required
/// Indicates whether the connection parameter was provided by the caller, or created by MySqlHelper
/// MySqlDataReader containing the results of the command
private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, MySqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create a reader
MySqlDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == MySqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the MySqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (MySqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(connString, "GetOrders");
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteReader(connectionString, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(connString, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
MySqlConnection connection = null;
try
{
connection = new MySqlConnection(connectionString);
connection.Open();
// Call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(connection, null, commandType, commandText, commandParameters, MySqlConnectionOwnership.Internal);
}
catch
{
// If we fail to return the SqlDatReader, we need to close the connection ourselves
if (connection != null) connection.Close();
throw;
}
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlConnection.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(conn, "GetOrders");
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(MySqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteReader(connection, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(conn, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
// Pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection, (MySqlTransaction)null, commandType, commandText, commandParameters, MySqlConnectionOwnership.External);
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlTransaction.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(trans, "GetOrders");
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteReader(transaction, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// MySqlDataReader dr = ExecuteReader(trans, "GetOrders", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Pass through to private overload, indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, MySqlConnectionOwnership.External);
}
#endregion ExecuteReader
#region ExecuteScalar
///

/// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount");
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteScalar(connectionString, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", new MySqlParameter("@prodid", 24));
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a MySqlConnection, and dispose of it after we are done
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
///

/// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided MySqlConnection.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount");
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteScalar(connection, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
///

/// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided MySqlTransaction.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount");
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of MySqlParameters
return ExecuteScalar(transaction, commandType, commandText, (MySqlParameter[])null);
}
///

/// Execute a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", new MySqlParameter("@prodid", 24));
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
MySqlCommand cmd = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the MySqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
#endregion ExecuteScalar
　
#region FillDataset
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///

///
/// e.g.:
/// FillDataset(connString, "GetOrders", ds, new string[] {"orders"});
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create & open a MySqlConnection, and dispose of it after we are done
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames);
}
}
///

/// Execute a MySqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(connString, "GetOrders", ds, new string[] {"orders"}, new MySqlParameter("@prodid", 24));
///
/// A valid connection string for a MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
public static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create & open a MySqlConnection, and dispose of it after we are done
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlConnection.
///

///
/// e.g.:
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"});
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
public static void FillDataset(MySqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
public static void FillDataset(MySqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params MySqlParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
///

/// Execute a MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlTransaction.
///

///
/// e.g.:
/// FillDataset(trans, "GetOrders", ds, new string[] {"orders"});
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
public static void FillDataset(MySqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}
///

/// Execute a MySqlCommand (that returns a resultset) against the specified MySqlTransaction
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(trans, "GetOrders", ds, new string[] {"orders"}, new MySqlParameter("@prodid", 24));
///
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
public static void FillDataset(MySqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params MySqlParameter[] commandParameters)
{
FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
}
///

/// Private helper method that execute a MySqlCommand (that returns a resultset) against the specified MySqlTransaction and MySqlConnection
/// using the provided parameters.
///

///
/// e.g.:
/// FillDataset(conn, trans, "GetOrders", ds, new string[] {"orders"}, new MySqlParameter("@prodid", 24));
///
/// A valid MySqlConnection
/// A valid MySqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
private static void FillDataset(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params MySqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create a command and prepare it for execution
MySqlCommand command = new MySqlCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command))
{
// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the MySqlParameters from the command object, so they can be used again
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
#endregion
#region UpdateDataset
///

/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///

///
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
///
/// A valid transact-SQL statement or stored procedure to insert new records into the data source
/// A valid transact-SQL statement or stored procedure to delete records from the data source
/// A valid transact-SQL statement or stored procedure used to update records in the data source
/// The DataSet used to update the data source
/// The DataTable used to update the data source.
public static void UpdateDataset(MySqlCommand insertCommand, MySqlCommand deleteCommand, MySqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null tableName.Length == 0) throw new ArgumentNullException("tableName");
// Create a MySqlDataAdapter, and dispose of it after we are done
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
#endregion
#region CreateCommand
///

/// Simplify the creation of a Sql command object by allowing
/// a stored procedure and optional parameters to be provided
///

///
/// e.g.:
/// MySqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
///
/// A valid MySqlConnection object
/// The name of the stored procedure
/// An array of string to be assigned as the source columns of the stored procedure parameters
/// A valid MySqlCommand object
public static MySqlCommand CreateCommand(MySqlConnection connection, string spName, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// Create a MySqlCommand
MySqlCommand cmd = new MySqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
// If we receive parameter values, we need to figure out where they go
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided source columns to these parameters based on parameter order
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the MySqlCommand object
AttachParameters(cmd, commandParameters);
}
return cmd;
}
#endregion
#region ExecuteNonQueryTypedParams
///

/// Execute a stored procedure via a MySqlCommand (that returns no resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///

/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteNonQuery(connectionString, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteNonQuery(connectionString, spName);
}
}
///

/// Execute a stored procedure via a MySqlCommand (that returns no resultset) against the specified MySqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
public static int ExecuteNonQueryTypedParams(MySqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteNonQuery(connection, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteNonQuery(connection, spName);
}
}
#endregion
#region ExecuteDatasetTypedParams
///

/// Execute a stored procedure via a MySqlCommand (that returns a resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///

/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
//If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteDataset(connectionString, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteDataset(connectionString, spName);
}
}
///

/// Execute a stored procedure via a MySqlCommand (that returns a resultset) against the specified MySqlConnection
/// using the dataRow column values as the store procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
public static DataSet ExecuteDatasetTypedParams(MySqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteDataset(connection, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteDataset(connection, spName);
}
}
#endregion
#region ExecuteReaderTypedParams
///

/// Execute a stored procedure via a MySqlCommand (that returns a resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///

/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteReader(connectionString, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteReader(connectionString, spName);
}
}
　
///

/// Execute a stored procedure via a MySqlCommand (that returns a resultset) against the specified MySqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A MySqlDataReader containing the resultset generated by the command
public static MySqlDataReader ExecuteReaderTypedParams(MySqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteReader(connection.ConnectionString, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteReader(connection.ConnectionString, spName);
}
}
#endregion
#region ExecuteScalarTypedParams
///

/// Execute a stored procedure via a MySqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///

/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteScalar(connectionString, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteScalar(connectionString, spName);
}
}
///

/// Execute a stored procedure via a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
public static object ExecuteScalarTypedParams(MySqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return MySqlHelper.ExecuteScalar(connection, spName, commandParameters);
}
else
{
return MySqlHelper.ExecuteScalar(connection, spName);
}
}
#endregion
}
///

/// MySqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
///

public sealed class MySqlHelperParameterCache
{
#region private methods, variables, and constructors
//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new MySqlHelperParameterCache()"
private MySqlHelperParameterCache() { }
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
///

/// Resolve at run time the appropriate set of MySqlParameters for a stored procedure
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// Whether or not to include their return value parameter
/// The parameter array discovered.
private static MySqlParameter[] DiscoverSpParameterSet(MySqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
MySqlCommand cmd = new MySqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
MySqlCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (MySqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
///

/// Deep copy of cached MySqlParameter array
///

///
///
private static MySqlParameter[] CloneParameters(MySqlParameter[] originalParameters)
{
MySqlParameter[] clonedParameters = new MySqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (MySqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
#endregion private methods, variables, and constructors
#region caching functions
///

/// Add parameter array to the cache
///

/// A valid connection string for a MySqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters to be cached
public static void CacheParameterSet(string connectionString, string commandText, params MySqlParameter[] commandParameters)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
paramCache[hashKey] = commandParameters;
}
///

/// Retrieve a parameter array from the cache
///

/// A valid connection string for a MySqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters
public static MySqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
MySqlParameter[] cachedParameters = paramCache[hashKey] as MySqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
#endregion caching functions
#region Parameter Discovery Functions
///

/// Retrieves the set of MySqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// An array of MySqlParameters
public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
///

/// Retrieves the set of MySqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid connection string for a MySqlConnection
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of MySqlParameters
public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}
///

/// Retrieves the set of MySqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid MySqlConnection object
/// The name of the stored procedure
/// An array of MySqlParameters
internal static MySqlParameter[] GetSpParameterSet(MySqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}
///

/// Retrieves the set of MySqlParameters appropriate for the stored procedure
///

///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid MySqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of MySqlParameters
internal static MySqlParameter[] GetSpParameterSet(MySqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
using (MySqlConnection clonedConnection = (MySqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
}
}
///

/// Retrieves the set of MySqlParameters appropriate for the stored procedure
///

/// A valid MySqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of MySqlParameters
private static MySqlParameter[] GetSpParameterSetInternal(MySqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null spName.Length == 0) throw new ArgumentNullException("spName");
string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
MySqlParameter[] cachedParameters;
cachedParameters = paramCache[hashKey] as MySqlParameter[];
if (cachedParameters == null)
{
MySqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[hashKey] = spParameters;
cachedParameters = spParameters;
}
return CloneParameters(cachedParameters);
}
#endregion Parameter Discovery Functions
}
　

I completed 1 year with BrickRed

Yesterday I woke up in the morning overhearing a little discussion between my elder brother and a delivery boy at door. Actually my company had sent a buquet and greeting cards for me on the occasion of completing one year with the company.

I wanted to share my one year summary yesterday only but I got now that to share due to certain unavoidable things.

I was hired for an Upload Module for a client dealing in Mortgage domain. The idea behind the project was to get the data from various users/clients/financial instutions in their specific format having their own schemas to be uploaded in an uniform database schema of the vendor. I was told to provide a smart client as well as scheduled windows services using FTP pickup solution. It was very exciting opportunity. I enjoyed it alongwith my manager and another team member. The main chunk was to write SQL to upload data in various tables with all robust mechanism like Transactions, Errors, Reportings, Queues, Recycling etc..
My Manger was thinking very seriously to make it as a product. I played a lot in windows services and Quartz.NET as well. It was a nice assignment and the best at that time :).

Next I worked on a similar assignment for aviation domain. But this time data was coming in hetrogenous file formats. So I was told to write parsers and use .NET reflection to load parser's assemply at runtime. as this time database used was Oracle 10g. So it was nice to work in Array based method counterpart to BCP in SQL server. I got a true experience writing stored procedures and managing other objects in Oracle 10g. I got some DBA experience as well ;).

Next I was awarded with another assignment for BPMS (Business Process Management System) suite. I was told to write components for that and certain pages on MVP model. The client was using their proprietary tool for ORM. Apart from the technical stuff, It's mentioning a worth here that I learnt a little Spanish too because client was from Argentina. And people there were not very good in English... thanks Google Translation and that girl from client side ;) she helped me in learning .... gracias!!

Next I was given a Reporting services assignment for a big company. I already mentioned about the project in a previous blog.


Currently I am working for loyalty based client. I hope I' ll invest my good and quality time for this project for a certain good amount of time. I have been working on short term assignments where lots of technical versatilities/intricacies were involved.
..........Looking forward to utilize my best for upcoming assignments... Thanks to the people I worked with : )

Best Regards
Sudhir

Dynamic Reports in SQL Server Reporting Services 2008

Problem Summary:
My last assignment was on SQL Server Reporting Services 2008. I was given to create/export direclty (into excel or pdf format) reports from ASP.NET web interface basis the search criterias where number of the columns were not fixed; columns were supposed to come from the rows of queries.
Now prabibly a question tosses in your mind Why SSRS, not the traditional way of COM/office component to write excel file instead of exporting by SSRS things. The answer is to uniform the development/runtime environment with stronger .NET based things. and off course to implement security for report access with SSRS 2008. Also reports can be bind any time with Report Viewer Control.

So what I did:
1. Wrote stored procedure to result data passing the search criterias; off course lots of dynamic sql to make columns based on the rows in the query results for specific input parameters and then populating desired data to those columns based on the certain manipulations.
2. Generated memory stream of .rdl (Report definition language) based on the dynamic schema being resulted after executing the stored procedures created in the first step. It involves lots of C# code to write XML format of rdl formatting and binding the data elements to the report skeleton.
3. Exported report in the desired format(.xls/pdf etc.) using the compiled proxy class of the Report Server Web service Library (ReportingService2005.dll).

Yes It was really a challenging stuff recently I faced.
I would like to share with you in detail if you are doing the similar kind of stuff.

Cheers!!
Sudhir