Load Dataset from SQL Server stored procedure


This article is written by Pon Saravanan  on 15-Dec-10 Last modified on :15-Dec-10





Load data from Stored Procedure

Loading data from stored procedures is essential for data bound applications. Executing stored procedures and retrieving data is not that difficult. With the use of SQL Command and SQL Data Adapter we can fill either a dataset or a strongly typed dataset.

Setting up Sample Code

I use Northwind sample database provided by Microsoft for sample source code in this website. So to run this code you need to download it from MSDN. After downloading, there will not be any stored procedure which is explained in this sample. So I have added SQL server stored procedure script with this sample code. So run this script to get the stored procedure inside the Sample DB before running the sample code.

Load Data using a SQL Adapter

As I explained earlier we need a SQL Command and parameters to form the Command. Then we have to use the SQL Adapter to execute the prepared command. First we need to add the necessary parameter to the command. In our case it is Category ID. Based on this category ID the products will be returned. So the prepared command must have this parameter otherwise exception will be thrown.
Once the command is ready, we can specify this command as the select command for the SQL adapter. To load the data into the dataset from a stored procedure, we have to use the fill method and pass the Dataset. This fill method will execute the stored procedure specified in the command along with the parameters given in the command, and finally the result from the stored procedure will be populated to the dataset.

Screen Capture

Source Code

Markup(Aspx.Vb)

<%@ Page Language="VB" AutoEventWireup="false"
    CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Stored Procedure Output Parameter Sample</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Category ID:
        <asp:TextBox runat="server" ID="txtCategoryID"></asp:TextBox>
        <asp:Button runat="server" ID="Button1" Text="List Products" />
        <asp:GridView runat="server" ID="GridView1">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Code-Behind

Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Private ReadOnly Property ConnectionString() As String
        Get
            Return "Server=.;Database=NorthWind;" & _
                    "Trusted_Connection=True"
        End Get
    End Property
    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(ConnectionString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property
    Private Function GetData() As DataView
        Dim SelectProc = "ListProductsByCategoryID"
        Dim SampleSource As New DataSet
        Dim SourceView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = SelectProc
            SampleCommand.CommandType = CommandType.StoredProcedure
            SampleCommand.Connection = Connection
            Dim CategoryIDParam = New SqlParameter()
            CategoryIDParam.ParameterName = "@CategoryID"
            CategoryIDParam.Value = txtCategoryID.Text
            SampleCommand.Parameters.Add(CategoryIDParam)
            SampleDataAdapter.SelectCommand = SampleCommand
            SampleDataAdapter.Fill(SampleSource)
            SourceView = SampleSource.Tables(0).DefaultView
        Catch ex As Exception
            Throw ex
        End Try
        Return SourceView
    End Function
    Private Sub LoadGrid()
        GridView1.DataSource = GetData()
        GridView1.DataBind()
    End Sub
  
 
    Protected Sub Button1_Click(ByVal sender As Object, _
                     ByVal e As System.EventArgs) Handles Button1.Click
        LoadGrid()
    End Sub
End Class

SQL Script


Create PROCEDURE ListProductsByCategoryID
 @CategoryID int output
AS
BEGIN
 Select
  CategoryID, ProductID, ProductName
 from
  Products
 where
  CategoryID = @CategoryID
END
GO
/*---Test Script
  ---
DECLARE @CategoryID int
set @CategoryID =1
EXECUTE ListProductsByCategoryID
@CategoryID OUTPUT

*/




« Previous -







Comments
  • GUEST
    Very informative post. Its really helped me lot. Thanks for sharing with us. Check out this link too its also having wonderful explanation on stored procedure in sql server.

    http://mindstick.com/Articles/79be36ec-d4e6-4ef5-ae5d-c65fd7ede2b9/?Stored%20Procedure%20in%20SQL

    Thanks
    12/23/2011 2:16:52 AM


Comments
   
Captcha Image
For you specially:  
Captcha Text Enter the text in the image.(Not Case sensitive)    



Spam Bot Trap



   



Select Theme
White
Blue
Brown
Gray