在ria service中使用存储过程分页

     正如在前面所提到的，绑定大数据量的情况下，通过store procedure的方式来查询数据，这种方式由于比较慢，还有一点就是数据量过大，一下子绑定到datagrid中就会导致页面卡死的情况。无奈~~  ria service 中可以通过webconfig来设置超时时间

<basicHttpBinding>
        <binding name="MyBasicHttpBinding" closeTimeout="00:10:00" openTimeout="00:10:00" sendTimeout="00:10:00" maxBufferSize="2147483647" maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647">
          <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
        </binding>
      </basicHttpBinding>

 

切入正题

  首先是要写个存储过程来确定数据量，以此来绑上datapager。写好存储过程后，用ef导入存储过程，返回标量，导入了存储过程以后，在Domainservice中写一个方法

        #region 自定义标量存储过程返回
        private T ExecuteFunction<T>(string functionName, System.Data.EntityClient.EntityParameter[] parameters) where T : struct
        {
            System.Data.EntityClient.EntityCommand cmd = ((System.Data.EntityClient.EntityConnection)this.ObjectContext.Connection).CreateCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameters);
            cmd.CommandText = this.ObjectContext.DefaultContainerName + "." + functionName;
            try
            {
                if (cmd.Connection.State != System.Data.ConnectionState.Open)
                    cmd.Connection.Open();
                var obj = cmd.ExecuteScalar();
                return (T)obj;
            }
            catch (System.Exception)
            {
                throw;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion
这样后，我们就能调用返回int的存储过程了，调用方式如下

public int GetPMTotalPages(int PageSize ,string starttime, string endtime,int pm_type,int group_type)
        {                      
            System.Data.EntityClient.EntityParameter[] op = {
                                                           new EntityParameter("startDate",DbType.String),
                                                           new EntityParameter("endDate",DbType.String),                                                          
                                                           new EntityParameter("pm_type",DbType.Int32),
                                                           new EntityParameter("group_type",DbType.Int32)
                                                           };
            op[0].Value = starttime;
            op[1].Value = endtime;
            op[2].Value = pm_type;
            op[3].Value = group_type;
            return ExecuteFunction<int>("proc_GetPM_Count", op) /( PageSize +1 ) +1;
        }

客户端的调用方法：

ds_t.GetPMTotalPages(Pagesize, starttime,endtime, PM_Type, cbb_method.SelectedIndex, s =>
            {
                if (!s.HasError)
                {
                    int totalpagers = s.Value;
                    for (int i = 1; i <= totalpagers; i++)
                        itemCount.Add(i);
                    PagedCollectionView pcv = new PagedCollectionView(itemCount);
                    pcv.PageSize = 1;  =======================>这里会引发pageindexchanged事件！
                    this.pagerGrid.Source = pcv;
                }
            }, null);

 

所以，只要在pageindexchanged事件中绑定数据即可

BackgroundWorker worker = new BackgroundWorker();
           
            worker.DoWork += (s, ea) =>
                {
                    Dispatcher.BeginInvoke(new Action(() =>
                        {
                            my_busy.IsBusy = true;
                            if (PM_Type == 0)
                            {
                                ds_t.Load(ds_t.Proc_T_AC_PM_ByPageQuery("2011-07-01", "2011-10-10", cbb_method.SelectedIndex, PageSize, ((DataPager)sender).PageIndex + 1),
                              (lo) =>
                              {
                                  if (lo.Error != null)
                                  {
                                      MessageBox.Show(lo.Error.ToString());
                                      return;
                                  }
                                 
                                  var query = from i in lo.Entities
                                              select new
                                              {
                                                  i.CPU利用率,
                                                  i.内存利用率,
                                                  i.上行速率,
                                                  i.下行速率
                                              };
                                  PagedCollectionView pcv = new PagedCollectionView(lo.Entities);
                                  this.PMGrid.ItemsSource = pcv;
                              }, false);
                            }
worker.RunWorkerCompleted += (o, ea) =>
            {                
                my_busy.IsBusy = false;
            };
            worker.RunWorkerAsync(); 
终于大功告成了，哈哈哈哈！