Updating an Employee

This is the code to update an employee. First we added in the Login class so we could have the employee login. The we created an employee class to store the Data related to the employee. We created an EmployeeManager class to get and update employee objects. We also added some error trapping and an error page. First I will show the classes and then the forms and code behind

Here is the LoginClass.cs

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//the Ado and config libraries
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// 

/// Summary description for LoginClass
/// this class handles the logins
/// it connects to the database and
/// checks whether the username and
/// password(social Security number)
/// match and then returns a boolean
/// true if successful, false if not
/// 
public class LoginClass
{
    //private fields
    private string user;
    private string pass;
 
    //constructor gets username and password
        public LoginClass(string username, string password)
        {
            user=username;
            pass=password;
        }

        public bool ValidateLogin()
        {
            bool valid = false;
            //Initalize connection object
            //connection string in web config
            SqlConnection connect = 
                new SqlConnection
                    (ConfigurationManager.
                    ConnectionStrings["CommunityAssistConnection"].
                    ToString());

            //sql string to check if user exists
            string Sql = "SELECT LastName, SSNumber "
                            + "FROM Person "
                            + "Inner Join Employee "
                            + "On Person.PersonKey=Employee.PersonKey "
                            + " Where Lastname=@user "
                            + "And SSNumber=@pass";

            SqlCommand cmd = new SqlCommand(Sql, connect);
            //command object adds parameters to handle the variables
            //in the Sql string
            cmd.Parameters.AddWithValue("@user", user);
            cmd.Parameters.AddWithValue("@pass", pass);

            //a dataset is an object that can store tables
            DataSet ds = new DataSet();

            SqlDataReader reader = null;
            try //this is where the code can fail
            {
                connect.Open(); //open the connection
                reader = cmd.ExecuteReader(); //execute the SQL
                //load the data into the datset
                ds.Load(reader, System.Data.LoadOption.OverwriteChanges, "Validate");
                reader.Dispose(); //get rid of the reader
            }
            catch (Exception ex)
            {
                //if there are any errors throw it
                //back to the calling method
                throw ex;
            }
            finally
            {
                //no matter what close the connection
                connect.Close();
            }

            foreach (DataRow row in ds.Tables["Validate"].Rows)
            {
                //get the values from the dataset
                if (row["LastName"].ToString().Equals(user) 
                    && row["SSNumber"].ToString().Equals(pass))
                {
                    valid = true;
                    
                }
            }



            return valid;
        }
}

---

Employee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// 

/// Summary description for Employee
/// this class is just a way to hold
/// all of the employee values in one
/// place. It consists of nothing but
/// fields and properties
/// 
public class Employee
{
    string sSNumber;

    public string SSNumber
    {
        get { return sSNumber; }
       
    }

    string lastName;

    public string LastName
    {
        get { return lastName; }
        set { lastName = value; }
    }
    string firstName;

    public string FirstName
    {
        get { return firstName; }
        set { firstName = value; }
    }
    int dependents;

    public int Dependents
    {
        get { return dependents; }
        set { dependents = value; }
    }

    private int personKey;

    public int PersonKey
    {
        get { return personKey; }
        set { personKey = value; }
    }

 public Employee(string ssn)
 {
        sSNumber = ssn;
 }
}

---

ManageEmployee.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// 

/// Summary description for ManageEmployee
/// this class handles the employee class
/// in the method getEmployee it creates an 
/// employee object from the data in the database
/// In the UpdateEmployee method it takes 
/// an employee object as a parameter and 
/// updates the database with any changes
/// 
public class ManageEmployee
{
    private Employee emp;
    private SqlConnection connect;

 public ManageEmployee()
 {
        connect = 
            new SqlConnection
             (ConfigurationManager.ConnectionStrings["CommunityAssistConnection"].ToString());
       
 }

    public Employee GetEmployee(string ssn)
    {
        string sql = "Select LastName, FirstName, Dependents, e.PersonKey "
                        + " From Person p "
                        + "Inner Join Employee e "
                        + "On p.PersonKey=e.PersonKey "
                        + "where SSNumber=@ssn";

        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@ssn", ssn);
        SqlDataReader reader = null;
        //the datatable is another object that can sore data
        //it is a lighter object than the dataset
        DataTable table = new DataTable();
        //there should be a try catch around this
        connect.Open();
        reader = cmd.ExecuteReader();
        table.Load(reader);
        reader.Close();
        connect.Close();

        emp = new Employee(ssn);

        //this loop writes the data from the table
        //to a new employee object
        foreach (DataRow row in table.Rows)
        {
            int dependents = 0;
            emp.LastName = row["LastName"].ToString();
            emp.FirstName = row["FirstName"].ToString();

            bool isInt = int.TryParse(row["Dependents"].ToString(), out dependents);
            emp.Dependents = dependents;
            emp.PersonKey = int.Parse(row["PersonKey"].ToString());
        }

        return emp;
    } //end get employee

    public bool UpdateEmployee(Employee employee)
    {
        bool success = false;

        //must update two tables
        string sql = "Update Person "
                             + " Set FirstName=@first, "
                             + " LastName=@last"
                             + " Where personKey=@PersonKey";

        string sql2 = "Update Employee "
                        + " Set Dependents = @depends "
                        + " Where SSNumber=@ssn";

        //a command for each of the sql strings
        SqlCommand cmd = new SqlCommand(sql, connect);
        cmd.Parameters.AddWithValue("@last", employee.LastName);
        cmd.Parameters.AddWithValue("@first", employee.FirstName);
        cmd.Parameters.AddWithValue("@PersonKey", employee.PersonKey);

        
        SqlCommand cmd2 = new SqlCommand(sql2, connect);
        cmd2.Parameters.AddWithValue("@depends", employee.Dependents);
        cmd2.Parameters.AddWithValue("@ssn", employee.SSNumber);

        //this also should have a try catch
        connect.Open();
        cmd.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();
        connect.Close();

        success = true;
        return success;
    }


}//end class

---

The connection string is in the webconfig.xml file

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
 <connectionStrings>
  <add name="CommunityAssistConnection" 
connectionString="Data Source=localhost;initial catalog=CommunityAssist;integrated Security=true"/>
 </connectionStrings>
 <system.web>
  <compilation debug="true" targetFramework="4.0"/>
 </system.web>
</configuration>

---

Here is the Default.aspx source

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    
    <form id="form1" runat="server">
    <div>
    <asp:Login ID="Login1" runat="server" 
onauthenticate="Login1_Authenticate">
    </asp:Login>
    </div>
    </form>
</body>
</html>

---

Here is the code behind Default.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
    {
        //instantiate the login class 
        //and pass it the user and password
        LoginClass lc = new LoginClass(Login1.UserName, Login1.Password);

        bool isGood = false;
        try
        {
            //call the validatelogin method
            //it returns true or false
            isGood = lc.ValidateLogin();
        }
        catch (Exception ex)
        {
            //if there is an error write it to a session variable
            //and redirect to the error page
            Session["err"] = ex;
            Response.Redirect("Error.aspx");
        }

        if (isGood)
        {
            //if it is good set authenticated to true
            e.Authenticated = true;
            //write the ss number to a session variable
            Session["ss"] = Login1.Password;
            //redirect to the view edit page
            Response.Redirect("Default2.aspx");
        }
    }
}

---

Here is Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="First Name"></asp:Label>
        <asp:TextBox ID="txtFirstName"
            runat="server"></asp:TextBox><br />
            <asp:Label ID="Label2" runat="server" Text="Last Name"></asp:Label>
        <asp:TextBox ID="txtLastName"
            runat="server"></asp:TextBox><br />
            <asp:Label ID="Label3" runat="server" Text="Dependents"></asp:Label>
        <asp:TextBox ID="txtDependents"
            runat="server"></asp:TextBox><br />
        <asp:Button ID="Button1" runat="server" Text="Edit" onclick="Button1_Click" />
        <asp:Label ID="lblResult" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

---

Here is the code behind Default2.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    Employee employee;
    int personKey;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            if (Session["ss"] != null)
            {
                //create a new employee and write the values
                //to the text boxes
                ManageEmployee me = new ManageEmployee();
                employee = me.GetEmployee(Session["ss"].ToString());
                txtFirstName.Text = employee.FirstName;
                txtLastName.Text = employee.LastName;
                txtDependents.Text = employee.Dependents.ToString();
                personKey = employee.PersonKey;
                //write the personkey to a session variable
                Session["personkey"] = personKey;
            }
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //get the personkey from the session variable
        int PK = (int)Session["personkey"];
        //create new employee and assign the values from the form
        Employee employee = new Employee(Session["ss"].ToString());
        employee.LastName = txtLastName.Text;
        employee.FirstName = txtFirstName.Text;
        employee.Dependents = int.Parse(txtDependents.Text);
        employee.PersonKey = PK; //assign PK from session variable

        ManageEmployee me = new ManageEmployee();
        //call the UpdateEmployee method
        bool result=me.UpdateEmployee(employee);
        if (result)
            lblResult.Text = "Your changes were successful";
    }
}

---

Finally, here is the source for Error.aspx

<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="Error.aspx.cs" Inherits="Error" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

---

Error.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Error : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["err"] != null)
        {
            Exception ex = (Exception)Session["err"];
            Label1.Text = ex.Message;
        }

    }
}

---

That was a lot of code!