/// <summary>
/// Gets pilot record from database for given Employee ssn.
/// </summary>
/// <param name="ssn">SSN</param>
/// <returns>Pilot Record</returns>
public PilotRecord getPilotData(string ssn)
{
string _cmdstr = " SELECT [empl_empno],[empl_ssn],[empl_fname],[empl_lname],[empl_minit],[empl_sex],[empl_dob]"
+ ",[empl_retire_dt],[empl_fulltime_dt],[empl_stat_dt],[empl_statcd],[addr_addr1]"
+ ",[addr_addr2],[addr_city],[addr_state],[addr_zip],[hraamt1],[hraamt2] "
+ " FROM [Employee],[Address],[HRA_Amounts] "
+ " WHERE (addr_empno = empl_empno) AND (empno = empl_empno) AND empl_ssn = @ssn AND addr_type = '001'";
PilotRecord pRec1 = new PilotRecord();
SqlCommand command = new SqlCommand(_cmdstr, conn);
command.Parameters.AddWithValue("@ssn", ssn);
SqlDataReader reader;
conn.Open();
using (conn)
{
reader = command.ExecuteReader();
while (reader.Read())
{
pRec1 = assignData(reader);
}
reader.Close();
}
conn.Close();
return(pRec1);
}
/// <summary>
/// Sets the pilot data from database to PilotRecord object.
/// </summary>
/// <param name="reader1">DataReader</param>
/// <returns>Pilot Record</returns>
protected PilotRecord assignData(SqlDataReader reader1)
{
PilotRecord pRec = new PilotRecord();
pRec.EmpNum = Convert.ToInt32(reader1["empl_empno"]);
pRec.SSN = reader1["empl_ssn"].ToString();
pRec.FirstName = reader1["empl_fname"].ToString();
pRec.LastName = reader1["empl_lname"].ToString();
pRec.MiddleInitial = reader1["empl_minit"].ToString();
pRec.Address1 = reader1["addr_addr1"].ToString();
pRec.Address2 = reader1["addr_addr2"].ToString();
pRec.State = reader1["addr_state"].ToString();
pRec.City = reader1["addr_city"].ToString();
pRec.Zip = reader1["addr_zip"].ToString();
pRec.DateBirth = Convert.ToDateTime(reader1["empl_dob"]);
pRec.SexCode = reader1["empl_sex"].ToString();
pRec.Status = reader1["empl_statcd"].ToString();
if (reader1["empl_retire_dt"] != DBNull.Value)
{
pRec.RetDate = Convert.ToDateTime(reader1["empl_retire_dt"]);
}
if (reader1["empl_fulltime_dt"] != DBNull.Value)
{
pRec.PermDate = Convert.ToDateTime(reader1["empl_fulltime_dt"]);
}
if (reader1["empl_stat_dt"] != DBNull.Value)
{
pRec.DeathDate = Convert.ToDateTime(reader1["empl_stat_dt"]);
}
if (reader1["hraamt1"] != DBNull.Value)
{
pRec.LumpSum = Convert.ToDecimal(reader1["hraamt1"]);
}
if (reader1["hraamt2"] != DBNull.Value)
{
pRec.HRAAmount = Convert.ToDecimal(reader1["hraamt2"]);
}
return(pRec);
}
public void updateEmployee(string atype, PilotRecord iRec)
{
int empno = iRec.EmpNum;
string ssn = iRec.SSN;
string fName = iRec.FirstName;
string lName = iRec.LastName;
string minit = iRec.MiddleInitial;
string sexCd = iRec.SexCode;
DateTime birthDate = iRec.DateBirth;
DateTime retDate = iRec.RetDate;
DateTime permDate = iRec.PermDate;
DateTime deathDate = iRec.DeathDate;
string status = iRec.Status;
string addr1 = iRec.Address1;
string addr2 = iRec.Address2;
string state = iRec.State;
string city = iRec.City;
string zip = iRec.Zip;
decimal amt1 = iRec.LumpSum;
decimal amt2 = iRec.HRAAmount;
string _cmdstr = "UPDATE Employee "
+ " SET [empl_ssn] = @ssn,[empl_fname] = @fname"
+ ",[empl_lname] = @lname,[empl_minit] = @minit,[empl_sex] = @sex,[empl_dob] = @dob "
+ ",[empl_retire_dt] = @retdt,[empl_fulltime_dt] = @permdt"
+ ",[empl_statcd] = @status,[empl_stat_dt] = @deathdt,[empl_row_eff_dt] = @roweffdt"
+ " WHERE [empl_empno] = @empno";
string _cmdstr1 = "UPDATE Address "
+ " SET [addr_type] = @atype,[addr_addr1] = @addr1,[addr_addr2] = @addr2"
+ ",[addr_city] = @city,[addr_state] = @state,[addr_zip] = @zip,[addr_row_eff_dt] = @roweffdt"
+ " WHERE [addr_empno] = @empno";
string _cmdstr2 = "UPDATE HRA_Amounts"
+ " SET [hraamt1] = @amt1,[hraamt2] = @amt2,[row_eff_dt] = @roweffdt"
+ " WHERE [empno] = @empno";
SqlCommand cmd = null;
SqlTransaction ts;
SqlDateTime sqldbnull;
conn.Open();
ts = conn.BeginTransaction();
try
{
cmd = new SqlCommand(_cmdstr, conn, ts);
cmd.Parameters.AddWithValue("@empno", empno);
cmd.Parameters.AddWithValue("@ssn", ssn);
cmd.Parameters.AddWithValue("@fname", fName);
cmd.Parameters.AddWithValue("@lname", lName);
if (!minit.Trim().Equals(""))
{
cmd.Parameters.AddWithValue("@minit", minit);
}
else
{
cmd.Parameters.AddWithValue("@minit", DBNull.Value);
}
cmd.Parameters.AddWithValue("@sex", sexCd);
if (birthDate.Equals(DateTime.MinValue))
{
cmd.Parameters.AddWithValue("@dob", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@dob", birthDate);
}
if (retDate.Equals(DateTime.MinValue))
{
cmd.Parameters.AddWithValue("@retdt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@retdt", retDate);
}
if (permDate.Equals(DateTime.MinValue))
{
cmd.Parameters.AddWithValue("@permdt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@permdt", permDate);
}
cmd.Parameters.AddWithValue("@status", status);
if (deathDate.Equals(DateTime.MinValue))
{
cmd.Parameters.AddWithValue("@deathdt", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@deathdt", deathDate);
}
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(_cmdstr1, conn, ts);
cmd.Parameters.AddWithValue("@empno", empno);
cmd.Parameters.AddWithValue("@atype", atype);
cmd.Parameters.AddWithValue("@addr1", addr1);
cmd.Parameters.AddWithValue("@addr2", addr2);
cmd.Parameters.AddWithValue("@city", city);
cmd.Parameters.AddWithValue("@state", state);
cmd.Parameters.AddWithValue("@zip", zip);
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(_cmdstr2, conn, ts);
cmd.Parameters.AddWithValue("@empno", empno);
cmd.Parameters.AddWithValue("@amt1", amt1);
cmd.Parameters.AddWithValue("@amt2", amt2);
cmd.Parameters.AddWithValue("@roweffdt", DateTime.Now);
cmd.ExecuteNonQuery();
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
throw (new Exception("Error in Updating Records"));
}
finally
{
conn.Close();
}
}