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