Using stored procedures with objectdatasource control - Part 12

Suggested Videos 
Part 9 - Formatting gridview based on row data
Part 10 - Design time and runtime formatting of gridview
Part 11 - Using stored procedures with sqldatasource control



In this video we will discuss about using a stored procedure with objectdatasource control, to retrieve and display data in a gridview control. Please watch Part 11 of asp.net gridview tuorial before proceeding with this video. We will be using same sql server tables and stored procedures that we used in Part 11 of asp.net gridview tuorial.



Create an asp.net web application. Add a class file with name, DepartmentDataAccessLayer.cs to the web application project. Copy and paste the following code in DepartmentDataAccessLayer.cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    //Business object to hold department related data
    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    }
    
    public class DepartmentDataAccessLayer
    {
        //Method to return all departments
        public static List<Department> GetAllDepartments()
        {
            List<Department> listDepartments = new List<Department>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetDepartments", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Department department = new Department();
                    department.DepartmentId = Convert.ToInt32(rdr["DepartmentId"]);
                    department.DepartmentName = rdr["Name"].ToString();

                    listDepartments.Add(department);
                }
            }

            return listDepartments;
        }
    }
}

Now, add another class file with name, EmployeeDataAccessLayer.cs. Copy and paste the following code in this file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    //Business object to hold employee related data
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
        public string DepartmentName { get; set; }
    }
    
    public class EmployeeDataAccessLayer
    {
        //Method to return all employees
        public static List<Employee> GetEmployeesByDepartment(int departmentId)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetEmployeesByDepartmentId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter parameter = new SqlParameter("@DepartmentId", departmentId);
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.EmployeeName = rdr["EmployeeName"].ToString();
                    employee.DepartmentName = rdr["DepartmentName"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Right click on the solution in solution explorer, and select "Build Solution", so that the classes we added are compiled.

Drag and drop 2 objectdatasource controls, a dropdownlist and a gridview control on WebForm1.aspx.

Configure "ObjectDataSource1" control to retrieve departments data
1. Right click on "ObjectDataSource1" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select "Demo.DepartmentDataAccessLayer" from "Choose your business object" dropdownlist and click "Next"
4. Select "GetAllDepartments()" from "Choose a method" dropdownlist and click finish

We are now done configuring "ObjectDataSource1" control. 

Let's now configure "DropDownList1" to use "ObjectDataSource1" control.
1. Right click on "DropDownList1" control and select "Show Smart Tag" 
2. Now click on "Choose Data Source" link
3. Select "ObjectDataSource1" from "Select a Data Source" dropdownlist
4. Select "DepartmentName" from "Select a data field to display in the dropdownlist" 
5. Select "DepartmentId" from "Select a data field for the value of the dropdownlist" and click OK

At this point, if you run the application, all the departments should be displayed in the dropdownlist control

Now let us configure "ObjectDataSource2" control, to retrieve employees by department.
1. Right click on "ObjectDataSource2" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select "Demo.EmployeeDataAccessLayer" from "Choose your business object" dropdownlist and click "Next"
4. Select "GetEmployeesByDepartment()" from "Choose a method" dropdownlist and click "Next"
5. On "Define Parameters" screen, select "Control" from "Parameter Source" dropdownlist
6. Select "DropDownList1" from "ControlID" dropdownlist and click "Finish"

Now let us associate "ObjectDataSource2" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag" 
2. Select "ObjectDataSource2" from "Choose Data Source" dropdownlist

Finally set AutoPostBack property of DropDownList1 control to true, so that the webform will automatically postback to the server whenever the selection in the dropdownlist changes.

Run the application and notice that, as the selection in the dropdownlist changes, the data in gridview control also changes.

For your reference, here is the complete HTML of WebForm1.aspx
<div style="font-family:Arial">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"
    DataSourceID="ObjectDataSource1" DataTextField="DepartmentName" 
    DataValueField="DepartmentId">
</asp:DropDownList>
<br /><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" 
    CellPadding="3" CellSpacing="2" DataSourceID="ObjectDataSource2">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            SortExpression="EmployeeId" />
        <asp:BoundField DataField="EmployeeName" HeaderText="EmployeeName" 
            SortExpression="EmployeeName" />
        <asp:BoundField DataField="DepartmentName" HeaderText="DepartmentName" 
            SortExpression="DepartmentName" />
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" 
    SelectMethod="GetAllDepartments" TypeName="Demo.DepartmentDataAccessLayer">
    </asp:ObjectDataSource>
<asp:ObjectDataSource ID="ObjectDataSource2" runat="server" 
    SelectMethod="GetEmployeesByDepartment" TypeName="Demo.EmployeeDataAccessLayer">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" Name="departmentId" 
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:ObjectDataSource>
</div>