How to Bind SQL Server Parameterized Stored Procedure (SP) Data into Asp.Net Repeater Control in C# VB.Net

To represent data in a tabular view more often we use Asp.Net Repeater Control. The Repeater Control is such a flexible control where we can bind data in lots of ways. Binding SQL Server Stored Procedure Data into the Repeater Control with SQL SP Parameter or without SQL SP Parameter is one of the most common way to represent data. Here in this article I will explain both way in C# & VB.Net.

Objective/Outcome:
Bind SQL Server Parameterized Stored Procedure Data into Asp.Net Repeater Control 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
) ON [PRIMARY]

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:

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

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.

Create a simple Stored Procedure without Parameter like:

CREATE PROCEDURE SP1
AS
BEGIN
SELECT * FROM tblStock
END

Create a simple Stored Procedure with Parameter like:

Create PROCEDURE [dbo].[SP2](@Param1 bigint=0,@Param2 bigint=0)
AS
BEGIN
SELECT * FROM tblStock WHERE PhysicalQuantity>@Param1 AND LogicalQuantity>@Param2 ORDER BY 1
END

Add below Namespaces:
C# Code:

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net Code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Now prepare the Asp.Net Repeater Control:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Repeater ID="Repeater1" runat="server">
    <HeaderTemplate>
        <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>
        </tr>
        </HeaderTemplate>
        <ItemTemplate>
        <tr>
            <td><%# Eval("BrandName")%></td>
            <td><%#Eval("CategoryName")%></td>
            <td><%#Eval("ProductName")%></td>
            <td><%#Eval("LogicalQuantity")%></td>
            <td><%#Eval("PhysicalQuantity")%></td>
            <td><%#Eval("QuarentineQuantity")%></td>
        </tr>
        </ItemTemplate>
        <AlternatingItemTemplate>
        <tr style="background:#99CCFF;">
            <td><%# Eval("BrandName")%></td>
            <td><%#Eval("CategoryName")%></td>
            <td><%#Eval("ProductName")%></td>
            <td><%#Eval("LogicalQuantity")%></td>
            <td><%#Eval("PhysicalQuantity")%></td>
            <td><%#Eval("QuarentineQuantity")%></td>
        </tr>
        </AlternatingItemTemplate>
        <FooterTemplate>
        </table>
        </FooterTemplate>
    </asp:Repeater><hr /><br /><br />
    <asp:Button ID="Button1" runat="server" Text="Bind SQL Server Stored Procedure Data Without Parameter" onclick="Button1_Click" /><br />
    <asp:Button ID="Button2" runat="server" Text="Bind SQL Server Stored Procedure Data With Parameter" onclick="Button2_Click" />
</asp:Content>

Now Bind SQL Server Stored Procedure Data into Repeater without SP Parameter:
C# Code:

    protected void Button1_Click(object sender, EventArgs e)
    {
        string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        SqlConnection Conn = new SqlConnection(sConstr);

        using (Conn)
        {
            SqlCommand command = new SqlCommand("SP1", Conn);
            command.CommandType = CommandType.StoredProcedure;
            Conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            Repeater1.DataSource = reader;
            Repeater1.DataBind();
        }
    }

VB.Net Code:

    Protected Sub Button1_Click(sender As Object, e As EventArgs)
        Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim Conn As New SqlConnection(sConstr)

        Using Conn
            Dim command As New SqlCommand("SP1", Conn)
            command.CommandType = CommandType.StoredProcedure
            Conn.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()

            Repeater1.DataSource = reader
            Repeater1.DataBind()
        End Using
    End Sub

Now Bind SQL Server Stored Procedure Data into Repeater with SP Parameter:

    protected void Button2_Click(object sender, EventArgs e)
    {
        string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        SqlConnection Conn = new SqlConnection(sConstr);

        using (Conn)
        {
            SqlCommand command = new SqlCommand("SP2", Conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@Param1", SqlDbType.BigInt));
            command.Parameters["@Param1"].Value = 8;
            command.Parameters.Add(new SqlParameter("@Param2", SqlDbType.BigInt));
            command.Parameters["@Param2"].Value = 20000;
            Conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            Repeater1.DataSource = reader;
            Repeater1.DataBind();
        }
    }

VB.Net Code:

    Protected Sub Button2_Click(sender As Object, e As EventArgs)
        Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim Conn As New SqlConnection(sConstr)

        Using Conn
            Dim command As New SqlCommand("SP2", Conn)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.Add(New SqlParameter("@Param1", SqlDbType.BigInt))
            command.Parameters("@Param1").Value = 8
            command.Parameters.Add(New SqlParameter("@Param2", SqlDbType.BigInt))
            command.Parameters("@Param2").Value = 20000
            Conn.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()

            Repeater1.DataSource = reader
            Repeater1.DataBind()
        End Using
    End Sub

Now run the project. Hope you can Bind Asp.Net Repeater Control with SQL Server Stored Procedure (SP) with SQL parameter or without SQL Parameter.

Download Code Example C#        Download Code Example VB.Net

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

Leave a Reply

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

     

*