public static string Generate_Code(string table) { SqlConnection connection = DAO_BASE.Get_Connection(); string column_code = table.Substring(4) + "Code"; string sql = " SELECT MAX(" + column_code + ")"; sql = sql + " FROM " + table; SqlCommand cmd = new SqlCommand(sql, connection); try { connection.Open(); string code = cmd.ExecuteScalar().ToString(); int number = int.Parse(code.Substring(2)) + 1; code = "000000" + number; code = code.Substring(code.Length - 6, 6); return(code); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Add(DIC_SALARY_RANK clsDIC_SALARY_RANK) { SqlConnection connection = DAO_BASE.Get_Connection(); string insertStatement = "INSERT " + " [DIC_SALARY_RANK] " + " ( " + " [RankCode] " + " ,[RankName] " + " ,[Description] " + " ) " + "VALUES " + " ( " + " @RankCode " + " ,@RankName " + " ,@Description " + " ) " + ""; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@RankCode", clsDIC_SALARY_RANK.RankCode); if (clsDIC_SALARY_RANK.RankName != null) { insertCommand.Parameters.AddWithValue("@RankName", clsDIC_SALARY_RANK.RankName); } else { insertCommand.Parameters.AddWithValue("@RankName", DBNull.Value); } if (clsDIC_SALARY_RANK.Description != null) { insertCommand.Parameters.AddWithValue("@Description", clsDIC_SALARY_RANK.Description); } else { insertCommand.Parameters.AddWithValue("@Description", DBNull.Value); } try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static DIC_DEPARTMENT Select_Record(DIC_DEPARTMENT clsDIC_DEPARTMENTPara) { DIC_DEPARTMENT clsDIC_DEPARTMENT = new DIC_DEPARTMENT(); SqlConnection connection = DAO_BASE.Get_Connection(); string selectStatement = "SELECT " + " [DepartmentCode] " + " ,[DepartmentName] " + " ,[Phone] " + " ,[Quantity] " + " ,[FactQuantity] " + " ,[Description] " + " ,[Active] " + "FROM " + " [DIC_DEPARTMENT] " + "WHERE " + " [DepartmentCode] = @DepartmentCode " + ""; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; selectCommand.Parameters.AddWithValue("@DepartmentCode", clsDIC_DEPARTMENTPara.DepartmentCode); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { clsDIC_DEPARTMENT.DepartmentCode = System.Convert.ToString(reader["DepartmentCode"]); clsDIC_DEPARTMENT.DepartmentName = reader["DepartmentName"] is DBNull ? null : reader["DepartmentName"].ToString(); clsDIC_DEPARTMENT.Phone = reader["Phone"] is DBNull ? null : reader["Phone"].ToString(); clsDIC_DEPARTMENT.Quantity = reader["Quantity"] is DBNull ? null : (Int32?)reader["Quantity"]; clsDIC_DEPARTMENT.FactQuantity = reader["FactQuantity"] is DBNull ? null : (Int32?)reader["FactQuantity"]; clsDIC_DEPARTMENT.Description = reader["Description"] is DBNull ? null : reader["Description"].ToString(); clsDIC_DEPARTMENT.Active = reader["Active"] is DBNull ? null : (Boolean?)reader["Active"]; } else { clsDIC_DEPARTMENT = null; } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(clsDIC_DEPARTMENT); }
public static bool Delete(DIC_SALARY_RANK clsDIC_SALARY_RANK) { SqlConnection connection = DAO_BASE.Get_Connection(); string deleteStatement = "DELETE FROM " + " [DIC_SALARY_RANK] " + "WHERE " + " [RankCode] = @OldRankCode " + " AND ((@OldRankName IS NULL AND [RankName] IS NULL) OR [RankName] = @OldRankName) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + ""; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldRankCode", clsDIC_SALARY_RANK.RankCode); if (clsDIC_SALARY_RANK.RankName != null) { deleteCommand.Parameters.AddWithValue("@OldRankName", clsDIC_SALARY_RANK.RankName); } else { deleteCommand.Parameters.AddWithValue("@OldRankName", DBNull.Value); } if (clsDIC_SALARY_RANK.Description != null) { deleteCommand.Parameters.AddWithValue("@OldDescription", clsDIC_SALARY_RANK.Description); } else { deleteCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static DIC_SALARY_STEP Select_Record(DIC_SALARY_STEP clsDIC_SALARY_STEPPara) { DIC_SALARY_STEP clsDIC_SALARY_STEP = new DIC_SALARY_STEP(); SqlConnection connection = DAO_BASE.Get_Connection(); string selectStatement = "SELECT " + " [StepCode] " + " ,[RankCode] " + " ,[StepName] " + " ,[Coefficient] " + " ,[Description] " + "FROM " + " [DIC_SALARY_STEP] " + "WHERE " + " [StepCode] = @StepCode " + " AND [RankCode] = @RankCode " + ""; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; selectCommand.Parameters.AddWithValue("@StepCode", clsDIC_SALARY_STEPPara.StepCode); selectCommand.Parameters.AddWithValue("@RankCode", clsDIC_SALARY_STEPPara.RankCode); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { clsDIC_SALARY_STEP.StepCode = System.Convert.ToInt32(reader["StepCode"]); clsDIC_SALARY_STEP.RankCode = System.Convert.ToString(reader["RankCode"]); clsDIC_SALARY_STEP.StepName = reader["StepName"] is DBNull ? null : reader["StepName"].ToString(); clsDIC_SALARY_STEP.Coefficient = reader["Coefficient"] is DBNull ? null : (Decimal?)(Double?)reader["Coefficient"]; clsDIC_SALARY_STEP.Description = reader["Description"] is DBNull ? null : reader["Description"].ToString(); } else { clsDIC_SALARY_STEP = null; } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(clsDIC_SALARY_STEP); }
public static DIC_RELIGION Select_Record(DIC_RELIGION clsDIC_RELIGIONPara) { DIC_RELIGION clsDIC_RELIGION = new DIC_RELIGION(); SqlConnection connection = DAO_BASE.Get_Connection(); string selectStatement = "SELECT " + " [ReligionCode] " + " ,[ReligionName] " + " ,[Description] " + " ,[Active] " + "FROM " + " [DIC_RELIGION] " + "WHERE " + " [ReligionCode] = @ReligionCode " + ""; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; selectCommand.Parameters.AddWithValue("@ReligionCode", clsDIC_RELIGIONPara.ReligionCode); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { clsDIC_RELIGION.ReligionCode = System.Convert.ToString(reader["ReligionCode"]); clsDIC_RELIGION.ReligionName = reader["ReligionName"] is DBNull ? null : reader["ReligionName"].ToString(); clsDIC_RELIGION.Description = reader["Description"] is DBNull ? null : reader["Description"].ToString(); clsDIC_RELIGION.Active = reader["Active"] is DBNull ? null : (Boolean?)reader["Active"]; } else { clsDIC_RELIGION = null; } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(clsDIC_RELIGION); }
public static void Update_Quanlity(string department_code) { string sql = ""; sql = sql + "update DIC_DEPARTMENT "; sql = sql + "set Quantity = (SELECT COUNT(*) FROM HRM_EMPLOYEE WHERE DepartmentCode=@DepartmentCode), "; sql = sql + "FactQuantity = (SELECT COUNT(*) FROM HRM_EMPLOYEE WHERE DepartmentCode=@DepartmentCode AND WorkStateCode IN('TT000001','TT000003')) "; sql = sql + "where DepartmentCode = @DepartmentCode "; SqlConnection connection = DAO_BASE.Get_Connection(); connection.Open(); SqlCommand updateCommand = new SqlCommand(sql, connection); updateCommand.Parameters.AddWithValue("@DepartmentCode", department_code); updateCommand.ExecuteNonQuery(); connection.Close(); }
public static bool Update(DIC_WORK_STATE oldDIC_WORK_STATE, DIC_WORK_STATE newDIC_WORK_STATE) { SqlConnection connection = DAO_BASE.Get_Connection(); string updateStatement = "UPDATE " + " [DIC_WORK_STATE] " + "SET " + " [WorkStateCode] = @NewWorkStateCode " + " ,[WorkStateName] = @NewWorkStateName " + "WHERE " + " [WorkStateCode] = @OldWorkStateCode " + " AND [WorkStateName] = @OldWorkStateName " + ""; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.CommandType = CommandType.Text; updateCommand.Parameters.AddWithValue("@NewWorkStateCode", newDIC_WORK_STATE.WorkStateCode); updateCommand.Parameters.AddWithValue("@NewWorkStateName", newDIC_WORK_STATE.WorkStateName); updateCommand.Parameters.AddWithValue("@OldWorkStateCode", oldDIC_WORK_STATE.WorkStateCode); updateCommand.Parameters.AddWithValue("@OldWorkStateName", oldDIC_WORK_STATE.WorkStateName); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Add(DIC_WORK_STATE clsDIC_WORK_STATE) { SqlConnection connection = DAO_BASE.Get_Connection(); string insertStatement = "INSERT " + " [DIC_WORK_STATE] " + " ( " + " [WorkStateCode] " + " ,[WorkStateName] " + " ) " + "VALUES " + " ( " + " @WorkStateCode " + " ,@WorkStateName " + " ) " + ""; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@WorkStateCode", clsDIC_WORK_STATE.WorkStateCode); insertCommand.Parameters.AddWithValue("@WorkStateName", clsDIC_WORK_STATE.WorkStateName); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static DIC_WORK_STATE Select_Record(DIC_WORK_STATE clsDIC_WORK_STATEPara) { DIC_WORK_STATE clsDIC_WORK_STATE = new DIC_WORK_STATE(); SqlConnection connection = DAO_BASE.Get_Connection(); string selectStatement = "SELECT " + " [WorkStateCode] " + " ,[WorkStateName] " + "FROM " + " [DIC_WORK_STATE] " + "WHERE " + " [WorkStateCode] = @WorkStateCode " + ""; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.CommandType = CommandType.Text; selectCommand.Parameters.AddWithValue("@WorkStateCode", clsDIC_WORK_STATEPara.WorkStateCode); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) { clsDIC_WORK_STATE.WorkStateCode = System.Convert.ToString(reader["WorkStateCode"]); clsDIC_WORK_STATE.WorkStateName = System.Convert.ToString(reader["WorkStateName"]); } else { clsDIC_WORK_STATE = null; } reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(clsDIC_WORK_STATE); }
protected static DataTable Select_Table(String sql) { SqlConnection connection = DAO_BASE.Get_Connection(); connection.Open(); try { SqlCommand cmd = new SqlCommand(sql, connection); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); return(dt); } catch (Exception ex) { throw ex; } finally { connection.Close(); } }
protected static bool Delete(String table_name, String column_code, String code) { SqlConnection connection = DAO_BASE.Get_Connection(); string deleteStatement = "UPDATE " + " [" + table_name + "] " + "SET " + " [ACTIVE] = 0 " + "WHERE [" + column_code + "] = @OldCode"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldCode", code); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Delete(DIC_WORK_STATE clsDIC_WORK_STATE) { SqlConnection connection = DAO_BASE.Get_Connection(); string deleteStatement = "DELETE FROM " + " [DIC_WORK_STATE] " + "WHERE " + " [WorkStateCode] = @OldWorkStateCode " + " AND [WorkStateName] = @OldWorkStateName " + ""; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldWorkStateCode", clsDIC_WORK_STATE.WorkStateCode); deleteCommand.Parameters.AddWithValue("@OldWorkStateName", clsDIC_WORK_STATE.WorkStateName); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Update(DIC_SALARY_RANK oldDIC_SALARY_RANK, DIC_SALARY_RANK newDIC_SALARY_RANK) { SqlConnection connection = DAO_BASE.Get_Connection(); string updateStatement = "UPDATE " + " [DIC_SALARY_RANK] " + "SET " + " [RankCode] = @NewRankCode " + " ,[RankName] = @NewRankName " + " ,[Description] = @NewDescription " + "WHERE " + " [RankCode] = @OldRankCode " + " AND ((@OldRankName IS NULL AND [RankName] IS NULL) OR [RankName] = @OldRankName) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + ""; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.CommandType = CommandType.Text; updateCommand.Parameters.AddWithValue("@NewRankCode", newDIC_SALARY_RANK.RankCode); if (newDIC_SALARY_RANK.RankName != null) { updateCommand.Parameters.AddWithValue("@NewRankName", newDIC_SALARY_RANK.RankName); } else { updateCommand.Parameters.AddWithValue("@NewRankName", DBNull.Value); } if (newDIC_SALARY_RANK.Description != null) { updateCommand.Parameters.AddWithValue("@NewDescription", newDIC_SALARY_RANK.Description); } else { updateCommand.Parameters.AddWithValue("@NewDescription", DBNull.Value); } updateCommand.Parameters.AddWithValue("@OldRankCode", oldDIC_SALARY_RANK.RankCode); if (oldDIC_SALARY_RANK.RankName != null) { updateCommand.Parameters.AddWithValue("@OldRankName", oldDIC_SALARY_RANK.RankName); } else { updateCommand.Parameters.AddWithValue("@OldRankName", DBNull.Value); } if (oldDIC_SALARY_RANK.Description != null) { updateCommand.Parameters.AddWithValue("@OldDescription", oldDIC_SALARY_RANK.Description); } else { updateCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Add(DIC_EDUCATION clsDIC_EDUCATION) { SqlConnection connection = DAO_BASE.Get_Connection(); string insertStatement = "INSERT " + " [DIC_EDUCATION] " + " ( " + " [EducationCode] " + " ,[EducationName] " + " ,[Description] " + " ,[Active] " + " ) " + "VALUES " + " ( " + " @EducationCode " + " ,@EducationName " + " ,@Description " + " ,@Active " + " ) " + ""; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@EducationCode", clsDIC_EDUCATION.EducationCode); if (clsDIC_EDUCATION.EducationName != null) { insertCommand.Parameters.AddWithValue("@EducationName", clsDIC_EDUCATION.EducationName); } else { insertCommand.Parameters.AddWithValue("@EducationName", DBNull.Value); } if (clsDIC_EDUCATION.Description != null) { insertCommand.Parameters.AddWithValue("@Description", clsDIC_EDUCATION.Description); } else { insertCommand.Parameters.AddWithValue("@Description", DBNull.Value); } if (clsDIC_EDUCATION.Active.HasValue == true) { insertCommand.Parameters.AddWithValue("@Active", clsDIC_EDUCATION.Active); } else { insertCommand.Parameters.AddWithValue("@Active", DBNull.Value); } try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Update(DIC_EDUCATION oldDIC_EDUCATION, DIC_EDUCATION newDIC_EDUCATION) { SqlConnection connection = DAO_BASE.Get_Connection(); string updateStatement = "UPDATE " + " [DIC_EDUCATION] " + "SET " + " [EducationCode] = @NewEducationCode " + " ,[EducationName] = @NewEducationName " + " ,[Description] = @NewDescription " + " ,[Active] = @NewActive " + "WHERE " + " [EducationCode] = @OldEducationCode " + " AND ((@OldEducationName IS NULL AND [EducationName] IS NULL) OR [EducationName] = @OldEducationName) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + " AND ((@OldActive IS NULL AND [Active] IS NULL) OR [Active] = @OldActive) " + ""; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.CommandType = CommandType.Text; updateCommand.Parameters.AddWithValue("@NewEducationCode", newDIC_EDUCATION.EducationCode); if (newDIC_EDUCATION.EducationName != null) { updateCommand.Parameters.AddWithValue("@NewEducationName", newDIC_EDUCATION.EducationName); } else { updateCommand.Parameters.AddWithValue("@NewEducationName", DBNull.Value); } if (newDIC_EDUCATION.Description != null) { updateCommand.Parameters.AddWithValue("@NewDescription", newDIC_EDUCATION.Description); } else { updateCommand.Parameters.AddWithValue("@NewDescription", DBNull.Value); } if (newDIC_EDUCATION.Active.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewActive", newDIC_EDUCATION.Active); } else { updateCommand.Parameters.AddWithValue("@NewActive", DBNull.Value); } updateCommand.Parameters.AddWithValue("@OldEducationCode", oldDIC_EDUCATION.EducationCode); if (oldDIC_EDUCATION.EducationName != null) { updateCommand.Parameters.AddWithValue("@OldEducationName", oldDIC_EDUCATION.EducationName); } else { updateCommand.Parameters.AddWithValue("@OldEducationName", DBNull.Value); } if (oldDIC_EDUCATION.Description != null) { updateCommand.Parameters.AddWithValue("@OldDescription", oldDIC_EDUCATION.Description); } else { updateCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } if (oldDIC_EDUCATION.Active.HasValue == true) { updateCommand.Parameters.AddWithValue("@OldActive", oldDIC_EDUCATION.Active); } else { updateCommand.Parameters.AddWithValue("@OldActive", DBNull.Value); } try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Add(DIC_DEPARTMENT clsDIC_DEPARTMENT) { SqlConnection connection = DAO_BASE.Get_Connection(); string insertStatement = "INSERT " + " [DIC_DEPARTMENT] " + " ( " + " [DepartmentCode] " + " ,[DepartmentName] " + " ,[Phone] " + " ,[Quantity] " + " ,[FactQuantity] " + " ,[Description] " + " ,[Active] " + " ) " + "VALUES " + " ( " + " @DepartmentCode " + " ,@DepartmentName " + " ,@Phone " + " ,@Quantity " + " ,@FactQuantity " + " ,@Description " + " ,@Active " + " ) " + ""; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.CommandType = CommandType.Text; insertCommand.Parameters.AddWithValue("@DepartmentCode", clsDIC_DEPARTMENT.DepartmentCode); if (clsDIC_DEPARTMENT.DepartmentName != null) { insertCommand.Parameters.AddWithValue("@DepartmentName", clsDIC_DEPARTMENT.DepartmentName); } else { insertCommand.Parameters.AddWithValue("@DepartmentName", DBNull.Value); } if (clsDIC_DEPARTMENT.Phone != null) { insertCommand.Parameters.AddWithValue("@Phone", clsDIC_DEPARTMENT.Phone); } else { insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value); } if (clsDIC_DEPARTMENT.Quantity.HasValue == true) { insertCommand.Parameters.AddWithValue("@Quantity", clsDIC_DEPARTMENT.Quantity); } else { insertCommand.Parameters.AddWithValue("@Quantity", DBNull.Value); } if (clsDIC_DEPARTMENT.FactQuantity.HasValue == true) { insertCommand.Parameters.AddWithValue("@FactQuantity", clsDIC_DEPARTMENT.FactQuantity); } else { insertCommand.Parameters.AddWithValue("@FactQuantity", DBNull.Value); } if (clsDIC_DEPARTMENT.Description != null) { insertCommand.Parameters.AddWithValue("@Description", clsDIC_DEPARTMENT.Description); } else { insertCommand.Parameters.AddWithValue("@Description", DBNull.Value); } if (clsDIC_DEPARTMENT.Active.HasValue == true) { insertCommand.Parameters.AddWithValue("@Active", clsDIC_DEPARTMENT.Active); } else { insertCommand.Parameters.AddWithValue("@Active", DBNull.Value); } try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Delete(DIC_DEPARTMENT clsDIC_DEPARTMENT) { SqlConnection connection = DAO_BASE.Get_Connection(); string deleteStatement = "DELETE FROM " + " [DIC_DEPARTMENT] " + "WHERE " + " [DepartmentCode] = @OldDepartmentCode " + " AND ((@OldDepartmentName IS NULL AND [DepartmentName] IS NULL) OR [DepartmentName] = @OldDepartmentName) " + " AND ((@OldPhone IS NULL AND [Phone] IS NULL) OR [Phone] = @OldPhone) " + " AND ((@OldQuantity IS NULL AND [Quantity] IS NULL) OR [Quantity] = @OldQuantity) " + " AND ((@OldFactQuantity IS NULL AND [FactQuantity] IS NULL) OR [FactQuantity] = @OldFactQuantity) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + " AND ((@OldActive IS NULL AND [Active] IS NULL) OR [Active] = @OldActive) " + ""; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldDepartmentCode", clsDIC_DEPARTMENT.DepartmentCode); if (clsDIC_DEPARTMENT.DepartmentName != null) { deleteCommand.Parameters.AddWithValue("@OldDepartmentName", clsDIC_DEPARTMENT.DepartmentName); } else { deleteCommand.Parameters.AddWithValue("@OldDepartmentName", DBNull.Value); } if (clsDIC_DEPARTMENT.Phone != null) { deleteCommand.Parameters.AddWithValue("@OldPhone", clsDIC_DEPARTMENT.Phone); } else { deleteCommand.Parameters.AddWithValue("@OldPhone", DBNull.Value); } if (clsDIC_DEPARTMENT.Quantity.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldQuantity", clsDIC_DEPARTMENT.Quantity); } else { deleteCommand.Parameters.AddWithValue("@OldQuantity", DBNull.Value); } if (clsDIC_DEPARTMENT.FactQuantity.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldFactQuantity", clsDIC_DEPARTMENT.FactQuantity); } else { deleteCommand.Parameters.AddWithValue("@OldFactQuantity", DBNull.Value); } if (clsDIC_DEPARTMENT.Description != null) { deleteCommand.Parameters.AddWithValue("@OldDescription", clsDIC_DEPARTMENT.Description); } else { deleteCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } if (clsDIC_DEPARTMENT.Active.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldActive", clsDIC_DEPARTMENT.Active); } else { deleteCommand.Parameters.AddWithValue("@OldActive", DBNull.Value); } try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Update(DIC_DEPARTMENT oldDIC_DEPARTMENT, DIC_DEPARTMENT newDIC_DEPARTMENT) { SqlConnection connection = DAO_BASE.Get_Connection(); string updateStatement = "UPDATE " + " [DIC_DEPARTMENT] " + "SET " + " [DepartmentCode] = @NewDepartmentCode " + " ,[DepartmentName] = @NewDepartmentName " + " ,[Phone] = @NewPhone " + " ,[Quantity] = @NewQuantity " + " ,[FactQuantity] = @NewFactQuantity " + " ,[Description] = @NewDescription " + " ,[Active] = @NewActive " + "WHERE " + " [DepartmentCode] = @OldDepartmentCode " + " AND ((@OldDepartmentName IS NULL AND [DepartmentName] IS NULL) OR [DepartmentName] = @OldDepartmentName) " + " AND ((@OldPhone IS NULL AND [Phone] IS NULL) OR [Phone] = @OldPhone) " + " AND ((@OldQuantity IS NULL AND [Quantity] IS NULL) OR [Quantity] = @OldQuantity) " + " AND ((@OldFactQuantity IS NULL AND [FactQuantity] IS NULL) OR [FactQuantity] = @OldFactQuantity) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + " AND ((@OldActive IS NULL AND [Active] IS NULL) OR [Active] = @OldActive) " + ""; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.CommandType = CommandType.Text; updateCommand.Parameters.AddWithValue("@NewDepartmentCode", newDIC_DEPARTMENT.DepartmentCode); if (newDIC_DEPARTMENT.DepartmentName != null) { updateCommand.Parameters.AddWithValue("@NewDepartmentName", newDIC_DEPARTMENT.DepartmentName); } else { updateCommand.Parameters.AddWithValue("@NewDepartmentName", DBNull.Value); } if (newDIC_DEPARTMENT.Phone != null) { updateCommand.Parameters.AddWithValue("@NewPhone", newDIC_DEPARTMENT.Phone); } else { updateCommand.Parameters.AddWithValue("@NewPhone", DBNull.Value); } if (newDIC_DEPARTMENT.Quantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewQuantity", newDIC_DEPARTMENT.Quantity); } else { updateCommand.Parameters.AddWithValue("@NewQuantity", DBNull.Value); } if (newDIC_DEPARTMENT.FactQuantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewFactQuantity", newDIC_DEPARTMENT.FactQuantity); } else { updateCommand.Parameters.AddWithValue("@NewFactQuantity", DBNull.Value); } if (newDIC_DEPARTMENT.Description != null) { updateCommand.Parameters.AddWithValue("@NewDescription", newDIC_DEPARTMENT.Description); } else { updateCommand.Parameters.AddWithValue("@NewDescription", DBNull.Value); } if (newDIC_DEPARTMENT.Active.HasValue == true) { updateCommand.Parameters.AddWithValue("@NewActive", newDIC_DEPARTMENT.Active); } else { updateCommand.Parameters.AddWithValue("@NewActive", DBNull.Value); } updateCommand.Parameters.AddWithValue("@OldDepartmentCode", oldDIC_DEPARTMENT.DepartmentCode); if (oldDIC_DEPARTMENT.DepartmentName != null) { updateCommand.Parameters.AddWithValue("@OldDepartmentName", oldDIC_DEPARTMENT.DepartmentName); } else { updateCommand.Parameters.AddWithValue("@OldDepartmentName", DBNull.Value); } if (oldDIC_DEPARTMENT.Phone != null) { updateCommand.Parameters.AddWithValue("@OldPhone", oldDIC_DEPARTMENT.Phone); } else { updateCommand.Parameters.AddWithValue("@OldPhone", DBNull.Value); } if (oldDIC_DEPARTMENT.Quantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@OldQuantity", oldDIC_DEPARTMENT.Quantity); } else { updateCommand.Parameters.AddWithValue("@OldQuantity", DBNull.Value); } if (oldDIC_DEPARTMENT.FactQuantity.HasValue == true) { updateCommand.Parameters.AddWithValue("@OldFactQuantity", oldDIC_DEPARTMENT.FactQuantity); } else { updateCommand.Parameters.AddWithValue("@OldFactQuantity", DBNull.Value); } if (oldDIC_DEPARTMENT.Description != null) { updateCommand.Parameters.AddWithValue("@OldDescription", oldDIC_DEPARTMENT.Description); } else { updateCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } if (oldDIC_DEPARTMENT.Active.HasValue == true) { updateCommand.Parameters.AddWithValue("@OldActive", oldDIC_DEPARTMENT.Active); } else { updateCommand.Parameters.AddWithValue("@OldActive", DBNull.Value); } try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Delete(DIC_PROFESSIONAL clsDIC_PROFESSIONAL) { SqlConnection connection = DAO_BASE.Get_Connection(); string deleteStatement = "DELETE FROM " + " [DIC_PROFESSIONAL] " + "WHERE " + " [ProfessionalCode] = @OldProfessionalCode " + " AND ((@OldProfessionalName IS NULL AND [ProfessionalName] IS NULL) OR [ProfessionalName] = @OldProfessionalName) " + " AND ((@OldDescription IS NULL AND [Description] IS NULL) OR [Description] = @OldDescription) " + " AND ((@OldActive IS NULL AND [Active] IS NULL) OR [Active] = @OldActive) " + ""; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.CommandType = CommandType.Text; deleteCommand.Parameters.AddWithValue("@OldProfessionalCode", clsDIC_PROFESSIONAL.ProfessionalCode); if (clsDIC_PROFESSIONAL.ProfessionalName != null) { deleteCommand.Parameters.AddWithValue("@OldProfessionalName", clsDIC_PROFESSIONAL.ProfessionalName); } else { deleteCommand.Parameters.AddWithValue("@OldProfessionalName", DBNull.Value); } if (clsDIC_PROFESSIONAL.Description != null) { deleteCommand.Parameters.AddWithValue("@OldDescription", clsDIC_PROFESSIONAL.Description); } else { deleteCommand.Parameters.AddWithValue("@OldDescription", DBNull.Value); } if (clsDIC_PROFESSIONAL.Active.HasValue == true) { deleteCommand.Parameters.AddWithValue("@OldActive", clsDIC_PROFESSIONAL.Active); } else { deleteCommand.Parameters.AddWithValue("@OldActive", DBNull.Value); } try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }