How to display data on a page using the GridView method of ASP.NET

Introduction

This blog post will explain how to display data from a database using GridView. I used the Northwind database, which you can download from this link.

https://github.com/microsoft/sql-server-samples/tree/master/samples/databases

Before you begin

To run this sample, you need a tool that can run Transact-SQL scripts. You can run the scripts in the following tools:

• SQL Server Management Studio (SSMS). To download SSMS, go to Download SQL Server Management Studio (SSMS).
• QL Server Data Tools (SSDT) or Visual Studio. To download SSDT, or to enable its features in Visual Studio, go to Download and install SQL Server Data Tools (SSDT) for Visual Studio.

Run the scripts in SSMS

• Open SSMS.
• Connect to the target SQL Server.
• Open the script in a new query window.
• Run the script.

Run the scripts in SSDT or Visual Studio

• Open SSDT or Visual Studio.
• Open the SQL Server Object Explorer.
• Connect to the target SQL Server.
• Open the script in a new query window.
• Run the script.

OR

You can create a database by running the below query if you have trouble with the above methods.

Create a database in SQL SERVER

The following SQL statement creates a database called “Northwind":

Example

1. CREATE DATABASE Northwind;   

SQL CREATE TABLE Example

The following example creates a table called "Customers" that contains eleven columns:

1. USE [Northwind]  
2. GO  
3. SET ANSI_NULLS ON  
4. GO  
5. SET QUOTED_IDENTIFIER ON  
6. GO  
7. CREATE TABLE [dbo].[Customers](  
8. [CustomerID] [nchar](5) NOT NULL,  
9. [CompanyName] [nvarchar](40) NOT NULL,  
10. [ContactName] [nvarchar](30) NULL,  
11. [ContactTitle] [nvarchar](30) NULL,  
12. [Address] [nvarchar](60) NULL,  
13. [City] [nvarchar](15) NULL,  
14. [Region] [nvarchar](15) NULL,  
15. [PostalCode] [nvarchar](10) NULL,  
16. [Country] [nvarchar](15) NULL,  
17. [Phone] [nvarchar](24) NULL,  
18. [Fax] [nvarchar](24) NULL,  
19. CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED  
20. (  
21. [CustomerID] ASC  
22. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
23. ) ON [PRIMARY]  
24. GO 

INSERT INTO Example

The following SQL statement inserts a new record in the "Customers" table:

1. INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders', N'Sales Representative', N'Obere Str. 57', N'Berlin', NULL, N'12209', N'Germany', N'030-0074321', N'030-0076545')  
2. INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo', N'Owner', N'Avda. de la Constitución 2222', N'México D.F.', NULL, N'05021', N'Mexico', N'(5) 555-4729', N'(5) 555-3745')  
3. INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno', N'Owner', N'Mataderos 2312', N'México D.F.', NULL, N'05023', N'Mexico', N'(5) 555-3932', NULL)  
4. INSERT [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (N'AROUT', N'Around the Horn', N'Thomas Hardy', N'Sales Representative', N'120 Hanover Sq.', N'London', NULL, N'WA1 1DP', N'UK', N'(171) 555-7788', N'(171) 555-6750') 

Create a stored Procedure

1. USE [Northwind]  
2. GO  
3. SET ANSI_NULLS ON  
4. GO  
5. SET QUOTED_IDENTIFIER ON  
6. GO  
7. ALTER procedure [dbo].[sp_select]  
8. as  
9. begin  
10. select top 10 * from Customers  
11. end 

Procedures

To create a new project using a standard template

• In Microsoft Visual Studio, Click File menu -> New -> Project, (or click File menu -> New Project).

• 

To add a page to the Web application

• In Solution Explorer, right-click the Web application name and then click Add -> New Item. The Add New Item dialog box is displayed.
• Select the Visual C# -> Web templates group on the left. Then, select Web Form from the middle list and name it Gridview_blog.aspx.

Switch to Source view

You can see the HTML in Source view that you created when you typed in Design view.

1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Gridview_blog.aspx.cs" Inherits="GRIDOPERATION.GRIDVIEW_PROJECT.Gridview_blog" %>  
2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
3. <html xmlns="http://www.w3.org/1999/xhtml">  
4. <head runat="server">  
5. <title></title>  
6. </head>  
7. <body>  
8. <form id="form1" runat="server">  
9. <div>  
10. <asp:GridView ID="GridView1" runat="server">  
11. </asp:GridView>  
12. </div>  
13. </form>  
14. </body>  
15. </html>  

Gridview_blog.aspx.cs

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Web;  
5. using System.Web.UI;  
6. using System.Web.UI.WebControls;  
7. using System.Data;  
8. using System.Data.SqlClient;  
9. using System. Configuration;  
10. using System.Web.Configuration;  
11. namespace GRIDOPERATION.GRIDVIEW_PROJECT  
12. {  
13. public partial class Gridview_blog : System.Web.UI.Page  
14. {  
15. string strConnString = WebConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString;  
16. protected void Page_Load(object sender, EventArgs e)  
17. {  
18. if (!this.IsPostBack)  
19. {  
20. getdata();  
21. }  
22. }  
23. public void getdata()  
24. {  
25. SqlConnection con = new SqlConnection(strConnString);  
26. SqlCommand cmd = new SqlCommand(“[sp_select]”, con);  
27. cmd.CommandType = CommandType.StoredProcedure;  
28. con.Open();  
29. SqlDataAdapter sda = new SqlDataAdapter(cmd);  
30. DataSet ds = new DataSet();  
31. sda.Fill(ds);  
32. GridView1.DataSource = ds;  
33. GridView1.DataBind();  
34. con.Close();  
35. }  
36. }  
37. }  

You can see the output on the page.