Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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>

5 comments:

  1. Hi Vnekat,

    thak you very much for sharing your technical stuff with community. your video tutorial were
    very impressive an infromative. if you have a time, plese provide the videos on webservices ,wcf and mvc

    thank you very much once agian

    ReplyDelete
  2. Hello sir,
    Please provide videos on wcf and mvc...
    Thanks.

    ReplyDelete
  3. Superb one.... but i am really wondering what is the need of first stored procedure.. we can retrieve values directly also :)

    ReplyDelete
  4. How to use a stored Procedure with a cursor in it?
    The GridView fetches only first record, as is returned by cursor. How to read all records in Gridview?

    Eg. - I want to display IN_Time, Out_Time , WorkHrs of each employee, everyday. So, i used a cursor that fetches the empid and one by one calculates the Workhrs for each of them everyday.

    When i execute this stored procedure, i can see values coming in output window for all the employees. However, when i call this stored procedure in GridView, only the records of first empid is retrieved.

    I have also tried using temporary table and storing all the values; but that way i wont be able to seperate record of each employee.

    ReplyDelete
  5. Please tell me in place of dropdownList if i am using web report component how should i select that

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.