The GridView control provides the fastest, easiest and most feature-rich display of tabular data . In this example we will use a Microsoft Sql Server and store procedure to retrieve all the records .

Here i will explain how to display data from SQL database using asp.net GridView control.

The GridView control provides the fastest, easiest and most feature-rich display of tabular data . In this example we will use a Microsoft Sql Server and store procedure to retrieve all the records from emp table(from database) and fill the records in the gridview.

• To use the Gridview Control, drag it from the data section of the visual studio toolbox .

text

I'm using Microsoft Sql Server , I'm creating one database and inside the database i'm creating one table also,in this table you can feed your data .

1. I'm creating my own database.

How to create your own Database in SQL ?

>> create database sharma

• sharma is my database name , you can create your database in sql using this statement create database database_name.

How to use your database in sql , how to create your table inside your database ?

>> use sharma

• This Statement help you for using your database in SQL , use database_name.

2. Now, i'm creating one table.

>> create table emp(empID int primary key , empName varchar(20) , 
     position varchar(20) , salary int )

• emp is your table name and empID , empName , position , salary is your column name .
• i'm taking empID as a primary key , that means you cannot enter duplicate values .
• create table table_name ( column_name Data_type , column_name Data_type , column_name Data_type , column_name Data_type ), This statement helps you to create your table in SQL .

How to show your table ?

>> select * from emp 

• This statement to show your table , select * from table_name .

How to insert data in a table ?

insert into emp values(1 , 'Shekhar' , 'Web Developer' , 100000)
insert into emp values(2 , 'Rahul' , 'Software Developer' , 80000)
insert into emp values(3 , 'Mohit' , 'Web Developer' , 45000)
insert into emp values(4 , 'Rakesh' , 'Testing' , 50000)
insert into emp values(5 , 'Rohit' , 'Database' , 60000)

• This statement insert data in a table , insert into table_name values() .

SQL Coding :

create database sharma

use sharma

create table emp(empID int primary key , empName varchar(20) , 
position varchar(20) , salary int )

select * from emp

insert into emp values(1 , 'Shekhar' , 'Web Developer' , 100000)
insert into emp values(2 , 'Rahul' , 'Software Developer' , 80000)
insert into emp values(3 , 'Mohit' , 'Web Developer' , 45000)
insert into emp values(4 , 'Rakesh' , 'Testing' , 50000)
insert into emp values(5 , 'Rohit' , 'Database' , 60000)
insert into emp values(6 , 'Deepankr' , 'Software Developer' , 70000)
insert into emp values(7 , 'Pankaj' , 'web developer' , 90000)
insert into emp values(8 , 'Ratnesh' , 'Testing' , 11000)
insert into emp values(9 , 'Hardik' , 'Networking' , 55000)
insert into emp values(10 , 'Ramandeep' , 'Designer' , 70000)

Set up project


I'm using visual studio 2013 , Create a ASP.NET WEB APPLICATION .

Go to File > New > Project .
Click on web and Choose Asp.net Empty web application.














































3. Go to solution explorer and right click on your application .

4. Click on Add > New Item .
























5. Choose Web Forms .

6. Double click on Web Forms .
































7. Go to toolbox , click on Data and choose GridView .

8. Drag GridView on your Web Form Design Page .

9. Drag one Button also ( Button name is 'Show Data' ) .


























<form id="form1" runat="server">
    <p align="center" style="font-family: 'Comic Sans MS'; font-size:x-large; text-decoration: underline blink">Retreive Data Using GridView</p>
    <div align="center">
    <asp:gridview ID="gv1" runat="server" CellPadding="4"  ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True"  ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
        </asp:gridview>
        <br />

        <asp:Button ID="Button1" runat="server" Font-Bold="True" Font-Names="Comic Sans MS" Height="30px" onclick="Button1_Click" Text="Show Data" Width="85px" />
    </div>
    </form>


10. Double Click On Button .

11. Add 2 Namespace on the top-:

using System.Data;
using System.Data.SqlClient;


12. Write this code inside Button:

If your SQL start on windows authentication then type this code :
SqlConnection con = new SqlConnection("data source=KARTIKAY;Initial catalog=sharma; integrated security=true");
con.Open();
SqlCommand cmd = new SqlCommand("select * from emp",con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
cmd.ExecuteNonQuery();
gv1.DataSource = ds;
gv1.DataBind();

>> SqlConnection con = new SqlConnection("data source=KARTIKAY; Initial catalog=sharma; integrated security=true; userid:sa; password:password@123");

data source : Type your SQL Server name .
Initial catalog : Type your SQL Database name .
integrated security : If your SQL Server start with windows authentication, integrated security set as true .
userid: Type your SQL username .
password : Type your SQL password .




SqlConnection is used for creating a connection .
con.Open() is used for open a connection .
SqlCommand is used for retrieve data , delete data , update data , insert data .
SqlDataAdapter is a Command object that retrieves data from the data source .
DataSet provides a disconnected representation of result sets from the Data Source, and it is completely independent from the Data Source .



.aspx.cs Page Code :
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication1
{
 public partial class RetreiveData : System.Web.UI.Page
 {
 protected void Page_Load(object sender, EventArgs e)
 {

 }
 protected void Button1_Click(object sender, EventArgs e)
 {


 SqlConnection con = new SqlConnection("data source=KARTIKAY; 
       Initial catalog=sharma; integrated security=true");

 con.Open();
 SqlCommand cmd = new SqlCommand("select * from emp",con);
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataSet ds=new DataSet();
 da.Fill(ds);
 cmd.ExecuteNonQuery();
 gv1.DataSource = ds;
 gv1.DataBind();


 }
}
}
SummaryNow we have learned how to submit the form data into the database and display it in the gridview using a single Stored Procedure. I hope this article is useful for all students and beginners. If you have any suggestion related to this article please contact me.