微软企业库5.0 调用 MySql 分页存储过程

浏览数：23 / 时间：2015年06月12日

1.需要完成两个前置条件后才可以使用微软企业库5.0 调用 MySql 存储过程

2.需要添加一个继承 IParameterMapper 接口的类分配查询参数

using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Grass.Extend;

namespace Grass.MySqlDal
{
  /// <summary>
  /// 分配查询参数
  /// </summary>
  public class MyDbParameterMapper : IParameterMapper
  {
    /// <summary>
    /// 实例化参数分配类实例
    /// </summary>
    /// <param name="pars">参数名数组</param>
    public MyDbParameterMapper(params IDataParameter[] pars)
    {
      m_pars = pars;
    }

    private IDataParameter[] m_pars = null;
    public DbCommand Cmd { set; get; }
    /// <summary>
    /// 分配查询参数
    /// </summary>
    /// <param name="command">查询命令对象</param>
    /// <param name="parameterValues">参数对象</param>
    public void AssignParameters(DbCommand command, object[] parameterValues)
    {
      Cmd = command;
      if (!parameterValues.IsNullOrDbNull() && parameterValues.Length>0)
        m_pars = parameterValues as IDataParameter[];

      if (m_pars == null)
        return;
      //输入参数
      if(!m_pars.IsNullOrDbNull())
        command.Parameters.AddRange(m_pars);
    }
  }
}

3.执行分页存储过程

    [TestMethod]
    public void TestExecStoreProcedure()
    {
      #region 输入参数
      List<MySqlParameter> parameters = new List<MySqlParameter>();
      //查询列
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.VarChar,
        ParameterName = @"_fields",
        Value = "order_no,order_date,order_type",
        Size = 2000
      });
      //查询表
      parameters.Add(new MySqlParameter
        {
          MySqlDbType  = MySqlDbType.Text,
          ParameterName = "_tables",
          Value = "`order`",
          Size = 0
        });
      //查询条件
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.VarChar,
        ParameterName = "_where",
        Value = "1=1",
        Size = 2000
      });
      //排序规则
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.VarChar,
        ParameterName = "_orderby",
        Value = "order_no asc",
        Size = 200
      });
      //查询页码
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.Int32,
        ParameterName = "_pageindex",
        Value = 1,
        Size = 8
      });
      //每页记录数
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.Int32,
        ParameterName = "_pagesize",
        Value = 5,
        Size = 8
      });
      //求和字段，使用逗号分隔
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.VarChar,
        ParameterName = "_sumfields",
        Value = "order_no,order_no,order_no",
        Size = 200
      });
      #endregion

      #region 输出参数
      //总记录数
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.Int32,
        ParameterName = "_totalcount",
        Value = 0,
        Direction = ParameterDirection.Output,
        Size = 8
      });
      //总页数
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.Int32,
        ParameterName = "_pagecount",
        Value = 0,
        Direction = ParameterDirection.Output,
        Size = 8
      });
      //求和结构，值之间使用逗号分隔
      parameters.Add(new MySqlParameter
      {
        MySqlDbType = MySqlDbType.VarChar,
        ParameterName = "_sumResult",
        Value = 0,
        Direction = ParameterDirection.Output,
        Size = 2000
      });
      #endregion
      //定义查询参数
      MyDbParameterMapper param = new MyDbParameterMapper(parameters.ToArray());
      //执行存储过程
      OrderDal dal = new OrderDal();//即 MySqlDatabase 对象的封装
      //其中 DbBase = (Database)MySqlDatabase;
      var accessor = dal.DbBase.CreateSprocAccessor<OrderModel>("sp_MvcCommonDataSource", param);
      IList<OrderModel> pinfo = new List<OrderModel>(accessor.Execute());
      //获取输出参数
      object totalcount = param.Cmd.Parameters["_totalcount"].Value;
      object pagecount = param.Cmd.Parameters["_pagecount"].Value;
      object sumResult = param.Cmd.Parameters["_sumResult"].Value;
      

      Assert.IsNotNull(pinfo);
    }