In this article I will show you how to use datatables.net in MVC4 with JSON.
First start VS, choose MVC4 and empty project.
Because we are creating empty project in reference list add EntityFramework, in my case, we have to add DLL like:
C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET MVC 4\Packages\EntityFramework.5.0.0\lib\net40\EntityFramework.dll
In same way add System.Web.Optimization: C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET MVC 4\Packages\Microsoft.AspNet.Web.Optimization.1.0.0\lib\net40\System.Web.Optimization.dll
and WebGrease: C:\Program Files (x86)\Microsoft ASP.NET\ASP.NET MVC 4\Packages\WebGrease.1.1.0\lib\WebGrease.dll
Also add System.Data.Entity:
Download latest version of datatables, and I just took all media folder and added to scripts part (scripts part I also just copy / pasted from generated project - not empty), like on picture, and script folder I copied from another project:
Now there is part which I don't understand, unfortunately, we need @RenderSection("scripts", required: false), and because of that I just added Shared folder and _ViewStart.cshtml, most probably because of this:
"RenderSection can only exist in Layout files (i.e. master pages)... its purpose is to allow the pages you can request directly to target various sections of a Layout (layout being a file common to all pages which choose to use it) and supply content for these different sections."
From here.
In my D:\Projects\VS\DataTablesMvc4\Views\Shared\_Layout.cshtml I have lines like:
@Scripts.Render("~/bundles/jquery")
@RenderSection("scripts", required: false)
</body>
And also, at beginning of _Layout.cshtml I added:
@using System.Web.Optimization
In App_Start add file BundleConfig.cs and code:
using System.Web.Optimization;
namespace DataTablesMvc4
{
public class BundleConfig
{
// For more information on Bundling, visit http://go.microsoft.com/fwlink/?LinkId=254725
public static void RegisterBundles(BundleCollection bundles)
{
bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
"~/Scripts/jquery-{version}.js"));
bundles.Add(new ScriptBundle("~/bundles/jqueryui").Include(
"~/Scripts/jquery-ui-{version}.js"));
bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
"~/Scripts/jquery.unobtrusive*",
"~/Scripts/jquery.validate*"));
// Use the development version of Modernizr to develop with and learn from. Then, when you're
// ready for production, use the build tool at http://modernizr.com to pick only the tests you need.
bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
"~/Scripts/modernizr-*"));
bundles.Add(new StyleBundle("~/Content/css").Include("~/Content/site.css"));
bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
"~/Content/themes/base/jquery.ui.core.css",
"~/Content/themes/base/jquery.ui.resizable.css",
"~/Content/themes/base/jquery.ui.selectable.css",
"~/Content/themes/base/jquery.ui.accordion.css",
"~/Content/themes/base/jquery.ui.autocomplete.css",
"~/Content/themes/base/jquery.ui.button.css",
"~/Content/themes/base/jquery.ui.dialog.css",
"~/Content/themes/base/jquery.ui.slider.css",
"~/Content/themes/base/jquery.ui.tabs.css",
"~/Content/themes/base/jquery.ui.datepicker.css",
"~/Content/themes/base/jquery.ui.progressbar.css",
"~/Content/themes/base/jquery.ui.theme.css"));
bundles.Add(new ScriptBundle("~/bundles/table").Include(
"~/Scripts/media/js/jquery.dataTables.js"));
bundles.Add(new StyleBundle("~/Content/themes/base/css").Include(
"~/Scripts/media/css/jquery.ui.core.css",
"~/Scripts/media/css/jquery.ui.resizable.css",
"~/Scripts/media/css/jquery.dataTables.css"));
}
}
}
This code is just copy / paste from generated file from Microsoft, only part:
bundles.Add(new ScriptBundle("~/bundles/table").Include(
"~/Scripts/media/js/jquery.dataTables.js"));
Is actually my.
In file Global.asax, add code:
BundleConfig.RegisterBundles(BundleTable.Bundles);
In your Web.config add connection string something like:
<connectionStrings>
<add name="MyTableDBContext" connectionString="Data Source=ServerName;
Initial Catalog=DataTablesWithJsonDB;
Integrated Security=False;
Persist Security Info=True;
User ID=userId;
Password=pass"
providerName="System.Data.SqlClient" />
</connectionStrings>
Create a model like:
using System.Data.Entity;
namespace DataTablesMvc4.Models
{
public class DataTablesWithJson
{
public int Id { get; set; }
public string Name { get; set; }
}
public class MyTableDbContext : DbContext
{
public DbSet<DataTablesWithJson> DataTablesWithJson { get; set; }
}
}
Build the solution, generate controllers, and create method like:
public ActionResult AjaxHand(
jQueryDataTableParamModel param
)
{
//var dataTablesWithJson = db.DataTablesWithJson.Include(p => p.Name);
return Json(new
{
sEcho = param.sEcho,
iTotalRecords = db.DataTablesWithJson.Count(),
iTotalDisplayRecords = db.DataTablesWithJson.Count(),
aaData = db.DataTablesWithJson.ToList()
},
JsonRequestBehavior.AllowGet
);
}
Where jQueryDataTableParamModel is:
public class jQueryDataTableParamModel
{
///
/// Request sequence number sent by DataTable,
/// same value must be returned in response
///
public string sEcho { get; set; }
///
/// Text used for filtering
///
public string sSearch { get; set; }
///
/// Number of records that should be shown in table
///
public int iDisplayLength { get; set; }
///
/// First record that should be shown(used for paging)
///
public int iDisplayStart { get; set; }
///
/// Number of columns in table
///
public int iColumns { get; set; }
///
/// Number of columns that are used in sorting
///
public int iSortingCols { get; set; }
///
/// Comma separated list of column names
///
public string sColumns { get; set; }
}
And finally your Index.cshtml should look like:
@using System.Web.Optimization
@model IEnumerable<DataTablesMvc4.Models.DataTablesWithJson>
@section scripts {
@Styles.Render("~/Content/themes/base/css")
@Scripts.Render("~/bundles/table")
<script type="text/javascript">
$(document).ready(function () {
var oTable = $('#myDataTable').dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "AjaxHand",
"fnServerData": function (sUrl, aoData, fnCallback, oSettings) {
oSettings.jqXHR = $.ajax({
"type": "POST",
"url": sUrl,
"data": aoData,
"success": fnCallback,
"dataType": "json",
"cache": false
});
},
"aoColumns": [
{ "sWidth": '200px', "mData": "Name" }
]
});
});
</script>
}
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<table id="myDataTable">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
</tr>
</tfoot>
</table>
I also added my controller to be as startup page, right click on project (not solution), and then click properties:
Click on WEB tab, and in specific page write address of your controller, in my case that is Datatableswithjson/Index
Example you can download from here.
Here you can see example of master - detail with datatables.