sql select values to session state

Phil Critchley · February 27th, 2011, 09:05 AM

Hi Guys

Wondering if anyone has any experience of working with session states; specifically In Process mode sessions? I have values returned from an sql select statement that I want to store in a session state but can't figure out the correct syntax?

Everything I have found on the web relates to SQL session mode but can't find anything on what I want it to do?

Any help will be really appreciated.

Phil

Imar · February 27th, 2011, 02:24 PM

Hi there,

Just assign them to the Session object using a key:

Session["KeyName"] = yourObject;

http://msdn.microsoft.com/en-us/library/ms972429.aspx

Storing data in session state does not depend on the configured session mode; in all cases the code is the same.

Cheers,

Imar

Phil Critchley · February 27th, 2011, 02:34 PM

Hi Imar

Thanks for the response, my problem is that I don't have a defined object to store as the session variable. I want the returned SQL query values to be stored in the session, as highlighted in red below, unfortuantely everywhere I have looked has suggested this cannot be done?

It would be simple as you suggest in your example if I have object to take the values from such as textbox, etc.

Code:

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)

        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdString As String = "SELECT [UserID], [UserName], [Password], [RoleType] FROM [Users], [Roles] WHERE" & " (([UserName]=@UserName) AND ([Password] = @Password) AND (Users.RoleID = Roles.RoleID))"

        conn = New SqlConnection(ConfigurationManager.ConnectionStrings("LiteratureCataloguingConnectionString").ConnectionString)
        cmd = New SqlCommand(cmdString, conn)

        cmd.Parameters.Add("UserName", SqlDbType.NVarChar, 60)
        cmd.Parameters("UserName").Value = Login1.UserName
        cmd.Parameters.Add("Password", SqlDbType.NVarChar, 50)
        cmd.Parameters("Password").Value = Login1.Password

        conn.Open()

        Dim myReader As SqlDataReader
        myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Session("UserID") = Response.Write("[UserID]")        If myReader.Read() Then 
            AddToSession()
            FormsAuthentication.RedirectFromLoginPage(Login1.UserName, True)
        Else
            Response.Write("Invalid Credentials")
        End If

    End Sub

Imar · February 27th, 2011, 02:51 PM

A SqlDataReader is connected to the database, so you can't store that object in Session state directly.

So, you need to decide how you want the data stored. You could store simple values:

Session("SomeKey") = reader.GetString(reader.GetOrdinal("ColumnName"))

Alternatively, you could create a custom class to hold the data and store an instance of that class in Session. E.g.:'

Code:

 
Public Class Person
  Public Property Id As Integer
  Public Property Name As String
End Class

And then in code you can do something like this:

Code:

 
Dim myPerson As New Person()
myPerson.Id = reader.GetInt32(reader.GetOrdinal("SomeColumnWithAnInt"))
myPerson.Name = reader.GetString(reader.GetOrdinal("SomeColumnWithAString"))
Session("SomeKey") = myPerson

Hope tis helps,

Imar

Phil Critchley · March 1st, 2011, 11:55 AM

Hi Imar

Thanks for the post the other day it really helped point me in the right direction.

Does anyone have any experience with custom role association? My code below gets the username and role from my own sql database and stores the values in a session state. The problem I have with my code is that I don't think it is passing the string role value from my retrieved session state to the instance of IPrincipal I have incorporated into my code. The website I have sourced this from (shown below) states the role (indicated in red below) should be specified as an array group, but because I only have one role associated to a each user I have stored this in a string value.

I think the syntax I have used "({RoleType})" is incorrect for this? Does anyone have any experience of using the IPrincipal procedure and if so has anyone else used just one associated user role instead of an array of many?

http://www.4guysfromrolla.com/webtech/121901-1.2.shtml

Code:

Partial Class Login
    Inherits System.Web.UI.Page

    Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs)

        Dim myPerson As New Person
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdString As String = "SELECT UserID, UserName, Password, RoleType FROM Users, Roles WHERE Users.RoleID = Roles.RoleID AND UserName=@UserName AND Password = @Password"

        conn = New SqlConnection(ConfigurationManager.ConnectionStrings("LiteratureCataloguingConnectionString").ConnectionString)
        cmd = New SqlCommand(cmdString, conn)

        cmd.Parameters.Add("UserName", SqlDbType.NVarChar, 60)
        cmd.Parameters("UserName").Value = Login1.UserName
        cmd.Parameters.Add("Password", SqlDbType.NVarChar, 50)
        cmd.Parameters("Password").Value = Login1.Password

        conn.Open()

        Dim myReader As SqlDataReader = cmd.ExecuteReader()

        While myReader.Read
            myPerson.UserID = myReader.GetInt32(0)
            myPerson.UserName = myReader.GetString(1)
            myPerson.RoleType = myReader.GetString(3)
            System.Web.HttpContext.Current.Session("User") = myPerson
            AssociateRole()
            FormsAuthentication.RedirectFromLoginPage(Login1.UserName, True)
        End While

        myReader.Close()

    End Sub

    Protected Sub AssociateRole()

        Dim loginStatusPerson As Person = System.Web.HttpContext.Current.Session("User")
        Dim RoleType As String
        Dim UserName As String

        RoleType = loginStatusPerson.GetRoleType
        UserName = loginStatusPerson.GetUserName

        Dim objIdentity As GenericIdentity = New GenericIdentity(UserName)
        Dim objPrincipal As GenericPrincipal = New GenericPrincipal(objIdentity, ({RoleType}))        Thread.CurrentPrincipal = objPrincipal

    End Sub

End Class

Imar · March 2nd, 2011, 07:40 PM

Have yoy tried new[] { Roleype } ?

Imar