How to fetch data from database in asp.net

Introduction
In this article i will show you how to fetch data from sql server to GridView by coding. Example of binding gridview with SQL Server database. There are many ways to fetch data from database table. I have to mention list of table:

1. Get Data from database using SqlDataReader.
2. Get Data from database using DataSet with SqlDataAdaptor
3. Get Data from Database using SqlDataSource Control.
4. Get Data From Database using EDMX File (Entity Framework)

The C# GridView Control

The C# GridView control is a Data bound control that displays the values of a data source in the form of a table . In this table , each column represents a field and each row represents a record . The C# GridView control exists within the System.Web.UI.Controls namespace . When you drag and drop the GridView control on the designer page , the following syntax is added to the source view of the page.

<asp:gridview id="GridView1" runat="server"> </asp:gridview>

ASP.NET reduce lots of code in binding process, provide binding controls such as SqlDataSource for connecting database. The GridView data control has a built-in capability of sorting ,paging , updating and deleting data. You can also set the column fields through the AutoGenerate property to indicate whether bound fields are automatically created for each field in the data source.
In the below mentioned example, step 1 is define for add gridview in the webpage. Now, come to second step create columns in the gridview, which is depend on user choice. Under the <Columns> tag, create TemplateFields, which is used for design first column, according to step 3.


STEP-1 : Drag Gridview from Toolbox and Drop on design window.

    <form id="form1" runat="server">
    <div>  
        <asp:GridView ID="GridView1" runat="server">        
        </asp:GridView>  
    </div>
    </form>

STEP-2 : Create columns inside Gidview

<asp:GridView ID="GridView1" runat="server">
            <Columns >
            </Columns>        
        </asp:GridView>

STEP-3 : Create first column using TemplateField

  <Columns >
<asp:TemplateField>
</asp:TemplateField>
            </Columns>

STEP-4 : Create ItemTemplate inside <asp:TemplateField>
STEP-5 :  Also bind with table Attribute/Field

<asp:TemplateField>
   <ItemTemplate>
    <%# Eval("EmployeeId") %>
        </ItemTemplate>
</asp:TemplateField>

STEP-6:
Make StoredProcedure for retrieving data from database table.

CREATE PROCEDURE [dbo].[getdata]

AS
SELECT * from [Table]

RETURN 0

STEP-7 : Create code for binding data

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

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

        }

    }

    private void getdataload()
    {
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "getdata";
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                using (DataSet ds = new DataSet())
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        GridView1.DataSource = ds;
                        GridView1.DataBind();

                    }

                }  
         
            }
       
        }

    }
}

Output of the program

Note : Some Extra Column appear in gridview like EmployeeId, name

if you want to remove these column add attribute inside gridview

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns ="false">

Main Output of the program are:

Here we are using single Template field. If you want to make more column then you must take more than one template field. Each template specifies column of the GridView.

Top related post

• How to add control to the footer of Gridview in ASP.NET
• How to Bind Radio Button Control inside the Grid View control in ASP.NET
• How to bind Gridview using strong model binding in asp.net
• How to Bind Gridview using Entity Framework in ASP.NET
• How to get cell value in selected row of DataGridView in windows form c#
• Dynamically add rows and columns in data table, also bind Gridview with data table
• Nested Gridview Example in ASP.NET
• How to change Gridview header's style like border style and color
• Change header Row color of GridView in ASP.NET
• Bind GridView in ASP.NET
• GridView Header's Border style change in ASP.NET
• Example of Top Text alignment of Gridview in asp.net
• GridView Row background color in ASP.NET
• GridView Row border color in ASP.NET
• GridView Control and their properties, methods and events in ASP.NET
• Gridview sorting Example in ASP.NET