DisplayProductsUnderCategories_NestedGrids_Final2

How to display data and pictures in nested grids (datalist inside gridview) in asp.net

Hello, today I will be discussing on how to display data in nested grids in asp.net from sql server database. Nested grids are very useful in displaying multiple related data in categorised format. The nested data can be displayed using various databound controls like gridview inside gridview, gridview inside datalist, datalist inside gridview, datalist inside datalist, repeater inside gridview, gridview inside repeater, repeater inside datalist, datalist inside repeater and so on. We can actually use almost any databound control for this like Gridview, Datalist, Repeater, FormView, DetailView or ListView.

The logic behind displaying any databound control inside another databound control is the same. When I say databound control, I am referring to the controls like gridview, datalist, repeater etc. Logically what we have to do is first bind and show the data in the outer databound control. And then in the rowdatabound or itemdatabound event of this outer databound control (which fires for every row or item of the outer databound control respectively), we have to find the inner databound control and bind it to show the data in the inner databound control. So let’s discuss one case, let’s say, we need to display a datalist inside gridview. To do this, follow the following steps:

1. Create a new website
CreateNewWebsite_NestedGrids

2. Select Asp.Net Empty Website and provide the name for the website (in our case, DisplayDataInNestedGrids)
ProvideProjectName_NestedGrids

3. Add new web form (let’s name it NestedGrids.aspx) which is nothing but a web page where we will be coding in order to display our nested grids.
AddNewWebForm_NestedGrids

4. Now create a database table(CATEGORY_TBL) where we will be storing our categories and enter some dummy data.

Structure of CATEGORY_TBL is shown below:
CategoryTableStructure

Make sure you set ID column as primary key, set its Identity Specification property to “Yes”, Is Identity to “Yes”, Identity Increment to “1” and Identity Seed to “1”, as shown below:

CategoryTableSetPrimaryColumnProperties

Dummy data in CATEGORY_TBL is shown below:
CategoriesData

5. Now create a database table (PRODUCT_TBL) where we will be storing the products and enter some dummy data.

Structure of PRODUCT_TBL is shown below:
ProductTableStructure1

Make sure you set ID column as primary key, set its Identity Specification property to “Yes”, Is Identity to “Yes”, Identity Increment to “1” and Identity Seed to “1”, as shown below:
ProductTableSetPrimaryColumnProperties2

Dummy data in PRODUCT_TBL is shown below:
ProductsData1

6. Make sure the values in the CATEGORYID column in PRODUCT_TBL is same as that of the ID column in CATEGORY_TBL. This CATEGORYID column in the PRODUCT_TBL tells as to which category the product belongs to.

7. Now that the relation between categories and products have been created, we are ready to start coding to display the nested data.

8. The next step is to create a connection to this database in our asp.net website.

To do this, add the following code in web.config file under <configuration> tag:

1
2
3
<connectionStrings>
    <add name="con" connectionString="server=SUNNY-PC\SQLEXPRESS;database=AspSkills;integrated security=true" />    
  </connectionStrings>

The above code is used to specify the details of the database to which we want to connect. Where “con” is any name that we provide which we want to use to call this connection, “connectionString” is the details of the databsae server and the database to which we want to connect, in our case, the database server name is SUNNY-PC\SQLEXPRESS and the database name is AspSkills and “integrated security” set to true, means that we want to use the Windows Authentication for the database connection and hence we don’t need to specify username and password for the connection.

9. Once the connection string is created in webconfig, we can use this connection string to connect to the database.

10. Before we connect to the database, create a photos folder on your server, and paste some photos there and make sure the name of the photos are same as that are stored in the PRODUCT_TBL. The photos will be shown in solution explorer as shown below:
CreatePhotosFolderInSolutionExplorer

11. Now that we have all the data (photos on server and data in database tables) in place, the next step is to actually write some code to connect to the database and display data.

12. Since we need to display products datalist under categories gridview, the first step is to display categories in the gridview.

13. In order to bind Categories to the gridview, write the following code on NestedGrids.aspx.cs page, the explanation for each line is right after “//” symbol, which is a comment symbol:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
protected void Page_Load(object sender, EventArgs e)
    {
        if(IsPostBack==false)
        {
            SqlConnection con = new SqlConnection();//Creates SqlConnection object
            con.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;//Sets ConnectionString of the connection object to the one we set in web.config
            SqlCommand cmd = new SqlCommand();//Creates SqlCommand object
            cmd.CommandText = "select * from category_tbl";//Sets the CommandText of the SqlCommand object to the select query which is used to get data from the sql table named category_tbl
            cmd.Connection = con; //Sets the connection property of the SqlCommand object, in order to tell the SqlCommand which database connection to use to connect to the database.
            SqlDataAdapter adp = new SqlDataAdapter(cmd); //Creates an object of SqlDataAdapted, which is used to execute the command and fetch the result data.
            DataSet ds = new DataSet();//Creates a dataset, which will be used to store the records that we need to display.
            adp.Fill(ds);//Code to actually execute the command and fill the dataset with the desired data.
            grdcategory.DataSource = ds; //Tells the gridview which data to show in this case, the data residing in ds dataset.
            grdcategory.DataBind(); //Binds the gridview with the dataset.
        }
}

14. In order to display Categories in the gridview, we need to set the template for the gridview, which just tells the databound control how to display the data. Simply write the following code on NestedGrids.aspx page:

1
2
3
4
5
6
7
8
9
10
11
12
<asp:GridView ID="grdcategory" AutoGenerateColumns="false" runat="server">
        <Columns>
            <asp:TemplateField>
               <HeaderTemplate>
                   Categories
               </HeaderTemplate>
                <ItemTemplate>
                    <%#Eval("NAME") %>
                </ItemTemplate>
      </asp:TemplateField>
</Columns>
</asp:GridView>

What we have done here is created a gridview and created a single column (after setting AutoGenerateColumns property of the gridview to false (which stops the gridview from generating columns automatically) with Header Text as “Categories” placed under HeaderTemplate tag and <%#Eval(“NAME”) %> placed under ItemTemplate tag, is used to display the values from the database under the column named “NAME” from the database table, which is returned by our sql query “select * from category_tbl”. Also it is interesting to note, that the ItemTemplate repeats as many times as there are records returned by the sql query in the dataset, which is binded with the gridview.

15. After writing this much code, you will start seeing the categories in the gridview, as shown below:

CategoriesDisplayedOnWebpag

16. Now the next step is to display products for each of these categories in the inner databound control (in our case datalist). Since we need to display products based on categories (which are already binded in the datalist), we require the category id from the outer databound control (in our case it’s datalist), so that we can find the products for each of these categories and display in the internal datalist. In order to do so, we need to make a small change in our already done code. We need to set the DataKeyName of the outer grid to the primary key of the table to which it is binded, i.e the ID field (i.e the CategoryId). By doing this, each element of the outer gridview will get associated with the CategoryId and that can be seen done in the following code:

1
2
3
4
5
6
7
8
9
10
11
12
<asp:GridView ID="grdcategory" DataKeyNames="ID" AutoGenerateColumns="false" runat="server">
        <Columns>
            <asp:TemplateField>
               <HeaderTemplate>
                   Categories
               </HeaderTemplate>
                <ItemTemplate>
                    <%#Eval("NAME") %>
                </ItemTemplate>
      </asp:TemplateField>
</Columns>
</asp:GridView>

<%#Eval(“NAME”) %> is the code that displays the value of “NAME” column from the database table (in this case it’s CATEGORY_TBL), that’s binded with the databound control and what data needs to be displayed is defined using the sql query.

17. Now that each element in the categories grid is associated with the category id, the next step is to loop through the elements of the outer categories grid, get the categoryid for each element, then execute an sql query to get products based on the categoryid, then find the inner databound control (which in our is datalist), and bind the results of the sql query (which is nothing but the products based on each categoryid) with the datalist.
Now you don’t really need to write a for loop or while loop to loop through the elements of the categories grid. There’s an event which does this for us and it’s the RowDataBound event (in case of gridview) and ItemDataBound event (in case of datalist, repeater etc). So what we have to do is find the inner databound control in this event and bind it with relevant data, and the following code demonstrate how it’s done:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
protected void grdcategory_RowDataBound(object sender,GridViewRowEventArgs e)
    {
        DataList objdtlstproduct = (DataList)e.Row.FindControl("dtlstproduct");
        //Find the inner databound control (which in this case is datalist)
        //The (DataList) written in the code above is known as type casting, which means casting the type of the control. Since FindControl returns a Control type, but we know that we are finding a datalist, so we can typecast this control to datalist so that it can be assigned to a datalist object which in our case is objdtlstproduct
        if (objdtlstproduct != null)//Now we need to check if the datalist is found or not, which can be done by checking if objdtlstproduct is null or not. If objdtlstproduct is not null, means the datalist is found, which is what we want.
        {
            objdtlstproduct.DataSource = GetProductsByCategoryId(Convert.ToString(grdcategory.DataKeys[e.Row.RowIndex].Value));
            //GetProductsByCategoryId is nothing but a function which returns a dataset of products based on the CategoryId passed to it.
            //This dataset is set to the DataSurce property of the datalist object, which in our case is objdtlstproduct.
            objdtlstproduct.DataBind();//And then we call the datalist's DataBind() function to bind the data.
        }
    }
 
    private DataSet GetProductsByCategoryId(String CategoryId)//GetProductsByCategoryId function returns a dataset of products based on the CategoryId passed to it.
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from product_tbl where categoryid=" + CategoryId;
        cmd.Connection = con;
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        return ds;
    }

18. After binding the internal datalist with the data, the next step is to set the template for this datalist, just like we did for the categories grid. So we need to add a new TemplateField in the outer grid, which just tells the Gridview control to generate a new column and then we need to set it’s template to define how we want to display data in this column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<asp:GridView ID="grdcategory" DataKeyNames="ID" OnRowDataBound="grdcategory_RowDataBound" AutoGenerateColumns="false" runat="server">
        <Columns>
            <asp:TemplateField>
               <HeaderTemplate>
                   Categories
               </HeaderTemplate>
                <ItemTemplate>
                    <%#Eval("NAME") %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
               <HeaderTemplate>
                   Products
               </HeaderTemplate>
                <ItemTemplate>
                   <asp:DataList ID="dtlstproduct" RepeatColumns="5" RepeatDirection="Horizontal" runat="server">
                       <HeaderTemplate>
                           <table border="0" cellpadding="0" cellspacing="0" width="100%">
                       </HeaderTemplate>
                       <ItemTemplate>
                           <tr>
                               <td>
                                   <%#Eval("NAME") %>
                               </td>
                            </tr>
                       </ItemTemplate>
                       <FooterTemplate>
                           </table>
                       </FooterTemplate>
                   </asp:DataList>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

19. After writing this much code, you will start seeing the categories and the products for each of these categories in a nested grid, as shown below:

CategoriesProductsName_NestedGrids

20. But something is still missing. Can you guess what it is? If you remember, we added 2 more columns in the PRODUCT_TBL i.e price and photo. Since price is simple text like name, so it can be displayed using the same template code that we used to display the name of the product, the only thing we added extra is the $ symbol before the price, since we need to display the price as $1000000 and not 1000000. But in order to display the picture of the product, we need to do a bit more, i.e we need to add an image control in the inner datalist and then bind that image with the value coming from the database under the column name “PHOTO”. The following code does this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<asp:GridView ID="grdcategory" DataKeyNames="ID" OnRowDataBound="grdcategory_RowDataBound" AutoGenerateColumns="false" runat="server">
        <Columns>
            <asp:TemplateField>
               <HeaderTemplate>
                   Categories
               </HeaderTemplate>
                <ItemTemplate>
                    <%#Eval("NAME") %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
               <HeaderTemplate>
                   Products
               </HeaderTemplate>
                <ItemTemplate>
                   <asp:DataList ID="dtlstproduct" RepeatColumns="5" RepeatDirection="Horizontal" runat="server">
                       <HeaderTemplate>
                           <table border="0" cellpadding="0" cellspacing="0" width="100%">
                       </HeaderTemplate>
                       <ItemTemplate>
                           <tr>
                               <td>
                                   <%#Eval("NAME") %>
                               </td>
                           </tr>
                           <tr>
                               <td>
                                   $<%#Eval("PRICE") %>
                               </td>
                           </tr>
                           <tr>
                               <td>
                                   <asp:Image ID="imgproduct" ImageUrl='<%# Eval("PHOTO","~/photos/{0}") %>' Width="100" Height="100" runat="server" />
                               </td>
                           </tr>
                       </ItemTemplate>
                       <FooterTemplate>
                           </table>
                       </FooterTemplate>
                   </asp:DataList>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

Displaying the product name and price is almost similar to how we displayed the name of the category, the only difference is that in case of price, we added a “$” symbol in front of it, so that the price is displayed as $1000000 instead of simply 1000000.

In order to display the image of the product, we have used the following code:

1
<asp:Image ID="imgproduct" ImageUrl='<%# Eval("PHOTO","~/photos/{0}") %>' Width="100" Height="100" runat="server" />

It’s nothing but a normal asp.net Image control with its ID set as “imgproduct”, and width and height set as 100, which means 100 pixels. Now if you pay attention to the ImageUrl, we have used the Eval to bind with the dataset, like we used for other columns, but in case of Image control, the syntax is a bit different and that is <%# Eval(“PHOTO”,”~/photos/{0}”) %>. This is nothing too complicated. It only tells that the image should be binded with the “PHOTO” column of the dataset and “~/photos” is the virtual path of the photos folder in your application and “/{0}” is used to refer to the first element in that item which is always at index 0.

21. That’s it. Now if you run the code, you will be able to see the categories and their products (including all the information of the products i.e Name, Price and Picture) in a nested grid format as shown below:

DisplayProductsUnderCategories_NestedGrids1

22. If you want to display the category names vertically aligned to top and not in the center, you just need to set the “VerticalAlign” property of ItemStyle to “Top” for Categories column in the categories gridview (grdcategory), as shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<asp:GridView ID="grdcategory" DataKeyNames="ID" OnRowDataBound="grdcategory_RowDataBound" AutoGenerateColumns="false" runat="server">
        <Columns>
            <asp:TemplateField>
               <HeaderTemplate>
                   Categories
               </HeaderTemplate>
                <ItemTemplate>
                    <%#Eval("NAME") %>
                </ItemTemplate>
                <ItemStyle VerticalAlign="Top" />
            </asp:TemplateField>
            <asp:TemplateField>
               <HeaderTemplate>
                   Products
               </HeaderTemplate>
                <ItemTemplate>
                   <asp:DataList ID="dtlstproduct" RepeatColumns="5" RepeatDirection="Horizontal" runat="server">
                       <HeaderTemplate>
                           <table border="0" cellpadding="0" cellspacing="0" width="100%">
                       </HeaderTemplate>
                       <ItemTemplate>
                           <tr>
                               <td>
                                   <%#Eval("NAME") %>
                               </td>
                            </tr>
                           <tr>
                               <td>
                                   $<%#Eval("PRICE") %>
                               </td>
                            </tr>
                          <tr>
                               <td>
                                   <asp:Image ID="imgproduct" ImageUrl='<%# Eval("PHOTO","~/photos/{0}") %>' Width="100" Height="100" runat="server" />
                               </td>
                           </tr>
                       </ItemTemplate>
                       <FooterTemplate>
                           </table>
                       </FooterTemplate>
                   </asp:DataList>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

DisplayProductsUnderCategories_NestedGrids_Final2

So as you can see we have achieved our desired final result with categories and products displaying in nested grids.

Leave a Reply

Your email address will not be published. Required fields are marked *