Using stored procedures with sqldatasource control - Part 11

Suggested Videos 
Part 8 - Formatting gridview using rowdatabound event
Part 9 - Formatting gridview based on row data
Part 10 - Design time and runtime formatting of gridview



In this video we will discuss about using a stored procedure with sqldatasource control, to retrieve and display data in a gridview control. First let's create required tables and populate them with sample data.

Create Table tblDepartment
(
DepartmentId int primary key identity,
Name nvarchar(50)
)



Insert into tblDepartment values ('IT')
Insert into tblDepartment values ('HR')
Insert into tblDepartment values ('Payroll')
Insert into tblDepartment values ('Administration')

Create Table tblEmployee
(
EmployeeId int Primary key identity,
Name nvarchar(50),
DeptId int Foreign key references tblDepartment(DepartmentId)
)

Insert into tblEmployee values ('Mark',1)
Insert into tblEmployee values ('John',1)
Insert into tblEmployee values ('Mary',3)
Insert into tblEmployee values ('Mike',4)
Insert into tblEmployee values ('Pam',3)
Insert into tblEmployee values ('David',1)
Insert into tblEmployee values ('Tom',2)
Insert into tblEmployee values ('Jason',4)
Insert into tblEmployee values ('Vicky',3)
Insert into tblEmployee values ('Todd',1)
Insert into tblEmployee values ('Ben',2)

Stored procedure to retrieve department Ids and Names
Create Procedure spGetDepartments
as
Begin
Select DepartmentId, Name from tblDepartment
End

Stored procedure to retrieve employees by department
Create Procedure spGetEmployeesByDepartmentId 
@DepartmentId int
as
Begin
Select EmployeeId, tblEmployee.Name as EmployeeName, 
                    tblDepartment.Name as DepartmentName
from tblEmployee
join tblDepartment
on tblDepartment.DepartmentId = tblEmployee.DeptId
where tblDepartment.DepartmentId = @DepartmentId
End

Create an asp.net web application. Drag and drop 2 sqldatasource controls, a dropdownlist and a gridview control on WebForm1.aspx.

Configure "SqlDataSource1" control to retrieve departments data using stored procedure "spGetDepartments"
1. Right click on "SqlDataSource1" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetDepartments" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On the subsequent screen, you can test the query, if you wish to.
8. Finally click Finish.

We are now done configuring "SqlDataSource1" control. 

Let's now configure "DropDownList1" to use "SqlDataSource1" control.
1. Right click on "DropDownList1" control and select "Show Smart Tag" 
2. Now click on "Choose Data Source" link
3. Select "SqlDataSource1" from "Select a Data Source" dropdownlist
4. Select "Name" 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 "SqlDataSource2" control, to retrieve employees by department.
1. Right click on "SqlDataSource2" control and select "Show Smart Tag" 
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetEmployeesByDepartmentId" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On "Define Parameters" screen, select "Control" from "Parameter Source" dropdownlist
8. Select "DropDownList1" from "ControlID" dropdownlist and click "Next"
9. On "Test Query" screen click "Finish"

Now let us associate "SqlDataSource2" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag" 
2. Select "SqlDataSource2" 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="SqlDataSource1" DataTextField="Name" 
    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" DataKeyNames="EmployeeId" 
    DataSourceID="SqlDataSource2">
    <Columns>
        <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" 
            InsertVisible="False" ReadOnly="True" 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>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>" 
    SelectCommand="spGetDepartments" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>
<br />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SampleConnectionString %>" 
    SelectCommand="spGetEmployeesByDepartmentId" 
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" DefaultValue="" 
            Name="DepartmentId" PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>
</div>