ObjectDataSource & GridView Control Explained

1. Introduction to ObjectDataSource Control

In this article, we will see the usage of the ObjectDataSource Control. We will create a web page with a GridView Control and link that control to the ObjectDataSource control. The user will perform data handling through the GridView control. The GridView intern uses the ObjectDataSource to delete or change a record.

First, we will see the merit of the ObjectDataSource control over the SqlDataSource control. Let us say an organisation has the employee payroll processing as an Internet application. For example, look at like this, there are three pages that access the employee data from the database and displays it. And each page uses the SqlDataSource control to fill UI controls in it. We know that the SqlDataSource control talks to the database directly. Now have a look at the picture below:

Asp.Net and SqlDataSource Control

Asp.Net and SqlDataSource Control

Looking at the picture we can understand that the SqlDataSource Control sticks on each page & directly contacts the database. This leads to a maintenance pain of changing all three pages when the employee table schema in the database is changed. But when we use the ObjectDataSource Control, the Control talks to a class object and that class object talks to the database. Now, the schema change for the employee table affects only the class that acts as a bridge and we call this class a business layer class.  This is shown in the below picture:

Asp.Net and ObjectDataSource via Business Layer

Asp.Net and ObjectDataSource via Business Layer

OK. Now, we will start the example of asp.net page accessing the authors table of the pubs database. The sample attached with this article has the script file that you can run on your SQL server database to create the Pubs database.

2. About The Example

As the page will have only one GridView, this example is easy to understand. Below is the screenshot of our example web page:

Data Retrieved through ObjectDataSource And Shown in GridView

Data Retrieved through ObjectDataSource And Shown in GridView

This web page pulls the data from the Authors table of the Pubs database.  The user can navigate through the pages of the GridView or they can delete or update a single record. We can easily do this using the SqlDataSource control. But we will use the ObjectDataSource Source in this example. From the previous section you are aware of the advantage of using the ObjectDataSource control.

3. Create Data Class for Authors Table

As you see from the sample application screenshot, we are taking only three columns from the Authors table. The picture below shows the picked columns from the Authors table:

Pubs.Authors table used in this example

Pubs.Authors table used in this example

The Data Class represents the table columns. From the above picture, we know that we will take only three columns, namely Au_id, au_fname and city from the Authors table. So, our Data Class will have three data members with the matching data type and variable name. We will add a class called Author to our project under the App_Code folder and have three data members to represent each column. We also provide the properties for all three picked columns. If App_code folder is not available by default, we can add it to the project using the helper screen shot below:

Adding app_code folder to ASP.Net Project

Adding app_code folder to ASP.Net Project

The Data Class for the Authors table is shown below:

Note, when we create the property, it should match the database column name. In the above class, we have three properties and each one is for one column that we are planning to display in the GridView.

4. Business Layer Class Of ObjectDataSource

Well! We are done with the data class. Let us define the Business Layer Class that operates on the data which is fed by the data class.

Now we add a class called AuthorDataSet  to the project, and this class tells the ObjectDataSource Control how the Select, Insert, Update and Delete operation will be performed on the database. Whereas the data class will supply the data required for the operation. We will see about that when we develop this sample on the Go.

4.1 Namespace

In the AuthorDataSet class first we get the required namespace for this example.

4.2 Connection String Setting

Next, we add the ‘web.config’ file if it is not already available as part of the project. To add, we select the project, right-click and choose the context menu item ‘Add new item…’ and then from the displayed dialog we select the Web Configuration file. After this we define the ‘Connection String’ in the web config file. This is shown in the below screenshot:

The Connection String in Config File

The Connection String in Config File

Here,

  1. Our Connection String name. This name will be used later, to get the connection string.
  2. Specifies the driver provider name. In our case, it is SqlClient.
  3. Here, we can specify the network machine name or IP address in which the database is running. We specified localhost as our database will run in the same machine in which we are developing this Example.
  4. Specifies which database we want to point on the database server.
  5. This is the Username
  6. This setting is for password.

4.3 Retrieve Connection String Setting

In the class file of AutherDataSet.cs, we retrieve the Connection string settings by the name ‘Pubs’ (Tag 1 in the previous picture). Once we have the ConnectionStringSettings objects, we can retrieve the connection string wherever we want.

4.4 Default Constructor For Business Layer Class

A default constructor must present in the Business Layer class as the DataObjectSource invokes this class. The default constructor will provide the ability to create the object on the fly.

4.5 Business Layer Returning DataTable

In the past step, we pointed out that the Business Layer class must keep a default constructor. It should also should have at least one method that returns an Array, List or DataTable etc., or simply the collection of data. The code here fetches the data from the database and returns it as a DataTable.

Code Explanation

First, we create the SqlConnection using the connection string brought back from the config file. Then, we create the Select statement in the SqlDataAdapter to fetch the data from the database. And, finally, we asked the adapter to fill the DataTable . The SqlDataAdapter does that by executing the select statement. The below picture shows the data movement:

Data Read by SQlDataAdapter and packed in DataTable Before giving it to ObjectDataSource

Data Read by SqlDataAdapter and packed in DataTable Before giving it to ObjectDataSource

OK. The Business Layer Class implemented a method which returns the collection of rows and columns fetched from the database as DataTable. Now, we need to design a web page to use this method and display the data gained in the DataTable. The video here shows how to design the form and display the data in the DataGrid by making use of ObjectDataSource Control.


Video 1: Configure ObjectDataSource Control & Bind with DataGrid


5. Update and Delete Via ObjectDataSource Control

In the past section, we saw fetching the data from the database and displaying it in the DataGrid through the ObjectDataSource Control. In this part, we will do the update and delete action on the DataGrid view. When a user makes an update or delete in the DataGrid, the ObjectDataSource control does the same action in the database.

5.1 Update Method

In the AuthorDataSet  class we define two methods, One for the Update and other One for the Delete operation. The code for the update method is below:

Code Explanation

The first thing we note here is that the Author  data class instance which is passed as an argument to this function. Who will supply this param? When the user clicks the Update link in the GridView control (After Adjusting the Data), the GridView control signals the ObjectDataSource control to state that the user changed a row. The ObjectDataSource is aware of the Business Layer method responsible for the update method. The ObjectDataSource fills the changed row in the Author data class object and after that; it passes that as a parameter to our UpdateAuthor function.

The function pulls a single row by forming the Select Query based on the primary key au_id. Note, we parameterized the where clause of the select query. The Author Data Class supplies a value for this param. Remember, this data class is holding the updated row from the DataGrid. Once Select Query is ready, we fill the DataTable author_updt by calling the Fill method of the DataAdapter.

Once we have the DataTable ready for the update action, we educate the DataAdapter with the update command. The SqlCommandBuilder instance builds the UpdateCommand and assign it back to the DataAdapter.

Finally, Once Adapter is aware of the UpdateCommand; we take the single row from the DataTable and update the columns with the changed data. This is coming from the DataGrid View as a Data Class instance argument to this function. Once the DataRow taken from the DataTable is revised with the user data, we call the Update method on the DataAdapter. This method applies the changes in the DataTable to the SQL Server database. The below picture shows the full sequence of action:

Data Update Through Asp.Net ObjectDataSource Control and Initiated by GridView

Data Update Through Asp.Net ObjectDataSource Control and Initiated by GridView

5.2 Delete Method

Now, we no need to explore much about the function used for deleting a record from the database. The function for the delete operation is below:

Code Explanation

The function is almost like what we wrote for the Update Operation. The only change is, we take a DataRow from the DataTable, and then we delete that before calling the update method on the DataAdapter.

Our ObjectDataSource Control only deals with the Select Operation. We can recall that from the previous video. Now we should alter the ObjectDataSource Control with the Update and Delete Operations. After doing so, we should also change the GridView control to aid these actions. The below video explains these steps.


Video 2: Providing the Support for Update and Delete Operation


6. Summary

Hope this article helped us in finding out how to use the ObjectDataSource control with the GridView control. When you extract ASP.net.zip, in the root folder you will get an SQL script file that you can run in your SQL Server to create the Microsoft’s Pubs sample database. You can open the project using the ‘Open Website’ Option in VS2005 IDE (Or Later with the conversion wizard).

Source Code: Download ObjectDataSource Example from Google Drive



Categories: Asp

Tags: , , , , ,

Do you like this Example? Please comment about it for others!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: