Post
Ask Question

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.