mysql怎么插入多条记录_mysql中插入多条记录-微软批处理

当向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

{

///

/// The MySqlHelper class is intended to encapsulate high performance, scalable best practices for

/// common uses of MySqlClient.

///

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() { }

///

/// 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.

///

/// The command to which the parameters will be added

/// an array of MySqlParameters tho be added to command

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);

}

}

///

/// This method assigns an array of values to an array of MySqlParameters.

///

/// array of MySqlParameters to be assigned values

/// array of objects holding the values to be assigned

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];

}

}

///

/// 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 PL/SQL command

/// an array of MySqlParameters to be associated with the command or 'null' if no parameters are required

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

///

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

/// the connection string.

///

///

/// e.g.:

/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");

///

/// a valid connection string for an MySqlConnection

/// the CommandType (stored procedure, text, etc.)

/// the stored procedure name or PL/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 an MySqlCommand (that returns no resultset) against the database specified in the connection string

/// using the provided parameters.

///

///

/// e.g.:

/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

/// a valid connection string for a MySqlConnection

/// the CommandType (stored procedure, text, etc.)

/// the stored procedure name or PL/SQL command

/// an array of MySqlParameters 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)

{

//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);

}

}

///

/// 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.

///

///

/// 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);

///

/// a valid connection string for a MySqlConnection

/// the name of the stored prcedure

/// an array of objects to be assigned as the input values of the stored procedure

/// an int representing the number of rows affected by the command

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);

}

}

///

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

///

///

/// e.g.:

/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");

///

/// a valid MySqlConnection

/// the CommandType (stored procedure, text, etc.)

/// the stored procedure name or PL/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 an MySqlCommand (that returns no resultset) against the specified MySqlConnection

/// using the provided parameters.

///

///

/// e.g.:

/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

/// a valid MySqlConnection

/// the CommandType (stored procedure, text, etc.)

/// the stored procedure name or PL/SQL command

/// an array of MySqlParamters 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)

{

//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();

}

///

/// 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.

///

///

/// 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);

///

/// a valid MySqlConnection

/// the name of the stored prcedure

/// an array of objects to be assigned as the input values of the stored procedure

/// an int representing the number of rows affected by the command

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,