Calling a stored procedure from global.asax file

Question

Hello I had developed the below code to save the dataset in Global.asax file. To consume that in webpage.aspx.vb file, now it is working fine.

In the similar way, how could I call the stored procedure in Global.asax?
Please help, Thank You in Advance!

Shared _cache As Cache = Nothing

Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    _cache = Context.Cache
    RefreshCache(Nothing, Nothing, 0)
End Sub

Private Shared Sub RefreshCache(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)
    Dim adapter As New SqlDataAdapter("SELECT * FROM dbo.table where logid = 1", "server=server;database=database;uid=userid;pwd=password")

    Dim ds As New DataSet()
    adapter.Fill(ds, "Quotations")

    Dim onRemove As CacheItemRemovedCallback

    onRemove = New CacheItemRemovedCallback(AddressOf RefreshCache)

    _cache.Insert("Quotes", ds, New CacheDependency("C:\AspNetSql\Quotes.Quotations"), Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.[Default], onRemove)
End Sub

You would call it in the same way you would call it in any code behind file. What exactly are you having trouble with? — Oded, Jul 13 '12 at 12:29
I answered this yesterday... [read this](http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET) — bluevector, Jul 13 '12 at 12:32
I was understanding what you guys saying to me, but my global.asax file with vb.net code and it is not written in one class to inherit any other base classes, any way, Thank You Guys, I have resolved this with your suggestions. before, I was thinking too much to implement the below answer code now I got it. — Software Enginner, Jul 13 '12 at 15:22

score 0 · Answer 1 · answered Jul 13 '12 at 15:18

Got the solution with below code.

<%@ Application Language="VB" %>
<%@ Import Namespace="System.DirectoryServices.AccountManagement" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<%@ Import Namespace="System.Runtime.Remoting.Contexts" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Caching" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="System.Web.Caching.Cache" %>

<script RunAt="server">

    Shared _cache As Cache = Nothing
    Protected Shared db As Database

    Private cnPubs As SqlConnection
    Private daPubs As SqlDataAdapter
    Private cmdSelPubInfo As SqlCommand


    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)

        _cache = Context.Cache
        RefreshCache(Nothing, Nothing, 0)
    End Sub

    Public Sub New(ByVal databaseName As String)
        db = New Database(databaseName)
    End Sub

    Private Sub RefreshCache(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)

        cnPubs = New SqlConnection("server=Servername;database=databasename;uid=userid;pwd=password")
        'select command
        cmdSelPubInfo = New SqlCommand
        cmdSelPubInfo.Connection = cnPubs
        cmdSelPubInfo.CommandType = CommandType.StoredProcedure
        cmdSelPubInfo.CommandText = "storedprocedurename"

        cmdSelPubInfo.Parameters.Add(New SqlParameter("@pStartDate", "01/01/2011"))
        cmdSelPubInfo.Parameters.Add(New SqlParameter("@pEndDate", "01/01/2012"))
        cmdSelPubInfo.Parameters.Add(New SqlParameter("@pErrorMessage", ""))

        'DataApapter
        daPubs = New SqlDataAdapter
        daPubs.SelectCommand = cmdSelPubInfo
        Dim dsPubs = New DataSet

        'Dim ds As New DataSet()
        daPubs.Fill(dsPubs)

        Dim onRemove As CacheItemRemovedCallback

        onRemove = New CacheItemRemovedCallback(AddressOf RefreshCache)

        '' Add the DataSet to the application cache.
        _cache.Insert("Quotes", dsPubs, New CacheDependency("C:\AspNetSql\Quotes.Quotations"), Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.[Default], onRemove)
    End Sub
</script>

Calling a stored procedure from global.asax file

1 Answers1