T-SQL Procedure as a Telerik Kendo Grid datasource
<p>I have a Kendo Grid (in ASP.NET MVC C# Application).  The Kendo Grid is:</p>

<pre><code>@(Html.Kendo().Grid&lt;RunSummary&gt;()
      .Name("CheckedPatients")                    
      .DataSource(datasource =&gt; datasource                
            .Ajax().PageSize(25)        
            .ServerOperation(false)                                        
            .Sort(sort =&gt; sort.Add("TimeOn").Descending())
            .Read(read =&gt; read.Action("GetRunSummaries", "PatientReport")))               

      .Columns(columns =&gt;
          {
              columns.Bound(c =&gt; c.UniqueId).Title(ELSORegistry.Resources.Views.Home.HomeStrings.UniqueId)
                  .ClientTemplate("&lt;input type='checkbox'  class='primaryBox' id='#= UniqueId #' value='#= UniqueId #'&gt;#= UniqueId #&lt;/input&gt;");                        
              columns.Bound(c =&gt; c.RunNo).Title(SharedStrings.Run);
              columns.Bound(c =&gt; c.Birthdate).Title(SharedStrings.Birthdate).Format("{0:g}").Filterable(true);

              columns.Bound(c =&gt; c.customAge).Title(SharedStrings.Age)
                     .Filterable(
                         filterable =&gt; filterable
                             .UI("AgeFilter")
                             .Extra(false)
                             .Operators(operators =&gt; operators
                                 .ForString(str =&gt; str.Clear().IsEqualTo("Is equal to"))
                                 )

                   );

              columns.Bound(c =&gt; c.TimeOn).Title(PatientStrings.DateOn)
                  .Format("{0:g}")
                  .Filterable(true);
              columns.Bound(c =&gt; c.TimeOff).Title(PatientStrings.DateOff)
                  .Format("{0:g}")
                  .Filterable(true);
              columns.Bound(c =&gt; c.DischargedAlive).Title(PatientStrings.DischargedAlive).Filterable(true);                  columns.Bound(c =&gt; c.ShowSubmitted).Title(PatientStrings.Submitted).Filterable(true);
              columns.Bound(c =&gt; c.SupportTypeEnum).Title(PatientStrings.SupportType).Filterable(true);              }
      )
      .Pageable(p =&gt; p.PageSizes(new[] {10, 25, 50, 100}))
      .Sortable()
      .Filterable( )
      .Events( e =&gt; e.FilterMenuInit("FilterMenuFuncWithAge") ) // apply x [closing box] on pop up filter box
      )
</code></pre>

<p>When the user checked suitable Unique Ids (or all Unique Ids, using the option Checked All), there are the buttons that provide checked UniqueIds to the action in the Controller to generate PDF :</p>

<pre><code>&lt;a href="#" onclick="checkAll();"&gt;Check All&lt;/a&gt;&amp;nbsp;
            &lt;a href="#" onclick="uncheckAll();"&gt;Uncheck All&lt;/a&gt;&amp;nbsp;
            &lt;a class="k-button k-button-icontext k-grid-Patient" id="hrefCheckedPatients" href="#" onclick="getChecked();"&gt;Generate Report&lt;/a&gt;&amp;nbsp;
            &lt;a class="k-button k-button-icontext k-grid-Patient" id="hrefAllCheckedPatients" style="display:none;" href="#" onclick="getAllChecked();"&gt;Generate Report&lt;/a&gt;&amp;nbsp;
</code></pre>

<p>The javascript functions checkAll() and getAllChecked() are:</p>

<pre><code>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 &lt; 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();
            }
        });
        }
</code></pre>

<p>In the Controller PatientReportController I have the method "GetRunSummaries": </p>

<pre><code> public JsonResult GetRunSummaries([ELSORegistry.Helpers.CustomDataSourceRequest] DataSourceRequest request)
    {
        var center = Session["Center"] as Center;
        var centerId = center != null &amp;&amp; center.CenterNo != 0 ? center.CenterId : (Guid?)null;

        List&lt;RunSummary&gt; myList = new Repository().GetRunSummariesWithIncompleteStoredProcedure(centerId);

        return Json(myList.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);


    }
</code></pre>

<p>(In Repository I call a stored procedure which returns fields for RunSummary). The code that calls the stored procedure is:</p>

<pre><code>public List&lt;RunSummary&gt; GetRunSummariesWithIncompleteStoredProcedure(Guid? centerId = null)
        {

            List&lt;RunSummary&gt; runSummaries = new List&lt;RunSummary&gt;();
            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;

        }
</code></pre>

<p>My problem is that the code works very slowly, and the client has to wait for too long to see the Kendo Grid. Is it possible to send the List to the Kendo Grid without reading each line in the Reader? How can I speed up the code?</p>