How to Get Data from SQL Server Stored Procedure Using Asp.Net C# VB.Net

Reading Sql Server data is a most common task in our lives. In this case i am considering the best ways To read Sql server stored procedure data using Asp.Net. I have no intention to write more on SP/asp.net code. I just want to show some easy steps which may reduce our most valuable time To retrieve data from Sql Server Stored Procedure (SP).

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.

First create a simple stored procedure like:

CREATE PROCEDURE SP1
AS
BEGIN
SELECT * FROM tblStock
END

Now we can retrieve data from SP using asp.net code:
C# Code:

    private void GetSP1()
    {
        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();

            while (reader.Read())
                Response.Write(reader.GetString(0) + "\t" + reader.GetString(1)+"<br/>");

            reader.Close();
        }
    }

VB.Net Code:

    Private Sub GetSP1()
        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()

            While reader.Read()
                Response.Write(reader.GetString(0) & vbTab & reader.GetString(1) & "<br/>")
            End While

            reader.Close()
        End Using
    End Sub

Now trying a little bit complex stored procedure. In this procedure we will try to retrieve record set by applying some filtering with some input parameters. For an example the SP like:

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

Note: Parameter=0 means if you not provide any value to the parameter then SQL Server will treat 0 as a default parameter.

To test this SP from Management Studio follow the below SQL:

EXEC SP2 @param2=10

Pass Parameters to Stored Procedure using Asp.Net:
C# Code:

    private void GetSP2()
    {
        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 = 10;
            Conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
                Response.Write(reader.GetString(0) + "\t" + reader.GetString(1)+"<br/>");

            reader.Close();
        }
    }

VB.Net Code:

    Private Sub GetSP2()
        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 = 10
            Conn.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()

            While reader.Read()
                Response.Write(reader.GetString(0) & vbTab & reader.GetString(1) & "<br/>")
            End While

            reader.Close()
        End Using
    End Sub

Now we can read records from SP with input parameters. But SQL Server provide us another option for parameter which is termed as OUTPUT parameter. Means we can send input parameter for filtering data within SP as well as we can send an output parameter to catch a value like “Call by ref” in Asp.Net or other languages. Sample SP to run the example is:

CREATE PROCEDURE SP3(@oParam3 VARCHAR(500) OUTPUT,@oParam4 bigint OUTPUT)
AS
BEGIN
SELECT @oParam3=BrandName,@oParam4=COUNT(*) FROM tblStock GROUP BY BrandName 
END

Note: Don’t forget to specify the size for VARCHAR. IF not then SP treats the size as 1.

To test the SQL use below query to Management Studio:

declare @oParam3 varchar(500)
declare @oParam4 bigint
EXEC SP3 @oParam3 OUTPUT,@oParam4 OUTPUT
print @oParam3

Get Stored Procedure Data with Output Parameter in Asp.Net:
C# Code:

    private void GetSP3()
    {
        string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        SqlConnection Conn = new SqlConnection(sConstr);

        using (Conn)
        {
            SqlCommand command = new SqlCommand("SP3", Conn);
            command.CommandType = CommandType.StoredProcedure;

            // MOST COMMON MISTAKE TO AVOID THE SIZE ARGUMENT
            SqlParameter BrandName = new SqlParameter("@oParam3", SqlDbType.VarChar, 500);
            BrandName.Direction = ParameterDirection.Output;
            command.Parameters.Add(BrandName);
            SqlParameter Counter = new SqlParameter("@oParam4", SqlDbType.BigInt);
            Counter.Direction = ParameterDirection.Output;
            command.Parameters.Add(Counter);

            Conn.Open();
            SqlDataReader reader = command.ExecuteReader();

            reader.Close();
            // TO READ OUTPUT PARAMETER VALUE KEEP IN MIND THAT YOU MUST USE AFTER READER CLOSED.
            Response.Write("One Brand : " + BrandName.Value + " Contains: " + Counter.Value.ToString() +" Product(s)");
        }
    }

VB.Net Code:

    Private Sub GetSP3()
        Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
        Dim Conn As New SqlConnection(sConstr)

        Using Conn
            Dim command As New SqlCommand("SP3", Conn)
            command.CommandType = CommandType.StoredProcedure

            ' MOST COMMON MISTAKE TO AVOID THE SIZE ARGUMENT
            Dim BrandName As New SqlParameter("@oParam3", SqlDbType.VarChar, 500)
            BrandName.Direction = ParameterDirection.Output
            command.Parameters.Add(BrandName)
            Dim Counter As New SqlParameter("@oParam4", SqlDbType.BigInt)
            Counter.Direction = ParameterDirection.Output
            command.Parameters.Add(Counter)

            Conn.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()

            reader.Close()
            ' TO READ OUTPUT PARAMETER VALUE KEEP IN MIND THAT YOU MUST USE AFTER READER CLOSED.
            Response.Write("One Brand : " + BrandName.Value & " Contains: " & Counter.Value.ToString() & " Product(s)")
        End Using
    End Sub

So now we have completed reading SP with input & output parameter. Now we try to improve our coding technique. Lets we have a stored procedure which has 100+ input/output parameters. To make life easier we can follow the below way:

private void ExecSP(string procName, string[] paramName, Object[] paramValue)
{
string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
SqlConnection Conn = new SqlConnection(sConstr);

SqlCommand command = new SqlCommand(procName, Conn);
command.CommandType = CommandType.StoredProcedure;

for (int i = 0; i < paramName.Length; i++)
command.Parameters.AddWithValue(paramName[i], paramValue[i]);

Conn.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
Response.Write(reader.GetString(0) + "\t" + reader.GetString(1) + "<BR/>");

reader.Close();
}

Call the ExecSP like:

ExecSP("SP2",new string[] { "@Param1","@Param2" }, new object[] { 8,10 });

For better re usability you can modify the above ExecSP by returnning a Ienumerable collection/datatable in your application data layer.

NOTE:
*Always wrap disposable objects like connections, commands and readers in using statements.
*This provides several advantages: cleaner code, exception safe and less code.
*To read large volume of data use sqldatareader instead of data dataadapter because its read only.
*Connection will be more efficient in using SQLDataAdapter class.

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