» » » » Retrieve Data from DB and Bind Gridview through DataTable Class

Retrieve Data from DB and Bind Gridview through DataTable Class

Posted By : Sri Kanth

Retrieve Data from DB and Bind Gridview through DataTable Class

Hi Friends,
In these tutorials you see the concept of creating a dynamic DataTable, retrieve Data from Database and store in DataTable, Binding DataTable Data to Gridview Controls.

Create Database 


 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 : Add Gridview to ASP.NET Page


 <%@ 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

C# Coding : Namespace

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;

C# Coding : Page Load

public partial class Add_ListofDealer : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FillGridview1();
        }
    }   
}

C# Coding : Bind Gridview

    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 :

 <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>
0 Komentar untuk "Retrieve Data from DB and Bind Gridview through DataTable Class"

Back To Top