[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.0

sessionState with MySQL and using Medium Trust

Asked by omegawkd in Programming for ASP.NET, MySQL Server, Microsoft Visual Basic.Net

Tags: VB.NET ASP.NET, ANY, None

Hello all.

I have an annoying problem which I need to solve with your help.

I searched high and low for a provider for sessionState using MySql. It turns out MSDN have one created for SQL server and using Harry Kimpel's ( http://kimpel.com/Blog.aspx ) adaption to of it to MySQL using C#.net I decided to port it to VB.net.

This took me a good day to work out the kinks for VB.net... especially with the DateTime bug.

Anyway, the problem at hand.
After testing mine today and putting it on to two seprate hosting/developement providers I came accross a bummer of an issue with both my code and Harry's ( And MSDN's )

On one server the code worked perfectly as it was a Full Trust server. Rare I know but thats what it is.
It didn't even hickup.

The catch is in a Medium trust ( any lower and this is stuffed ).

The code reads from the Web.Config file the sessionState data and places it into a SessionStateSection type in .NET. All well and good and does the trick only the following code will not work in Medium trust:

Dim cfg As System.Configuration.Configuration = _
      WebConfigurationManager.OpenWebConfiguration(ApplicationName)

pConfig = _
              CType(cfg.GetSection("system.web/sessionState"), SessionStateSection)

OpenWebConfiguration fires a IO exception.

Though, changing <those> lines to the following will bring one step closer to this working:

pConfig = WebConfigurationManager.GetSection("system.web/sessionState")

Will allow for the code to at least try and get the data.

What happens is it then fires an exception saying the section requires a requiresPermission="false" attribute. The issue there is that the sessionState container will not allow for that attribute, to the best of my knowledge and that is the problem I am having.

I tried adding a section sessionState tag with the attribute and eventually got it to load the connection strings ( at this point I jumped up with arms in the air shouting like Hero from Heroes ) but then realised that the MySQL connection wasn't allowed to the database. Bummer!
( Maybe this is the correct way but I was missing something ? )
As follows:
(Note: The Version and PublicKey were different in the one I got to work [ Newer ] )
<configSections>
      <section name="sessionState"
         type="System.Web.SessionState.SessionStateSectionHandler,
         System.Web, Version=1.0.3300.0, Culture=neutral,
         PublicKeyToken=b03f5f7f11d50a3a"
         allowDefinition="MachineToApplication"/>
 </configSections>

Simply, what I need is a MySQL sessionState provider that works in Medium trust.
The key to this issue I believe is the  WebConfigurationManager.GetSection() routine.

I have scoured the net for an iota of a solution for this issue but have come up with nothing.

My idea is that as  MSDN wrote a sample provider for SQL server that worked on the basis of WebConfigurationManager.OpenWebConfiguration surely they or someone has come accros this same issue with Medium trust and solved it. I would say that is likely since most servers are Medium trust.

So my thinking is if someone has a solution for SQL Server it could be easily ported to MySQL in the same manner as regardless of what server is used with this provider the outcome is still the same.

Sorry its so long winded. I want to get as much info out as possible.

This has been a kick in the teeth all day and night with me so far and would really like to get a handle for sorting this as it is very useful to me and many many others.

Thanks for your help :P

Class file named as : MySqlSessionStateStore.vb
MySQL DLL: mysql-connector-net-5.2.5-noinstall 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:
633:
634:
635:
636:
637:
638:
639:
640:
641:
642:
643:
644:
645:
646:
647:
648:
649:
650:
651:
652:
653:
654:
655:
656:
657:
658:
659:
660:
661:
662:
663:
664:
665:
666:
667:
668:
669:
670:
671:
672:
673:
674:
675:
676:
677:
678:
679:
680:
681:
682:
683:
684:
685:
686:
687:
688:
689:
690:
691:
692:
693:
694:
695:
696:
697:
698:
699:
700:
701:
702:
703:
704:
705:
706:
707:
708:
709:
710:
711:
712:
713:
714:
715:
716:
717:
718:
719:
720:
721:
722:
723:
724:
725:
726:
727:
728:
729:
730:
731:
732:
733:
734:
735:
736:
737:
738:
739:
740:
741:
742:
743:
744:
745:
746:
747:
748:
749:
750:
751:
752:
753:
754:
755:
756:
757:
758:
759:
760:
761:
762:
763:
764:
765:
766:
767:
768:
769:
770:
771:
772:
773:
774:
775:
776:
777:
778:
779:
780:
781:
782:
783:
784:
785:
786:
787:
788:
789:
790:
791:
792:
793:
794:
795:
796:
797:
798:
799:
800:
801:
802:
803:
804:
805:
806:
807:
808:
809:
810:
811:
812:
813:
814:
815:
816:
817:
818:
819:
820:
821:
822:
823:
824:
825:
826:

VB.NET CLASS FILE
-----------------
 
Imports System
Imports System.Web
Imports System.Web.Configuration
Imports System.Configuration
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System.Web.SessionState
Imports System.Data
Imports System.Diagnostics
Imports System.IO
Imports MySql.Data.MySqlClient
Imports Microsoft.VisualBasic
 
'   This session state store provider supports the following schema:
'
'
'   Requires at least the mysql-connector-net-5.2.5-noinstall, dll file in the bin folder
'   Note: This though may be only required for development
'
'   Ust this to create the table, the table name IS CASE SENSITIVE and so is the SQL statements !!!!!
' 
'       CREATE TABLE MySqlSessionStateStore
'       (
'           SessionId       VARCHAR(80)  NOT NULL,
'           ApplicationName VARCHAR(255) NOT NULL,
'           Created         DateTime  NOT NULL,
'           Expires         DateTime  NOT NULL,
'           LockDate        DateTime  NOT NULL,
'           LockId          Integer   NOT NULL,
'           Timeout         Integer   NOT NULL,
'           Locked          TINYINT UNSIGNED NOT NULL,
'           SessionItems    TEXT,
'           Flags           Integer   NOT NULL,
'           CONSTRAINT PKSessions PRIMARY KEY (SessionId, ApplicationName)
'       )
' 
'   This session state store provider does not automatically clean up 
'   expired session item data. It is recommended
'   that you periodically delete expired session information from the
'   data store with the following code (where 'conn' is the MySqlConnection
'   for the session state store provider):
' 
'   Dim commandString As String = "DELETE FROM Sessions WHERE Expires < ?"
'   Dim conn As MySqlConnection = New MySqlConnection(connectionString)
'   Dim cmd As MySqlCommand = New MySqlCommand(commandString, conn)
'   cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = DateTime.Now
'   conn.Open()
'   cmd.ExecuteNonQuery()
'   conn.Close()
'
'
'   In the Web.Config file the following is required:
'
'       < Make sure to change as required >
'
'   Add this to the connection strings section
'       <add name="MySqlSessionServices" connectionString="Database=db1007831_sessionstate;Data Source=mysql106.cp.blacknight.com;UserId=u1007831_test;Password=Password01"/>
'
'
'   Change the header on the configuration section to at least this
'       <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
'
'   Add this to the  configuration section 
'
'       <sessionState 
'           cookieless="false" 
'           regenerateExpiredSessionId="true" 
'           mode="Custom" 
'           customProvider="MySqlSessionProvider">
'
'           <providers>
'
'               <add name="MySqlSessionProvider" 
'                       type="Arthron.AspNet.Session.MySqlSessionStateStore" 
'                       connectionStringName="MySqlSessionServices" 
'                       writeExceptionsToEventLog="false"/>
'
'           </providers>
'       </sessionState>
 
 
Namespace Gigastence.AspNet.SQL
 
    Public NotInheritable Class MySqlSessionStateStore
        Inherits SessionStateStoreProviderBase
 
        Private pConfig As SessionStateSection = Nothing
        Private connectionString As String
 
 
 
        Private pConnectionStringSettings As ConnectionStringSettings
 
 
 
 
        Private eventSource As String = "MySqlSessionStateStore"
        Private eventLog As String = "Application"
        Private exceptionMessage As String = _
          "An exception occurred. Please contact your administrator."
        Private pApplicationName As String
 
 
        '
        ' If False, exceptions are thrown to the caller. If True,
        ' exceptions are written to the event log.
        '
 
        Private pWriteExceptionsToEventLog As Boolean = False
 
        Public Property WriteExceptionsToEventLog() As Boolean
            Get
                Return pWriteExceptionsToEventLog
            End Get
            Set(ByVal value As Boolean)
                pWriteExceptionsToEventLog = value
            End Set
        End Property
 
 
        '
        ' The ApplicationName property is used to differentiate sessions
        ' in the data source by application.
        '
 
        Public ReadOnly Property ApplicationName() As String
            Get
                Return pApplicationName
            End Get
        End Property
 
 
        '
        ' ProviderBase members
        '
 
 
        Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
 
            '
            ' Initialize values from web.config.
            '
 
            If config Is Nothing Then _
              Throw New ArgumentNullException("config")
 
            If name Is Nothing OrElse name.Length = 0 Then _
              name = "MySqlSessionStateStore"
 
            If String.IsNullOrEmpty(config("description")) Then
                config.Remove("description")
                config.Add("description", "Sample MySql Session State Store provider")
            End If
 
            ' Initialize the abstract base class.
            MyBase.Initialize(name, config)
 
 
            '
            ' Initialize the ApplicationName property.
            '
 
            pApplicationName = _
              System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
 
 
            '
            ' Get <sessionState> configuration element.
            '
 
            '   The below errors in medium trust levels on .NET
            '       Medium trust levels require the use of WebConfigurationManager.GetSection()
            Dim cfg As System.Configuration.Configuration = _
              WebConfigurationManager.OpenWebConfiguration(ApplicationName)
 
            pConfig = _
              CType(cfg.GetSection("system.web/sessionState"), SessionStateSection)
 
            '
            ' Initialize MySqlConnection.
            '
 
            pConnectionStringSettings = _
              ConfigurationManager.ConnectionStrings(config("connectionStringName"))
 
            If pConnectionStringSettings Is Nothing OrElse _
              pConnectionStringSettings.ConnectionString.Trim() = "" Then
 
                'Throw New HttpException("Connection string cannot be blank.")
                Throw New ProviderException("Connection string cannot be blank.")
            End If
 
            connectionString = pConnectionStringSettings.ConnectionString
 
 
            '
            ' Initialize WriteExceptionsToEventLog
            '
 
            pWriteExceptionsToEventLog = False
 
            If Not config("writeExceptionsToEventLog") Is Nothing Then
                If config("writeExceptionsToEventLog").ToUpper() = "TRUE" Then _
                  pWriteExceptionsToEventLog = True
            End If
        End Sub
 
 
        '
        ' SessionStateStoreProviderBase members
        '
 
        Public Overrides Sub Dispose()
 
        End Sub
 
 
        '
        ' SessionStateProviderBase.SetItemExpireCallback
        '
 
        Public Overrides Function SetItemExpireCallback( _
        ByVal expireCallback As SessionStateItemExpireCallback) As Boolean
 
            Return False
        End Function
 
 
        '
        ' SessionStateProviderBase.SetAndReleaseItemExclusive
        '
 
        Public Overrides Sub SetAndReleaseItemExclusive(ByVal context As HttpContext, _
        ByVal id As String, _
        ByVal item As SessionStateStoreData, _
        ByVal lockId As Object, _
        ByVal newItem As Boolean)
 
            ' Serialize the SessionStateItemCollection as a string.
            Dim sessItems As String = Serialize(CType(item.Items, SessionStateItemCollection))
 
 
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            Dim cmd As MySqlCommand
            Dim deleteCmd As MySqlCommand = Nothing
 
            If newItem Then
                ' MySqlCommand to clear an existing expired session if it exists.
                deleteCmd = New MySqlCommand("DELETE FROM MySqlSessionStateStore " & _
                    "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND Expires < ?Expires", conn)
                deleteCmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                deleteCmd.Parameters.Add( _
                  "?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
                deleteCmd.Parameters.Add( _
                "?Expires", MySqlDbType.DateTime).Value = DateTime.Now()
 
                ' MySqlCommand to insert the New session item.
                cmd = New MySqlCommand("INSERT INTO MySqlSessionStateStore " & _
                  " (SessionId, ApplicationName, Created, Expires, " & _
                  "  LockDate, LockId, Timeout, Locked, SessionItems, Flags) " & _
                  " Values(?SessionId, ?ApplicationName, ?Created, ?Expires, " & _
                  "  ?LockDate, ?LockId, ?Timeout, ?Locked, ?SessionItems, ?Flags)", conn)
                cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                cmd.Parameters.Add( _
                  "?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
                cmd.Parameters.Add( _
                  "?Created", MySqlDbType.DateTime).Value = DateTime.Now
                cmd.Parameters.Add( _
                  "?Expires", MySqlDbType.DateTime).Value = DateTime.Now.AddMinutes(CDbl(item.Timeout))
                cmd.Parameters.Add( _
                  "?LockDate", MySqlDbType.DateTime).Value = DateTime.Now
                cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = 0
                cmd.Parameters.Add( _
                  "?Timeout", MySqlDbType.Int32).Value = item.Timeout
                cmd.Parameters.Add("?Locked", MySqlDbType.Bit).Value = False
                cmd.Parameters.Add( _
                  "?SessionItems", MySqlDbType.VarChar, sessItems.Length).Value = sessItems
                cmd.Parameters.Add("?Flags", MySqlDbType.Int32).Value = 0
            Else
 
                ' MySqlCommand to update the existing session item.
                cmd = New MySqlCommand( _
                  "UPDATE MySqlSessionStateStore SET Expires = ?Expires, SessionItems = ?SessionItems, Locked = ?Locked  " & _
                  " WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND LockId = ?LockId", conn)
                cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = _
                  DateTime.Now.AddMinutes(CDbl(item.Timeout))
                cmd.Parameters.Add("?SessionItems", _
                  MySqlDbType.VarChar, sessItems.Length).Value = sessItems
                cmd.Parameters.Add("?Locked", MySqlDbType.Bit).Value = False
                cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                cmd.Parameters.Add( _
                  "?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
                cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = lockId
            End If
 
            Try
                conn.Open()
 
                If Not deleteCmd Is Nothing Then deleteCmd.ExecuteNonQuery()
 
                cmd.ExecuteNonQuery()
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "SetAndReleaseItemExclusive")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub
 
 
        '
        ' SessionStateProviderBase.GetItem
        '
 
        Public Overrides Function GetItem(ByVal context As HttpContext, _
        ByVal id As String, _
          ByRef locked As Boolean, _
          ByRef lockAge As TimeSpan, _
          ByRef lockId As Object, _
          ByRef actionFlags As SessionStateActions) _
          As SessionStateStoreData
 
            Return GetSessionStoreItem(False, context, id, locked, lockAge, lockId, actionFlags)
        End Function
 
 
        '
        ' SessionStateProviderBase.GetItemExclusive
        '
 
        Public Overrides Function GetItemExclusive(ByVal context As HttpContext, _
        ByVal id As String, _
          ByRef locked As Boolean, _
          ByRef lockAge As TimeSpan, _
          ByRef lockId As Object, _
          ByRef actionFlags As SessionStateActions) _
          As SessionStateStoreData
 
            Return GetSessionStoreItem(True, context, id, locked, lockAge, lockId, actionFlags)
        End Function
 
 
        '
        ' GetSessionStoreItem is called by both the GetItem and 
        ' GetItemExclusive methods. GetSessionStoreItem retrieves the 
        ' session data from the data source. If the lockRecord parameter
        ' is True (in the case of GetItemExclusive), then GetSessionStoreItem
        ' locks the record and sets a New LockId and LockDate.
        '
 
        Private Function GetSessionStoreItem(ByVal lockRecord As Boolean, _
        ByVal context As HttpContext, _
        ByVal id As String, _
          ByRef locked As Boolean, _
          ByRef lockAge As TimeSpan, _
          ByRef lockId As Object, _
          ByRef actionFlags As SessionStateActions) _
          As SessionStateStoreData
 
            ' Initial values for Return value and out parameters.
            Dim item As SessionStateStoreData = Nothing
            lockAge = TimeSpan.Zero
            lockId = Nothing
            locked = False
            actionFlags = 0
 
            ' Connection to MySQL database.
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            ' MySqlCommand for database commands.
            Dim cmd As MySqlCommand = Nothing
            ' DataReader to read database record.
            Dim reader As MySqlDataReader = Nothing
            ' DateTime to check if current session item is expired.
            Dim expires As DateTime
            ' String to hold serialized SessionStateItemCollection.
            Dim serializedItems As String = ""
            ' True if a record is found in the database.
            Dim foundRecord As Boolean = False
            ' True if the returned session item is expired and needs to be deleted.
            Dim deleteData As Boolean = False
            ' Timeout value from the data store.
            Dim timeout As Integer = 0
 
            Try
                conn.Open()
 
                ' lockRecord is True when called from GetItemExclusive and
                ' False when called from GetItem.
                ' Obtain a lock if possible. Ignore the record if it is expired.
                If lockRecord Then
                    cmd = New MySqlCommand("UPDATE MySqlSessionStateStore SET" & _
                      " Locked = ?Locked1, LockDate = ?LockDate " & _
                      " WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND Locked = ?Locked2 AND Expires > ?Expires", conn)
                    cmd.Parameters.Add("?Locked1", MySqlDbType.Bit).Value = True
                    cmd.Parameters.Add("?LockDate", MySqlDbType.DateTime).Value = _
                      DateTime.Now
                    cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                    cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
                      255).Value = ApplicationName
                    cmd.Parameters.Add("?Locked2", MySqlDbType.Int32).Value = False
                    cmd.Parameters.Add( _
                      "?Expires", MySqlDbType.DateTime).Value = DateTime.Now
 
                    If cmd.ExecuteNonQuery() = 0 Then
                        ' No record was updated because the record was locked or not found.
                        locked = True
                    Else
                        ' The record was updated.
                        locked = False
                    End If
                End If
 
                ' Retrieve the current session item information.
                cmd = New MySqlCommand("SELECT Expires, SessionItems, LockId, LockDate, Flags, Timeout " & _
                  "  FROM MySqlSessionStateStore " & _
                  "  WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName", conn)
                cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
                  255).Value = ApplicationName
 
                ' Retrieve session item data from the data source.
                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
 
                Do While reader.Read()
                    expires = reader.GetDateTime(0)
 
                    If expires < DateTime.Now Then
                        ' The record was expired. Mark it as not locked.
                        locked = False
                        ' The session was expired. Mark the data for deletion.
                        deleteData = True
                    Else
                        foundRecord = True
                    End If
 
                    serializedItems = reader.GetString(1)
                    lockId = reader.GetInt32(2)
                    lockAge = DateTime.Now.Subtract(reader.GetDateTime(3))
                    actionFlags = CType(reader.GetInt32(4), SessionStateActions)
                    timeout = reader.GetInt32(5)
                Loop
 
                reader.Close()
 
 
                ' If the returned session item is expired, 
                ' delete the record from the data source.
                If deleteData Then
                    cmd = New MySqlCommand("DELETE FROM MySqlSessionStateStore " & _
                      "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName", conn)
                    cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                    cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
                      255).Value = ApplicationName
 
                    cmd.ExecuteNonQuery()
                End If
 
                ' The record was not found. Ensure that locked is False.
                If Not foundRecord Then _
                  locked = False
 
                ' If the record was found and you obtained a lock, then set 
                ' the lockId, clear the actionFlags,
                ' and create the SessionStateStoreItem to return.
                If foundRecord AndAlso Not locked Then
                    lockId = CInt(lockId) + 1
 
                    cmd = New MySqlCommand("UPDATE MySqlSessionStateStore SET" & _
                      " LockId = ?LockId, Flags = 0 " & _
                      " WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName", conn)
                    cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = lockId
                    cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
                    cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
 
                    cmd.ExecuteNonQuery()
 
                    ' If the actionFlags parameter is not InitializeItem, 
                    ' deserialize the stored SessionStateItemCollection.
                    If actionFlags = SessionStateActions.InitializeItem Then
                        item = CreateNewStoreData(context, pConfig.Timeout.Minutes)
                    Else
                        item = Deserialize(context, serializedItems, timeout)
                    End If
                End If
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetSessionStoreItem")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try
 
            Return item
        End Function
 
 
 
 
        '
        ' Serialize is called by the SetAndReleaseItemExclusive method to 
        ' convert the SessionStateItemCollection into a Base64 string to    
        ' be stored in an Access Memo field.
        '
 
        Private Function Serialize(ByVal items As SessionStateItemCollection) As String
            Dim ms As MemoryStream = New MemoryStream()
            Dim writer As BinaryWriter = New BinaryWriter(ms)
 
            If Not items Is Nothing Then _
              items.Serialize(writer)
 
            writer.Close()
 
            Return Convert.ToBase64String(ms.ToArray())
        End Function
 
        '
        ' Deserialize is called by the GetSessionStoreItem method to 
        ' convert the Base64 string stored in the Access Memo field to a 
        ' SessionStateItemCollection.
        '
 
        Private Function Deserialize(ByVal context As HttpContext, _
        ByVal serializedItems As String, ByVal timeout As Integer) As SessionStateStoreData
 
            Dim ms As MemoryStream = _
              New MemoryStream(Convert.FromBase64String(serializedItems))
 
            Dim sessionItems As SessionStateItemCollection = _
              New SessionStateItemCollection()
 
            If ms.Length > 0 Then
                Dim reader As BinaryReader = New BinaryReader(ms)
                sessionItems = SessionStateItemCollection.Deserialize(reader)
            End If
 
            Return New SessionStateStoreData(sessionItems, _
              SessionStateUtility.GetSessionStaticObjects(context), _
              timeout)
        End Function
 
        '
        ' SessionStateProviderBase.ReleaseItemExclusive
        '
 
        Public Overrides Sub ReleaseItemExclusive(ByVal context As HttpContext, _
        ByVal id As String, _
        ByVal lockId As Object)
 
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            Dim cmd As MySqlCommand = _
              New MySqlCommand("UPDATE MySqlSessionStateStore SET Locked = 0, Expires = ?Expires " & _
              "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND LockId = ?LockId", conn)
            cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = _
              DateTime.Now.AddMinutes(pConfig.Timeout.Minutes)
            cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
            cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
              255).Value = ApplicationName
            cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = lockId
 
            Try
                conn.Open()
 
                cmd.ExecuteNonQuery()
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "ReleaseItemExclusive")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub
 
 
        '
        ' SessionStateProviderBase.RemoveItem
        '
 
        Public Overrides Sub RemoveItem(ByVal context As HttpContext, _
        ByVal id As String, _
        ByVal lockId As Object, _
        ByVal item As SessionStateStoreData)
 
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            Dim cmd As MySqlCommand = New MySqlCommand("DELETE * FROM MySqlSessionStateStore " & _
              "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName AND LockId = ?LockId", conn)
            cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
            cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
              255).Value = ApplicationName
            cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = lockId
 
            Try
                conn.Open()
 
                cmd.ExecuteNonQuery()
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "RemoveItem")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub
 
 
 
        '
        ' SessionStateProviderBase.CreateUninitializedItem
        '
 
        Public Overrides Sub CreateUninitializedItem(ByVal context As HttpContext, _
        ByVal id As String, _
        ByVal timeout As Integer)
 
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            Dim cmd As MySqlCommand = New MySqlCommand("INSERT INTO MySqlSessionStateStore " & _
              " (SessionId, ApplicationName, Created, Expires, " & _
              "  LockDate, LockId, Timeout, Locked, SessionItems, Flags) " & _
              " Values(?SessionId, ?ApplicationName, ?Created, ?Expires, " & _
              "  ?LockDate, ?LockId, ?Timeout, ?Locked, ?SessionItems, ?Flags)", conn)
            cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
            cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
              255).Value = ApplicationName
            cmd.Parameters.Add("?Created", MySqlDbType.DateTime).Value = _
              DateTime.Now
            cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = _
              DateTime.Now.AddMinutes(CDbl(timeout))
            cmd.Parameters.Add("?LockDate", MySqlDbType.DateTime).Value = _
              DateTime.Now
            cmd.Parameters.Add("?LockId", MySqlDbType.Int32).Value = 0
            cmd.Parameters.Add("?Timeout", MySqlDbType.Int32).Value = timeout
            cmd.Parameters.Add("?Locked", MySqlDbType.Bit).Value = False
            cmd.Parameters.Add("?SessionItems", MySqlDbType.VarChar, 0).Value = ""
            cmd.Parameters.Add("?Flags", MySqlDbType.Int32).Value = 1
 
            Try
                conn.Open()
 
                cmd.ExecuteNonQuery()
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "CreateUninitializedItem")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub
 
 
        '
        ' SessionStateProviderBase.CreateNewStoreData
        '
 
        Public Overrides Function CreateNewStoreData( _
        ByVal context As HttpContext, _
        ByVal timeout As Integer) As SessionStateStoreData
 
            Return New SessionStateStoreData(New SessionStateItemCollection(), _
              SessionStateUtility.GetSessionStaticObjects(context), _
              timeout)
        End Function
 
 
 
        '
        ' SessionStateProviderBase.ResetItemTimeout
        '
 
        Public Overrides Sub ResetItemTimeout(ByVal context As HttpContext, _
        ByVal id As String)
            Dim conn As MySqlConnection = New MySqlConnection(connectionString)
            Dim cmd As MySqlCommand = _
              New MySqlCommand("UPDATE MySqlSessionStateStore SET Expires = ?Expires " & _
              "WHERE SessionId = ?SessionId AND ApplicationName = ?ApplicationName", conn)
            cmd.Parameters.Add("?Expires", MySqlDbType.DateTime).Value = _
              DateTime.Now.AddMinutes(pConfig.Timeout.Minutes)
            cmd.Parameters.Add("?SessionId", MySqlDbType.VarChar, 80).Value = id
            cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, _
              255).Value = ApplicationName
 
            Try
                conn.Open()
 
                cmd.ExecuteNonQuery()
            Catch e As MySqlException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "ResetItemTimeout")
                    Throw New Exception(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub
 
 
        '
        ' SessionStateProviderBase.InitializeRequest
        '
 
        Public Overrides Sub InitializeRequest(ByVal context As HttpContext)
 
        End Sub
 
 
        '
        ' SessionStateProviderBase.EndRequest
        '
 
        Public Overrides Sub EndRequest(ByVal context As HttpContext)
 
        End Sub
 
 
        '
        ' WriteToEventLog
        ' This is a helper function that writes exception detail to the 
        ' event log. Exceptions are written to the event log as a security
        ' measure to ensure Private database details are not returned to 
        ' browser. If a method does not Return a status or Boolean
        ' indicating the action succeeded or failed, the caller also 
        ' throws a generic exception.
        '
 
        Private Sub WriteToEventLog(ByVal e As Exception, ByVal action As String)
            Dim log As EventLog = New EventLog()
            log.Source = eventSource
            log.Log = eventLog
 
            Dim message As String = _
              "An exception occurred communicating with the data source." & vbCrLf & vbCrLf
            message &= "Action: " & action & vbCrLf & vbCrLf
            message &= "Exception: " & e.ToString()
 
            log.WriteEntry(message)
        End Sub
    End Class
End Namespace
 
 
WEB.CONFIG FILE
---------------
<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
 
  <appSettings>
  </appSettings>
  
	<connectionStrings>
		<add name="MySqlSessionServices" connectionString="Database=databasename;Data Source=thathost;UserId=username;Password=password"/>
	</connectionStrings>
  
	<system.web>
     <!--
        <trust level="Medium"/>
     -->
    
    <customErrors mode="Off"/>
    
    <sessionState cookieless="false" regenerateExpiredSessionId="true" mode="Custom" customProvider="MySqlSessionProvider">
        <providers>
          <add name="MySqlSessionProvider"
               type="Gigastence.AspNet.SQL.MySqlSessionStateStore"
               connectionStringName="MySqlSessionServices"
               writeExceptionsToEventLog="false"
               timeout="30"
               />
        </providers>
		</sessionState>
    
		<!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
		<compilation debug="true"/>
		<!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
		<authentication mode="Windows"/>
		<!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
 
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
	</system.web>
</configuration>
[+][-]11/30/08 10:07 AM, ID: 23064098Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01/18/09 01:04 PM, ID: 23406500Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: Programming for ASP.NET, MySQL Server, Microsoft Visual Basic.Net
Tags: VB.NET ASP.NET, ANY, None
Sign Up Now!
Solution Provided By: EE_AutoDeleter
Participating Experts: 1
Solution Grade: A
 
 
Loading Advertisement...
20091021-EE-VQP-81 - Hierarchy / EE_QW_2_20070628