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
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
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
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
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
The Data Class for the Authors table is shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
/// <summary> /// Summary description for Author /// </summary> public class Author { //Sample 01: Defaut Ctor public Author() { } //Sample 02: Private Members private string m_Au_id; private string m_fname; private string m_city; //Sample 03: Properties public string Au_id { get { return m_Au_id; } set { m_Au_id = value; } } public string au_Fname { get { return m_fname; } set { m_fname = value; } } public string City { get { return m_city; } set { m_city = value; } } } |
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.
|
1 2 |
//Sample 04: Name Space using System.Data.SqlClient; |
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
Here,
- Our Connection String name. This name will be used later, to get the connection string.
- Specifies the driver provider name. In our case, it is SqlClient.
- 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.
- Specifies which database we want to point on the database server.
- This is the Username
- 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.
|
1 2 3 |
//Sample 05b: Connection string from web.config private ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Pubs"]; |
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.
|
1 2 3 4 |
//Sample 06: Default Ctor public AuthorDataSet() { } |
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
.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
//Sample 07: For Select Command public DataTable ListOfAuthors() { //Sample 7.1: Create connection and Data Table DataTable authors = new DataTable(); authors.TableName = "Authors"; SqlConnection Con = new SqlConnection(settings.ConnectionString); //Sample 7.2: Create a bridge between DB and Table of Data SqlDataAdapter adapter = new SqlDataAdapter( "Select Au_id, Au_fname, city from Authors", Con); //Sample 7.3: Fill the Data in DataTable adapter.Fill(authors); return authors; } |
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
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//Sample 08: For Update Command public void UpdateAuthor(Author aut) { //Sample 8.1: Create Connection and Data Table DataTable author_updt = new DataTable(); SqlConnection Con = new SqlConnection(settings.ConnectionString); //Sample 8.2: Create SQL Adaptor to get a single //row based on primary key SqlDataAdapter adapter = new SqlDataAdapter( "Select Au_id, Au_fname, city from Authors " + "where au_id = @Author_id", Con); adapter.SelectCommand.Parameters.AddWithValue( "@Author_id", aut.Au_id); adapter.Fill(author_updt); //Sample 8.3: Suppy the adapter the Update command SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); //Sample 8.4: Perform the change in the data row and perform //the update on Data Adaptor DataRow Row_tobe_updated = author_updt.Rows[0]; Row_tobe_updated["Au_fname"] = aut.au_Fname; Row_tobe_updated["City"] = aut.City; adapter.Update(author_updt); } |
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
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
//Sample 09: For Delete Command public void DeleteAuthor(Author autD) { //Sample 1.1: Create Connection and Data Table DataTable author_delete = new DataTable(); SqlConnection Con = new SqlConnection(settings.ConnectionString); //Sample 9.2: Create SQL Adaptor to get a single row //based on primary key SqlDataAdapter adapter = new SqlDataAdapter( "Select Au_id, Au_fname, city from Authors " + "where au_id = @Author_id", Con); adapter.SelectCommand.Parameters.AddWithValue( "@Author_id", autD.Au_id); adapter.Fill(author_delete); //Sample 8.3: Perform the delete operation (On a single row in //the data table) SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.DeleteCommand = builder.GetDeleteCommand(); //Sa,ple 8.4: Delete the data row and asks the adaptor to //update the db author_delete.Rows[0].Delete(); adapter.Update(author_delete); } |
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 2.0
Tags: Business Layer Class, Data Class, Delete Record, Display Data, Edit Record, ObjectDataSource
1 reply ›
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.
The website is very useful.