T-SQL过程作为Telerik剑道网格数据源
本文关键字：网格 数据源 Telerik 过程 T-SQL | 更新日期: 2023-09-27 17:58:13
我有一个Kendo网格（在ASP.NET MVC C#应用程序中）。剑道网格是：
@(Html.Kendo().Grid<RunSummary>()
.Name("CheckedPatients")
.DataSource(datasource => datasource
.Ajax().PageSize(25)
.ServerOperation(false)
.Sort(sort => sort.Add("TimeOn").Descending())
.Read(read => read.Action("GetRunSummaries", "PatientReport")))
.Columns(columns =>
{
columns.Bound(c => c.UniqueId).Title(ELSORegistry.Resources.Views.Home.HomeStrings.UniqueId)
.ClientTemplate("<input type='checkbox' class='primaryBox' id='#= UniqueId #' value='#= UniqueId #'>#= UniqueId #</input>");
columns.Bound(c => c.RunNo).Title(SharedStrings.Run);
columns.Bound(c => c.Birthdate).Title(SharedStrings.Birthdate).Format("{0:g}").Filterable(true);
columns.Bound(c => c.customAge).Title(SharedStrings.Age)
.Filterable(
filterable => filterable
.UI("AgeFilter")
.Extra(false)
.Operators(operators => operators
.ForString(str => str.Clear().IsEqualTo("Is equal to"))
)
);
columns.Bound(c => c.TimeOn).Title(PatientStrings.DateOn)
.Format("{0:g}")
.Filterable(true);
columns.Bound(c => c.TimeOff).Title(PatientStrings.DateOff)
.Format("{0:g}")
.Filterable(true);
columns.Bound(c => c.DischargedAlive).Title(PatientStrings.DischargedAlive).Filterable(true); columns.Bound(c => c.ShowSubmitted).Title(PatientStrings.Submitted).Filterable(true);
columns.Bound(c => c.SupportTypeEnum).Title(PatientStrings.SupportType).Filterable(true); }
)
.Pageable(p => p.PageSizes(new[] {10, 25, 50, 100}))
.Sortable()
.Filterable( )
.Events( e => e.FilterMenuInit("FilterMenuFuncWithAge") ) // apply x [closing box] on pop up filter box
)
当用户选中合适的唯一ID（或所有唯一ID，使用checked all选项）时，控制器中的按钮会为生成PDF:的操作提供选中的唯一ID
<a href="#" onclick="checkAll();">Check All</a>
<a href="#" onclick="uncheckAll();">Uncheck All</a>
<a class="k-button k-button-icontext k-grid-Patient" id="hrefCheckedPatients" href="#" onclick="getChecked();">Generate Report</a>
<a class="k-button k-button-icontext k-grid-Patient" id="hrefAllCheckedPatients" style="display:none;" href="#" onclick="getAllChecked();">Generate Report</a>
javascript函数checkAll（）和getAllChecked（）是：
function checkAll() {
$('#hrefCheckedPatients').hide();
$('#hrefAllCheckedPatients').show();
$('input').prop('checked', 'checked');
checkedArray = [];
var dataSource = $('#CheckedPatients').data('kendoGrid').dataSource;
var filters = dataSource.filter();
var allData = dataSource.data();
var query = new kendo.data.Query(allData);
var data = query.filter(filters).data;
var totalNumber = data.length;
for (var i = 0; i < totalNumber; i++) {
var currentDataItem = data[i];
checkedArray.push(currentDataItem.get("UniqueId"));
}
}
function getAllChecked() {
$.ajax({
type: "POST",
url: "/PatientReport/ExportToPDF",
dataType: "json",
traditional: true,
data: { uniqueIds: checkedArray },
success: function (data) {
if (data.success) {
$('#myFrame').attr('src', '/PatientReport/DownloadFile' + '?fName=' + data.fName);
}
},
error: function (jqXHR, textStatus, errorThrown) {
$("#effect:visible").fadeOut();
$('#checkedMsg').text('@ELSORegistry.Resources.Views.Patient.PatientStrings.CheckedError').show();
$('#hrefCheckedPatients').blur();
}
});
}
在控制器PatientReportController中，我有方法"GetRunSummaries"：
public JsonResult GetRunSummaries([ELSORegistry.Helpers.CustomDataSourceRequest] DataSourceRequest request)
{
var center = Session["Center"] as Center;
var centerId = center != null && center.CenterNo != 0 ? center.CenterId : (Guid?)null;
List<RunSummary> myList = new Repository().GetRunSummariesWithIncompleteStoredProcedure(centerId);
return Json(myList.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}
（在Repository中，我调用一个存储过程，该过程返回RunSummary的字段）。调用存储过程的代码是：
public List<RunSummary> GetRunSummariesWithIncompleteStoredProcedure(Guid? centerId = null)
{
List<RunSummary> runSummaries = new List<RunSummary>();
var context = new ELSORegistryEntities();
context.Configuration.ProxyCreationEnabled = false;
string connection = context.Database.Connection.ConnectionString;
using (SqlConnection con = new SqlConnection(connection))
{
SqlCommand cmd = new SqlCommand("IGD_spPatientListReportFillGrid", con);
System.Data.SqlClient.SqlParameter @CenterId = new System.Data.SqlClient.SqlParameter
{
ParameterName = "@CenterId"
,
DbType = DbType.Guid
,
Value = centerId.HasValue ? (object)centerId : DBNull.Value
};
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(@CenterId);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
RunSummary runSummary = new RunSummary();
runSummary.UniqueId = rdr["UniqueId"].ToString();
runSummary.RunNo = Convert.ToInt16(rdr["RunNo"]);
runSummary.Birthdate = Convert.ToDateTime(rdr["Birthdate"]);
runSummary.customAge = rdr["customAge"].ToString();
runSummary.TimeOn = Convert.ToDateTime(rdr["TimeOn"]);
if (rdr["TimeOff"] != DBNull.Value)
{
runSummary.TimeOff =Convert.ToDateTime(rdr["TimeOff"]);
}
if (rdr["DischargedAlive"] != DBNull.Value)
{
runSummary.DischargedAlive = Convert.ToBoolean(rdr["DischargedAlive"]);
}
if (rdr["ShowSubmitted"] != DBNull.Value)
{
runSummary.ShowSubmitted = Convert.ToBoolean(rdr["ShowSubmitted"]);
}
runSummaries.Add(runSummary);
}
}
return runSummaries;
}
我的问题是代码运行非常缓慢，客户端必须等待太长时间才能看到Kendo网格。是否可以在不阅读阅读器中的每一行的情况下将列表发送到剑道网格？如何加速代码？
尝试以下操作：
使用Kendo DatasourceRequest来返回List对象。
同时只发送行中剑道网格所需的记录（等于页面大小）。
当用户移动到下一页/排序/筛选网格时，Make.ServerOperation（true）只从Controller发送必要的记录。您可以向SQL存储过程传递一个名为pagesize和page的参数，以获取所需的记录。
[HttpPost]
public JsonResult Get([DataSourceRequest] DataSourceRequest request)
{
var employees = db.Employees.Where(e => e.IsActive);
var employeeViewModel = employees.Project().To<EmployeeViewModel>();
var results = employeeViewModel.ToDataSourceResult(request);
return Json(results);
}
编辑：
请检查以下逻辑是否有效：
场景1：为所有唯一ID 生成PDF
我认为这很容易，因为你需要所有的唯一ID，而不是必须的——它应该来自网格，你可以使用"GetRunSummaries"的Action的修改版本和Session["Center"]的值来获得它。
场景2：为选定的唯一ID 生成PDF
当用户单击（选中/取消选中）网格中的特定行时，会填充一个类似的全局数组。这可能比下面的代码稍微困难一些，但你可以做到
$(".primaryBox").on('click', '#CheckedPatients', function()
{
//Fill a global/public array named "checkedArray = [];" with the Unique Id value of that particular Checkbox whenever user check that respective checkbox
});
点击"生成"按钮
$("#hrefCheckedPatients").on('click', function(){
// get value from global array checkedArray[];
// call the Ajax function and send the value from above array.
});