How to Insert/Update Logout in DB

[muzikhera]

I have table name Logs with a field name user , login, logout.

Once the user log his/her name is being inserted to the db.

My question is how i can update/insert the date to the logout field of the current user whose using my application?

And can someone give some idea on how not to duplicate the user it will just update the login and logout?

Thanks in advance.
Skies the Limit:D

[samacoba]

Question one.Heres a suggestion you could play with,on the Page unload event,you could Do a DB insert,during which you would insert the user id and the current DateTime will be inserted into the DB,heres an example but im writing to a text file none the less its the same concept.

protected void Page_UnLoad(object sender, EventArgs e)
        {

            TextWriter txtFile = new StreamWriter(@"C:\txtlog.txt");
            txtFile.WriteLine("Logged Out : " + DateTime.Now.ToString());
            txtFile.Close();
        }

Question Two,i am not sure what you are trying to achieve, but according to my understanding of your question heres what i would do,i would create a procedure that grabs a userID(PK) when a user logs out,it would then check if a user exists on the logs table ,if a user does not exist i would insert a new record,if a user exists i would update the logout field using the userId(PK).

[Mitja Bonca]

But this is not a slolution he is looking for. He uses a DataBase, not a file.
I`ll try to do the code for you. Wait a bit...

EDIT: I would like to know which data type are column login and logout? Are type of DataTime? Or something else?
Its important, becuase if they are DateTime, the value inserted cannot be null (DateTime cannot be null, it always needs to have a value).

later

[Mitja Bonca]

One more thing: How many users can login to the aplication?
Only one, or more? This is very important.

Beucase if there will be more users logged in, things become a bit more complicated.

[Mitja Bonca]

Here is the code I did only for you. Hope it helps.
In case if yout app. is made to accect more then one user, this one will not work. Let me know, I will change to for you only!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;   //IN SOLUTION EXPLORER CLICK ON REFERENCES, AND CHOOSE ADD REFERENCE - CHOOSE SYSTEM.CONFIGURATION!

namespace Nov25Exercise
{
    public partial class Form1 : Form
    {
        string userName;
        List<LoggedUsers> listUsers;
        public Form1()
        {
            InitializeComponent();
            this.AcceptButton = this.buttonLogin;
            this.StartPosition = FormStartPosition.CenterScreen;
            listUsers = new List<LoggedUsers>();
        }

        protected override void OnShown(EventArgs e)
        {
            this.buttonLogout.Visible = false;
            base.OnShown(e);
        }

        private void buttonLogin_Click(object sender, EventArgs e)
        {
            string newUser = this.textBoxUserName.Text.Trim();
            if (!String.IsNullOrEmpty(newUser))
            {
                //check for the correct login!
                bool bCheckingLogin = DAL.CheckingUserExistance(newUser);
                if (bCheckingLogin)
                {
                    userName = newUser;
                    NewUserLogin(userName);
                    MessageBox.Show(userName + " has successfully logged in.");
                }
                else
                    MessageBox.Show("The user name does not exist in the database.\nPlease try to login again...");
                //setting the form to suit the needs:
                ChangingForm(bCheckingLogin);
            }
            else
            {
                MessageBox.Show("Please enter your user name.");
                this.textBoxUserName.Focus();
            }
        }

        private void buttonLogout_Click(object sender, EventArgs e)
        {
            DateTime logoutTime = DateTime.Now;
            DAL.UserLoginLogout(false, userName, new DateTime(1900, 1, 1), logoutTime);

            //erasing the user from the list:
            UsersLogout();
            //changing the form to starting state:
            ChangingForm(false);
        }

        private void NewUserLogin(string userName)
        {
            LoggedUsers logged = new LoggedUsers();
            logged.userName = userName;
            logged.loginTime = DateTime.Now;
            logged.logoutTime = new DateTime(1900, 1, 1);
            //when doing a login - logout will be set to 1.1. 1900
            //thats only because value cannot be null
            //and its easy to check if user has logout yet
            //if its 1900,1,1 he is still here - he didnt logout yet!             
            listUsers.Add(logged);    
            
            //Inserting login of the user to db:
            DAL.UserLoginLogout(true, userName, logged.loginTime, logged.logoutTime);
        }

        private void UsersLogout()
        {
            int i = 0;
            foreach (LoggedUsers user in listUsers)
            {
                string _userName = user.userName;
                if (_userName == userName)
                {
                    listUsers.RemoveAt(i);
                    MessageBox.Show(userName + " has successfully logged out.");
                    userName = null;
                    break;
                }
                i++;
            }
        }

        private void ChangingForm(bool bChecking)
        {
            if (bChecking)
            {
                this.textBoxUserName.Visible = false;
                this.buttonLogin.Visible = false;
                this.buttonLogout.Visible = true;
            }
            else
            {
                this.textBoxUserName.Visible = true;
                this.textBoxUserName.Text = null;
                this.buttonLogin.Visible = true;
                this.buttonLogout.Visible = false;
                this.textBoxUserName.Focus();
            }
        }       
    }

    public class DAL
    {
        private static string connString = ConfigurationManager.ConnectionStrings["NameOfConnectionString"].ConnectionString;

        public static void UserLoginLogout(bool bLogin, string name, DateTime loginTime, DateTime logoutTime)
        {
            using (SqlConnection sqlConn = new SqlConnection())
            {
                string strPresence = null;
                if (bLogin)
                    strPresence = String.Format(@"INSERT INTO Logs VALUES (@userName, @login, @logout)");
                else
                    strPresence = String.Format(@"UPDATE Logs SET Logout = @logout WHERE UserName = @userName)");
                using (SqlCommand cmd = new SqlCommand(/*strPresence, sqlConn*/))
                {
                    cmd.Parameters.Add("@userName", SqlDbType.VarChar, 50).Value = name;
                    cmd.Parameters.Add("@logout", SqlDbType.DateTime).Value = logoutTime;
                    if (bLogin)
                        cmd.Parameters.Add("@login", SqlDbType.DateTime).Value = loginTime;
                    cmd.Connection.Open();
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("An error has occured while login/logout of the user:\n" + ex.Message);
                    }
                    finally
                    {
                        cmd.Connection.Close();
                    }
                }
            }
        }

        public static bool CheckingUserExistance(string userName)
        {
            //METHOD WHICH CHECKS IF THE USER EXISTS IN THE DATABASE:
            return true;
        }
    }

    class LoggedUsers
    {
        public string userName { get; set; }
        public DateTime loginTime { get; set; }
        public DateTime logoutTime { get; set; }    
    }
}

best regards
Mitja

[samacoba]

Hi,i am sorry for the misunderstanding i was not offering a straight solution i was merely offering a concept from which one could work from,i was aware of the DB issue,which is why i specified that clearly when putting up the text file code.

[muzikhera]

Thats fine samacoba still thanks for your response.

And to Mitja im still working with your code hope you can still help me fixing this one.

Thanks and best regards.