Please disable your adblock and script blockers to view this page
 

Populate multiple dropdownlist from same datasource in single database call in Asp.Net C#,VB using params or ParamArray

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.
Populate multiple dropdownlist controls from same datasource in single database call using params or ParamArray in Asp.net C#,Vb

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." 
Previous
Next Post »

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.. ConversionConversion EmoticonEmoticon