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