In previous articles we learned how to enable tracing in asp.net. Now in this article let’s learn to bind gridview using sqldatasource in asp.net at design time with database.
What is C#.net Gridview?
GridView is most commonly used asp.net control that retrieves data from database and shows it on asp.net web page. The GridView shows the data in tabular format. This control is very versatile and so features rich for insertion, deletion and modifications of data from database that is showing in Gridview.
Asp.net Gridview prepares it’s columns and rows automatically with default options and then displays it immediately. The main advantage of this control is that it has a very rich set of automatic controls that let you perform basic CRUD operations i.e. “Create, Read, Update and Delete“, which don’t need much complex code and hence you don’t need to write much code to bind gridview at design time.
Gridview in asp.net must be bound to any DataSource in order to show the data on webpage. SQLDatasource is one of the DataSource that GridView supports. The task of the SQLDataSource is to retrieve the data from SQL server Database.
We can bind SQLDataSource with GridView in two ways i.e either at Design time or in code behind. This article covers step by step procedure to bind the GridView in asp.net using SQLDataSource at Design Time. In this Gridview example the final output will be like below screenshot –
|GridView: Bind with SQLDataSource : Showing Data|
In above image, asp.net GridView is showing 6 columns and 3 rows of data. In this example I am showing information about an employee of an organization. You may want to see other article that explains simple steps to insert, update and delete in asp.net Gridview.
Prerequisite of the article:
- This article is based on VS-2010 and SQL server 2008/2005.
- Before proceed to bind gridview using sqldatasource in asp.net at design time with database; your database should be ready. If you do not have any yet, then execute below sql queries on your SQL server 2008/2005 to create a Database “EmployeeDatabase” with table “Employee”.
/****** Object: Database [EmployeeDatabase] Script Date: 08/30/2013 10:24:17 ******/
IF EXISTS(SELECT name FROM sys.databases WHERE name = N’EmployeeDatabase’)
DROP DATABASE [EmployeeDatabase]
CREATE DATABASE [EmployeeDatabase]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/****** Object: Table [Employee] Description: Drop the Table EMPLOYEE if already exist ******/
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EmployeeDatabase].[dbo].[Employee]’) AND type in(N’U’))
DROP TABLE [EmployeeDatabase].[dbo].[Employee]
/****** Object: Table [Employee] Description: Create the Table EMPLOYEE ******/CREATE TABLE [EmployeeDatabase].[dbo].[Employee](
[EmployeeID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[First_Name] [nvarchar](50) NOT NULL,
[Last_Name] [nvarchar](50) NOT NULL,
[Gender] [nvarchar](1) NOT NULL,
[Department] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
/****** Object: Table [Employee] Description: Insert few rows in Table EMPLOYEE ******/
INSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]) VALUES (‘FIRSTNAME1’,‘LASTNAME1’,‘M’,‘19750320’,‘ENGG’)
INSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]) VALUES (‘FIRSTNAME2’,‘LASTNAME2’,‘F’,‘19901030’,‘ENGG’)I
NSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]) VALUES (‘FIRSTNAME3’,‘LASTNAME3’,‘M’,‘19880714’,‘QUALITY’)
SELECT * FROM [EmployeeDatabase].[dbo].[Employee]
Steps to Bind Gridview in Asp.net using SQLDataSource at Design time with database.
- Create a new Asp.net Web application.
- Add C#.Net GridView in your asp.net page. To add GridView in your page, drag & drop Asp.net gridview control from toolbox as shown below –
GridView control in toolbox
- As soon as you drag & drop GridView in Asp.net, it would look like below image –
GridView on web page after drag & drop
Observe that the C# Gridview is showing column name as “Column0”, “Column1” and “Column2”. The reason is DataSource not yet bind to GridView.
- To bind the SQLDataSource with GridView, click on down arrow of “Choose Data Source” and select “<New data source>”.
Create a new DataSource for Gridview
- It would open “Data Source Configuration Wizard”. Please select here “SQL Database”, then specify an ID of the DataSource and click on “Ok” button.
GridView Datasource Configuration Wizard
Now, specify an ID for the data source. By default “Data source configuration wizard” prompts with default data source name “SqlDataSource1”. I have kept it as it is, you can change it to anything. Click on Ok button.
- “Configure Data Source – SqlDataSource1” window will open. Click on “New Connection” button and it will open “Add connection” window.
- Enter here all the details for ex : server name, database name etc. All these details will create a ConnectionString for us.
GridView Add Connection Wizard
- Data source: It shows DataSource name. I have used “Microsoft SQL server (Sqlclient)”.
- Server Name: If you not know the server name/instance, you can click on down arrow on “Server name” combo box. It will show you all the available SQL server instances. My Server instance name is “localhost” hence I have entered “localhost” in this box.
- Log on to the server: Select the appropriate option. I am using windows Authentication.
- Connect to a database: Now select here database name to which you want to connect. I have already created a Database “EmployeeDatabase” before starting these steps. Hence I am using “EmployeeDatabase” here. Clicking on down arrow of this combo box will prompt you all the available Databases, choose whatever you want.
- Click on “Test Connection”. If everything is correct then it would prompt you a message “Test connection succeeded.” Verify all settings again if you do not see this message.
- Click on Ok button.
- It will now show you the complete connection string as shown below. Click on “Next” button.
GridView Choosing Data connection
- Now a window will open to save the connection string with a name. If you save it with a name then it will act as a shortcut for the connection and then you can use it anywhere also.
GridView Save Connection String
Click on next button.
- It will open a window where you can customize your “select” statement (query). I have kept it as it is. Click on “Next” button.
GridView Configure Select Statement
- A test query windows will open where you can test your select statement (query). Click on “Test Query” button and all data will appear on the same window. Click on “Finish” button to complete/close the SQL data source configuration wizard.
- Now, your gridview is configured with connection string to retrieve the data from database. Execute the application to see the data in GridView on web page. Press F5 button to see below output –
GridView Output: Showing data from database
I hope this gridview example in asp.net helps you to bind gridview using sqldatasource in asp.net at design time with database. If you find this article helpful, then could you please share the article on your social network?
Following articles might be of your interest