Server side pagination with MVC C# and MySql

Question

I didn't understand how does server-side pagination works with MySql and Datatable in a C# MVC. I created a Controlled in C#, in which I established the connection with a MySql database (I followed this example in order to do that):

public ActionResult connectDB()
    {

        const string DB_CONN_STR = "Server=MyServer;Port=MyPort;Uid=MyUid;Database=MyDB;";

        MySqlConnection cn = new MySqlConnection(DB_CONN_STR);

        try
        {

            string sqlCmd = "select * from t_documento";

            MySqlDataAdapter adr = new MySqlDataAdapter(sqlCmd, cn);
            adr.SelectCommand.CommandType = CommandType.Text;
            DataTable dt = new DataTable();
            adr.Fill(dt); //opens and closes the DB connection automatically !! (fetches from pool)

            return Content(JsonConvert.SerializeObject(dt).ToString());
        }
        catch (Exception ex)
        {
            Console.WriteLine("{oops - {0}", ex.Message);
            return Content(ex.ToString());
        }
        finally
        {
            cn.Dispose(); // return connection to pool

        }        
    }

However, in this way, I retrieve all the records stored in that table, but I want to fill the Datatable (the inizialization of my datatable is located in a cshtml page) by implementing the pagination. I read a lot of articles but I didn't found a clear example with a MySql DB. Can someone help me, please? Thank you!

You can do this using (stored) procedure. Simply add a page number to your controller and pass it to the model and procedure. Here are some good references for pagination in SQL Server: [A More Efficient Method for Paging Through Large Result Sets](http://www.4guysfromrolla.com/webtech/042606-1.shtml) and [Row Offset in SQL Server](https://stackoverflow.com/questions/187998/row-offset-in-sql-server). I am sure MySql DB will be very similar. — Weihui Guo, Aug 04 '17 at 16:06

score 0 · Answer 1 · answered May 30 '17 at 07:47

Try this Example of server side pagination.

/Controllers/ProductController.cs

public class ProductController : Controller
{
    public object Index(int? page)
    {
        var products = MyProductDataSource.FindAllProducts(); //returns IQueryable<Product> representing an unknown number of products. a thousand maybe?

        var pageNumber = page ?? 1; // if no page was specified in the querystring, default to the first page (1)
        var onePageOfProducts = products.ToPagedList(pageNumber, 25); // will only contain 25 products max because of the pageSize

        ViewBag.OnePageOfProducts = onePageOfProducts;
        return View();
    }
}

/Views/Products/Index.cshtml

@{
    ViewBag.Title = "Product Listing"
}
@using PagedList.Mvc; //import this so we get our HTML Helper
@using PagedList; //import this so we can cast our list to IPagedList (only necessary because ViewBag is dynamic)

<!-- import the included stylesheet for some (very basic) default styling -->
<link href="/Content/PagedList.css" rel="stylesheet" type="text/css" />

<!-- loop through each of your products and display it however you want. we're just printing the name here -->
<h2>List of Products</h2>
<ul>
    @foreach(var product in ViewBag.OnePageOfProducts){
        <li>@product.Name</li>
    }
</ul>

<!-- output a paging control that lets the user navigation to the previous page, next page, etc -->
@Html.PagedListPager( (IPagedList)ViewBag.OnePageOfProducts, page => Url.Action("Index", new { page }) )

Thank you, but I'm interested only in this specific implementation... I need Datatables AND MySql, NOT Sql Server or another type of database... I don't know if the particular type of db is relevant or not, but I'm really having difficult in retrieving examples that use this kind of combination. — Robychan, May 30 '17 at 08:16

Server side pagination with MVC C# and MySql

1 Answers1