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.
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:
