The data in a tabular format provides the ease to sort and manipulate it. The tables provide rows and columns. So we store the data in a table. In ASP.NET, we can retrieve the data from the database in a GridView. The GridView is a special type of control that is used to display the table data into the client’s section. In this post, we are going to know about the GridView and its uses. In our last tutorial, we already saw how to connect the ASP.NET web application to the SQL Server Database. Also, we had created the SQL Server database and a table. In that table, we had inserted some records from the user interface (Registration form). Here, I’m going to use the same project and the database. So that we can understand how to open the existing project/website with the database.
If you didn’t go with the first post of database connectivity in ASP.NET, then it is highly recommended to follow that tutorial from the below link.
- 1 GridView Control in ASP.NET
- 2 Open Existing Project/Website
- 3 Adding GridView in the Web Form
- 4 Retrieving Records by SQL Query
- 5 Retrieve Data through Assigning the Data Source
GridView Control in ASP.NET
The GridView is an ASP.NET control that is used to bind the data source in a table. It contains the rows and columns. The rows refer to the table records (data) and the columns refer to the table field. It has the following features that we can use as per our need.
- It binds the data source controls like SqlDataSource.
- Built-in-sort capability – It can sort the table records in Ascending or Descending order.
- Comes with the update and delete features – We can manipulate the data by updating and deleting.
- Paging – It provides the pagination that helps when the number of records is very large.
- Select – We can select the particular row by enabling the Select option.
- Mulitple key fields – The key field makes a unique identification of the table columns. We can create multiple key fields.
- Customizable appearance – We can customize the appearance by changing its themes, colors and the fonts.
Here, I’m going to open the previously created project so it will easy to bind the data source. You can create a new project and the database table. But it will require having some records in the database table.
Open Existing Project/Website
- To open the existing project/website navigate the option.
File -> Open ->Project/Web Site
- Here, I’m opening the Web Site because, in the previous post, I have created a website.
- Select the project file that you want to open. I have selected my project file which is DatabaseConnection.
- The project/website will be saved default inside the Visual Studio folder.
- Once the project/website has loaded in the Visual Studio, you can see it by navigating to the Solution Explorer window.
- The interesting thing is that the database will also load along with the project. This is because we have included the database in the same project folder.
Add New Web Form in the Project/Web Site
- Here, we have to add a new web form in the same project.
- For adding new item go the Solution Explorer window and right-click on your project/website name.
Then Add->Add New Item
- Shown in the below image.
- You will see a new window having the various components.
- Select the Web Form under the Visual C# template.
- Change the Web Form name by renaming it. The default name will be default.aspx of the web form.
- Now, click on Add button to add it to the project.
- Here is the loaded form in the project.
Adding GridView in the Web Form
- Go to the ToolBox and search for GridView.
- Select and drag it to the web form.
Change the Appearance of the GridView
- Now, go the Design view of the Web Form.
- Select the GridView and click on the arrow option on the top right corner.
- Click on the Auto Format.
4. Select any theme that you want to use with the GridView. I have selected Snowy Pine.
5. Click on Apply then OK.
Retrieving Records by SQL Query
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RetrieveStudent.aspx.cs" Inherits="RetrieveStudent" %>
<title>GridView Example in ASP.NET</title>
margin: 0 auto;
<form id="form1" runat="server">
<h2 class="headigText">Student Details</h2>
<asp:GridView ID="myGrid" CssClass="gridStyle" runat="server" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" Height="34px" HorizontalAlign="Center" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" Height="30px" HorizontalAlign="Center"/>
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
public partial class RetrieveStudent : System.Web.UI.Page
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\CHIKOO\Documents\Visual Studio 2013\WebSites\DatabaseConnection\App_Data\StudentDatabase.mdf;Integrated Security=True");
protected void Page_Load(object sender, EventArgs e)
// Methods for retrieving student record
public void showStudent()
string cmd = "SELECT student_id as [Student ID],student_name as [Student Name],course as [Course], address as [Address], email as [Email], phone as [Phone] FROM student";
SqlDataAdapter da = new SqlDataAdapter(cmd,con);
DataTable dt = new DataTable();
myGrid.DataSource = dt;
- In the above cs code, I have created a method for retrieving the table records
- The method contains the SQL statement for retrieving the records from the student table.
- I have assigned the data source to the GridView.
- The GridView will bind the data as a table. So that it will display as a form of rows and columns.
Save and run the above code to see the result.
Retrieve Data through Assigning the Data Source
- We can retrieve the data from the database table by assigning the data source directly to the GridView.
- In this process, we would not require writing any single line of code for retrieving the data.
- This will generate the Stored Procedure for accessing the database table.
Steps are the following-
- Go to the Design view of the web form. Select the GridView and click on the option available in the top right corner (a tiny arrow).
- Then you will see there is no any Data Source for this GridView.
3. Now, click on the New data source.
4. It will open the Data Source Configuration Wizard window.
5. Click on the Database and then OK. It will provide the SqlDataSource name.
6. Choose the Database Name in the given list. Then click Next.
7. In the next window leave it as default with the ConnectionString and click Next.
8. The next window came with the fields name (columns) that you want to display in the GridView. So in very first step, select the table from the dropdown list. In my case student table is selected. Then check on * for all columns of the table. Since I want to display all columns of the table.
9. If you want to display specific columns then select only that columns.
10. Now you can check whether the process is going on correct or not. So for this, click on the Test Query.
11. If your table has the records, it will return there. Otherwise, only columns name will appear.
12. At last, click on Finish.
Now, you can see here, I have a SqlDataSource in the form.
Save and Run the project again.
Finally, we got the result in both steps – By SQL Query and by assigning the Data Source directly. We have seen how to create the GridView in the ASP.NET. The GridView provides the table approach for display the records with various functionality. We can perform the various task in GridView such as – Edit, Delete, Insert and the Arithmetic operations like Addition, Subtraction, Multiplication and the Division of the columns. Further, I will present with these tutorials. So till stay connect with us and keep coding. 🙂