/// <summary>/// MySqlHelper 的摘要說明。/// </summary>public class MySqlHelper{public static string connectionString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;#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 SqlHelper()".private SqlHelper(){}/// <summary>/// This method is used to attach array of SqlParameters to a SqlCommand./// /// 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 SqlParameters tho be added to command</param>private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters){try{foreach (MySqlParameter p in commandParameters){//check for derived output value with no value assignedif ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)){p.Value = DBNull.Value;}else{if (p.Value == null){p.Value = DBNull.Value;}}command.Parameters.Add(p);}}catch (Exception ex){Utils.WriteLogFile(ex.Message.ToString(), "異常日志");}}/// <summary>/// This method assigns an array of values to an array of SqlParameters./// </summary>/// <param name="commandParameters">array of SqlParameters to be assigned values</param>/// <param name="parameterValues">array of Components holding the values to be assigned</param>private static void AssignParameterValues(MySqlParameter[] commandParameters, object[] parameterValues){try{if ((commandParameters == null) || (parameterValues == null)){//do nothing if we get no datareturn;}// we must have the same number of values as we pave parameters to put them inif (commandParameters.Length != parameterValues.Length){throw new ArgumentException("Parameter count does not match Parameter Value count.");}//iterate through the SqlParameters, assigning the values from the corresponding position in the //value arrayfor (int i = 0, j = commandParameters.Length; i < j; i++){commandParameters[i].Value = parameterValues[i];}}catch (Exception ex){Utils.WriteLogFile(ex.Message.ToString(), "異常日志");}}/// <summary>/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command./// </summary>/// <param name="command">the SqlCommand to be prepared</param>/// <param name="connection">a valid SqlConnection, on which to execute this command</param>/// <param name="transaction">a valid SqlTransaction, or 'null'</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParameters 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){try{//if the provided connection is not open, we will open itif (connection.State != ConnectionState.Open){connection.Open();}//associate the connection with the commandcommand.Connection = connection;//set the command text (stored procedure name or SQL statement)command.CommandText = commandText;//if we were provided a transaction, assign it.if (transaction != null){command.Transaction = transaction;}//set the command typecommand.CommandType = commandType;//attach the command parameters if they are providedif (commandParameters != null){AttachParameters(command, commandParameters);}}catch (Exception ex){Utils.WriteLogFile(ex.Message.ToString(), "異常日志");}return;}#endregion private utility methods & constructors#region DataHelperspublic static string CheckNull(object obj){return (string)obj;}public static string CheckNull(DBNull obj){return null;}#endregion#region AddParameterspublic static object CheckForNullString(string text){if (text == null || text.Trim().Length == 0){return System.DBNull.Value;}else{return text;}}public static MySqlParameter MakeInParam(string ParamName, object Value){return new MySqlParameter(ParamName, Value);}/// <summary>/// Make input param./// </summary>/// <param name="ParamName">Name of param.</param>/// <param name="DbType">Param type.</param>/// <param name="Size">Param size.</param>/// <param name="Value">Param value.</param>/// <returns>New parameter.</returns>public static MySqlParameter MakeInParam(string ParamName, MySqlDbType DbType, int Size, object Value){return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);}/// <summary>/// Make input param./// </summary>/// <param name="ParamName">Name of param.</param>/// <param name="DbType">Param type.</param>/// <param name="Size">Param size.</param>/// <returns>New parameter.</returns>public static MySqlParameter MakeOutParam(string ParamName, MySqlDbType DbType, int Size){return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);}/// <summary>/// Make stored procedure param./// </summary>/// <param name="ParamName">Name of param.</param>/// <param name="DbType">Param type.</param>/// <param name="Size">Param size.</param>/// <param name="Direction">Parm direction.</param>/// <param name="Value">Param value.</param>/// <returns>New parameter.</returns>public static MySqlParameter MakeParam(string ParamName, MySqlDbType DbType, Int32 Size,ParameterDirection Direction, object Value){MySqlParameter param;if (Size > 0)param = new MySqlParameter(ParamName, DbType, Size);elseparam = new MySqlParameter(ParamName, DbType);param.Direction = Direction;if (!(Direction == ParameterDirection.Output && Value == null))param.Value = Value;return param;}#endregion#region ExecuteNonQuery/// <summary>/// Execute a SqlCommand (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 a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-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 SqlParametersreturn ExecuteNonQuery(connectionString, commandType, commandText, (MySqlParameter[])null);}public static int ExecuteNonQuery(CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//pass through the call providing null for the set of SqlParametersreturn ExecuteNonQuery(connectionString, commandType, commandText, commandParameters);}/// <summary>/// Execute a SqlCommand (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 SqlParameter("?prodid", 24));/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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 a SqlConnection, 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 stringreturn ExecuteNonQuery(cn, commandType, commandText, commandParameters);}}/// <summary>/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-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 SqlParametersreturn ExecuteNonQuery(connection, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){MySqlTransaction sqltran;sqltran = connection.BeginTransaction();int retval = -1;try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, sqltran, commandType, commandText, commandParameters);//cmd.CommandTimeout = 5;//finally, execute the command.retval = cmd.ExecuteNonQuery();sqltran.Commit();// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();}catch (Exception ex){try{sqltran.Rollback();}catch (Exception e){}string str = string.Empty;for (int i = 0; i < commandParameters.Length - 1; i++){str += commandParameters[i].ParameterName + "=" + commandParameters[i].Value.ToString() + ",";}Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString() + "參數(shù)值為:" + str, "異常日志");retval = -1;}finally{connection.Close();}return retval;}/// <summary>/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-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 SqlParametersreturn ExecuteNonQuery(transaction, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);//cmd.CommandTimeout = 5;//finally, execute the command.int retval = cmd.ExecuteNonQuery();// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();return retval;}catch (Exception ex){string str = string.Empty;if (commandParameters != null){for (int i = 0; i < commandParameters.Length - 1; i++){str += commandParameters[i].ParameterName + "=" + commandParameters[i].Value.ToString() + ",";}}Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString() + "參數(shù)值為:" + str, "異常日志");return -1;}finally{//transaction.Connection.Close();}}#endregion ExecuteNonQuery#region ExecuteDataSetpublic static DataSet ExecuteDataset(CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataset(connectionString, commandType, commandText, commandParameters);}/// <summary>/// Execute a SqlCommand (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 a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a dataset containing the resultset generated by the command</returns>public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataset(connectionString, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (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 SqlParameter("?prodid", 24));/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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 a SqlConnection, 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 stringreturn ExecuteDataset(cn, commandType, commandText, commandParameters);}}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-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 SqlParametersreturn ExecuteDataset(connection, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);//create the DataAdapter & DataSetMySqlDataAdapter da = new MySqlDataAdapter(cmd);DataSet ds = new DataSet();//fill the DataSet using default values for DataTable names, etc.da.Fill(ds);// detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear();//return the datasetreturn ds;}catch (Exception ex){Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}finally{connection.Close();}return null;}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-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 SqlParametersreturn ExecuteDataset(transaction, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);//create the DataAdapter & DataSetMySqlDataAdapter da = new MySqlDataAdapter(cmd);DataSet ds = new DataSet();//fill the DataSet using default values for DataTable names, etc.da.Fill(ds);// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();//return the datasetreturn ds;}catch (Exception ex){transaction.Connection.Close();Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}return null;}#endregion ExecuteDataSet#region ExecuteDataTablepublic static DataTable ExecuteDataTable(CommandType commandType, string commandText){return ExecuteDataTable(connectionString, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataTable(connectionString, commandType, commandText, (MySqlParameter[])null);}public static DataTable ExecuteDataTable(CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//create & open a SqlConnection, 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 stringreturn ExecuteDataTable(cn, commandType, commandText, commandParameters);}}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//create & open a SqlConnection, 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 stringreturn ExecuteDataTable(cn, commandType, commandText, commandParameters);}}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataTable(connection, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText,params MySqlParameter[] commandParameters){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();cmd.CommandTimeout = 140;PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);//create the DataAdapter & DataTableMySqlDataAdapter da = new MySqlDataAdapter(cmd);DataTable dt = new DataTable();//fill the DataTable using default values for DataTable names, etc.da.Fill(dt);// detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear();return dt;}catch (Exception ex){Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}finally{connection.Close();}return null;}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteDataTable(transaction, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a DataTable containing the resultset generated by the command</returns>public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText,params MySqlParameter[] commandParameters){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();cmd.CommandTimeout = 140;PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);//create the DataAdapter & DataTableMySqlDataAdapter da = new MySqlDataAdapter(cmd);DataTable dt = new DataTable();//fill the DataTable using default values for DataTable names, etc.da.Fill(dt);// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();//return the DataTablereturn dt;}catch (Exception ex){transaction.Connection.Close();Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}return null;}#endregion ExecuteDataTable#region ExecuteReader/// <summary>/// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that/// we can set the appropriate CommandBehavior when calling ExecuteReader()/// </summary>private enum SqlConnectionOwnership{/// <summary>Connection is owned and managed by SqlHelper</summary>Internal,/// <summary>Connection is owned and managed by the caller</summary>External}/// <summary>/// Create and prepare a SqlCommand, 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 SqlConnection, on which to execute this command</param>/// <param name="transaction">a valid SqlTransaction, or 'null'</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParameters 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 SqlHelper</param>/// <returns>SqlDataReader containing the results of the command</returns>private static MySqlDataReader ExecuteReader(MySqlConnection connection, MySqlTransaction transaction,CommandType commandType, string commandText,MySqlParameter[] commandParameters,SqlConnectionOwnership connectionOwnership){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);//create a readerMySqlDataReader dr;// call ExecuteReader with the appropriate CommandBehaviorif (connectionOwnership == SqlConnectionOwnership.External){dr = cmd.ExecuteReader();}else{dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);}// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();return dr;}catch (Exception ex){connection.Close();Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}return null;}public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText){//pass through the call providing null for the set of SqlParametersreturn ExecuteReader(connectionString, commandType, commandText, (MySqlParameter[])null);}public static MySqlDataReader ExecuteReader(CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//pass through the call providing null for the set of SqlParametersreturn ExecuteReader(connectionString, commandType, commandText, commandParameters);}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a SqlDataReader 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 SqlParametersreturn ExecuteReader(connectionString, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a SqlDataReader containing the resultset generated by the command</returns>public static MySqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//create & open a SqlConnectionMySqlConnection cn = new MySqlConnection(connectionString);cn.Open();try{//call the private overload that takes an internally owned connection in place of the connection stringreturn ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);}catch{//if we fail to return the SqlDatReader, we need to close the connection ourselvescn.Close();throw;}}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a SqlDataReader 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 SqlParametersreturn ExecuteReader(connection, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a SqlDataReader 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 connectionreturn ExecuteReader(connection, (MySqlTransaction)null, commandType, commandText, commandParameters,SqlConnectionOwnership.External);}/// <summary>/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <returns>a SqlDataReader 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 SqlParametersreturn ExecuteReader(transaction, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>/// <returns>a SqlDataReader 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 callerreturn ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters,SqlConnectionOwnership.External);}#endregion ExecuteReader#region ExecuteScalar/// <summary>/// Execute a SqlCommand (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 SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL 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 SqlParametersreturn ExecuteScalar(connectionString, commandType, commandText, (MySqlParameter[])null);}public static object ExecuteScalar(CommandType commandType, string commandText,params MySqlParameter[] commandParameters){//pass through the call providing null for the set of SqlParametersreturn ExecuteScalar(connectionString, commandType, commandText, commandParameters);}/// <summary>/// Execute a SqlCommand (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 SqlParameter("?prodid", 24));/// </remarks>/// <param name="connectionString">a valid connection string for a SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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 SqlConnection, 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 stringreturn ExecuteScalar(cn, commandType, commandText, commandParameters);}}/// <summary>/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary>/// <remarks>/// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL 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 SqlParametersreturn ExecuteScalar(connection, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="connection">a valid SqlConnection</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);//execute the command & return the resultsobject retval = cmd.ExecuteScalar();// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();return retval;}catch (Exception ex){Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}finally{connection.Close();}return null;}/// <summary>/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. /// </summary>/// <remarks>/// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL 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 SqlParametersreturn ExecuteScalar(transaction, commandType, commandText, (MySqlParameter[])null);}/// <summary>/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction/// using the provided parameters./// </summary>/// <remarks>/// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("?prodid", 24));/// </remarks>/// <param name="transaction">a valid SqlTransaction</param>/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>/// <param name="commandText">the stored procedure name or T-SQL command</param>/// <param name="commandParameters">an array of SqlParamters 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){try{//create a command and prepare it for executionMySqlCommand cmd = new MySqlCommand();PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);//execute the command & return the resultsobject retval = cmd.ExecuteScalar();// detach the SqlParameters from the command object, so they can be used again.cmd.Parameters.Clear();return retval;}catch (Exception ex){transaction.Connection.Close();Utils.WriteLogFile("執(zhí)行" + commandText + "時" + ex.Message.ToString(), "異常日志");}return null;}#endregion ExecuteScalar}

?

轉(zhuǎn)載于:https://www.cnblogs.com/Warmsunshine/p/5740687.html

與50位技術(shù)專家面對面20年技術(shù)見證，附贈技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的MySql 数据操作类的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。