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:

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 = ONON [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.