Bind SQL Server Stored Procedure Data into DataList using Asp.Net C# VB.Net

To represent data in a tabular view more often we use Asp.Net DataList Control. The DataList Control is such a flexible control where we can bind data in lots of ways. Binding SQL Server Stored Procedure Data into the DataList 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 Stored Procedure Data into DataList using Asp.Net Csharp 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

Prepare the DataList HTML:

<asp:DataList id="DataList1" runat="server">
            <ItemTemplate>
                <b>Brand Name:</b> 
                <asp:Label id="lblBrandName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "BrandName") %>'></asp:Label><br />
                  
  
                <b>Category Name:</b> 
                <asp:Label id="lblCategoryName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "CategoryName") %>'></asp:Label><br />
                  
  
                <b>Product Name:</b>
                <asp:Label id="lblProductName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "ProductName") %>'></asp:Label><br />
            </ItemTemplate>
              
            <HeaderTemplate>
                <asp:Label id="lblHeader" runat="server" Font-Names="Tahoma" ><h1>List of Products:</h1></asp:Label>
                <hr />
            </HeaderTemplate>
            <FooterTemplate>
                <hr />
            </FooterTemplate>
            <SeparatorTemplate>
                <hr />
            </SeparatorTemplate>
        </asp:DataList><br /><br />
    <asp:Button ID="Button1" runat="server" 
        Text="Bind SQL Server Stored Procedure without Parameter" 
        onclick="Button1_Click" /><br />
    <asp:Button ID="Button2" runat="server" 
        Text="Bind SQL Server Stored Procedure with Parameter" 
        onclick="Button2_Click" />

Now Bind SQL Server Stored Procedure Data into DataList 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();

            DataList1.DataSource = reader;
            DataList1.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()

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

Now Bind SQL Server Stored Procedure Data into DataList with SP Parameter:
C# Code:

    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();

            DataList1.DataSource = reader;
            DataList1.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()

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

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

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code