ASP.NET session state store provider for MySql 14


As I mentioned earlier here, my website ultimately is .NET ready .
Since I have quite a number of photos online that are off the record, I wanted to ‘protect’ them using forms authentication (within the former web hosting package developed in PHP I was using .htaccess and .htpassword files in order to protect complete directories).
So, from my professional experience, I know that saving data in session is feasible. However, the problem comes with a clustered environment that you usually run into in production environments. One possible solution now is to store session data in a database. Since the package I ordered at Easy CGI does not come with a MS SQL Server database (unless you add it as an additional feature to your package) you are limited to MS Access or MySql as your database management system … well, this actually isn’t that much of a choice since MS Access is not really suitable in a multi-user environment.
Looking on the web I was not able to find a suitable solution that uses MySql as its session state store. It may certainly be possible that there is already a better solution available (if so, please do not hesitate to contact me). However, I was able to find a sample session state store provider using MS Access. That’s it! Port it to MySql … and that’s what I did.
But, let’s do it step by step. The list below gives you an idea on what I did in order to get it to work:
  1. Downloaded the sample session state store provider for MS Access from MSDN.
  2. Downloaded the .NET Connector (using version 5.0.7) from MySql.
  3. Ported the sample code provided by Microsoft for use with MySql (the zipped source code can be found here).
  4. Adjusted the Web.config accordingly:

<configuration xmlns=http://schemas.microsoft.com/.NetConfiguration/v2.0>

<connectionStrings>

<add name=MySqlSessionServices connectionString=Database=<name of database>; Data Source=<host>; User Id=<login>; Password=<password>/>

 

</connectionStrings>

<system.web>

<sessionState cookieless=false regenerateExpiredSessionId=true mode=Custom customProvider=MySqlSessionProvider>

<providers>

<add name=MySqlSessionProvider type=Samples.AspNet.Session.MySqlSessionStateStore connectionStringName=MySqlSessionServices writeExceptionsToEventLog=false/>  

</providers>  

</sessionState>

</system.web>

 

 

 

</configuration>

 

You can check it out at www.kimpel.com … it works like a charm .

Please do not hesitate to contact me if you have any questions and/or comments.

 


Leave a comment

Your email address will not be published. Required fields are marked *

14 thoughts on “ASP.NET session state store provider for MySql

  • Harry

    Hi,
     
    I am just trying port the state store provider to oracle. As advised in the msdn site, I have placed the oraclesessionstoreprovider.cs in the App_Code folder. But when I try to run it I get an error
    The type ‘Ora.AspNet.Session.OracleSessionStateStore’ is ambiguous: it could come from assembly ‘C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Filesxd19b1f1c87ce17235App_Code.pfrkdhzl.DLL’ or from assembly ‘C:Inetpubwwwrootxdbinxdp.DLL’. Please specify the assembly explicitly in the type name
    Did the same problem occur in your case. If yes, how to resolve it.
    Clean and rebuild the solution, removing the file from C:Windows folder and recompiling does not seem to work ok.
    Any tips on this is welcome. Thanks .  

  • Harry

    Hi,
    not, that did not happen to me.
    Is there an oraclesessionstoreprovider.old file in the bin folder? Try removing it if it exists.
    Best,
    harry

  • Sam-kns

    Hi,
     
    Thankyou for the response. Initially I was trying to put it under the App_Code folder as suggested in there, but now removing that and trying to compile it as a separate dll and copying to my application bin folder resolves that issue. However, I get another error in the SetAndReleaseItemExclusive method when trying to execute the query using the ExecuteNonQuery()..
    System.Data.OleDb.OleDbException: ‘MSDAORA’ failed with no error message available .result code: E_FAIL(0x80004005)
    Any thoughts on what could this be due to..Thanks in advance.

  • Harry

    Hi,
     
    did you change the SQL statement? In Oracle the corresponding syntax should look similiar to this (having the variables specified just with a question mark):

    "INSERT INTO sessions (SessionId, ApplicationName, Created, Expires, LockDate, LockId, Timeout, Locked, SessionItems, Flags) Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    If this does not help, did you try to catch the exception in the coresponding catch-block (line 264) and get the error messag from there?
    Best,
    Harry

  • Sam-kns

    Hi,
     
    Yes, that was the problem. Thankyou very much for pointing that out as I am still a starter on this. Also, just wondering if you also periodically delete the expired session data using the query indicated in the msdn site. If yes, how and where do you do you clear it.
     
    Also, what general testing did you do ensure that the session managment indeed works completely fine. That should be helpful to me as well. Thanks for your help on this.

  • Harry

    … right now, I am not deleting expired session data. I just haven’t looked at it yet. This is obviously something you have to look at when dealing with a more frequented environment.As for testing, I used to debug the complete session management class. In addition to that I used the Microsoft Web Application Stress Tool to simulate some load on my test server.

  • Dilip

    I am doing Some changes in <sessionState><sessionState mode="Custom" cookieless ="true" regenerateExpiredSessionId="true" customProvider="OracleSessionStateStore">      <providers>        <add name="OracleSessionStateStore"          type="OracleSessionStateStore" />      </providers>    </sessionState>After this changes Method "GetItemExclusive" is called continuesly. So that i am not able to loged in to my site.Thanks,Waiting for your reply

  • joel

    Thanks for sharing. This is awesome. I did notice the following sql that was failing though. "DELETE * FROM sessions " + "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND LockId = ?LockId"Shouldn’t that be "DELETE FROM sessions " + "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND LockId = ?LockId"?I had to change it for MySql 5.

  • Ciprian

    Hey, thanks for this wrapper, I just wanted to add that you have an error here in the RemoveItem methodDELETE * FROM… should be DELETE FROMKeep it up

  • Unknown

    Thanks so much for the wonderful code! However, I am quite the beginner with asp.net! How do I implement this in code. I have included the class in my project, but evertime I attempt to debug, I just get a "Could not load type ‘Samples.AspNet.Session.MySqlSessionStateStore’". How do I include this file in my project???Thanks for any help!Dan

  • Harry

    Well, basically you just put the file in your "App_Code" folder and reference it in web.config (see above). You just have to be careful if you rename the namespace of the session state store provider. The namespace within the code and in web.config need to match.