Hi sani.ss501,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespaces
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
Controller
public class HomeController : Controller
{
public ActionResult Index()
{
DataSet ds = new DataSet();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = @"SELECT TOP 25 CustomerID Id,ContactName Name,Country FROM Customers";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(ds);
}
}
}
return View(ds);
}
}
View
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<DataSet>" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Index</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css" />
<script type="text/javascript" src="https://code.jquery.com/jquery-1.12.3.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#example').DataTable({
dom: 'Brtip',
'iDisplayLength': 10,
buttons: [{
extend: 'excel',
text: 'Export to Excel',
className: 'exportExcel',
filename: 'Customers',
exportOptions: { modifier: { page: 'all' /*'current'*/} }
// Set 'current' to export only current page record.
}]
});
});
</script>
</head>
<body>
<table id="example" class="table table-striped table-bordered table-hover display">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Country</th>
</tr>
</thead>
<tbody>
<%foreach (DataRow row in Model.Tables[0].Rows)
{ %>
<tr>
<td><%=row["Id"] %></td>
<td><%=row["Name"] %></td>
<td><%=row["Country"] %></td>
</tr>
<%} %>
</tbody>
</table>
</body>
</html>
Screenshot
