Post
Ask Question

Bind Data in ListView Controls Using Database in ASP.Net

We will learn here how to bind controls with data with a database and how to handle paging on controls. The binding will be done depending on the link button text on the link button click event.

Also read

Initial Chamber

Step 1

Open your Visual Studio and create an empty website, then name it ListViewLinkWithDB.

Step 2

In Solution Explorer you will get your empty website, then add some web forms.

ListViewLinkWithDB (your empty website). Right-click and select Add New Item Web Form. Name it ListVieLinkWithDB.aspx.

For SQL Server database

Create a database in your SQL Server and name it ProductDB. (You can give your own name instead.)

Database Chamber

Step 3

In ProductDB create a table named Product.

Here's the query:

  1. USE [ProductDb]  
  2. GO  
  3.   
  4. /****** Object:  Table [dbo].[Product]    Script Date: 31.07.2015 10:39:45 AM ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7.   
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.   
  11. SET ANSI_PADDING ON  
  12. GO  
  13.   
  14. CREATE TABLE [dbo].[Product](  
  15.     [ProductId] [bigint] IDENTITY(1,1) NOT NULL,  
  16.     [CategoryId] [nchar](10) NULL,  
  17.     [ProductName] [varchar](50) NULL,  
  18.     [Qty] [int] NULL,  
  19.     [Price] [decimal](18, 2) NULL,  
  20.     [Description] [text] NULL,  
  21.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED   
  22. (  
  23.     [ProductId] ASC  
  24. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  25. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  26.   
  27. GO  
  28.   
  29. SET ANSI_PADDING OFF  
  30. GO  
  31.   
  32. ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Product] FOREIGN KEY([ProductId])  
  33. REFERENCES [dbo].[Product] ([ProductId])  
  34. GO  
  35.   
  36. ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Product]  
  37. GO  

The Table Product structure is here:

design view
Figure 1: Tbl_Data

Query execution:

tbl
Figure 2: tbl

Design Chamber

Step 4

Open the ListVieLinkWithDB.aspx file and write some code for the design of the application.

Step 4.1

Put stylesheet code in the head of the page like the following:

  1. <style type="text/css">  
  2.    .linkCat {  
  3.       background-color:aqua;  
  4.    }  
  5. </style>  

Set your style of the page depending on your design needs.

Step 4.2

Choose the control from the toolbox and make on your design page like the following:

  1. <div>  
  2.       <h3>ListView</h3>  
  3.   
  4.       <%-- Linkbutton --%>  
  5.       <asp:LinkButton ID="lnkCat1" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Cat1" OnClick="ENameLinkBtn_Click" CommandArgument="Cat1"></asp:LinkButton>  
  6.       <asp:LinkButton ID="lnkCat2" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Cat2" OnClick="ENameLinkBtn_Click" CommandArgument="Cat2"></asp:LinkButton>  
  7.       <asp:LinkButton ID="lnkDefault" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Default" OnClick="ENameLinkBtn_Click" CommandArgument="Cat2"></asp:LinkButton>  
  8.        
  9.       <asp:HiddenField ID="hdnText" runat="server" ClientIDMode="Static" Value="" />  
  10.       <%-- end --%>  
  11.   
  12.       <asp:ListView ID="lvCustomers" runat="server" GroupPlaceholderID="groupPlaceHolder1"  
  13.           ItemPlaceholderID="itemPlaceHolder1" OnPagePropertiesChanging="OnPagePropertiesChanging">  
  14.           <LayoutTemplate>  
  15.               <table border="1">  
  16.                   <tr>  
  17.                       <th>Product  
  18.                       </th>  
  19.                       <th>Quantity  
  20.                       </th>  
  21.                       <th>Price  
  22.                       </th>  
  23.                       <th>Category  
  24.                       </th>  
  25.                   </tr>  
  26.                   <asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>  
  27.                   <tr>  
  28.                       <td colspan="3">  
  29.                           <asp:DataPager ID="DataPager1" runat="server" PagedControlID="lvCustomers" PageSize="2">  
  30.                               <Fields>  
  31.                                   <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="false" ShowPreviousPageButton="true"  
  32.                                       ShowNextPageButton="false" />  
  33.                                   <asp:NumericPagerField ButtonType="Link" />  
  34.                                   <asp:NextPreviousPagerField ButtonType="Link" ShowNextPageButton="true" ShowLastPageButton="false" ShowPreviousPageButton="false" />  
  35.                               </Fields>  
  36.                           </asp:DataPager>  
  37.                       </td>  
  38.                   </tr>  
  39.               </table>  
  40.           </LayoutTemplate>  
  41.           <GroupTemplate>  
  42.               <tr>  
  43.                   <asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>  
  44.               </tr>  
  45.           </GroupTemplate>  
  46.           <ItemTemplate>  
  47.               <td>  
  48.                   <%# Eval("ProductName") %>  
  49.               </td>  
  50.               <td>  
  51.                   <%# Eval("Qty") %>  
  52.               </td>  
  53.               <td>  
  54.                   <%# Eval("Price") %>  
  55.               </td>  
  56.               <td>  
  57.                   <%# Eval("CategoryId") %>  
  58.               </td>  
  59.           </ItemTemplate>  
  60.       </asp:ListView>  
  61.   </div>  
Here I've designed the ListView Control and used some property as in the following.

Now the design page looks as in the following.

Design Page
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListVieLinkWithDB.aspx.cs" Inherits="ListVieLinkWithDB" %>  
  2.   
  3. <!DOCTYPE html>  
  4.   
  5. <html xmlns="http://www.w3.org/1999/xhtml">  
  6. <head runat="server">  
  7.     <title>ListView Using DataBase</title>  
  8.     <style type="text/css">  
  9.         .linkCat {  
  10.             background-color: aqua;  
  11.         }  
  12.     </style>  
  13. </head>  
  14. <body>  
  15.     <form id="form1" runat="server">  
  16.         <div>  
  17.             <h3>ListView</h3>  
  18.   
  19.             <%-- Linkbutton --%>  
  20.             <asp:LinkButton ID="lnkCat1" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Cat1" OnClick="ENameLinkBtn_Click" CommandArgument="Cat1"></asp:LinkButton>  
  21.             <asp:LinkButton ID="lnkCat2" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Cat2" OnClick="ENameLinkBtn_Click" CommandArgument="Cat2"></asp:LinkButton>  
  22.             <asp:LinkButton ID="lnkDefault" CssClass="linkCat" runat="server" ClientIDMode="Static" Text="Default" OnClick="ENameLinkBtn_Click" CommandArgument="Cat2"></asp:LinkButton>  
  23.              
  24.             <asp:HiddenField ID="hdnText" runat="server" ClientIDMode="Static" Value="" />  
  25.             <%-- end --%>  
  26.   
  27.             <asp:ListView ID="lvCustomers" runat="server" GroupPlaceholderID="groupPlaceHolder1"  
  28.                 ItemPlaceholderID="itemPlaceHolder1" OnPagePropertiesChanging="OnPagePropertiesChanging">  
  29.                 <LayoutTemplate>  
  30.                     <table border="1">  
  31.                         <tr>  
  32.                             <th>Product  
  33.                             </th>  
  34.                             <th>Quantity  
  35.                             </th>  
  36.                             <th>Price  
  37.                             </th>  
  38.                             <th>Category  
  39.                             </th>  
  40.                         </tr>  
  41.                         <asp:PlaceHolder runat="server" ID="groupPlaceHolder1"></asp:PlaceHolder>  
  42.                         <tr>  
  43.                             <td colspan="3">  
  44.                                 <asp:DataPager ID="DataPager1" runat="server" PagedControlID="lvCustomers" PageSize="2">  
  45.                                     <Fields>  
  46.                                         <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="false" ShowPreviousPageButton="true"  
  47.                                             ShowNextPageButton="false" />  
  48.                                         <asp:NumericPagerField ButtonType="Link" />  
  49.                                         <asp:NextPreviousPagerField ButtonType="Link" ShowNextPageButton="true" ShowLastPageButton="false" ShowPreviousPageButton="false" />  
  50.                                     </Fields>  
  51.                                 </asp:DataPager>  
  52.                             </td>  
  53.                         </tr>  
  54.                     </table>  
  55.                 </LayoutTemplate>  
  56.                 <GroupTemplate>  
  57.                     <tr>  
  58.                         <asp:PlaceHolder runat="server" ID="itemPlaceHolder1"></asp:PlaceHolder>  
  59.                     </tr>  
  60.                 </GroupTemplate>  
  61.                 <ItemTemplate>  
  62.                     <td>  
  63.                         <%# Eval("ProductName") %>  
  64.                     </td>  
  65.                     <td>  
  66.                         <%# Eval("Qty") %>  
  67.                     </td>  
  68.                     <td>  
  69.                         <%# Eval("Price") %>  
  70.                     </td>  
  71.                     <td>  
  72.                         <%# Eval("CategoryId") %>  
  73.                     </td>  
  74.                 </ItemTemplate>  
  75.             </asp:ListView>  
  76.         </div>  
  77.     </form>  
  78. </body>  
  79. </html>  
Your design looks as in Figure 3.

design
Figure 3: Design

Config Chamber

Add the following code to the Web.config.
  1. <connectionStrings>  
  2.    <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=ProductDb;" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>  
Code Chamber

Step 5

In the code chamber we will write some code so that our application works.

Adding the following namespaces to the namespace section of your code behind page: 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Configuration;  
Now your page will look the following.

Code behind page
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Configuration;  
  10.   
  11. public partial class ListVieLinkWithDB : System.Web.UI.Page  
  12. {  
  13.   
  14.     string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;  
  15.     protected void Page_Load(object sender, EventArgs e)  
  16.     {  
  17.         if (!IsPostBack) {  
  18.             ListViewControlBind("");  
  19.         }  
  20.     }  
  21.   
  22.   
  23.     protected void ListViewControlBind(string sGroup)  
  24.     {  
  25.         if (sGroup == "Cat1")  
  26.             sGroup = "1";  
  27.         if (sGroup == "Cat2")  
  28.             sGroup = "2";  
  29.         else  
  30.             sGroup = "";  
  31.         SqlConnection con = new SqlConnection(connStr);  
  32.         con.Open();  
  33.         string sQry = @"select ProductName,Qty,Price,CategoryId from dbo.Product";  
  34.         SqlCommand cmd = new SqlCommand(sQry, con);  
  35.         SqlDataAdapter da = new SqlDataAdapter(cmd);  
  36.         DataTable dt = new DataTable();  
  37.         DataSet ds = new DataSet();  
  38.         da.Fill(dt);  
  39.         con.Close();  
  40.         ds.Tables.Add(dt);  
  41.         if (sGroup != "")  
  42.         {  
  43.             var dv = ds.Tables[0].DefaultView;  
  44.             dv.RowFilter = "CategoryId='" + sGroup + "'";  
  45.             DataSet ds1 = new DataSet();  
  46.             var newdt = dv.ToTable();  
  47.             ds1.Tables.Add(newdt);  
  48.             //bind data to data controls  
  49.             lvCustomers.DataSource = ds1.Tables[0];  
  50.             lvCustomers.DataBind();  
  51.         }  
  52.         else  
  53.         {  
  54.             lvCustomers.DataSource = ds.Tables[0];  
  55.             lvCustomers.DataBind();  
  56.         }  
  57.   
  58.     }  
  59.     //paging code  
  60.     protected void OnPagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)  
  61.     {  
  62.         (lvCustomers.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, false);  
  63.   
  64.         if (hdnText.Value != "")  
  65.         {  
  66.             string yourValue = hdnText.Value.ToString();  
  67.             if (yourValue == "Default")  
  68.             {  
  69.   
  70.                 ListViewControlBind("");  
  71.             }  
  72.             else  
  73.             {  
  74.                 this.ListViewControlBind(yourValue);  
  75.             }  
  76.         }  
  77.         else  
  78.         {  
  79.             this.ListViewControlBind("");  
  80.         }  
  81.     }  
  82.     protected void ENameLinkBtn_Click(object sender, EventArgs e)  
  83.     {  
  84.         LinkButton btn = (LinkButton)(sender);  
  85.         string yourValue = btn.CommandArgument;  
  86.         // do what you need here  
  87.         if (yourValue == "Default")  
  88.         {  
  89.             hdnText.Value = yourValue;  
  90.             ListViewControlBind("");  
  91.         }  
  92.         else  
  93.         {  
  94.             hdnText.Value = yourValue;  
  95.             ListViewControlBind(yourValue);  
  96.         }  
  97.     }    
  98. }  
Output Chamber

On initial load:

On initial load
Figure 4: On Initial Load

On page click:

On page click
Figure 5: On page click

On second link click:

ListView
Figure 6: ListView

I hope you liked this. Have a good day. Thank you for reading.


Similar Articles