Multiple Data Binding using SQL DataReader with Next Result

Hello Everybody,

Here I am going to explain you a very simple but much useful thing in our regular use of coding life. We almost use it in our each and every project or web application. I was just explain to one of my friend, he was trying to do the way it was very lengthy and bunch of coding.

So I explained him a very simple way to do it and reusable also. Because while we start coding we should not think only for that particular project or only for that scope of demand, we should think beyond the scope. So while making this habit we can not only save our time but also we will b start writing a very neat and suitable coding for our future demand.

Here I am explain you how we can Bind Multiple Drop Down List in our application in very quick way, like most of the time we need some dependency kind of scenario like, we need to populate city based on the country and area based on city and so on.  

Below I have tried to explain you how we can do it by using SQL Data Reader and it’s property Next Result. Here we go…

Here is .aspx code:

<fieldset style="width:450px;">

    <legend>Multiple Data Binding using SQL Data Reader</legend>

    <table>

    <tr><td>City</td><td><asp:DropDownList ID="ddlCity" runat="server" DataValueField="CityID" DataTextField="CityName" OnSelectedIndexChanged="getRegion" AutoPostBack="true"></asp:DropDownList></td></tr>

    <tr><td>Region</td><td><asp:DropDownList ID="ddlRegion" runat="server" DataValueField="RegionID" DataTextField="RegionName" OnSelectedIndexChanged="getArea" AutoPostBack="true"></asp:DropDownList></td></tr>

    <tr><td>Area</td><td><asp:DropDownList ID="ddlArea" runat="server" DataValueField="AreaID" DataTextField="AreaName"></asp:DropDownList></td></tr>

    </table>

    </fieldset>

Here is .aspx.cs code:

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

public partial class DataReaderNextResult : System.Web.UI.Page

{

    SqlConnection dbConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ToString());

    int _cityID = 0; int _regionID = 0;

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindCityRegionArea(_cityID, _regionID);

            ddlCity.Items.Insert(0, new ListItem("Select", "0"));

            ddlRegion.Items.Insert(0, new ListItem("Select", "0"));

            ddlArea.Items.Insert(0, new ListItem("Select", "0"));

        }

    }

    protected void BindCityRegionArea(int _cityID, int _regionID)

    {

        _cityID = ddlCity.SelectedIndex >= 0 ? Convert.ToInt32(ddlCity.SelectedValue) : 0;

        _regionID = ddlRegion.SelectedIndex >= 0 ? Convert.ToInt32(ddlRegion.SelectedValue) : 0;

        SqlCommand cmd = new SqlCommand();

        string procedure = "usp_GetCityAreaRegion";

        cmd.CommandText = procedure;

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CityID", _cityID);

        cmd.Parameters.AddWithValue("@RegionID", _regionID);

        dbConn.Open();

        cmd.Connection = dbConn;

        SqlDataReader dr = cmd.ExecuteReader();

        if (ddlCity.SelectedIndex <= 0)

        {

            ddlCity.DataSource = dr;

            ddlCity.DataBind();

        }

        dr.NextResult();

        if (ddlRegion.SelectedIndex <= 0)

        {

            ddlRegion.DataSource = dr;

            ddlRegion.DataBind();

        }

        dr.NextResult();

        ddlArea.DataSource = dr;

        ddlArea.DataBind();

        dr.Dispose();

        cmd.Dispose();

        dbConn.Close();

    }

    protected void getRegion(object sender, EventArgs e)

    {

        BindCityRegionArea(_cityID, _regionID);

    }

    protected void getArea(object sender, EventArgs e)

    {

        BindCityRegionArea(_cityID, _regionID);

    }

Here is Stored Procedure we have used:

/*

Here We get the City and Region By CityID and Area by RegionID

 */

ALTER PROCEDURE usp_GetCityAreaRegion

@CityID int ,

@RegionID int

AS

BEGIN

SELECT CityID, CityName FROM myCityTable ORDER BY CityName;

IF (@CityID > 0)

BEGIN

--Get Region by passing CityID

     SELECT RegionID, RegionName FROM myRegionTable WHERE CityID = @CityID ORDER BY RegionName;

END

IF (@RegionID > 0)

BEGIN

--Get Area by passing RegionID

     SELECT AreaID, AreaName FROM myAreaTable WHERE RegionID = @RegionID ORDER BY AreaName;

END

END

I hope you will enjoy the topic, feel free to post your feedback/comment to improve my writing skills.

Cheers,

Ved Pathak