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

Bertha 。 2024-02-17 14:43 3阅读 0赞

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

[微软企业库5.0 支持 MySql][5.0 _ MySql]

[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>

[5.0 _ MySql]: http://blog.csdn.net/xxj_jing/article/details/28611215
[MySql]: http://blog.csdn.net/xxj_jing/article/details/28610767