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
  4. /****** Object: Table [dbo].[Product] Script Date: 31.07.2015 10:39:45 AM ******/
  5. SET ANSI_NULLS ON
  6. GO
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  11. SET ANSI_PADDING ON
  12. GO
  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]
  27. GO
  29. SET ANSI_PADDING OFF
  30. GO
  32. ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Product] FOREIGN KEY([ProductId])
  33. REFERENCES [dbo].[Product] ([ProductId])
  34. GO
  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>
  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>
  9. <asp:HiddenField ID="hdnText" runat="server" ClientIDMode="Static" Value="" />
  10. <%-- end --%>
  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" %>
  3. <!DOCTYPE html>
  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>
  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>
  24. <asp:HiddenField ID="hdnText" runat="server" ClientIDMode="Static" Value="" />
  25. <%-- end --%>
  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;
  11. public partial class ListVieLinkWithDB : System.Web.UI.Page
  12. {
  14. string connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
  15. protected void Page_Load(object sender, EventArgs e)
  16. {
  17. if (!IsPostBack) {
  18. ListViewControlBind("");
  19. }
  20. }
  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. }
  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);
  64. if (hdnText.Value != "")
  65. {
  66. string yourValue = hdnText.Value.ToString();
  67. if (yourValue == "Default")
  68. {
  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.