.net mvc调用存储过程 c#调用mysql存储过程

　　由于MYSQL或MSSQL存储过程参数不确定，一般情况下.net 调用存储过程时需针对具体存储过程写不同的调用方法。 本文通过实现先调用系统表中存储过程参数列表，针对参数名称、类型、精度、长度等，动态生成存储过程调用参数，为通用的存储过程调用提供方便。注意类型转换部分要仔细自行验证，确保转换正确。用于MSSQL时类型转换有所不同。

 

/// <summary>
/// 获取存储过程的参数,生成存储过程调用参数列表
/// </summary>
/// <param name="ProcedureName">存储过程名称</param>
/// <param name="parameters">存储过程调用参数值集合</param>
/// <returns></returns>
private MySqlParameter[] GetMySQLParameters(string ProcedureName, object[] parameters)
{
    DataTable dt = new DataTable();
    string sqlString = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + ProcedureName + "' order by ORDINAL_POSITION";
    if (!Select(sqlString, dt))
        return null;
 
    List<MySqlParameter> sqlParameters = new List<MySqlParameter>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        MySqlParameter sqlParameter = new MySqlParameter();
        sqlParameter.ParameterName = dt.Rows[i]["PARAMETER_NAME"].ToString();
        sqlParameter.Direction = (dt.Rows[i]["PARAMETER_MODE"].ToString() == "IN") ? ParameterDirection.Input : ParameterDirection.Output;
 
 
        #region 匹配参数类型
        switch (dt.Rows[i]["DATA_TYPE"].ToString().ToLower ())
        {
            case "bit":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (bool)(parameters[i]);
                sqlParameter.MySqlDbType = MySqlDbType.Bit;
                break;
 
            case "int64":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = Convert.ToInt64(parameters[i]);
                sqlParameter.MySqlDbType = MySqlDbType.Int64;
                break;
            case "int":
            case "int32":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    if (parameters[i] == DBNull.Value)//参数为DBNull时
                    {
                        sqlParameter.Value = DBNull.Value;
                    }
                    else
                    {
                        sqlParameter.Value = Convert.ToInt32(parameters[i]);
                    }
 
                sqlParameter.MySqlDbType = MySqlDbType.Int32;
                break;
 
            case "decimal":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = Convert.ToDecimal(parameters[i]);
                sqlParameter.MySqlDbType = MySqlDbType.Decimal;
                sqlParameter.Precision = Convert.ToByte(dt.Rows[i]["NUMERIC_PRECISION"]);
                sqlParameter.Scale = Convert.ToByte(dt.Rows[i]["NUMERIC_SCALE"]);
                break;
 
            case "varchar":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = parameters[i];
                sqlParameter.Size = (int)dt.Rows[i]["CHARACTER_MAXIMUM_LENGTH"];
                sqlParameter.MySqlDbType = MySqlDbType.VarChar;
                break;
 
 
            case "tinyblob":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.TinyBlob;
                break;
 
            case "meduimblob":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.MediumBlob;
                break;
            case "longblob":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.LongBlob;
                break;
            case "blob":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.Blob;
                break;
 
            case "tinytext":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.TinyText;
                break;
            case "text":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (string)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.Text;
                break;
 
            case "datetime":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.DateTime;
                break;
            case "smalldatetime":
                if (sqlParameter.Direction == ParameterDirection.Input)
                    sqlParameter.Value = (DateTime)parameters[i];
                sqlParameter.MySqlDbType = MySqlDbType.DateTime;
                break;
 
            case "uniqueidentifier":
                sqlParameter.MySqlDbType = MySqlDbType.Text;
                break;
 
            default: break;
        }
        #endregion
 
        sqlParameters.Add(sqlParameter);
    }
 
    return sqlParameters.ToArray ();
}
 
/// <summary>
/// 调用存储过程基本方法，返回DataSet结果
/// </summary>
/// <param name="storedProcName">存储过程</param>
/// <param name="parameters">IDataParameter参数</param>
/// <returns></returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters)
{
    using (MySqlConnection connection = new MySqlConnection(this.connectString))
    {
        connection.Open();
        MySqlCommand cmd = new MySqlCommand(storedProcName, connection);
 
        cmd.CommandType = CommandType.StoredProcedure;
        if (parameters != null)
        {
            foreach (MySqlParameter parameter in parameters)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
 
        }
        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds, "ds");
                cmd.Parameters.Clear();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }
 
}
 
 
//调用存储过程返回DataSet结果集：
public Dataset GetResultByProcedure(string ProcedureName, object[] parameters)
{ 
   MySqlParameter[] sqlParameters = GetMySQLParameters(ProcedureName,parameters);
   DataSet ds= RunProcedure(ProcedureName, sqlParameters);
   return ds;
}

 

 

 

实际使用：

如通过ID获取某城市信息，存储过程为Procedure_GetCityByID，参数为ID，ID=1，则：

Dataset ds=GetResultByProcedure("Procedure_GetCityByID",1)