Tuesday, April 16, 2013

[ ASP.Net ] - Binding the GridView Control to Database Using ObjectDataSource Control


1. Introduction

In this article, we are going to see the usage of the Object Data Source control. We will create the web page with a GridView control and link that control to the ObjectDataSource control.

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



Looking at the picture you 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 middle tier and we call this class as business layer class.  This shown in the below picture:


OK. Let us start moving through 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 Sample Application

As the page is going to have only one GridView the sample application is easily imaginable. The screenshot of the sample application is shown below:


The Data is retrieved from the Authors table of the Pubs database.  The user can navigate through the pages of the GridView or they can modify or update a single record. This can be easily done using the SqlDataSource control. But we will use the ObjectDataSource Source in this example and from the previous section you are aware of the advantage of using the ObjectDataSource control.



3. The DataClass - Author

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:


The data class represents the table column data. From the above picture, we know that we are going to 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 data name. Add a class called Author to your project under the App_Code folder and provide the properties for all three picked columns. If App_code folder is not available by default, add it to the project using the helper screen shot below:


The data class for the Authors table is shown below:

/// <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 that when you create the property, the property should match the database column name. In the above class, I have three properties and each one is for one column that we are planning to display in the GridView.



4. The Business Layer Class - AuthorDataSet

Well. We are done with the data class. Let us go ahead and define the Business class that operates on the data supplied by the data class.

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.

1) In the AuthorDataSet class get the required namespace for this example

//Sample 04: Name Space
using System.Data.SqlClient;


2) Next, add the web.config file if it is not already available as part of the project. To add, select the project, right-click and choose the context menu item “Add new item…” and then from the displayed dialog select the Web Configuration file. OK, in the web configuration file define the connection string setting. This is shown in the below screenshot:




1: Your connection string name. This name will be used later, to get the connection string
2: Specifies the provider name. In our case, it is SqlClient
3: Here, you can specify the network machine name or IP address in which the database in running. I specified localhost as the database is running on the same machine in which I am developing this sample.
4: Specifies which database you want to point on the database server.
5: Username
6: Password


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

//Sample 05b: Connection string from web.config
private ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Pubs"];


4) A default constructor must present in the business layer class as the “DataObjectSource” accesses this class and the default constructor will provide the ability to create the object on the fly.

//Sample 06: Default Ctor
public AuthorDataSet()
{
}


5) In the previous step, I specified that the Business Layer Class must provide a default constructor. It should also provide at least one method that returns an Array, List or DataTable etc., (Simply a collection of data). The below-given code retrieves the data from the database and returns that as a data table.


//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 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;
}

Explanation

First, a SqlConnection object is created using the connection string retrieved from the config file. Then, Select statement to retrieve the data from the database is defined in the SqlDataAdapter. And, finally, we asked the adapter to fill the data table with the result of executing the select statement. This can be depicted as follows:


OK. Now the business layer object implemented a method, that returns the collection of rows and columns retrieved from the database as DataTable. We need to design the web page to utilise this method and display the data returned through the DataTable. In the video given below, I explained designing the form and displaying the data in the DataGrid by making use of ObjectDataSource Control.


Video 1: Configure Object Data Source & Bind with DataGrid



5. Support Update and Delete operations


In the previous section, we saw retrieving the data from the database and displaying it in the DataGrid through the ObjectDataSource Control. In this section, we will perform the update and delete operation on the DataGrid view so that it will be reflected in the on the database.

In the AuthorDataSet class define two new methods, One for the Update and other One for the Delete operation. The Update Method is given below:

//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);
}

Explanation

The first thing you note here is that the Author data class instance but is passed as the parameter to this function. Who will supply this parameter? Well, when you click the Update link in the GridView control (After Modifying the Data), the GridView control notifies the ObjectDataSource control that a particular row is modified. Now ObjectDataSource is aware of the method that should be informed of the update Operation. The modified row is filled in the instance of the data class Author and after that, it is passed as the parameter to the function UpdateAuthor function.

The function retrieves single row by forming the Select Query based on the primary key au_id. Note that the parameter field for the select query filled by taking data carried by the at instance passed in a parameter. Once Select Query is ready we fill the DataTable author_updt by calling the Fill method in the DataAdapter.

Once we have the DataTable ready for the modification, we educate the DataAdapter with the Update command. The SqlCommandBuilder instance builder is used to build the UpdateCommand and assign it to the DataAdapter back.

Finally, Once Adapter is Aware of the UpdateCommand; we take the single row from the DataTable and update the columns with the changed in data coming from the DataGridView as a parameter to this function. Once the DataRow taken from the DataTable is updated with the modified data, we call the Update method on the DataAdapter. This method applies the changes made to the DataTable to the underlying SQL Server database. The Entire operation can be depicted as shown in the below picture:


Now, I no need to explain much about the function that used for the deleting a record from the database. The function for the delete operation is listed below:

//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);
}

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

Remember, From the Previous video you are aware that our ObjectDataSource Controls only deals with the Select Operation Only. Now we should update the ObjectDataSource Control with the Update and Delete Operation. After doing so, we should also modify the GridView control to support Modification and Deletion. In the below video explained these steps:


Video 2: Providing the Support for Update and Delete Operation



6. Summary

Hope this article helped you in understanding how do we use the ObjectDataSource control with the GridView control. The attachment contents are given below:

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

3 comments:

  1. Nice article learn so much from here i hope you will give us such kind of articles in future also

    thanks


    ReplyDelete
  2. I will give Azad. Thanks for the reply

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete

Leave your comment(s) here.

Like this site? Tell it to your Firend :)