How store login and logout time into the database? Also store info on browser close.

Hi,

Basically your logic should do the following.

When a user logs in via your login form you insert a new record with the userID & login date time (datetime.now). Now when the user clicks the logout button you set the logout datetime.

However if the session ends you will have to a) figure out if the session is bound to a user, b) submit your logout logic. Now when the session ends you should know that the person actually left at around the time of datetime.now - session timeout. I know its not perfect math, but basically the way the session end gets fired is because a timeout happened since the user performed the last action. So although they may be sitting there reading the page you dont know as the web is stateles.

So (from what i can see) we can forget the session_start event. But in your login script add the insert statement to insert the date time they login (logout will have to be of a nullable type in your database). Also when they logout you will need an update statement to finish the logout.

Here is a mock example of your login script, you will have to enter your own parameters

SqlConnection con = new SqlConnection("connectionstring");
            SqlCommand com = new SqlCommand("insert into logintable (userID, login) values (@userid, @login)", con);
            com.CommandType  = System.Data.CommandType.Text;
            com.Parameters.AddWithValue("@userid", "member user name");
            com.Parameters.AddWithValue("@login", DateTime.Now);
            com.ExecuteNonQuery(); 

Here is a sample logout script, when the user clicks the logout button.

SqlConnection con = new SqlConnection("connectionstring");
SqlCommand com = new SqlCommand("update logintable set logout=@logout where userID=@userid and logout Is Null", con);
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@userid", "member user name");
com.Parameters.AddWithValue("@logout", DateTime.Now);
com.ExecuteNonQuery();

So as you see in the update sql command the update statement updates the logout column to datetime.now where the userid equals the current user name and logout is null. Meaning all rows that dont have a logout setting.

Next we have to tackle the session_end. So in your global.asax we can add the logic here such as. (very similar to the logout button)

void Session_End(object sender, EventArgs e)
{
DateTime logoutTime = DateTime.Now.AddMinutes(-1 * Session.Timeout);
SqlConnection con = new SqlConnection("connectionstring");
SqlCommand com = new SqlCommand("update logintable set logout=@logout where userID=@userid and logout Is Null", con);
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@userid", "member user name");
com.Parameters.AddWithValue("@logout", logoutTime);
com.ExecuteNonQuery();
}

Now there is one caveate to the above logic. We are assuming the user will be logging in and logging out via a single session. But what happens if i open two browsers and login to both and start navigating around the site. Well this will create 2 entries into my database. Now if i logout of one but let the session expire on the other our metrics are completly messed up as when they logout or the first session expires both rows will be updated to the first logout time.

To remedy this issue i would suggest adding a new column to your database called sessionID, when the person signs in the session id is also passed in, and when we logout (or session ends) we add a clause to our where statement to work with just the current session id.. Something like....

Login script

SqlConnection con = new SqlConnection("connectionstring");
SqlCommand com = new SqlCommand("insert into logintable (userID, login, sessionID) values (@userid, @login, @sessionID)", con);
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@userid", "member user name");
com.Parameters.AddWithValue("@login", DateTime.Now);
com.Parameters.AddWithValue("@sessionID", Session.SessionID);
com.ExecuteNonQuery();

Logout Script

SqlConnection con = new SqlConnection("connectionstring");
SqlCommand com = new SqlCommand("update logintable set logout=@logout where userID=@userid and logout Is Null and sessionID=@sessionID", con);
com.CommandType = System.Data.CommandType.Text;
com.Parameters.AddWithValue("@userid", "member user name");
com.Parameters.AddWithValue("@logout", DateTime.Now);
com.Parameters.AddWithValue("@sessionID", Session.SessionID);
com.ExecuteNonQuery();

Session End Script

SqlConnection con = new SqlConnection("connectionstring");
            SqlCommand com = new SqlCommand("update logintable set logout=@logout where userID=@userid and logout Is Null and sessionID=@sessionID", con);
            com.CommandType  = System.Data.CommandType.Text;
            com.Parameters.AddWithValue("@userid", "member user name");
            com.Parameters.AddWithValue("@logout", logoutTime);
            com.Parameters.AddWithValue("@sessionID", Session.SessionID);
            com.ExecuteNonQuery(); 

for more example check the below link
http://www.codeproject.com/Questions/263953/how-to-save-login-logout-time-in-database-using-as