CODEWITHSUNDEEP
c#sql-server
StateofDK
StateofDK

Reputation: 153

Too many sessions in table preventing new session from generating

I wrote simple function that generates a session variable on login and stores it in the Session table in a sql database. It works perfectly 99% of the time, however occasionally, it seems that the session table fills up or something, because the login page will redirect to the home page as if properly logged in, however no session will have been created and no user data will be present on any page in the web app.

Accessing the Session table directly through Sql Server Mgmt Studio and manually deleting all existing sessions under that username seems to correct the issue in the short-term, however I would like to know what causes the issue, and how I can alter my code to prevent it in the future.

any constructive pointers on how to improve my approach are also welcome

Thanks!

Here is my c#:

protected void CreateSession(object sender, EventArgs e)
        {
        //generate SessionId
        var AvailableCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyx1234567890";
        var random = new Random();
        var SessionId = new string(Enumerable.Repeat(AvailableCharacters, 20).Select(s => s[random.Next(s.Length)]).ToArray());

        String UserIdOut = null;

        Session["New"] = SessionId.ToString();

        string connstring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        using (SqlConnection conn = new SqlConnection(connstring))
        {
            //retreive user id
            try
            {
                SqlCommand cmd = new SqlCommand("spInsertSessionId", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = tb_username.Text;
                cmd.Parameters.Add("@createdate", SqlDbType.VarChar).Value = DateTime.Now.ToString();
                cmd.Parameters.Add("@SessionId", SqlDbType.VarChar).Value = SessionId.ToString();

                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                message = "error inserting session: " + ex.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + ex.Message + "');", true);
            }
        }
    }

Here is the stored procedure that inserts the session ID:

CREATE PROCEDURE [dbo].[spInsertSessionId]
    @username       VARCHAR(200)    =   NULL    ,
    @UserId         VARCHAR(200)    =   NULL    ,
    @createdate     VARCHAR(200)    =   NULL    ,
    @SessionId      VARCHAR(200)    =   NULL

AS
BEGIN
SELECT 
    @UserId     =   user_id
FROM dbo.users
    WHERE
        username    =   @username

INSERT INTO dbo.session
(
    UserId          ,
    CreateDate      ,
    SessionId
)
VALUES 
(
    @userid         ,
    @createdate     ,
    @SessionId
)

DELETE FROM dbo.session 
        WHERE UserId = @UserId
            AND SessionId NOT IN 
            (
            SELECT TOP(5) SessionId FROM dbo.session 
                WHERE UserId    =   @UserId
                ORDER BY CreateDate DESC
            )
END

Upvotes: 1

Views: 118

Answers (2)

Ivelin Ivanov
Ivelin Ivanov

Reputation: 168

try configuring your application to use <sessionState mode=SQLServer> and use aspnet_regsql.exe to configure your database tables, then use Session["key"] collection to store you data. Then the application will manage sessions itself. And if you aren't ok with that, you can always write your custom session provider.

Upvotes: 1

Evk
Evk

Reputation: 101533

Given provided info, my wild guess is that problem is in spInsertSessionId procedure. After inserting session, you delete "old" sessions, leaving only top 5 ordered by CreateDate DESC. However, you use varchar for CreateDate column, and strings are not guaranteed to be ordered the same way as dates, if you are not using datetime formats which have this property. In your case you just do DateTime.Now.ToString(), which even depends on your culture. So in some cases, session you just inserted might not be in top 5 ordered by your criteria, and hence deleted right after creation. Even if that is not the case - don't use varchar to store dates.

Sample:

 declare @d1 varchar(max) = '10/21/2016 00:00:00';
 declare @d2 varchar(max) = '10/22/2015 00:00:00';
 select (case when @d1 > @d2 then 1 else 0 end)

Here d1 is obviously later than d2, but query returns "0". Those dates are from DateTime.ToString() with invariant culture.

Upvotes: 2

Related Questions