Introduction:
In this tutorial, we will delve into the dynamic creation of DataTable, retrieving data from a database, and binding it to a GridView in ASP.NET. This comprehensive guide will walk you through the process, helping you enhance your web development skills.
Creating the Database:
Begin by setting up a sample database with a 'Book' table. Use the SQL script provided to create and populate the table with dummy data. This dataset will serve as the basis for our tutorial.
USE [master]
GO
/****** Object: Table [dbo].[Book] Script Date: 05/11/2015 15:04:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book](
[Book_Id] [nvarchar](5) NULL,
[Book_name] [nvarchar](50) NULL,
[Book_author] [nvarchar](30) NULL,
[Book_Publisher_name] [nvarchar](50) NULL,
[Book_Published_date] [nvarchar](30) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'bk', N'sfjskj', N'jfdskfj', N'fjdksjfk', N'4/4/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'bk8', N'kk', N'kk', N'kk', N'4/8/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk1', N'ASP.NET', N'Krishna', N'MyHome Publisher', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk2', N'C#.NET', N'Arvind', N'James Street Publishers', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk3', N'SQL SERVER', N'James', N'Hi-Tech Publishers', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk4', N'JAVA', N'JAVA RAM', N'MyHome Publisher', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk5', N'HTML', N'HTML Raghu', N'James Street Publishers', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk6', N'CSS', N'CSS Chandra', N'Hi-Tech Publishers', N'3/30/2015 12:00:00 AM')
INSERT [dbo].[Book] ([Book_Id], [Book_name], [Book_author], [Book_Publisher_name], [Book_Published_date]) VALUES (N'Bk7', N'JAVASCRIPT', N'JAVA RAM', N'MyHome Publishers', N'3/30/2015 12:00:00 AM')
HTML Markup: Adding GridView to ASP.NET Page:
Next, integrate a GridView into your ASP.NET page using the provided HTML markup. This GridView will later be populated with data retrieved from the database.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.Gridviews.WebForm1" %>
<!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">
<!--- ADD CSS Code Here ---->
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" HeaderStyle-CssClass="HeaderStyle" </asp:GridView>
</div>
</form>
</body>
</html>
C# Coding: Retrieving and Binding Data:
Now, let's delve into the C# code. In the 'Page_Load' event, we call the 'FillGridView' method to retrieve data from the database and bind it to the GridView.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class Add_ListofDealer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillGridview1();
}
}
public void FillGridview()
{
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);
string sqlquery = "Select * from Book";
SqlDataAdapter da = new SqlDataAdapter(sqlquery, con);
DataSet ds_grd = new DataSet();
da.Fill(ds_grd);
DataTable dt = new DataTable();
DataColumn dc;
DataRow dw;
DataView dv;
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Book ID";
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Book Name";
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Book Author";
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Book Publisher Name";
dt.Columns.Add(dc);
dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
dc.ColumnName = "Book Published Date";
dt.Columns.Add(dc);
if (ds_grd.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds_grd.Tables[0].Rows.Count; i++)
{
dw = dt.NewRow();
dw["Book ID"] = ds_grd.Tables[0].Rows[i]["Book_Id"].ToString();
dw["Book Name"] = ds_grd.Tables[0].Rows[i]["Book_name"].ToString();
dw["Book Author"] = ds_grd.Tables[0].Rows[i]["Book_author"].ToString();
dw["Book Publisher Name"] = ds_grd.Tables[0].Rows[i]["Book_Publisher_name"].ToString();
dw["Book Published Date"] = ds_grd.Tables[0].Rows[i]["Book_Published_date"].ToString();
dt.Rows.Add(dw);
}
dv = new DataView(dt);
GridView1.DataSource = dv;
GridView1.DataBind();
}
}
catch
{
throw;
}
}
}
CSS Code: Styling the GridView:
Finally, apply CSS styling to enhance the appearance of the GridView. This CSS code defines the background, header styles, and cell alignment.
<style type="text/css">
#GridView1
{
background:#fafafa;
}
#GridView1 .HeaderStyle
{
background:#0066cc;
color:#ffffff;
padding:7px 10px 7px 10px;
}
#GridView1 th
{
padding:10px 15px 10px 15px;
}
#GridView1 td
{
padding:5px 10px 5px 10px;
text-align:left;
}
</style>
Conclusion:
Congratulations! You've successfully learned how to retrieve data from a database, store it in a DataTable, and bind it to a GridView in ASP.NET. This knowledge is crucial for creating dynamic and data-driven web applications. Experiment with different datasets and styling to further refine your skills. Happy coding!
0 Comments