Introduction: In this article I
am going to share how to use C# "params" keyword and its equivalent "ParamArray" in VB.Net to bind multiple dropdownlist controls from the same data source in a
single sql server database call in Asp.Net.
In previous articles i explained how to bind category and sub categories in single dropdownlist and and Insert item at first or last position in databound dropdownlist and Bind dropdownlist and disable some items based on condition and Populate time in dropdownlist with interval of minutes or hours dynamically and fill dropdownlist with days, month and year
Description: By using C Sharp "params" keyword or VB.Net's "ParamArray" we can pass an arbitrary number of arguments
to a function.
To call such function we send a comma-separated
list of arguments of the type specified in the parameter declaration or an
array of arguments of the specified type.
As demonstrated in the example
below I have created a function BindDepartments() that takes an array of
ListControl and also used params keyword with this. By doing so we can pass any
number of ListControl e.g. dropdownlist separated by comma to that function.
In
the function BindDepartments() I have fetched the department records from the sql
server database once and then I loop through each control in ListControl array(in
my case 3 dropdownlists) and added department data in each dropdownlist control.
Implementation:
Let’s create a page to demonstrate the concept.
- First of all create a table and add dummy data into it using the following script:
CREATE TABLE tbDepartmentMaster
(
DepartmentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
DepartmentName VARCHAR(100)
)
INSERT INTO tbDepartmentMaster VALUES
('Administration'), ('HR'), ('IT'), ('Sales'), ('Inventory'), ('Accounts')
- Now create a stored procedure to fetch all the departments from table to bind in dropdownlist
CREATE PROCEDURE spBindDepartments
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM tbDepartmentMaster
END
- Now In the <configuration> tag of web.config file add your connectionstring as:
<connectionStrings>
<add name="conStr" connectionString="Data Source=LALIT-PC;Initial
Catalog=MyDataBase;Integrated Security=True"/>
</connectionStrings>
Note: Replace Data Source and
Catalogue i.e. Database name
as per your application
HTML
Source Code:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<fieldset
style="width:400px;
height:180px">
<legend>Populate multiple dropdownlist from
database in single call</legend>
<table>
<tr>
<td>Previous Department:
</td>
<td>
<asp:DropDownList ID="ddlPreviousDept" width="200px" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td>Current Department: </td>
<td>
<asp:DropDownList ID="ddlCurrentDept" width="200px" runat="server"></asp:DropDownList></td>
</tr>
<tr>
<td>Promoting to
Department: </td>
<td>
<asp:DropDownList ID="ddlPromotingToDept" width="200px" runat="server"></asp:DropDownList></td>
</tr>
</table>
</fieldset>
</div>
</form>
</body>
</html>
Asp.Net
C# Code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
//Create Connection
object and get connection string defined in web.config file
SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//
Pass any number of dropdownlist controls separated by comma as a parameter to
the functions
BindDepartments(ddlPreviousDept,
ddlCurrentDept, ddlPromotingToDept);
}
}
protected void BindDepartments(params
ListControl[]
controls)
{
try
{
DataTable dt = new DataTable();
using (sqlCon)
{
using (SqlCommand cmd = new SqlCommand("spBindDepartments", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
using
(SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
//fill data in datatable through
sqldataadapter
adp.Fill(dt);
//Check if datatable has data or not.
if (dt.Rows.Count >
0)
{ //loop through each
control(DropDownList)
foreach
(ListControl
ctrl in controls)
{
//Clear Previous filled items from dropdownlist(if
any)
ctrl.Items.Clear();
//loop through each row in datatable
foreach
(DataRow
dtRow in dt.Rows)
{
//Read desired columns from datatable's
row and add in control(dropdownlist)
ctrl.Items.Add(new ListItem(dtRow["DepartmentName"].ToString(), dtRow["DepartmentId"].ToString()));
}
if (ctrl.Items.Count
> 0)
{
//Insert "-select-" at the
first position in dropdownlist
ctrl.Items.Insert(0, new ListItem("-Select-", "0"));
}
else
{
//Insert "-No data-" at the
first position in dropdownlist
ctrl.Items.Insert(0, new ListItem("-No
Data-", "0"));
}
}
}
}
sqlCon.Close();
}
}
}
catch (Exception ex)
{
Response.Write("Error:" + ex.Message.ToString());
}
}
Asp.Net VB Code
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI
Imports System.Web.UI.WebControls
'Create
Connection object and get connection string defined in web.config file
Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
Protected Sub Page_Load(sender As Object,
e As EventArgs)
Handles
Me.Load
If Not Page.IsPostBack Then
'
Pass any number of dropdownlist controls separated by comma as a parameter to
the functions
BindDepartments(ddlPreviousDept,
ddlCurrentDept, ddlPromotingToDept)
End If
End Sub
Protected Sub BindDepartments(ParamArray
controls As ListControl())
Try
Dim dt As New DataTable()
Using sqlCon
Using cmd As New SqlCommand("spBindDepartments", sqlCon)
cmd.CommandType = CommandType.StoredProcedure
sqlCon.Open()
Using
adp As New SqlDataAdapter(cmd)
'fill data in datatable through
sqldataadapter
adp.Fill(dt)
'Check if datatable has data or not.
If dt.Rows.Count >
0 Then
'loop through each control(DropDownList)
For Each ctrl As ListControl
In controls
'Clear Previous filled items from
dropdownlist(if any)
ctrl.Items.Clear()
'loop through each row in datatable
For Each dtRow As DataRow
In dt.Rows
'Read desired columns from datatable's
row and add in control(dropdownlist)
ctrl.Items.Add(New ListItem(dtRow("DepartmentName").ToString(), dtRow("DepartmentId").ToString()))
Next
If ctrl.Items.Count
> 0 Then
'Insert "-select-" at the first
position in dropdownlist
ctrl.Items.Insert(0, New ListItem("-Select-", "0"))
Else
'Insert "-No data-" at the
first position in dropdownlist
ctrl.Items.Insert(0, New ListItem("-No
Data-", "0"))
End If
Next
End If
End Using
sqlCon.Close()
End Using
End Using
Catch ex As Exception
Response.Write("Error:" + ex.Message.ToString())
End Try
End Sub
Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..