Retrive data from database using Stored procedure and class file in Asp.Net

Step1: Create SP in database

-- =============================================

-- Author: rajesh

-- Create date: 6th Nov 2008

-- Description: this SP is to find the keyword used to do search.

-- =============================================

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('JAU_SP_Getkeyword'))

DROP PROCEDURE JAU_SP_Getkeyword

GO

CREATE PROCEDURE JAU_SP_Getkeyword

(

@piMemberID INT

)

AS

BEGIN

select sTitle from JAU_Favouritesearch Group by sTitle

END

GO

Step2: call SP from class file

(1)à Create function in class file

ASP.net Code

'function made by rajesh for search

Public Function findkeyword() As DataSet

Dim objDBManager As New commonlib.Common.DBManager(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

Dim keywordresult As New DataSet

Dim sSQL As String = "JAU_SP_Getkeyword"

Dim p(0) As SqlParameter

Try

p(0) = New SqlParameter("@piMemberID", _iMemberID)

keywordresult = Objdbmanager.ExecuteDataSet(CommandType.StoredProcedure, sSQL, p)

Dim str As String = Objdbmanager.ErrorMessage

Return keywordresult

Catch ex As Exception

ex = Nothing

End Try

End Function

(2)à Create property in class file

Public Property memberID() As Integer

Get

Return _iMemberID

End Get

Set(ByVal value As Integer)

_iMemberID = value

End Set

End Property

(3)à Declare _MemberID as protected in class file

Protected _iMemberID As Integer

Step3: call function defined in class file in aspx file

Dim objsearch As New Search /*Search here is class file name*/

/* By Below code you can use the data form the database */

'code done by rajesh to find keyword

Dim dskeyword As DataSet

dskeyword = objsearch.findkeyword()

Dim keywordtitle As String = Trim(Request.QueryString("Text"))

For i As Integer = 0 To dskeyword.Tables(0).Rows.Count - 1

Dim str As String = Trim(dskeyword.Tables(0).Rows(i).Item(0))

If str = keywordtitle Then

linkfav.Visible = False

End If

Next

'

Hope this post will help you,
if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Indianic Infotech Ltd (India)
rajesh@indianic.com