how to simplified bind parent category and child cate gory in repeater

Vote count:

0

My questions is how to bind repeater in parent category and child category with simple way. Every time connection should not be open

in my code sql connection frequently open thats why my page also open slowly

if soloutions plz help me out

here is my code

categories_BLL cat = new categories_BLL();
    DataSet ds;
    string parent_id;
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["justsearch4u"].ToString());

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //fi();
            BindParentDataList();
            BindNestedDataList();
        }

    }

    public void BindParentDataList()
    {
        try
        {
            // string variable to store the connection string
            // retrieved from the connectionStrings section of web.config


            // sql command object initialized with select command text
            SqlCommand mySqlCommand = new SqlCommand("select * from parent_category order by p_cat_name asc", connection);

            // check the connection state and open it accordingly.
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            // Sql datareader object to read the stream of rows from SQL Server Database
            SqlDataReader myDataReader = mySqlCommand.ExecuteReader();

            // Pass the Sql DataReader object to the DataSource property
            // of DataList control to render the list of items.
            DataList1.DataSource = myDataReader;
            DataList1.DataBind();

            // close the Sql DataReader object
            myDataReader.Close();

            // check the connection state and close it accordingly.
            if (connection.State == ConnectionState.Open)
                connection.Close();

            // foreach loop over each item of DataList control
            foreach (RepeaterItem Item in DataList1.Items)
            {

                BindNestedDataList();
            }
        }
        catch
        {


        }

    }

    public void BindNestedDataList()
    {

        try
        {
            int i;
            for (i = 0; i < DataList1.Items.Count; i++)
            {
                // get CategoryID value for the current datalist item
                // DataKeys collection object returns the associated value
                // at specified Item Index of DataList
                Label lit = (Label)DataList1.Items[i].FindControl("idLabel");

                // string variable to store the connection string
                // retrieved from the connectionStrings section of web.config
                //string connectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;

                //// sql connection object
                //SqlConnection mySqlConnection = new SqlConnection(connectionString);

                // sql command object initialized with select command text




                SqlCommand mySqlCommand = new SqlCommand("select * from sub_category where parent_id='" + lit.Text + "' ORDER BY sub_cat_name OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY", connection);
                //SqlCommand mySqlCommand = new SqlCommand("select * from sub_category where parent_id='" + lit.Text + "'", connection);



                // check the connection state and open it accordingly.
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                // Sql datareader object to read the stream of rows from SQL Server Database
                SqlDataReader myDataReader = mySqlCommand.ExecuteReader();
                //SqlDataReader myDataReader1 = mySqlCommand1.ExecuteReader();

                // findControl function to get the nested datalist control
                Repeater nestedDataList = (Repeater)DataList1.Items[i].FindControl("nestedDataList");

                //datalist.DataSource = myDataReader1;
                //datalist.DataBind();

                nestedDataList.DataSource = myDataReader;
                nestedDataList.DataBind();








                // close the Sql DataReader object
                myDataReader.Close();

                // check the connection state and close it accordingly.
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        catch
        {


        }
    }

this is aspx code

 <asp:Repeater ID="DataList1" runat="server">
                        <ItemTemplate>
                            <div class="span3" style="width: 260px;">
                                <ul class="nav nav-tabs nav-stacked nav-coupon-category menu_item<%#Eval("parent_id") %>">
                                    <li class="active">
                                        <a class="accordion-toggle active" title='<%# Eval("p_cat_name") %>' style="color: #111111" data-toggle="collapse" data-parent="#accordion" href="#collapse-1">
                                            <asp:Label ID="Label1" runat="server" Style="margin-left: -30px; color: #000; font-weight: bold;" Text='<%# Bind("p_cat_name") %>'></asp:Label></a>
                                        <asp:Label ID="idLabel" runat="server" Text='<%#Bind("parent_id") %>' Visible="false"></asp:Label></a>
                                    </li>
                                    <asp:Repeater ID="nestedDataList" runat="server">
                                        <ItemTemplate>
                                            <li class="menu_options<%#Eval("parent_id") %>" style="display: none;">

                                                <a title='<%#Eval("sub_cat_name") %>' href="searchresults/camaccess.aspx?p_id=<%#Eval("parent_id") %>&sub_id=<%#Eval("sub_id") %>&p_name=<%#Eval("sub_cat_name") %>&city_id=">
                                                    <i class="icon-location-arrow"></i>
                                                    <asp:Label ID="Label2" Style="font-weight: bold; color: #000" runat="server" Text='<%# Bind("sub_cat_name") %>'></asp:Label></a>

                                            </li>
                                        </ItemTemplate>
                                    </asp:Repeater>



                                    <asp:Button ID="Button1" CssClass="btn btn-lg btn-primary" UseSubmitBehavior="false" Visible="false" CommandName="cli" runat="server" Text="More" />
                                    <!-- END ACCORDION -->
                                    <!-- START ACCORDION -->

                                    <!-- END ACCORDION -->
                                    <!-- START ACCORDION -->

                                    <!-- END ACCORDION -->
                                </ul>
                            </div>




                        </ItemTemplate>
                    </asp:Repeater>

asked 1 min ago

Lingraj Gowda

36

how to simplified bind parent category and child cate gory in repeater