C# Paginate SQL result from a proc call

Question

I have the following method that calls a proc in my database and returns the results into a dataset. The dataset is then used to populate a table I render using MVC & cshtml.

The method is:

public DataSet CallProcToDataSet(string procName)
{
    DataSet ds = new DataSet();

    string constr = ConfigurationManager.ConnectionStrings["UAT"].ConnectionString;

    using (SqlConnection con = new SqlConnection(constr))
    {

        using (SqlCommand cmd = new SqlCommand(procName))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;


            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                sda.Fill(ds);
            }
        }
    }

    return ds;
}

This works fine, however what I want is to have some way of paginating the results so that only 10 at a time are displayed. What is the best way for me to achieve this that doesn't involve any changes to the proc calls?

What pagination method you're currently using? This post can help you decide: https://stackoverflow.com/questions/446196/how-do-i-do-pagination-in-asp-net-mvc. — Tetsuya Yamamoto, Aug 25 '17 at 09:26
@TetsuyaYamamoto I'm not using any at the moment. But I'll take a look at the link — N0xus, Aug 25 '17 at 09:29

score 0 · Answer 1 · answered Aug 25 '17 at 09:44

0

Try following :

            DataSet ds = new DataSet();
            DataTable dt = ds.Tables[0];

            for (int i = 0; i < dt.Rows.Count; i += 10)
            {
                DataTable pageTable = dt.AsEnumerable().Where((x, n) => (n >= i) && (n < i + 10)).CopyToDataTable();
            }

answered Aug 25 '17 at 09:44

jdweng

33,250
2
15
20

Wouldn't a `skip` and `take` be easier? – Stefan Aug 25 '17 at 09:47
OP hasn't decided which pagination method to use yet. So it is possible to use other methods like `Skip` & `Take`. – Tetsuya Yamamoto Aug 25 '17 at 09:52
I just thought of Where() method first. – jdweng Aug 25 '17 at 09:58
@jdweng placed the datatable and for statement before i return my dataset at the end of my method but no pagination is happening. – N0xus Aug 25 '17 at 10:00
You code needs to be changed to allow looping. I just did a very simple example of how to get 10 rows at a time. I don't think you want to keep on querying the database every time you want the next 10 rows so you need to add a new method for paging through the table. – jdweng Aug 25 '17 at 10:23

score 0 · Accepted Answer · answered Aug 25 '17 at 10:09

You can use this overload of the Fill method

public int Fill(int startRecord, int maxRecords, params DataTable[] dataTables);

(link: https://msdn.microsoft.com/en-us/library/0z5wy74x(v=vs.110).aspx) This way you will only return a subset of the records without modifying your stored procedure.

Example (MSTest)

 [TestMethod]
        public void TestMethod1()
        {
            DataSet ds = new DataSet();
            var procName = "sp_server_info";
            string constr = ConfigurationManager.ConnectionStrings["UAT"].ConnectionString;
            var tblName = "result";
            var tbls = new[] { ds.Tables.Add(tblName) };
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(procName))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = con;

                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        sda.Fill(0, 10, tbls);
                    }
                }
            }
            Assert.AreEqual(tbls[0].Rows.Count, 10);

        }

Thank you. What would then be the best way to render the page option? Like if I had 100 records, I should have 10 pages with the ability to go between. What would you suggest? — N0xus, Aug 25 '17 at 10:23
You're welcome, I would ask a separate question about that by specifying that you already have paging at data layer level. — Andrea Scarcella, Aug 25 '17 at 10:26

C# Paginate SQL result from a proc call

2 Answers2