Displaying Data in Gridview Using Three-Layer Architecture

Last week I displayed table information from an Oracle database to a Grid View since that was the requirement from the client, to display reports in that format and export them to the Excel format. So today I will talk about Grid View. While working in projects there is a need to display the information to the user in a Tabular view form. Then a Grid View is useful. A Data Grid View displays data from a database. If required, the data can be exported to an Excel file for the user.

So friends, we will now work on displaying the information to the user using a Grid View and exporting them to the Excel file.

Step 1

Create a Website.



Step 2

Select ASP.NET Web Forms.



Step 3

Delete the default templates since they are not required in the application.



Step 4

Add a new form.





Click on Add.

Step 5

Add a Grid View to the form.



Step 6

Add a Grid View and Button.



Let's get started with the database requirements to display to the user depending on the requirements.

I am making a table with customer information, like ID, FirstName, LastName, Age and Address that will be displayed to the user in the GridView.

So let's make a table in SQL Server.

Step 7

Connect to your SQL Server.



Step 8

Create a table and insert records into it.



Name it Information (or whatever depending on your naming conventions).



Now insert records into the table. Right-click on the table name and click on edit top 200 rows and now you can enter your records into the table.



Here you go, I have inserted the records into the table, now I will create a simple procedure that will fetch the records from the table.

Inserted the records.



Step 9

Create a Stored Procedure.

For understanding Stored Procedures, kindly refer to my article Execution Process of Stored Procedure so that you are able to understand how Stored Procedures work and why we use Stored Procedures in our applications. So to create a Stored Procedure use the following procedure.

Click on Programmability and you can see the Stored Procedure option.



Right-click on the Stored Procedure option and click on New Stored Procedure.



A default Template for Stored Procedure will appear and I will edit them and create new Stored Procedure depending on my needs.





I have created the simple Stored Procedure with the name TC_Fetch_Customer_Records to fetch the details from the Information Table. Now since we are done with our DB designing and inserting data into it, now we will focus on our web application.

Step 10

Dividing your project into a Three-Layer Architecture.

To learn about Three-Layer Architecture read my article 3 Layer Architecture in ASP.NET for your reference, it will be helpful for you to read and grasp in depth the information regarding 3-layer architecture.

I am making the diagram so that we can have an abstract view of how the application will interact in a 3-layer architecture.



As we can see, there will be 3 layers, the UI Layer for the User Interface Layer, the Business Logic containing the class information and the Data Access Layer (DAL) code related to the database will be in the DAL Layer.

Now add a class called BAL.





Class Named as BAL.CS.

Add your class properties.

Properties

Properties are named members of classes, structures and interfaces. Member variables or methods in a class or structures are called fields. Properties are an extension of fields and are accessed using the same syntax. The important use of a property is so that you can inject logic or validation into it. They use accessors by which the values of the private fields can be read, written or manipulated.

If I want to insert the values into the database then I need to create the properties since they will be allocated the values received from the UI Form. I have just given the example of an _ID, _FirstName, _LastName, _Age and _Address as properties.



To define a property you can use a keyword prop and press the tab button automatically. A property will be created and you can name it depending on your requirements or else create a private variable then right-click on the variable name then go to Refractor and press the Encapsulate field, your get and set method properties will be created.



We can inject our logic into the properties and throw the Exception. Right now if we don't create any properties then it will be no problem, if you want to insert the information then you need to create properties. In this we will use the BAL Class as the mediator and will create a function that will be called by the UI and then this BAL function will call the DAL function. Now add a function BAL Class that will call the DAL function.

We create a DAL object and call the FetchView function in the DAL layer that will be assigned to a dataset.



Add the database namespaces. I will now write small logic and functions in the button event and we will call the BAL class and then the BAL class will call the DAL class to do the Three-Layer Architecture.

In the UI button click event we need to create an object of the BAL class that will act as a mediator between the UI and the DAL. I have created a dataset object that will receive the dataset value from the BAL function and then the BAL function will call the DAL function and it will be returned to the UI Layer. So now I will create the function View() in the BAL.

Since I will return a dataset to the UI I have created a function with a return type of Dataset. Now I have created an object DAL and will call the FetchView Function in the DAL. So now we need to create the FetchView Function in the DAL layer.

Step 11

Now add our DAL class.



Step 12

Create a function of the DAL Layer.

Before creating a function in the DAL we need to create a connection string for the application that will connect to our database.

Use the following procedure.

1. Go to View then click on the Server Explorer.
   
   The following Data Connections option will be there:
   
   
   
   
   
   
2. Right-click on Data Connections.
   
   
   
   
3. Click on Add connection.
   
   Select your connection for your database.
   
   
   
   Test the connection and click OK. One data connection will be connected to the database as shown below:
   
   
   
   Right-click on the newly added connection and go to properties as in the following:
   
   
   
   Go to Connection String.
   
   
   
   Copy them and insert them into the WebConFigure file in the web application as shown below.
   
   
   
   Since we have added the connection string to the WebConFigure file we need to call the connection string in our DAL layer.

Now I will create a class and name it conString that will contain the connection string provider name so that we don't need to create a connection string every time we want to do some db related actions. The constructor will be automatically called when we click on the submit button that will initialize the object of the Sqlconnection.





Now whenever we want our SQLconnection, I will just use its class and SqlConnection object Con.

Now let's get started with our DAL layer and do all the calling of the procedures and returning of a value to the BAL and then the BAL to the UI.



As you can see, I am returning the dataset values once it is filled by the data adapter. Now in the button click event I will code a small amount of logic as shown below and bind the dataset to the Grid View.

Add the following code to the button's Click event for Grid View binding.



So finally we are ready to run the application, let's run the program and create the breakpoint to see how the flow is happening.

Flow of Web Application

1. User Interface.
   
   
   
   
2. User clicks on Generate Reports.
   
   
   
   So the compiler comes and creates an object of the BAL Class. We created a dataset dstObj and we have called Cust.View that has been assigned to the dataset object. Now this will call the BAL function View().
   
   
3. The compiler will come to the BAL class function View and Create an object of the DAL Class and again we have assigned a value of the DAL class object calling the DAL layer function FetchView to the dataset object. Now the control will move to the DAL Layer finally who is responsible for doing all the database related functions (insert, update, delete and so on) as shown in the following figures.
   
   
   
   
   
   Above we can clearly see the DAL class FetchView Being executed and the DataAdapter fills the dataset and that is returned to the BAL class as shown below.
   
   
   
   The dataset is empty until this line of execution. After the da.fill(ds) is done the data is filled in the dataset object, as shown below and the same dataset object is returned back to the BAL class function from where it was called.
   
   
   Figure: Demonstrating when Data Adaptor fills the DataSet object
   
   
   Figure: Demonstrating when the Dataset object is returned from the DAL layer and is assigned to a dataset object of the BAL Class, we can see in the Dataset visualizer all the values received from the DAL dataset object.
   
   Now it is returned to the UI Layer where it will ne bound to the Grid View as shown below.
   
   
   
   

So here, we learned how to fetch information from the table and display it in a Grid View. I know this article has been quite lengthy but that's the beauty of learning, you need to remain focused on the target. I will write Part 2 of this article for exporting the Grid View to an Excel File and the major issues I encountered while doing that. I hope this article is helpful for all my beginner friends. Kindly do reply with all the necessary corrections and feedbacks.