In this post I will demonstrate with a hands on example how to use the ObjectDataSource web server control to fetch data from a database. I will use this control in an ASP.Net application. I will also use GridView,DropDownlist,DetailsView web server controls.

Everything you will see in this post can also be done using the SQLDataSource web server control.So why we should bother with the ObjectDataSource web server control? Well, when using the SQLDataSource web server control to fetch data from the database, there is not a lot of code reuse.As developers we do not want that.We do not want to duplicate the same code to multiple pages.Basically with the SQLDataSource web server control we have a single-tier application where we have our SQL statements in our .aspx pages.That is not the best way to write large web applications because we mix layout and code that is responsible for fetching data from the database. Those kind of applications are not easy to maintain and is harder to debug them.

The ObjectDataSource web server control provides declarative data binding between objects and controls. By objects I mean custom objects such as business objects that we can bind to the ObjectDataSource web server control.We can have N-tier/N-layer architectures that separate presentation,business anda data layers.It supports paging and sorting. It also supports select,insert,update and delete operations.All these select,insert,update,delete methods can be defined in the business object. It also provides built-in caching.

I will use Visual Studio 2010 ultimate edition. You can use VS 2010/2008. Express editions will suffice. I am going to use the Pubs database. You can download the installation scripts of the Pubs database from here.

I am going to retrieve data from the Authors table.In the DropDownlist control I will get the distinct cities from the Authors table. By that I mean I will call a method in a class (data access layer) that will bind all cities of Authors table to the DropDownlist control.Then when I select an author from the GridView control, all their details will appear in the DetailsView control.I will use methods that I will write from my data access class to do that. This is the small web application we will try to build.

I will not use classic ADO.Net (SQLConnection,SQLCommand,SQLDatareader) or Dataset objects.I will use LINQ to SQL instead. Have a look at my other posts on LINQ to SQL in this blog. You will find an abundance of information.

1) Launch Visual Studio.Create an empty website and give an appropriate name. Choose C# as the development language.

2) Add a new item to your website, a web form. Leave the default name. Add a DropDownlist control , a GridView control, a DetailsView control on the default.aspx page.Leave the default names

3) Add a new item in your website, a class file. Name it DAL.cs. Inside there I will have 3 methods. The first one will bring back the cities from the Authors table. The next one will get a paramater ( the name of the city) and will return the Authors living in that city. The third one will take an input parameter(authorid) from the selected author in the GridView control and display the author's details. Place this file in the special folder App_Code.

4) Add a new item in your website, A Linq to SQL classes item.I have named it Pubs.dbml.Place this file in the special folder App_Code.From Server Explorer window, create a new connection to your local instance of the SQL Server and to the pubs database.Have a look at the picture below.

5)  Then from the Server Explorer window expand the Pubs database connection and then drag and drop the Authors table on the designer . Now we have a brand new Author data class we can talk to.

6)  Now I will create the methods in the DAL class that will bind to the web server controls. I will use LINQ queries of course.

The first method will be GetCities() . The code for the method follows.

 public List<String> GetCities()
    {
        using (var ctx = new PubsDataContext())
        {
 
            var query = (from auth in ctx.authors
                        select auth.city).Distinct().ToList();
 
            return query;
 
 
 
        }

It is very easy to understand what I am doing with this method . Again, I assume you have some experience using LINQ to SQL.I have a simple method that returns a list of strings(cities). I create a connection to the class using the PubsDataContext object.Then I use a variable to store the distinct cities return from the LINQ to SQL query.

Now we can bind this method to the DropDownList control.Click on the DropDownList's control smart tag and then click Choose DataSource. From the wizzard that pops up, in the Choose a DataSource Type, select Object. Name the object CitiesDataSource and click OK.

Have a look at the picture below.

Now, after you click OK, the wizzard goes on and you have to choose your business object. From the dropdown select DAL, which is our business object.Then click Next.

Have a look at the picture below.

After you click Next , you have to choose the method of the business object that returns data. In our case it is the method GetCities().Then click Finish and then OK to close the wizzard. Have a look at the picture below.

Now if we go to our default.aspx page we will see that the markup we have for our CitiesDataSource is this

 <asp:ObjectDataSource ID="CitiesDataSource" runat="server" 
        SelectMethod="GetCities" TypeName="DAL"></asp:ObjectDataSource>

The markup for the DropDownList control is this

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 

AppendDataBoundItems="True" DataSourceID="CitiesDataSource">
        <asp:ListItem Text="Select a city" Value=""> </asp:ListItem>
    </asp:DropDownList>

Run your application and make sure the cities appear in the DropDownList control.

7) Let's create our second method.In the DAL.cs file we must create a new method, GetAuthorsByCity , that will take an input parameter that represents the city so we can filter according the authors according to the city.

The code for the method follows

 public List<author> GetAuthorsByCity(string city)
    {
        using (var ctx= new PubsDataContext())
        {
            var query = (from auth in ctx.authors
                         where auth.city == city
                         select auth).ToList();
 
            return query;
 
 
        }
    } 

In this method I will return a list of authors by writing a simple LINQ to SQL query.

8) Now , you must follow the same steps as in step 6. You must click on the GridView's smart tag and create a new ObjectDatasource.Name it AuthorsDataSource.Then you need to select the business object (DAL) and then select from the available methods the GetAuthorsByCity method.

In the last step of the wizzard you will see something like the picture below. We provide the parameter that our method needs from the DropDownList control selected value.Click Finish to finish the wizzard.

Enable Paging and Selection on the GridView control. I have deleted some of the fields in the GridView control.The markup for the GridView control follows.

<asp:GridView ID="GridView1" runat="server" BackColor="White" 
   BorderColor="#336666" BorderStyle="Double" BorderWidth="3px" CellPadding="4" 
   GridLines="Horizontal" AllowPaging="True" AutoGenerateColumns="False" 
   DataKeyNames="au_id" DataSourceID="AuthorsDataSource">
    <Columns>
     <asp:CommandField ShowSelectButton="True" />
   <asp:BoundField DataField="au_id" HeaderText="au_id" SortExpression="au_id" />
   <asp:BoundField DataField="au_lname" HeaderText="au_lname" 
      SortExpression="au_lname" />
   <asp:BoundField DataField="au_fname" HeaderText="au_fname" 
      SortExpression="au_fname" />
   <asp:BoundField DataField="city" HeaderText="city" SortExpression="city" />
  </Columns>
   <FooterStyle BackColor="White" ForeColor="#333333" />
   <HeaderStyle BackColor="#336666" Font-Bold="True" ForeColor="White" />
   <PagerStyle BackColor="#336666" ForeColor="White" HorizontalAlign="Center" />
   <RowStyle BackColor="White" ForeColor="#333333" />
   <SelectedRowStyle BackColor="#339966" Font-Bold="True" ForeColor="White" />
   <SortedAscendingCellStyle BackColor="#F7F7F7" />
   <SortedAscendingHeaderStyle BackColor="#487575" />
   <SortedDescendingCellStyle BackColor="#E5E5E5" />
   <SortedDescendingHeaderStyle BackColor="#275353" />
    </asp:GridView>

 Run your application and select a city from the dropdownlist control. Note the data in the GridView being filtered to correspond to the authors from that particular city.

9) Now we must write our final method, that returns author's details that match the selected author from the GridView control.

The code for the method follows

    public author GetAuthor(string AuthorID)
    {
        using (var ctx = new PubsDataContext())
        {
            var query = (from auth in ctx.authors
                         where auth.au_id == AuthorID
                         select auth).SingleOrDefault();
 
            return query;
 
 
        }
 
    }

In this method we return an object of type Author.This method takes an input parameter(AuthorID). This parameter will take its value from the GridView control.

Now , you must follow the same steps as in step 8. You must click on the DetailsView's smart tag and create a new ObjectDatasource.Name it AuthorsDataSourceDetails.Then you need to select the business object (DAL) and then select from the available methods the GetAuthor method. In the last step of the wizzard you will see something like the picture below. We provide the parameter that our method needs from the GridView control selected value.Click Finish to finish the wizzard.

Run your application and make sure that when you click a row from the Gridview the author's details are displayed to the DetailsView. Your application should look something like this

In this post, we showed how easy is to use the ObjectDataSource control to bind data to web server controls from a custom business object.

Hope it helps. Email me if you need the source code.