c# 创建mysql存储过程_C# 调用Mysql存储过程

using MySql.Data.MySqlClient;

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Linq;

using System.Text;

namespace LoginFrom

{

class SQLStoredProcedure:DBControl

{

private static string serverIP = ConfigHelper.GetAppConfig("ServerIP");

private static string db = ConfigHelper.GetAppConfig("DataBase");

private static string user = ConfigHelper.GetAppConfig("user");

private static string password = ConfigHelper.GetAppConfig("password");

private static String connectionString = "server=" + serverIP + ";User Id=" + user + ";password=" + password + ";Database=" + db;

///

/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

///

/// 存储过程名

/// 存储过程参数

/// SqlDataReader

public static MySqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)

{

MySqlConnection connection = new MySqlConnection(connectionString);

MySqlDataReader returnReader;

connection.Open();

MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return returnReader;

}

///

/// 执行存储过程

///

/// 存储过程名

/// 存储过程参数

/// DataSet结果中的表名

/// DataSet

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

DataSet dataSet = new DataSet();

connection.Open();

MySqlDataAdapter sqlDA = new MySqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.Fill(dataSet, tableName);

connection.Close();

return dataSet;

}

}

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

DataSet dataSet = new DataSet();

connection.Open();

MySqlDataAdapter sqlDA = new MySqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.SelectCommand.CommandTimeout = Times;

sqlDA.Fill(dataSet, tableName);

connection.Close();

return dataSet;

}

}

///

/// 构建 MySqlCommand 对象(用来返回一个结果集，而不是一个整数值)

///

/// 数据库连接

/// 存储过程名

/// 存储过程参数

/// SqlCommand

private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

MySqlCommand command = new MySqlCommand(storedProcName, connection);

command.CommandType = CommandType.StoredProcedure;

foreach (MySqlParameter parameter in parameters)

{

if (parameter != null)

{

// 检查未分配值的输出参数,将其分配以DBNull.Value.

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

command.Parameters.Add(parameter);

}

}

return command;

}

///

/// 执行存储过程，返回Output输出参数值

///

/// 存储过程名

/// 存储过程参数

/// object

public static object RunProcedure(string storedProcName, IDataParameter[] paramenters)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

connection.Open();

MySqlCommand command = BuildQueryCommand(connection, storedProcName, paramenters);

command.ExecuteNonQuery();

object obj = command.Parameters["@Output_Value"].Value; //@Output_Value和具体的存储过程参数对应

if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))

{

return null;

}

else

{

return obj;

}

}

}

///

/// 执行存储过程，返回影响的行数

///

/// 存储过程名

/// 存储过程参数

/// 影响的行数

///

public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

int result;

connection.Open();

MySqlCommand command = BuildIntCommand(connection, storedProcName, parameters);

rowsAffected = command.ExecuteNonQuery();

result = (int)command.Parameters["ReturnValue"].Value;

//Connection.Close();

return result;

}

}

///

/// 创建 MySqlCommand 对象实例(用来返回一个整数值)

///

/// 存储过程名

/// 存储过程参数

/// SqlCommand 对象实例

private static MySqlCommand BuildIntCommand(MySqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

MySqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.Parameters.Add(new MySqlParameter("ReturnValue",

MySqlDbType.Int16,4, ParameterDirection.ReturnValue,

false, 0, 0, string.Empty, DataRowVersion.Default, null));

return command;

}

}

}