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

To represent data in a tabular view more often we use Asp.Net GridView Control. The GridView Control is such a flexible control where we can bind data in lots of ways. Binding SQL Server Stored Procedure Data into the GridView 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:
How to Bind SQL Server Parameterized Stored Procedure (SP) Data into Asp.Net GridView 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

Prepare the GridView HTML:

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5"></asp:GridView><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" />
</asp:Content>

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

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

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

Now Bind SQL Server Stored Procedure Data into GridView 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();
            
            GridView1.DataSource = reader;
            GridView1.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()

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

Now run the project. Hope you can Bind GridView 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#, Gridview, VB.Net

Leave a Reply

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

AlphaOmega Captcha Classica  –  Enter Security Code
     
 

*