Bind More than One Dropdownlist from DataBase using SqlDataReader

In this article I am going to explain how to bind multiple drop down list from single sqldatareader.

First I have created two table and one procedure. Run given below script in your Sql Server.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sha_Country]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Sha_Country](                 [CountryId] [int] IDENTITY(1,1) NOT NULL,                 [CountryName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Sha_Country] ON INSERT [dbo].[Sha_Country] ([CountryId], [CountryName]) VALUES (1, N'Afghanistan') INSERT [dbo].[Sha_Country] ([CountryId], [CountryName]) VALUES (2, N'Brazil') INSERT [dbo].[Sha_Country] ([CountryId], [CountryName]) VALUES (3, N'Cuba') INSERT [dbo].[Sha_Country] ([CountryId], [CountryName]) VALUES (4, N'Egypt') INSERT [dbo].[Sha_Country] ([CountryId], [CountryName]) VALUES (5, N'India') SET IDENTITY_INSERT [dbo].[Sha_Country] OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sha_Services]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Sha_Services](                 [ServiceID] [int] IDENTITY(1,1) NOT NULL,                 [ServiceName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] END GO SET ANSI_PADDING OFF GO SET IDENTITY_INSERT [dbo].[Sha_Services] ON INSERT [dbo].[Sha_Services] ([ServiceID], [ServiceName]) VALUES (1, N'Service1') INSERT [dbo].[Sha_Services] ([ServiceID], [ServiceName]) VALUES (2, N'Service2') INSERT [dbo].[Sha_Services] ([ServiceID], [ServiceName]) VALUES (3, N'Service3') INSERT [dbo].[Sha_Services] ([ServiceID], [ServiceName]) VALUES (4, N'Service4') INSERT [dbo].[Sha_Services] ([ServiceID], [ServiceName]) VALUES (5, N'Service5') SET IDENTITY_INSERT [dbo].[Sha_Services] OFF SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sha_GettwoList]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- ============================================= -- Author:                              Sharad Gupta -- Create date: 6-Jan-2015 -- Description:       -- ============================================= CREATE PROCEDURE [dbo].[sha_GettwoList] AS BEGIN                 -- SET NOCOUNT ON added to prevent extra result sets from                 -- interfering with SELECT statements.                 SET NOCOUNT ON;                   SELECT ServiceID,                                 ServiceName                 FROM sha_services                   SELECT CountryId,                                 CountryName                 FROM Sha_Country END ' END GO

Now Here code for c# to bind two dropdown list from single SqlDataReader using NextResult() method.

 C# code

using System; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; public partial class DropDown : System.Web.UI.Page {     protected void Page_Load(object sender, EventArgs e)     {         if (!IsPostBack)         {             GetDropDownValues();         }     }     private void GetDropDownValues()     {         SqlConnection con = new SqlConnection("data source=.;Database=test;uid=********;pwd=********");         con.Open();         SqlCommand cmd = new SqlCommand("sha_GettwoList", con);         cmd.CommandType = CommandType.StoredProcedure;         SqlDataReader dr = cmd.ExecuteReader();         if (dr.HasRows)// it is not necessity to check it returen true or false || true if one or more row otherwise false         {             ddlService.DataSource = dr;             ddlService.DataTextField = "ServiceName";             ddlService.DataValueField = "ServiceID";             ddlService.DataBind();             ddlService.Items.Insert(0, new ListItem("Select Services", "0"));             dr.NextResult();// for next select statement             if (dr.HasRows)             {                 ddlCountry.DataSource = dr;                 ddlCountry.DataTextField = "CountryName";                 ddlCountry.DataValueField = "CountryId";                 ddlCountry.DataBind();                 ddlCountry.Items.Insert(0, new ListItem("Select Country", "0"));             }             else {                 ddlCountry.Items.Insert(0, new ListItem("Country Not Found", "0"));             }         }         else{             ddlService.Items.Insert(0, new ListItem("Services Not Found", "0"));         }     } }

Source Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DropDown.aspx.cs" Inherits="DropDown" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <form id="form1" runat="server">     <div>     <fieldSet>     <legend>Multi DropDown List</legend>     <table>     <tr>     <td>Services:</td>     <td><asp:DropDownList ID="ddlService" runat="server"></asp:DropDownList> </td>     </tr>     <tr>     <td>Country:</td>     <td><asp:DropDownList ID="ddlCountry" runat="server"></asp:DropDownList> </td>     </tr>     </table>     </fieldSet>     </div>     </form> </body> </html>

OutPut: