How to Bind Load Populate Asp.Net Repeater Control from SQL Server Database Table using SqlDataAdapter DataTable in C# VB.Net

The main usage of Repeater Control is to display a repeated list of items that are bound to the control. For tabular data representation Repeater Control is one of the most important data bound control. Repeater control is a template based control. You can define layout for the Repeater control by creating different templates based on your requirements. Here in this article I will explain “How to Bind Database Data into Repeater Control using SqlDataAdapter DataTable in C# VB.Net“.

Repeater Control Templates Description:
Repeater controls provides us 5 different templates to build rich user interface. These are as follows:

1. HeaderTemplate
2. ItemTemplate
3. AlternatingItemTemplate
4. FooterTemplate
5. SeparatorTemplate

ItemTemplate: Defines how each item is rendered from data source collection.

AlternatingItemTemplate: Defines how each alternate item is rendered from data source collection.

HeaderTemplate: Defines the style for Header Elements.

FooterTemplate: Defines the style for Footer Elements.

SeparatorTemplate: Defines separator for element which separates each Item in Item collection.

How to Bind Load Populate Asp.Net Repeater Control from SQL Server Database Table using SqlDataAdapter DataTable in C# VB.Net

To do the Example First Create a Database Table:

CREATE TABLE [dbo].[tblStock](
    [BrandName] [varchar](max) NULL,
    [CategoryName] [varchar](max) NULL,
    [ProductName] [varchar](max) NULL,
    [LogicalQuantity] [bigint] NULL,
    [PhysicalQuantity] [bigint] NULL,
    [QuarentineQuantity] [bigint] NULL

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now modify the Web.Config File to Connect to Database:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now Map the Repeater Control Bound Column to DataBase Table Column:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Repeater ID="Repeater1" runat="server">
        <table border="1px" cellpadding="5" cellspacing="0" style="text-align:center;border-style:solid;border-collapse:collapse;">
        <tr style="background:CornflowerBlue;Color:White;">
            <th>Brand Name</th>
            <th>Category Name</th>
            <th>Product Name</th>
            <th>Logical Qty</th>
            <th>Physical Qty</th>
            <th>Quarentine Qty</th>
            <td><%# Eval("BrandName")%></td>
        <tr style="background:#99CCFF;">
            <td><%# Eval("BrandName")%></td>

Now Load or Bind Data into Repeater Control using SqlDataReader:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            DataTable dt;
            String SQL = "SELECT * FROM tblStock";

            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(sConstr))
                using (SqlCommand comm = new SqlCommand(SQL, conn))
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                        dt = new DataTable("tbl");

            Repeater1.DataSource = dt;

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable
            Dim SQL As [String] = "SELECT * FROM tblStock"

            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Using conn As New SqlConnection(sConstr)
                Using comm As New SqlCommand(SQL, conn)
                    Using da As New SqlDataAdapter(comm)
                        dt = New DataTable("tbl")
                    End Using
                End Using
            End Using

            Repeater1.DataSource = dt
        End If
    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

Now run the project. hope you are getting Sql Server Database Table Data into your Repeater Control.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net,, C#, Repeater, VB.Net

Leave a Reply

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