How to implement paging in mvc with sql query

Question

Hi I have one query that is made in sql server as below and the sp name is as sp_StudentRequest

Select DISTINCT SR.StudentRequestId,SR.RegistrationId,SR.Location,SR.PaymentMethod,SR.CreatedOn,C.ClassName,CC.CampusName,
CASE WHEN ISNULL(TSR.StatusId,0)=0 THEN 1 ELSE TSR.StatusId END AS StatusId,
 substring(
(
    Select ', '+REPLACE(REPLACE(ST1.FromTime,'AM',''),'PM','')+'-'+ST1.ToTime AS [text()]
    From dbo.StudentRequestTimings ST1
    Where ST1.StudentRequestId = SRT.StudentRequestId
    ORDER BY ST1.CreatedOn
    For XML PATH ('')
), 2, 1000) [Time]
FROM StudentRequest SR
Inner JOIN Registration R ON R.RegistrationId=SR.RegistrationId
INNER JOIN Campus CC ON CC.CampusId=R.CampusId
INNER JOIN Class C ON C.ClassId=SR.ClassId 
LEFT JOIN TutorClasses TC ON SR.ClassId=TC.ClassId
LEFT JOIN StudentRequestTimings SRT ON SR.StudentRequestId=SRT.StudentRequestId
LEFT JOIN TutorStudentRequest TSR ON TSR.StudentRequestId=SRT.StudentRequestId AND TutorId=@RegistrationId
where TC.RegistrationId=@RegistrationId
ORDER BY SR.CreatedOn DESC

and I am calling this code from controller as

public ActionResult TutorDashboard(int? page)
        {               
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        using (SqlCommand cmd = new SqlCommand("sp_StudentRequest"))
                        {

                            DataSet GetData = new DataSet();
                            cmd.Connection = con;
                            con.Open();
                            cmd.Parameters.AddWithValue("@Operation", "GetTutorRequestDetail");
                            cmd.Parameters.AddWithValue("@RegistrationId", 1);

                            cmd.CommandType = CommandType.StoredProcedure;
                            SqlDataAdapter ad = new SqlDataAdapter(cmd);
                            ad.Fill(GetData);
                            if (GetData.Tables[0].Rows.Count > 0)
                            {
                                ViewBag.TutorRequest = GetData.Tables[0].ToPagedList(page ?? 1, 1);
                            }  
                            con.Close();
                        }
                    }
            return View(ViewBag.TutorRequest);
        }

Now I need tom implement the paging in MVC with the help of PagedList, How Can I do so?

If I am not able to make use of this sql query then what is the way to convert this query in entity framework?

Any help will be useful to me.

You need to project your data in to a model, not a `DataSet` — , Nov 07 '17 at 09:53
@StephenMuecke to get data in model I need to implement entity/linq query right? or we can directly get data from sql query and can fill in model? — Xtremcool, Nov 07 '17 at 09:55
You could use a `DataReader` to read the results. But like using a `DataSet` that defeats the whole purpose of server side paging since you downloading all the data. Using EF with linq is the best approach (.ToPagedList()` uses `.Skip()` and `.Take()` internally to generate the sql query that returns only the records your need) — , Nov 07 '17 at 09:59
But if you do want you use your sp, then add additional parameters for `OFFSET` and `NEXT` (refer [this answer](https://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-query) for an example (and then use a `DataReader` to project the result into a model) — , Nov 07 '17 at 10:01
@StephenMuecke can you help me out for making this sql query as linq/entity query? — Xtremcool, Nov 07 '17 at 10:04
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/158405/discussion-between-stephen-muecke-and-xtremcool). — , Nov 07 '17 at 11:05
It's same sp as written in this question I was trying to transfer this query in entity I am stuck for comma separated value — Xtremcool, Nov 08 '17 at 00:07
@Stephen Muecke can u help me out to simplify this query in linq. I have tried that in that another question but stucked for how to get comma separated value — Xtremcool, Nov 08 '17 at 00:09
Also updated that question . As I had edited from my mobile the layout of sp may not look proper — Xtremcool, Nov 08 '17 at 00:23
Please stick to chat :). I'll take a look in an hour or so (and edit it for you as well) — , Nov 08 '17 at 00:26

score 1 · Accepted Answer · answered Nov 09 '17 at 11:25

To apply paging I had converted my query in entity frame work and below is the code

var query = (from sr in db.StudentRequests
             join r in db.Registrations on sr.RegistrationId equals r.RegistrationId
             join cc in db.Campus on r.CampusId equals cc.CampusId
             join c in db.Classes on sr.ClassId equals c.ClassId
             from tc in db.TutorClasses.Where(t => t.ClassId == sr.ClassId).DefaultIfEmpty()
             from srt in db.StudentRequestTimings.Where(s => s.StudentRequestId == sr.StudentRequestId).DefaultIfEmpty()
             from tsr in db.TutorStudentRequests.Where(t => t.StudentRequestId == srt.StudentRequestId && t.TutorId == registrationid).DefaultIfEmpty()
             where tc.RegistrationId == registrationid
             select new
             {
               StudentRequestId = sr.StudentRequestId,
               RegistrationId = sr.RegistrationId,
               Location = sr.Location,
               PaymentMethod = sr.PaymentMethod,
               CreatedOn = sr.CreatedOn,
               ClassName = c.ClassName,
               CampusName = cc.CampusName,
               StatusId = tsr.StatusId == null ? 1 : tsr.StatusId,
               Time = db.StudentRequestTimings.Where(p => p.StudentRequestId == sr.StudentRequestId).Select(p => p.FromTime.ToString().Replace("AM", "").Replace("PM", "") + "-" + p.ToTime)
               }).ToList().GroupBy(p => new { p.StudentRequestId }).Select(g => g.First()).ToList();


          var model = query.AsEnumerable().Select(x => new TutorDashboard
           {
              StudentRequestId = x.StudentRequestId,
              RegistrationId = x.RegistrationId,
              Location = x.Location,
              PaymentMethod = x.PaymentMethod,
              CreatedOn = x.CreatedOn,
              ClassName = x.ClassName,
              CampusName = x.CampusName,
              StatusId = x.StatusId == null ? 1 : x.StatusId,
              Time = string.Join(",", x.Time),
               }).ToList().ToPagedList(page ?? 1, 1);

And at last I had pass this model on view side.

How to implement paging in mvc with sql query

1 Answers1

Linked