C# .NET - re: How to retrieve memberID - Asked By Judy Lim on 12-Nov-11 10:08 PM

I need to retrieve memberID from the database. Can someone help me if the coding in bold can retrieve the memberID? Or i do not even need that coding at all?

try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                connection = new SqlConnection(connectionString);
                connection.Open();
                //prepare sql statements
                string sql = "SELECT * from Member where emailaddress='" + emailaddress + "' And Password='" + password + "'";
                Response.Write(sql);
                command = new SqlCommand(sql, connection);
                SqlDataAdapter da = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                da.Fill(dt);
                if (dt.Rows.Count > 0)
                {
                    /* add value to Session */

                    Session["emailaddress"] = emailaddress;
                    Session["MemberId"] = dt.Rows[0]["MemberID"].ToString();
                    validUser = true;


                    /* Logging Information to Logtable */
                    command = new SqlCommand("Insert into LoggedInUsers(EmailAddress,LoginTime) values(@EmailAddress,@LoginTime)", connection);
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@EmailAddress", emailaddress);
                    command.Parameters.AddWithValue("@LoginTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    command.ExecuteNonQuery();
             


                    dataReader = command.ExecuteReader();


                    Label1.Text = "Invalid email address or password.";
                    connection.Close();


                    //if (dataReader.Read())
                    //{
                    //save emailaddress in session
                    Response.Write("valid user:" + emailaddress);
                    String SQL = "SELECT MemberID FROM Member where emailaddress = @EmailAddress";
                    command.Parameters.AddWithValue("@EmailAddress", emailaddress);
                    validUser = true;



                    //}
                    //else
                    //{

                    //}
                    connection.Close();


                }
            }
            catch (Exception ex)
            {
                //Response.Write(ex.Message);
                Label1.Text = ex.Message.ToString();
            }
            if (validUser)
            {
                if (UpdateOnlineStatus(emailaddress) == true)
                {
                    Session.Add("emailaddress", emailaddress);
                }
                
            }
        }
Kirtan Patel replied to Judy Lim on 12-Nov-11 10:51 PM
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand comm = new SqlCommand("SELECT MemberID FROM Member where emailaddress = @EmailAddress",connection);
comm.Parameters.AddWithValue("@EmailAddress", emailaddress);
int MemberID = Convert.ToInt32(comm.ExecuteScalar());
connection.Close();
validUser = true;
Judy Lim replied to Kirtan Patel on 12-Nov-11 10:53 PM
thank you.
how do i save this memberID session?
Kirtan Patel replied to Judy Lim on 12-Nov-11 11:11 PM
Session["MemberID"] = MemberID;

and on other page you can retrieve the session value on another page like'

if(Session["MemberID"]!=null)
{
  int MemberID = Convert.ToInt32(Session["MemberID"]);
}
Judy Lim replied to Kirtan Patel on 12-Nov-11 11:21 PM
Thank you very much.
How do I pass the memberID session to global.asax?

protected void Session_End(object sender, EventArgs e)
        {
      
           string displayname = (string) Session["memberID"];
            UpdateLoggedInUsersLogoutTime(memberID);
            //Clear all session variables:
            Session.Abandon();
            Session.Clear();
        }


        protected void Application_End(object sender, EventArgs e)
        {
            //code that runs on application shutdown
        }   

protected Boolean UpdateLoggedInUsersLogoutTime(string displayname)
        {
            SqlConnection connection = null;
            SqlCommand command = null;
            Boolean successFlag = false;


            try
            {


                string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                connection = new SqlConnection(connectionString);
                connection.Open();


                //retrieve the logid 


                //update record based on logid


                //prepare sql statements
                string sql = "UPDATE  LoggedInUsers set LogoutTime=@LogoutTime where displayname=@DisplayName";
                //Response.Write(sql + "<br />");




                command = new SqlCommand(sql, connection);
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@LogoutTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                command.Parameters.AddWithValue("@DisplayName", displayname);


                int rowCount = command.ExecuteNonQuery();
                if (rowCount != 0)
                {
                    successFlag = true;
Kirtan Patel replied to Judy Lim on 12-Nov-11 11:25 PM
you can't access Session information in Global.asax.
As far as i know, the earliest stage where the session is available is HttpApplication's AcquireRequestState event,
you can access the session in this event's handler. When you are in BeginRequest, there is a long way to go. BeginRequest AuthenticateRequest AuthorizeRequest PostAuthorizeRequest ResolveRequestCache PostMapRequestHandler AcquireRequestState.

Thanks
Judy Lim replied to Kirtan Patel on 12-Nov-11 11:35 PM
Hi. Do i need to create a new connection string or can i "share" it with the connection string that i had created previously?
Judy Lim replied to Kirtan Patel on 12-Nov-11 11:46 PM
really? but i was told by my teacher to pass the session to global.asax.