ASP.NET

Fill GridView Based on DropDownList Using Stored Procedure

Here you will find the steps,

Step 1

You need to create a database with table named “Customers”.



After creating table, you can fill it with data rows as shown below:



Step 2

You proceed to create stored procedure named “Customers_ByCountry” with input parameter @Country which will be used to retrieve data from tables as follows,

  1. CREATEPROCEDURECustomers_ByCountry  
  2. @CountryVARCHAR(50)  
  3. AS  
  4. BEGIN  
  5.   
  6. SELECT*FROMCustomersWHERECustomerCountry=@Country  
  7. END  
Step 3

Open Visual Studio, Add New Project.



Step 4

Configuring Entity Framework to use Stored Procedure.

Now, I will show you the steps that allow us to configure Entity Data Model.

For doing this, you need to Add New Item > Data > ADO.NET Entity Data Model as shown below:









Step 5

Import stored procedure into the Entity Framework.

Now, you will need to click right in Model1.edmx, Add>then click on Function Import.



After clicking on Function Import, Add Function Import dialog window will be displayed as below:



Here, we need to specify the Function Import Name (in our case “Customer_ByCountry_Result”) and then select the stored procedure (“Customers_ByCountry”) which will be used when the function is called.

After selecting the stored procedure, you need to click on Get Column Information button.

Now you will need to select complex RadioButton and click Create New ComplexTypebutton for having a complex Type Class that contains stored procedure records and finally click OK.

Step 6

Create Web Page named “DisplayDataInGridView.aspx”



HTML Markup

At this level, you to add Grid view and dropdown list controls from Toolbox.

DisplayDataInGridView.aspx
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="DisplayDataInGridView.aspx.cs"Inherits="GridView_StoredProcedure.DisplayDataInGridView"%>  
  2.   
  3.     <!DOCTYPEhtml>  
  4.   
  5.     <htmlxmlns="http://www.w3.org/1999/xhtml">  
  6.         <headrunat="server">  
  7.             <metahttp-equiv="Content-Type" content="text/html; charset=utf-8" />  
  8.             <title></title>  
  9.             </head>  
  10.   
  11.             <body>  
  12.                 <formid="form1" runat="server">  
  13.   
  14.                     <table>  
  15.   
  16.                         <tr>  
  17.                             <td> Select Country </td>  
  18.                             <td>  
  19.                                 <asp:DropDownListID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>  
  20.                             </td>  
  21.                         </tr>  
  22.   
  23.                     </table><br/><br/>  
  24.   
  25.                     <div>  
  26.                         <asp:GridViewID="GridView1" runat="server" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical">  
  27.                             <AlternatingRowStyleBackColor="White" />  
  28.                             <FooterStyleBackColor="#CCCC99" />  
  29.                             <HeaderStyleBackColor="#6B696B" Font-Bold="True" ForeColor="White" />  
  30.                             <PagerStyleBackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />  
  31.                             <RowStyleBackColor="#F7F7DE" />  
  32.                             <SelectedRowStyleBackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />  
  33.                             <SortedAscendingCellStyleBackColor="#FBFBF2" />  
  34.                             <SortedAscendingHeaderStyleBackColor="#848384" />  
  35.                             <SortedDescendingCellStyleBackColor="#EAEAD3" />  
  36.                             <SortedDescendingHeaderStyleBackColor="#575357" />  
  37.                             </asp:GridView>  
  38.                     </div>  
  39.                 </form>  
  40.             </body>  
  41.   
  42.         </html>  
DisplayDataInGridView.aspx.cs
  1. using System;  
  2. usingSystem.Collections.Generic;  
  3. usingSystem.Linq;  
  4. usingSystem.Web;  
  5. usingSystem.Web.UI;  
  6. usingSystem.Web.UI.WebControls;  
  7. usingSystem.Data.SqlClient;  
  8. usingSystem.Data;  
  9. namespaceGridView_StoredProcedure  
  10. {  
  11.     publicpartialclassDisplayDataInGridView: System.Web.UI.Page  
  12.     {  
  13.         protectedvoidPage_Load(object sender, EventArgs e)  
  14.         {  
  15.             SqlConnectionconx = newSqlConnection("Data Source=.;Initial Catalog=EntityFrameworkTest;Integrated Security=True");  
  16.             if (!this.IsPostBack)  
  17.             {  
  18.                 using(SqlCommandcmd = newSqlCommand("SELECT Distinct CustomerCountry FROM CUSTOMERS", conx))  
  19.                 {  
  20.                     conx.Open();  
  21.                     DropDownList1.DataSource = cmd.ExecuteReader(); // fill dropdownlist with all countries from database  
  22.                     DropDownList1.DataTextField = "CustomerCountry";  
  23.                     DropDownList1.DataValueField = "CustomerCountry";  
  24.                     DropDownList1.DataBind();  
  25.                     conx.Close();  
  26.                 }  
  27.             }  
  28.         }  
  29.         protectedvoid DropDownList1_SelectedIndexChanged(object sender, EventArgs e)  
  30.         {  
  31.             EntityFrameworkTestEntities2db = newEntityFrameworkTestEntities2();  
  32.             GridView1.DataSource = db.Customers_ByCountry_Result(DropDownList1.SelectedItem.ToString()); // call stored procedure, and then pass dropdownlist as parameter  
  33.             GridView1.DataBind();  
  34.         }  
  35.     }  
  36. }  
Step 7

Run Application.

 
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs