Implement a session and retrieve from mysql table?

Question

How do I implement this session: (UserID is part of the login table)

Session["UserID"]="usrName";

Into this code?

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Odbc;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Login1.Authenticate += Login1_Authenticate;
    }
    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        //database connection string
        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=x; Password=x; OPTION=3;");
        cn.Open();
        OdbcCommand cmd = new OdbcCommand("Select * from User where username=? and password=?", cn);

        //Select the username and password from mysql database in login table

        cmd.Parameters.Add("@username", OdbcType.VarChar);
        cmd.Parameters["@username"].Value = this.Login1.UserName;

        cmd.Parameters.Add("@password", OdbcType.VarChar);
        cmd.Parameters["@password"].Value = this.Login1.Password;
        //use asp login control to check username and password
        cmd.Parameters.Add(new OdbcCommand("@UserID", 'id.int'));


        OdbcDataReader dr = default(OdbcDataReader);
        // Initialise a reader to read the rows from the login table.  
        // If row exists, the login is successful  

        dr = cmd.ExecuteReader();
        int id = cmd.Parameters["@UserID"].Value;
        Session["UserID"]="usrName";

        if (dr.Read())
        {
            e.Authenticated = true;
            Response.Redirect("UserProfileWall.aspx");
            // Event Authenticate is true forward to user profile
        }

    }
}

I need to be able to retrieve the correct UserID when some one inputs there username and password upon login and then some how retrieve it on a new page something like this to retireve it but unsure?

string usrName = Convert.ToString(Session["UserID"]);

I just dont know how to add the first part, the session into my login code so I can some how store the UserID in my session but also retrieve the correct UserID from the submitted data that takes from my mysql login table.

Answer 1

It's dirty, and wrong, but this would work in a pinch I guess.

    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)

    {
        var sql = @"SELECT * from Users where userName = ?user and password = ?pass";
        DataSet ds = new DataSet();

        MySqlCommand commandWithParams = new MySqlCommand(sql);

        commandWithParams.Parameters.AddWithValue("?user", Login1.UserName);
        commandWithParams.Parameters.AddWithValue("?pass", Login1.Password);

        MySqlConnection conn = new MySqlConnection("myconn string");

        if (conn.State != ConnectionState.Open)
            conn.Open();

        commandWithParams.Connection = conn;


        MySqlDataAdapter da = new MySqlDataAdapter(commandWithParams);

        da.Fill(ds);

        DataTable dt = ds.Tables[0];
        DataRow dr = dt.Rows[0];

        conn.Close();
        conn.Dispose();
        da.Dispose();

        if (dt.Rows.Count != 0)//I'm sure this has a better way
        {
            Session["userId"] = Convert.ToString(dr["userId"]);
            Session["userName"] = Convert.ToString(dr["userName"]);
            e.Authenticated = true;
            Reponse.Redirect("your_page.aspx");
        }
        else
        {
            e.Authenticated = false;
        }
    }

Here it is again using the Reader method and Odbc.

        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=x; Password=x; OPTION=3;");
        cn.Open();

        OdbcCommand cmd = new OdbcCommand("Select * from users where username=? and password=?", cn);

        cmd.Parameters.Add("@username", OdbcType.VarChar);
        cmd.Parameters["@username"].Value = "test";

        cmd.Parameters.Add("@password", OdbcType.VarChar);
        cmd.Parameters["@password"].Value = "test1";


        OdbcDataReader dr = default(OdbcDataReader);

        dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            string theUser = (string)dr["userName"];
            string theUserId = Convert.ToString(dr["userId"]);
        }

The you can set theUser and theUserId into session like so:

Session.Add("userName", theUser);
Session.Add("userId", theUserId);