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

来源：互联网发布：p2p网络摄像机怎么用编辑：程序博客网时间：2024/05/16 01:59

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

微软企业库5.0 支持 MySql

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);    }

</pre><pre>

0 0