当向mysql中插入大量数据时，可以使用微软的批处理方式。这样可以防止内存溢出又提高了效率。我写了一个mysql中插入多条记录的例子。赋值代码可以直接使用。
1、首先需要添加两个dll
MySql.Data.dll和MySqlBulkCopy.dll
2、把MySqlHelper.cs这个类文件加入到项目中 （我把代码贴出来，复制后建一个MySqlHelper.cs）
MySqlHelper.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Collections; using System.Data; using MySql.Data.MySqlClient; namespace Baitour.Utility { /// <summary> /// The MySqlHelper class is intended to encapsulate high performance, scalable best practices for /// common uses of MySqlClient. /// </summary> public sealed class MySqlHelper { #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()". public static readonly string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["MySqlConnString"]; private MySqlHelper() { } /// <summary> /// This method is used to attach array's of MySqlParameters to an 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. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">an array of MySqlParameters tho be added to command</param> private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters) { foreach (MySqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /// <summary> /// This method assigns an array of values to an array of MySqlParameters. /// </summary> /// <param name="commandParameters">array of MySqlParameters to be assigned values</param> /// <param name="parameterValues">array of objects holding the values to be assigned</param> private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the MySqlParameters, assigning the values from the corresponding position in the //value array for (int i = 0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } /// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the MySqlCommand to be prepared</param> /// <param name="connection">a valid MySqlConnection, on which to execute this command</param> /// <param name="transaction">a valid MySqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or MySql statement) command.CommandText = commandText; //honyu添加 //command.CommandTimeout = connection.ConnectionTimeout; command.CommandTimeout = 300;//超时时间5分钟 //if we were provided a transaction, assign it. if (transaction != null) { 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 /// <summary> /// Execute an MySqlCommand (that returns no resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParameters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create & open an MySqlConnection, and dispose of it after we are done. using (MySqlConnection cn = new MySqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns no resultset) against the database specified in /// the connection string using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.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); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlDbCommand (that returns no resultset and takes no parameters) against the provided MySqlConnection. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns no resultset) against the specified MySqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters); //finally, execute the command. return cmd.ExecuteNonQuery(); } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns no resultset) against the specified MySqlConnection /// using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(MySqlConnection connection, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlCommand (that returns no resultset and takes no parameters) against the provided MySqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an int representing the number of rows affected by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns no resultset) against the specified MySqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //finally, execute the command. return cmd.ExecuteNonQuery(); } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns no resultset) against the specified /// MySqlTransaction using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an int representing the number of rows affected by the command</returns> public static int ExecuteNonQuery(MySqlTransaction transaction, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteNonQuery #region ExecuteDataSet /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset( CommandType commandType, string commandText) { //pass through the call providing null for the set of MySqlParameters return ExecuteDataset(ConnectionString, commandType, commandText, (MySqlParameter[])null); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create & open an MySqlConnection, and dispose of it after we are done. using (MySqlConnection cn = new MySqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the database specified in /// the conneciton string using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the specified MySqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); //return the dataset return ds; } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the specified MySqlConnection /// using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(MySqlConnection connection, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the specified MySqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet MySqlDataAdapter da = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); //return the dataset return ds; } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the specified /// MySqlTransaction using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>a dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(MySqlTransaction transaction, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteDataSet #region ExecuteReader /// <summary> /// this enum is used to indicate weather the connection was provided by the caller, or created by MySqlHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader() /// </summary> private enum MySqlConnectionOwnership { /// <summary>Connection is owned and managed by MySqlHelper</summary> Internal, /// <summary>Connection is owned and managed by the caller</summary> External } /// <summary> /// Create and prepare an MySqlCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// 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. /// </remarks> /// <param name="connection">a valid MySqlConnection, on which to execute this command</param> /// <param name="transaction">a valid MySqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by MySqlHelper</param> /// <returns>MySqlDataReader containing the results of the command</returns> private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters, MySqlConnectionOwnership connectionOwnership) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); //create a reader MySqlDataReader dr; // call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == MySqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } return dr; } /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParameters used to execute the command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create & open an MySqlbConnection MySqlConnection cn = new MySqlConnection(connectionString); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, MySqlConnectionOwnership.Internal); } catch { //if we fail to return the MySqlDataReader, we neeed to close the connection ourselves cn.Close(); throw; } } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// MySqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> public static MySqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.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); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlConnection. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the specified MySqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the specified MySqlConnection /// using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// MySqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> public static MySqlDataReader ExecuteReader(MySqlConnection connection, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute an MySqlCommand (that returns a resultset and takes no parameters) against the provided MySqlTransaction. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> 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); } /// <summary> /// Execute an MySqlCommand (that returns a resultset) against the specified MySqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// MySqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of MySqlParameters used to execute the command</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, MySqlConnectionOwnership.External); } /// <summary> /// Execute a stored procedure via an MySqlCommand (that returns a resultset) against the specified /// MySqlTransaction using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// MySqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an MySqlDataReader containing the resultset generated by the command</returns> public static MySqlDataReader ExecuteReader(MySqlTransaction transaction, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteReader #region ExecuteScalar /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> 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); } /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create & open a MySqlConnection, and dispose of it after we are done. using (MySqlConnection cn = new MySqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a stored procedure via a MySqlCommand (that returns a 1x1 resultset) against the database specified in /// the conneciton string using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided MySqlConnection. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> 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); } /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters); //execute the command & return the results return cmd.ExecuteScalar(); } /// <summary> /// Execute a stored procedure via a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlConnection /// using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); /// </remarks> /// <param name="connection">a valid MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(MySqlConnection connection, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided MySqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> 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); } /// <summary> /// Execute a MySqlCommand (that returns a 1x1 resultset) against the specified MySqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <param name="commandParameters">an array of MySqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters) { //create a command and prepare it for execution MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //execute the command & return the results return cmd.ExecuteScalar(); } /// <summary> /// Execute a stored procedure via a MySqlCommand (that returns a 1x1 resultset) against the specified /// MySqlTransaction using the provided parameter 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. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); /// </remarks> /// <param name="transaction">a valid MySqlTransaction</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(MySqlTransaction transaction, string spName, params object[] parameterValues) { //if we got parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { //pull the parameters for this stored procedure from the parameter cache (or discover them & populet the cache) MySqlParameter[] commandParameters = MySqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); //assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); //call the overload that takes an array of MySqlParameters return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); } //otherwise we can just call the SP without params else { return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteScalar } /// <summary> /// MySqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the /// ability to discover parameters for stored procedures at run-time. /// </summary> 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()); /// <summary> /// resolve at run-time the appropriate set of MySqlParameters for a stored procedure /// </summary> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <param name="includeReturnValueParameter">weather or not to onclude ther return value parameter</param> /// <returns></returns> private static MySqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { using (MySqlConnection cn = new MySqlConnection(connectionString)) using (MySqlCommand cmd = new MySqlCommand(spName, cn)) { cn.Open(); cmd.CommandType = CommandType.StoredProcedure; MySqlCommandBuilder.DeriveParameters(cmd); if (!includeReturnValueParameter) { if (ParameterDirection.ReturnValue == cmd.Parameters[0].Direction) cmd.Parameters.RemoveAt(0); } MySqlParameter[] discoveredParameters = new MySqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters, 0); 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 /// <summary> /// add parameter array to the cache /// </summary> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <param name="commandParameters">an array of MySqlParamters to be cached</param> public static void CacheParameterSet(string connectionString, string commandText, params MySqlParameter[] commandParameters) { string hashKey = connectionString + ":" + commandText; paramCache[hashKey] = commandParameters; } /// <summary> /// retrieve a parameter array from the cache /// </summary> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="commandText">the stored procedure name or T-MySql command</param> /// <returns>an array of MySqlParameters</returns> public static MySqlParameter[] GetCachedParameterSet(string connectionString, string commandText) { string hashKey = connectionString + ":" + commandText; MySqlParameter[] cachedParameters = (MySqlParameter[])paramCache[hashKey]; if (cachedParameters == null) { return null; } else { return CloneParameters(cachedParameters); } } #endregion caching functions #region Parameter Discovery Functions /// <summary> /// Retrieves the set of MySqlParameters appropriate for the stored procedure /// </summary> /// <remarks> /// This method will query the database for this information, and then store it in a cache for future requests. /// </remarks> /// <param name="connectionString">a valid connection string for a MySqlConnection</param> /// <param name="spName">the name of the stored prcedure</param> /// <returns>an array of MySqlParameters</returns> public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName) { return GetSpParameterSet(connectionString, spName, true); } /// <summary> /// Retrieves the set of MySqlParameters appropriate for the stored procedure /// </summary> /// <remarks> /// This method will query the database for this information, and then store it in a cache for future requests. /// </remarks> /// <param name="connectionString">a valid connection string for an MySqlConnection</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="includeReturnValueParameter">a bool value indicating weather the return value parameter should be included in the results</param> /// <returns>an array of MySqlParameters</returns> public static MySqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : ""); MySqlParameter[] cachedParameters; cachedParameters = (MySqlParameter[])paramCache[hashKey]; if (cachedParameters == null) { cachedParameters = (MySqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter)); } return CloneParameters(cachedParameters); } #endregion Parameter Discovery Functions } }
3、配置config字符串
<appSettings>
<add key="MySqlConnString" value="Data Source=192.168.2.98;User Id=root;Password=123456;Database=test2;CharSet=utf8;AllowUserVariables=True;" />
</appSettings>
4、程序里写代码
DataTable dt = new DataTable(); dt.Columns.Add("name", typeof(string)); for (int i = 0; i < 10; i++) { DataRow dr = dt.NewRow(); dr["name"] = "hh" + i.ToString(); dt.Rows.Add(dr); } bool isExistValidFile = false; StringBuilder ExternalIDs = new StringBuilder(); #region load data infile string filename = ""; if (System.Environment.CurrentDirectory + "\\" == AppDomain.CurrentDomain.BaseDirectory) { filename = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString().ToUpper().Replace("-", "") + ".txt"; } else { filename = AppDomain.CurrentDomain.BaseDirectory + @"Bin\" + Guid.NewGuid().ToString().ToUpper().Replace("-", "") + ".txt"; } using (System.IO.FileStream fs = new System.IO.FileStream(filename, System.IO.FileMode.Append, System.IO.FileAccess.Write, System.IO.FileShare.Read)) { using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, Encoding.Default)) { foreach (DataRow drObj in dt.Rows) { sw.WriteLine(drObj["name"]); isExistValidFile = true; } sw.Close(); } fs.Close(); GC.Collect(); } if (isExistValidFile) { if (System.IO.File.Exists(filename)) { #region 提交数据库 StringBuilder loadDataSql = new StringBuilder(); loadDataSql.Append("LOAD DATA LOCAL INFILE '" + @filename.Replace("\\", "/") + "' INTO TABLE student CHARACTER SET GBK FIELDS TERMINATED BY '|' "); loadDataSql.Append("(name)"); using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(MySqlHelper.ConnectionString)) { con.Open(); MySql.Data.MySqlClient.MySqlTransaction st = con.BeginTransaction(IsolationLevel.ReadUncommitted); try { MySqlHelper.ExecuteNonQuery(st, CommandType.Text, loadDataSql.ToString()); st.Commit(); GC.Collect(); } catch (Exception ex) { StringBuilder tmpError = new StringBuilder(); tmpError.Append(ex.TargetSite).Append("\r\n" + ex.StackTrace).Append("\r\n" + ex.InnerException).Append("\r\n" + ex.Source).Append("\r\n" + ex.Message); st.Rollback(); } finally { if (con != null) { con.Close(); con.Dispose(); } if (!string.IsNullOrEmpty(filename)) { System.IO.File.Delete(filename); } } } #endregion } else { } } else { } #endregion
