/// <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(); } }