Asp.Net Stored Procedure Parameters


This article is written by Pon Saravanan  on 05-Nov-10 Last modified on :05-Nov-10





Use Parameters in Stored procedures

Using stored procedures in asp.net application helps in various ways to run couple of SQL statements, and makes the applications to get the data by calling those procedures. When using stored procedures, the parameters have to be sent to while executing the stored procedures.

SqlParameter to pass the value

Since most of the samples I am working on are related to SQL Server, I need to use the SqlParameter. There are various ways to pass the values from asp.net (or win forms, WPF applications, win service, etc...) applications.  I prefer just create a new parameter by specifying its name and type while initializing and then pass the value using the value parameter.

Stored procedure

Stored procedures which are accepting the parameters have to specify its datatype and the length of the data. If you are expecting that there will be a possibility that the parameter may not be passed at all i.e. the optional parameter, this can be easily defined in the stored procedure by assigning the null while declaring the parameters.

Screen Capture


Source Code

Markup(*.aspx)

 

<%@ Page Language="VB" AutoEventWireup="false" CodeBehind="Default.aspx.vb"
    Inherits="StoredProcedureParameters._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 id="Head1" runat="server">
    <title>Stored Procedure Parameters Sample</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Category Name :
        <asp:TextBox runat="server" ID="txtCategoryName"></asp:TextBox>
        <asp:Button runat="server" ID="btnGo" Text="Go" /><br />
        <br />
        <asp:GridView runat="server" ID="GridView1">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

 

Code Behind(*.aspx.vb)


Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
    Inherits System.Web.UI.Page
    Private ReadOnly Property ConnectionString() As String
        Get
            Return "Server=.\SQLEXPRESS2008;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(ByVal CategoryName As String) As DataView
        Dim SampleSource As New DataSet
        Dim SourceView As DataView
        Try
            Dim SampleCommand As New SqlCommand()
            Dim SampleDataAdapter = New SqlDataAdapter()
            SampleCommand.CommandText = "ListCategoryByName"
            SampleCommand.CommandType = CommandType.StoredProcedure
            SampleCommand.Connection = Connection
            Dim NameParameter = New SqlParameter("CategoryName", SqlDbType.VarChar)
            NameParameter.Value = CategoryName
            SampleCommand.Parameters.Add(NameParameter)
            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(txtCategoryName.Text)
        GridView1.DataBind()
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        LoadGrid()
    End Sub
   
End Class


 

SQL Script(*.sql)


Use Northwind;
GO
IF  EXISTS (SELECT * FROM sys.objects
   WHERE object_id = OBJECT_ID(N'[dbo].[ListCategoryByName]')
   AND type in (N'P', N'PC'))
 DROP PROCEDURE [dbo].[ListCategoryByName]
GO
CREATE PROCEDURE ListCategoryByName
(@CategoryName as varchar(100)=null)
AS
 SELECT
  [CategoryID]
  ,[CategoryName]
  ,[Description]
  ,[Picture]
   FROM
  [dbo].[Categories]
   WHERE
  @CategoryName is null or CategoryName like '%' + @CategoryName + '%'
 
 
GO


 











Comments

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