Hi firasataries,
Please refer below sample code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
HTML
<html lang="en">
<head>
<title>Planning</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
<!-- Including the bootstrap CDN -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<%-- <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js"></script>--%>
<style>
.custom { width: 235px !important; }
.mycolor { background-color: #154290; }
.mycolorbg { background-color: #E1EBFB; }
.auto-style3 { color: #808000; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<nav class="navbar navbar-expand-sm bg-light col-md-12">
<img height="50" src="images\ngsa-logo-main.png" />
<span style="font-family: Times New Roman; text-align: center; font-size: xx-large; color: #F5791E"> <strong> System Planning Business Unit</strong>
</span>
<ul class="navbar-nav ml-auto">
<li class="nav-item">
<a class="nav-link" href="#">Contacts
</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Settings
</a>
</li>
</ul>
</nav>
</div>
<div class="container mt-3 col-md-12">
<div id="content">
<!-- Topbar -->
<div class="card">
<div class="card-header h4 auto-style3 text-center">Multipurpose Dashboard</div>
<div class="card-body justify-content-center mycolorbg">
<div class="col-md-12 text-center">
<button type="button" class="btn btn-primary btn-lg custom mycolor">Manpower</button>
<button type="button" class="btn btn-primary btn-lg custom mycolor">Budget</button>
<button type="button" class="btn btn-primary btn-lg custom mycolor">Training</button>
<button type="button" class="btn btn-primary btn-lg custom mycolor">Imporovement Studies</button>
</div>
</div>
<!-- grid view datatable to
<!--
<div style="width: 500px">
-->
</div>
<div class="container-fluid bg-white">
<div class="row">
<!-- Give this div your desired background color -->
<div class="container">
<div class="row">
<div class="col-md-12">
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer Id" />
<asp:BoundField DataField="Name" HeaderText="Contact Name" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
<asp:HyperLinkField DataTextField="CustomerID" HeaderText="" />
</Columns>
</asp:GridView>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</form>
<%--<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>--%>
<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://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">
$(function () {
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
});
function OnSuccess(response) {
$("[id*=gvCustomers]").DataTable({
bLengthChange: true,
lengthMenu: [[5, 10, -1], [5, 10, "All"]],
bFilter: true,
bSort: true,
bPaginate: true,
data: response.d,
dom: 'Blfrtip',
columns: [
{ 'data': 'CustomerID' },
{ 'data': 'Name' },
{ 'data': 'City' },
{ 'data': 'Country' },
{
'data': 'CustomerID',
"render": function (data, type, row, meta) {
var navigateUrl = "logout.aspx?CustomerId=" + data;
if (type === 'display') {
data = '<a href="' + navigateUrl + '" target="_blank">' + data + '</a>';
}
return data;
}
}
],
buttons: [
{
extend: 'excel', text: 'Excel', className: 'exportExcel',
filename: 'Customers', exportOptions: { modifier: { page: 'all' } }
},
{
extend: 'pdf', text: 'PDF', className: 'exportExcel', filename: 'Customers',
exportOptions: { modifier: { page: 'all' }, columns: ':visible' }, orientation: 'landscape', pageSize: 'LEGAL'
}
]
});
};
</script>
<div class="card-footer text-secondary text-center">Copyright © National Grid SA 2023</div>
</body>
</html>
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services
Class Property
C#
public class Customer
{
public string CustomerID { get; set; }
public string Name { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
VB.Net
Public Class Customer
Public Property CustomerID As String
Public Property Name As String
Public Property City As String
Public Property Country As String
End Class
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4] {
new DataColumn("CustomerID"),
new DataColumn("Name"),
new DataColumn("City"),
new DataColumn("Country")
});
dt.Rows.Add();
gvCustomers.DataSource = dt;
gvCustomers.DataBind();
//Required for jQuery DataTables to work.
gvCustomers.UseAccessibleHeader = true;
gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
[WebMethod]
public static List<Customer> GetCustomers()
{
List<Customer> customers = new List<Customer>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerID, ContactName, City, Country FROM Customers", con))
{
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(new Customer
{
CustomerID = sdr["CustomerID"].ToString(),
Name = sdr["ContactName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString()
});
}
}
con.Close();
}
}
return customers;
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(3) {
New DataColumn("CustomerID"),
New DataColumn("Name"),
New DataColumn("City"),
New DataColumn("Country")})
dt.Rows.Add()
gvCustomers.DataSource = dt
gvCustomers.DataBind()
'Required for jQuery DataTables to work.
gvCustomers.UseAccessibleHeader = True
gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader
End If
End Sub
<WebMethod>
Public Shared Function GetCustomers() As List(Of Customer)
Dim customers As List(Of Customer) = New List(Of Customer)()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerID, ContactName, City, Country FROM Customers", con)
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(New Customer With {
.CustomerID = sdr("CustomerID").ToString(),
.Name = sdr("ContactName").ToString(),
.City = sdr("City").ToString(),
.Country = sdr("Country").ToString()
})
End While
End Using
con.Close()
End Using
End Using
Return customers
End Function
Screenshot
