Bind Repeater data control with Sql database in Asp.Net(C#, VB)

Introduction:

In this article we will learn How to bind data from SQL database table in Repeater data control in asp.net using both C# and Vb.Net languages (With Example). 

Implementation: Let's create a simple web application to understand the concept:

First create a Database in Sql server and name it "test". Create a table in Sql server and name it "EmDetails"

Create a connection to sql database using connection string as given below:

<connectionStrings>

                                <add name="EmpCon" connectionString="Data Source=localhost;Initial Catalog=test;Integrated Security=True"/>

                </connectionStrings>

Source Code:

  In the design page(.aspx) place a repeater control as:

<fieldset style="width:314px">

    <legend>Repeater Example</legend>

    <asp:Repeater ID="reptBook" runat="server">

        <HeaderTemplate>

   

        <table style=" border:1px solid #c1650f; width:300px" cellpadding="0">

<tr style="background-color:#F2f2f2; color:#000">

<td colspan="2">

<b><center>Employee Details</center></b>

</td>

</tr>

</HeaderTemplate>

<ItemTemplate>

<tr style="background-color:#EBEFF0">

<td>

<table style="background-color:#EBEFF0;border-top:1px dotted #c1650f; width:300px" >

<tr>

<td>

<b>Name:</b>

<asp:Label ID="lblBookName" runat="server" Text='<%#Eval("Name") %>'/>

</td>

</tr>

<tr>

<td>

<b>Address:</b>

<asp:Label ID="lblAuthor" runat="server" Text='<%#Eval("Address") %>'/>

</td>

</tr>

<tr>

<td>

<b>City:</b>

<asp:Label ID="lblPublisher" runat="server" Text='<%#Eval("City") %>'/>

</td>

</tr>

<tr>

</tr>

</table>

</td>

</tr>

</ItemTemplate>

<FooterTemplate>

</table>

</FooterTemplate>

        </asp:Repeater>

    </fieldset>

C#.NET Code :

Write this code in code behind file:

Include these namespaces :

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

then write the code as:

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindRepeater();

        }

    }

   

protected void BindRepeater()

    {

        SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString.ToString());

        SqlCommand cmd = new SqlCommand("Select * from EmDetails", con);

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        DataTable dt = new DataTable();

        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        adp.Fill(dt);

        reptBook.DataSource = dt;

        reptBook.DataBind();

        con.Close();

    }

VB.NET Code 

Import the following namespaces:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

then write the code as:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not Page.IsPostBack Then

            BindRepeater()

        End If

    End Sub

   

Protected Sub BindRepeater()

        Dim con As NewSqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString.ToString())

        Dim cmd As New SqlCommand("Select * from EmDetails", con)

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        Dim dt As New DataTable()

        Dim adp As New SqlDataAdapter(cmd)

        adp.Fill(dt)

        reptBook.DataSource = dt

        reptBook.DataBind()

        con.Close()

    End Sub