Retrieve Data from DB and Bind Gridview through DataTable Class


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