ASP.NET Connection String

Question

I am having some trouble with one of my ASP.NET 2.0 application's connection string. There are instances that I would get a ConnectionString Property Has not Been Initialized problem which occurs randomly and intermittently and without any reason.

My Connection string is actually coming from a webservice, because different kinds of users are provided with different sets of connection string depending on their user level.

What I have done so far goes like this:

I have a master page (mstr.page) and the corresponding code behind (mstr.page.vb).

In my master page, I retrieve initially the connection string and store the same into a session variable, i.e.

Session("ConnString") = "RetrievedConnectionString"

Now in one of my pages, let us say page1.aspx.vb, I use public shared functions from a class (named MyClass.vb) and use it in my page1.aspx.vb.

Some codes for reference:

[MyClass.vb]
Imports System.Web

NameSpace ClassNameSpace
  Public Class Admin
    Protected Shared da as New DataAccess()

    Public Shared Function MYFunction() as String
      'Execute some sql statements here, using the DataAccess
      stringToReturn = Ctype(da.ExecuteScalar("SQLCommand"), String)
      Return stringToReturn
    End Function
  End Class
End NameSpace

[DataAccessClass.vb]
Public Class DataAccess()
  Private m_ConStr As String = ""

  Public Sub New()
    m_ConStr = HttpContext.Current.Session("ConnString")
  End Sub

  'Some methods for SQL statement execution (ExecuteQuery, ExecuteScalar)
End Class

[Page1.aspx.vb]
Imports ClassNameSpace

Protected Sub Page_Load(....) Handles Me.Load
  Dim strValue as String = Admin.MyFunction()
End Sub

I have placed the code above to show you some rough idea of how things are going.

Basically, the function Admin.MyFunction() at times fails, because in the data access class, the connection string seems to have lost it's value (either blank or Nothing).

This has troubled me for quite some time already.

I hope someone can point me in the right direction to resolve this. Basically, I want my connection string which is retrieved by each user visiting the web application be maintained across all the time and be used anywhere. Session variable does not seem to be the best fit since when the ASP.NET recycles its process, the session is lost.

By the way, I am retrieving the connectionstring initially via the master page from a web service. I tried to place the same retrieve function in the Data Access class when conditions is that the session variable is lost, but I think my application cannot connect to the Web Service during the recycle process.

Any advice would be greatly appreciated on this.

Update on this:

I tried to use Session Variable and set the mode to State Server, but apparently some DLLs which I am using cannot be serialized, thus I am back to square one.

Is there a better way to do this?

Answer 1

One thing to check is if your Session is getting clobbered. If your using the (default) in-memory Session, then sessions die anytime an ASP.NET worker process is recycled. If this is causing your issue, you might have to look into using the ASP.NET SessionServer in IIS, or SQL Server as your Session storage.

Answer 2

This is the exact same problem I was experiencing, and it turned out to be the session variables dying so the connection string couldn't be initialized properly.

Answer 3

Why cant your app use just one connection string? and why does the connection string need to come in through a webservice? that adds a huge amount of latency to the entire process. I assume it probably has to do something with data security in your database.

If you have to do this, I'd say, can't you have a fall back / default connectionstring? Wrap your code that attempts to pull it out of the session with some error handeling and if it fails revert to your default?

Answer 4

I would place your web service and web app in different app pools. Then increase the timeout length of your web apps pool.

Answer 5

I'd try to limit your use of the Session for this type of thing as much as possible. If you utilize the Web.Config for storing your connection strings, you can access it at anytime and it will not expire on you like the session.

You may also consider having a static data access class if it is the same for all users for the applications instance...