SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
SqlCommand sqlcmd;
SqlDataAdapter da;
DataTable dt = new DataTable();

//Login button click event
protected void Button1_Click(object sender, EventArgs e)
{
        sqlcon.Open();      
        sqlcmd = new SqlCommand("select * from usr_detail where uid='" + txtUserID.Text + "' and pwd='" + txtpwd.Text + "'",sqlcon);
        dt.Clear();
        da = new SqlDataAdapter(sqlcmd);
        da.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            Session["LoginUserId"] = txtUserID.Text;
            //Set user name like this way
            Session["LoginUserName"] = dt.Rows[0]["UserName"].ToString(); //change exact field name here as per in your table
            Response.Redirect("Default2.aspx"); //Login success page
        }
        else {
            TextBox1.Text = "";
            TextBox2.Text = "";
            Label1.Text = "Invalid Login Details!";
        }
    }