Calling Stored Procedure from ASP.NET page

Following is the function which I wrote to execute the stored procedure on SQL 2005 database. The stored procedure returns the result set which is parsed in the following code. Result set is count of number of records based on some criteria. In the end stored procedure returns the number of records to calling procedure. This function would provide you insight about how to make a stored procedure call and then parse the result set.

Please let me know if it helped. Thanks

 

Protected Function TiffMonitorGetTotalTransactionsfromDB() As Integer

        -- Declare connection variables. Getconnectionstring is a custom function to get connection string from web.config file.

        Dim sqlConnection1 As New SqlConnection(GetConnectionString())

        Dim cmd As New SqlCommand

        Dim NumberOfDatabaseRecords = 0

        Dim reader As SqlDataReader

        -- Set stored procedure name here

        cmd.CommandText = "XyzStoredProcedure"

        -- set parameters in stored procedure.

        cmd.Parameters.Add(New SqlParameter("@labeldate", Data.SqlDbType.DateTime, 10))

        -- here one of the SP parameter @labeldate is set to label value

        cmd.Parameters("@labeldate").Value = LabelDate.Text

        cmd.CommandType = CommandType.StoredProcedure

        cmd.Connection = sqlConnection1

        sqlConnection1.Open()

        -- Execute reader to read the result set

        reader = cmd.ExecuteReader()

        Do While reader.Read()

            NumberOfDatabaseRecords = reader(0)

        Loop

        sqlConnection1.Close()

        Return NumberOfDatabaseRecords

    End Function