/// <summary>
/// 新建部门
/// </summary>
/// <param name="department">部门实体对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回处理后的部门实体对象</returns>
public Department Add(Department department, ICTransaction tran)
{
if (department == null)
{
throw new ArgumentNullException("department");
}
department.DepartmentId = KeyGenerator.GenNewGuidKey();
StringBuilder query = new StringBuilder();
query.AppendLine(@"INSERT INTO ");
query.AppendLine(@" [Department] ( ");
query.AppendLine(@" [DepartmentId] ");
query.AppendLine(@" ,[DepartmentCode] ");
query.AppendLine(@" ,[CompanyId] ");
query.AppendLine(@" ,[Name] ");
query.AppendLine(@" ,[RVersion] ");
query.AppendLine(@" ,[Status] ");
query.AppendLine(@" ,[CreaterId] ");
query.AppendLine(@" ,[CreateTime] ");
query.AppendLine(@" ,[UpdatorId] ");
query.AppendLine(@" ,[UpdateTime] ");
query.AppendLine(@" )");
query.AppendLine(@"VALUES (");
query.AppendLine(@" @DepartmentId ");
query.AppendLine(@" ,@DepartmentCode ");
query.AppendLine(@" ,@CompanyId ");
query.AppendLine(@" ,@Name ");
query.AppendLine(@" ,@RVersion ");
query.AppendLine(@" ,@Status ");
query.AppendLine(@" ,@CreaterId ");
query.AppendLine(@" ,@CreateTime ");
query.AppendLine(@" ,@UpdatorId ");
query.AppendLine(@" ,@UpdateTime ");
query.AppendLine(@"); ");
DBParamCollection<DBParam> paramCollection = new DBParamCollection<DBParam>();
paramCollection.Add(new DBParam("@DepartmentId", department.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentCode", department.DepartmentCode, DbType.String, 10));
paramCollection.Add(new DBParam("@CompanyId", department.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@Name", department.Name, DbType.String, 100));
paramCollection.Add(new DBParam("@RVersion", department.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", department.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", department.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", department.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", department.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", department.UpdateTime, DbType.DateTime));
try
{
int effectCount = 0;
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
if (effectCount == 0)
{
department.DepartmentId = string.Empty;
throw new ResponseException((int)ResultCode.NoDataInsert, department.DepartmentCode);
}
}
catch(Exception ex)
{
department.DepartmentId = string.Empty;
throw new Exception(ex.Message, ex);
}
return department;
}
/// <summary>
/// 更新部门数据
/// </summary>
/// <param name="department">部门实体对象</param>
/// <param name="tran">中间事务对象</param>
public void Update(Department department, ICTransaction tran)
{
Department oldDepartment = this.FindSingle(department.DepartmentId, tran);
int updateColCount = 0;
if (department == null)
{
throw new ArgumentException("department");
}
if (oldDepartment == null)
{
throw new ResponseException((int)ResultCode.NoDataExists, department.DepartmentCode);
}
if (department.RVersion != oldDepartment.RVersion)
{
throw new ResponseException((int)ResultCode.VersionChanged, oldDepartment.RVersion.ToString());
}
StringBuilder query = new StringBuilder();
query.AppendLine(@"UPDATE ");
query.AppendLine(@" [Department]");
query.AppendLine(@"SET ");
query.AppendLine(@" [DepartmentId] = @DepartmentId ");
if ((!string.IsNullOrEmpty(department.DepartmentCode) && !department.DepartmentCode.Equals(oldDepartment.DepartmentCode))
|| (!string.IsNullOrEmpty(oldDepartment.DepartmentCode) && !oldDepartment.DepartmentCode.Equals(department.DepartmentCode)))
{
updateColCount++;
query.AppendLine(@" ,[DepartmentCode] = @DepartmentCode ");
}
if ((!string.IsNullOrEmpty(department.CompanyId) && !department.CompanyId.Equals(oldDepartment.CompanyId))
|| (!string.IsNullOrEmpty(oldDepartment.CompanyId) && !oldDepartment.CompanyId.Equals(department.CompanyId)))
{
updateColCount++;
query.AppendLine(@" ,[CompanyId] = @CompanyId ");
}
if ((!string.IsNullOrEmpty(department.Name) && !department.Name.Equals(oldDepartment.Name))
|| (!string.IsNullOrEmpty(oldDepartment.Name) && !oldDepartment.Name.Equals(department.Name)))
{
updateColCount++;
query.AppendLine(@" ,[Name] = @Name ");
}
if (oldDepartment.Status != department.Status)
{
updateColCount++;
query.AppendLine(@" ,[Status] = @Status ");
}
if ((!string.IsNullOrEmpty(department.CreaterId) && !department.CreaterId.Equals(oldDepartment.CreaterId))
|| (!string.IsNullOrEmpty(oldDepartment.CreaterId) && !oldDepartment.CreaterId.Equals(department.CreaterId)))
{
updateColCount++;
query.AppendLine(@" ,[CreaterId] = @CreaterId ");
}
if (oldDepartment.CreateTime.CompareTo(department.CreateTime) != 0 && department.CreateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[CreateTime] = @CreateTime ");
}
if ((!string.IsNullOrEmpty(department.UpdatorId) && !department.UpdatorId.Equals(oldDepartment.UpdatorId))
|| (!string.IsNullOrEmpty(oldDepartment.UpdatorId) && !oldDepartment.UpdatorId.Equals(department.UpdatorId)))
{
updateColCount++;
query.AppendLine(@" ,[UpdatorId] = @UpdatorId ");
}
if (oldDepartment.UpdateTime.CompareTo(department.UpdateTime) != 0 && department.UpdateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[UpdateTime] = @UpdateTime ");
}
query.AppendLine(@" ,[RVersion] = @RVersion ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" [DepartmentId] = @DepartmentId ");
if (updateColCount == 0)
{
return;
}
department.UpdateTime = DateTime.Now;
DBParamCollection<DBParam> paramCollection = new DBParamCollection<DBParam>();
paramCollection.Add(new DBParam("@DepartmentId", department.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentCode", department.DepartmentCode, DbType.String, 10));
paramCollection.Add(new DBParam("@CompanyId", department.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@Name", department.Name, DbType.String, 100));
paramCollection.Add(new DBParam("@RVersion", department.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", department.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", department.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", department.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", department.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", department.UpdateTime, DbType.DateTime));
try
{
int effectCount = 0;
if (department != null)
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
}
// 抛出一个异常
if (effectCount == 0)
{
throw new ResponseException((int)ResultCode.NoDataUpdate, department.DepartmentCode);
}
}
catch(Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 新建公司
/// </summary>
/// <param name="company">公司实体对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回处理后的公司实体对象</returns>
public Company Add(Company company, ICTransaction tran)
{
if (company == null)
{
throw new ArgumentNullException("company");
}
company.CompanyId = KeyGenerator.GenNewGuidKey();
StringBuilder query = new StringBuilder();
query.AppendLine(@"INSERT INTO ");
query.AppendLine(@" [Company] ( ");
query.AppendLine(@" [CompanyId] ");
query.AppendLine(@" ,[CompanyCode] ");
query.AppendLine(@" ,[Name] ");
query.AppendLine(@" ,[Address] ");
query.AppendLine(@" ,[ParentCompanyId] ");
query.AppendLine(@" ,[RVersion] ");
query.AppendLine(@" ,[Status] ");
query.AppendLine(@" ,[CreaterId] ");
query.AppendLine(@" ,[CreateTime] ");
query.AppendLine(@" ,[UpdatorId] ");
query.AppendLine(@" ,[UpdateTime] ");
query.AppendLine(@" ) ");
query.AppendLine(@"VALUES ( ");
query.AppendLine(@" @CompanyId ");
query.AppendLine(@" ,@CompanyCode ");
query.AppendLine(@" ,@Name ");
query.AppendLine(@" ,@Address ");
query.AppendLine(@" ,@ParentCompanyId ");
query.AppendLine(@" ,@RVersion ");
query.AppendLine(@" ,@Status ");
query.AppendLine(@" ,@CreaterId ");
query.AppendLine(@" ,@CreateTime ");
query.AppendLine(@" ,@UpdatorId ");
query.AppendLine(@" ,@UpdateTime ");
query.AppendLine(@"); ");
DBParamCollection <DBParam> paramCollection = new DBParamCollection <DBParam>();
paramCollection.Add(new DBParam("@CompanyId", company.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@CompanyCode", company.CompanyCode, DbType.String, 5));
paramCollection.Add(new DBParam("@Name", company.Name, DbType.String, 200));
paramCollection.Add(new DBParam("@Address", company.Address, DbType.String, 500));
paramCollection.Add(new DBParam("@ParentCompanyId", company.ParentCompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@RVersion", company.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", company.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", company.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", company.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", company.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", company.UpdateTime, DbType.DateTime));
try
{
int effectCount = 0;
if (company != null)
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
}
if (effectCount == 0)
{
company.CompanyId = string.Empty;
throw new ResponseException((int)ResultCode.NoDataInsert, company.CompanyCode);
}
}
catch (Exception ex)
{
company.CompanyId = string.Empty;
throw new Exception(ex.Message, ex);
}
return(company);
}
/// <summary>
/// 更新公司数据
/// </summary>
/// <param name="company">公司实体对象</param>
/// <param name="tran">中间事务对象</param>
public void Update(Company company, ICTransaction tran)
{
Company oldCompany = this.FindSingle(company.CompanyId, tran);
int updateColCount = 0;
if (company == null)
{
throw new ArgumentException("company");
}
if (oldCompany == null)
{
throw new ResponseException((int)ResultCode.NoDataExists, company.CompanyCode);
}
if (company.RVersion != oldCompany.RVersion)
{
throw new ResponseException((int)ResultCode.VersionChanged, oldCompany.RVersion.ToString());
}
StringBuilder query = new StringBuilder();
query.AppendLine(@"UPDATE ");
query.AppendLine(@" [Company] ");
query.AppendLine(@"SET ");
query.AppendLine(@" [CompanyId] = @CompanyId ");
if ((!string.IsNullOrEmpty(company.CompanyCode) && !company.CompanyCode.Equals(oldCompany.CompanyCode)) ||
(!string.IsNullOrEmpty(oldCompany.CompanyCode) && !oldCompany.CompanyCode.Equals(company.CompanyCode)))
{
updateColCount++;
query.AppendLine(@" ,[CompanyCode] = @CompanyCode ");
}
if ((!string.IsNullOrEmpty(company.Name) && !company.Name.Equals(oldCompany.Name)) ||
(!string.IsNullOrEmpty(oldCompany.Name) && !oldCompany.Name.Equals(company.Name)))
{
updateColCount++;
query.AppendLine(@" ,[Name] = @Name ");
}
if ((!string.IsNullOrEmpty(company.Address) && !company.Address.Equals(oldCompany.Address)) ||
(!string.IsNullOrEmpty(oldCompany.Address) && !oldCompany.Address.Equals(company.Address)))
{
updateColCount++;
query.AppendLine(@" ,[Address] = @Address ");
}
if ((!string.IsNullOrEmpty(company.ParentCompanyId) && !company.ParentCompanyId.Equals(oldCompany.ParentCompanyId)) ||
(!string.IsNullOrEmpty(oldCompany.ParentCompanyId) && !oldCompany.ParentCompanyId.Equals(company.ParentCompanyId)))
{
updateColCount++;
query.AppendLine(@" ,[ParentCompanyId] = @ParentCompanyId ");
}
if (oldCompany.Status != company.Status)
{
updateColCount++;
query.AppendLine(@" ,[Status] = @Status ");
}
if ((!string.IsNullOrEmpty(company.CreaterId) && !company.CreaterId.Equals(oldCompany.CreaterId)) ||
(!string.IsNullOrEmpty(oldCompany.CreaterId) && !oldCompany.CreaterId.Equals(company.CreaterId)))
{
updateColCount++;
query.AppendLine(@" ,[CreaterId] = @CreaterId ");
}
if (oldCompany.CreateTime.CompareTo(company.CreateTime) != 0 && company.CreateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[CreateTime] = @CreateTime ");
}
if ((!string.IsNullOrEmpty(company.UpdatorId) && !company.UpdatorId.Equals(oldCompany.UpdatorId)) ||
(!string.IsNullOrEmpty(oldCompany.UpdatorId) && !oldCompany.UpdatorId.Equals(company.UpdatorId)))
{
updateColCount++;
query.AppendLine(@" ,[UpdatorId] = @UpdatorId ");
}
if (oldCompany.UpdateTime.CompareTo(company.UpdateTime) != 0 && company.UpdateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[UpdateTime] = @UpdateTime ");
}
query.AppendLine(@" ,[RVersion] = @RVersion ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" [CompanyId] = @CompanyId ");
if (updateColCount == 0)
{
return;
}
company.UpdateTime = DateTime.Now;
DBParamCollection <DBParam> paramCollection = new DBParamCollection <DBParam>();
paramCollection.Add(new DBParam("@CompanyId", company.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@CompanyCode", company.CompanyCode, DbType.String, 5));
paramCollection.Add(new DBParam("@Name", company.Name, DbType.String, 200));
paramCollection.Add(new DBParam("@Address", company.Address, DbType.String, 500));
paramCollection.Add(new DBParam("@ParentCompanyId", company.ParentCompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@RVersion", oldCompany.RVersion + 1, DbType.Int32));
paramCollection.Add(new DBParam("@Status", company.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", company.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", company.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", company.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", company.UpdateTime, DbType.DateTime));
try
{
int effectCount = 0;
if (company != null)
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
}
// 抛出一个异常
if (effectCount == 0)
{
throw new ResponseException((int)ResultCode.NoDataUpdate, company.CompanyCode);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
var columns = (List <SqlColumn>)ViewState["_columns"];
SqlColumn column = null;
var objactValues = new Dictionary <string, object>();
object p_k_value = null;
string p_k = null;
foreach (GridViewRow row in gvColumns.Rows)
{
if (row.RowType != DataControlRowType.DataRow)
{
continue;
}
var lblName = row.FindControl("lblName") as Label;
var txtValue = row.FindControl("txtValue") as TextBox;
var txtDate = row.FindControl("txtDate") as SarsCalendar;
var chkAnswer = row.FindControl("chkAnswer") as CheckBox;
if (lblName == null || txtValue == null || txtDate == null || chkAnswer == null)
{
return;
}
object value = null;
if (txtValue.Visible)
{
value = txtValue.Text;
}
if (txtDate.Visible)
{
value = txtDate.Text;
}
if (chkAnswer.Visible)
{
value = chkAnswer.Checked;
}
var foundColumn = columns.Find(c => c.Name.Equals(lblName.Text));
if (foundColumn != null && foundColumn.IsPrimaryKey)
{
column = foundColumn;
p_k_value = value;
p_k = lblName.Text;
}
//var column = columns.Find(c => c.Name.Equals(lblName.Text));
if (foundColumn != null && !foundColumn.IsPrimaryKey)
{
objactValues.Add(lblName.Text, value);
}
}
if (column == null)
{
return;
}
var sql = string.Format("UPDATE {0}.{1} SET ", column.Schema, column.TableName);
var oParams = new DBParamCollection();
foreach (var updateValues in objactValues)
{
sql += string.Format("{0} = @{1}, ", updateValues.Key, updateValues.Key);
oParams.Add(string.Format("@{0}", updateValues.Key), updateValues.Value);
}
oParams.Add(string.Format("@{0}", p_k), p_k_value);
sql = sql.TrimEnd(", ".ToCharArray());
sql += string.Format(" WHERE ({0} = @{0})", p_k);
using (var oCommand = new DBCommand(sql, QueryType.TransectSQL, oParams))
{
var saved = oCommand.Execute();
if (saved > 0)
{
ddlTables_SelectedIndexChanged(ddlTables, EventArgs.Empty);
//MessageBox.Show("Record Updated Successfully");
ScriptManager.RegisterStartupScript(this, GetType(), "Show MessageBox", "showMessageBox();", true);
}
}
}
/// <summary>
/// 更新员工数据
/// </summary>
/// <param name="employee">员工实体对象</param>
/// <param name="tran">中间事务对象</param>
public void Update(Employee employee, ICTransaction tran)
{
Employee oldEmployee = this.FindSingle(employee.EmployeeId, tran);
int updateColCount = 0;
if (employee == null)
{
throw new ArgumentException("employee");
}
if (oldEmployee == null)
{
throw new ResponseException((int)ResultCode.NoDataExists, employee.EmployeeCode);
}
if (employee.RVersion != oldEmployee.RVersion)
{
throw new ResponseException((int)ResultCode.VersionChanged, oldEmployee.RVersion.ToString());
}
StringBuilder query = new StringBuilder();
query.AppendLine(@"UPDATE ");
query.AppendLine(@" [Employee]");
query.AppendLine(@"SET ");
query.AppendLine(@" [EmployeeId] = @EmployeeId ");
if ((!string.IsNullOrEmpty(employee.EmployeeCode) && !employee.EmployeeCode.Equals(oldEmployee.EmployeeCode))
|| (!string.IsNullOrEmpty(oldEmployee.EmployeeCode) && !oldEmployee.EmployeeCode.Equals(employee.EmployeeCode)))
{
updateColCount++;
query.AppendLine(@" ,[EmployeeCode] = @EmployeeCode ");
}
if ((!string.IsNullOrEmpty(employee.Name) && !employee.Name.Equals(oldEmployee.Name))
|| (!string.IsNullOrEmpty(oldEmployee.Name) && !oldEmployee.Name.Equals(employee.Name)))
{
updateColCount++;
query.AppendLine(@" ,[Name] = @Name ");
}
if (((DateTime)oldEmployee.Birthday).CompareTo(employee.Birthday) != 0 && employee.Birthday != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[Birthday] = @Birthday ");
}
if (employee.Sex != oldEmployee.Sex)
{
updateColCount++;
query.AppendLine(@" ,[Sex] = @Sex ");
}
if ((!string.IsNullOrEmpty(employee.CompanyId) && !employee.CompanyId.Equals(oldEmployee.CompanyId))
|| (!string.IsNullOrEmpty(oldEmployee.CompanyId) && !oldEmployee.CompanyId.Equals(employee.CompanyId)))
{
updateColCount++;
query.AppendLine(@" ,[CompanyId] = @CompanyId ");
}
if ((!string.IsNullOrEmpty(employee.DepartmentId) && !employee.DepartmentId.Equals(oldEmployee.DepartmentId))
|| (!string.IsNullOrEmpty(oldEmployee.DepartmentId) && !oldEmployee.DepartmentId.Equals(employee.DepartmentId)))
{
updateColCount++;
query.AppendLine(@" ,[DepartmentId] = @DepartmentId ");
}
if ((!string.IsNullOrEmpty(employee.PositionId) && !employee.PositionId.Equals(oldEmployee.PositionId))
|| (!string.IsNullOrEmpty(oldEmployee.PositionId) && !oldEmployee.PositionId.Equals(employee.PositionId)))
{
updateColCount++;
query.AppendLine(@" ,[PositionId] = @PositionId ");
}
if (employee.Rand != oldEmployee.Rand)
{
updateColCount++;
query.AppendLine(@" ,[Rand] = @Rand ");
}
if (employee.Status != oldEmployee.Status)
{
updateColCount++;
query.AppendLine(@" ,[Status] = @Status ");
}
if ((!string.IsNullOrEmpty(employee.CreaterId) && !employee.CreaterId.Equals(oldEmployee.CreaterId))
|| (!string.IsNullOrEmpty(oldEmployee.CreaterId) && !oldEmployee.CreaterId.Equals(employee.CreaterId)))
{
updateColCount++;
query.AppendLine(@" ,[CreaterId] = @CreaterId ");
}
if (oldEmployee.CreateTime.CompareTo(employee.CreateTime) != 0 && employee.CreateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[CreateTime] = @CreateTime ");
}
if ((!string.IsNullOrEmpty(employee.UpdatorId) && !employee.UpdatorId.Equals(oldEmployee.UpdatorId))
|| (!string.IsNullOrEmpty(oldEmployee.UpdatorId) && !oldEmployee.UpdatorId.Equals(employee.UpdatorId)))
{
updateColCount++;
query.AppendLine(@" ,[UpdatorId] = @UpdatorId ");
}
if (oldEmployee.UpdateTime.CompareTo(employee.UpdateTime) != 0 && employee.UpdateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[UpdateTime] = @UpdateTime ");
}
if (((DateTime)oldEmployee.StartWorkDate).CompareTo(employee.StartWorkDate) != 0 && employee.StartWorkDate != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[StartWorkDate] = @StartWorkDate ");
}
if (((DateTime)oldEmployee.JoinDate).CompareTo(employee.JoinDate) != 0 && employee.JoinDate != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[JoinDate] = @JoinDate ");
}
query.AppendLine(@" ,[RVersion] = @RVersion ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" [EmployeeId] = @EmployeeId ");
if (updateColCount == 0)
{
return;
}
employee.UpdateTime = DateTime.Now;
DBParamCollection<DBParam> paramCollection = new DBParamCollection<DBParam>();
paramCollection.Add(new DBParam("@EmployeeId", employee.EmployeeId, DbType.String, 40));
paramCollection.Add(new DBParam("@EmployeeCode", employee.EmployeeCode, DbType.String, 15));
paramCollection.Add(new DBParam("@Name", employee.Name, DbType.String, 50));
paramCollection.Add(new DBParam("@Birthday", employee.Birthday, DbType.DateTime));
paramCollection.Add(new DBParam("@Sex", employee.Sex, DbType.Int32));
paramCollection.Add(new DBParam("@CompanyId", employee.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentId", employee.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@PositionId", employee.PositionId, DbType.String, 40));
paramCollection.Add(new DBParam("@Rand", employee.Rand, DbType.Int32));
paramCollection.Add(new DBParam("@RVersion", employee.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", employee.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", employee.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", employee.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", employee.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", employee.UpdateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@StartWorkDate", employee.StartWorkDate, DbType.DateTime));
paramCollection.Add(new DBParam("@JoinDate", employee.JoinDate, DbType.DateTime));
try
{
int effectCount = 0;
if (employee != null)
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
}
// 抛出一个异常
if (effectCount == 0)
{
throw new ResponseException((int)ResultCode.NoDataUpdate, employee.EmployeeCode);
}
}
catch(Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 新建员工
/// </summary>
/// <param name="employee">员工实体对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回处理后的员工实体对象</returns>
public Employee Add(Employee employee, ICTransaction tran)
{
if (employee == null)
{
throw new ArgumentNullException("employee");
}
employee.EmployeeId = KeyGenerator.GenNewGuidKey();
StringBuilder query = new StringBuilder();
query.AppendLine(@"INSERT INTO ");
query.AppendLine(@" [Employee] ( ");
query.AppendLine(@" [EmployeeId] ");
query.AppendLine(@" ,[EmployeeCode] ");
query.AppendLine(@" ,[Name] ");
query.AppendLine(@" ,[Birthday] ");
query.AppendLine(@" ,[Sex] ");
query.AppendLine(@" ,[CompanyId] ");
query.AppendLine(@" ,[DepartmentId] ");
query.AppendLine(@" ,[PositionId] ");
query.AppendLine(@" ,[Rand] ");
query.AppendLine(@" ,[RVersion] ");
query.AppendLine(@" ,[Status] ");
query.AppendLine(@" ,[CreaterId] ");
query.AppendLine(@" ,[CreateTime] ");
query.AppendLine(@" ,[UpdatorId] ");
query.AppendLine(@" ,[UpdateTime] ");
query.AppendLine(@" ,[StartWorkDate] ");
query.AppendLine(@" ,[JoinDate] ");
query.AppendLine(@" ) ");
query.AppendLine(@"VALUES (");
query.AppendLine(@" @EmployeeId ");
query.AppendLine(@" ,@EmployeeCode ");
query.AppendLine(@" ,@Name ");
query.AppendLine(@" ,@Birthday ");
query.AppendLine(@" ,@Sex ");
query.AppendLine(@" ,@CompanyId ");
query.AppendLine(@" ,@DepartmentId ");
query.AppendLine(@" ,@PositionId ");
query.AppendLine(@" ,@Rand ");
query.AppendLine(@" ,@RVersion ");
query.AppendLine(@" ,@Status ");
query.AppendLine(@" ,@CreaterId ");
query.AppendLine(@" ,@CreateTime ");
query.AppendLine(@" ,@UpdatorId ");
query.AppendLine(@" ,@UpdateTime ");
query.AppendLine(@" ,@StartWorkDate ");
query.AppendLine(@" ,@JoinDate ");
query.AppendLine(@"); ");
DBParamCollection<DBParam> paramCollection = new DBParamCollection<DBParam>();
paramCollection.Add(new DBParam("@EmployeeId", employee.EmployeeId, DbType.String, 40));
paramCollection.Add(new DBParam("@EmployeeCode", employee.EmployeeCode, DbType.String, 15));
paramCollection.Add(new DBParam("@Name", employee.Name, DbType.String, 50));
paramCollection.Add(new DBParam("@Birthday", employee.Birthday, DbType.DateTime));
paramCollection.Add(new DBParam("@Sex", employee.Sex, DbType.Int32));
paramCollection.Add(new DBParam("@CompanyId", employee.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentId", employee.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@PositionId", employee.PositionId, DbType.String, 40));
paramCollection.Add(new DBParam("@Rand", employee.Rand, DbType.Int32));
paramCollection.Add(new DBParam("@RVersion", employee.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", employee.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", employee.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", employee.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", employee.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", employee.UpdateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@StartWorkDate", employee.StartWorkDate, DbType.DateTime));
paramCollection.Add(new DBParam("@JoinDate", employee.JoinDate, DbType.DateTime));
try
{
int effectCount = 0;
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
if (effectCount == 0)
{
employee.EmployeeId = string.Empty;
throw new ResponseException((int)ResultCode.NoDataInsert, employee.EmployeeCode);
}
}
catch(Exception ex)
{
employee.EmployeeId = string.Empty;
throw new Exception(ex.Message, ex);
}
return employee;
}
/// <summary>
/// 更新员工数据
/// </summary>
/// <param name="employee">员工实体对象</param>
/// <param name="tran">中间事务对象</param>
public void Update(Employee employee, ICTransaction tran)
{
Employee oldEmployee = this.FindSingle(employee.EmployeeId, tran);
int updateColCount = 0;
if (employee == null)
{
throw new ArgumentException("employee");
}
if (oldEmployee == null)
{
throw new ResponseException((int)ResultCode.NoDataExists, employee.EmployeeCode);
}
if (employee.RVersion != oldEmployee.RVersion)
{
throw new ResponseException((int)ResultCode.VersionChanged, oldEmployee.RVersion.ToString());
}
StringBuilder query = new StringBuilder();
query.AppendLine(@"UPDATE ");
query.AppendLine(@" [Employee]");
query.AppendLine(@"SET ");
query.AppendLine(@" [EmployeeId] = @EmployeeId ");
if ((!string.IsNullOrEmpty(employee.EmployeeCode) && !employee.EmployeeCode.Equals(oldEmployee.EmployeeCode)) ||
(!string.IsNullOrEmpty(oldEmployee.EmployeeCode) && !oldEmployee.EmployeeCode.Equals(employee.EmployeeCode)))
{
updateColCount++;
query.AppendLine(@" ,[EmployeeCode] = @EmployeeCode ");
}
if ((!string.IsNullOrEmpty(employee.Name) && !employee.Name.Equals(oldEmployee.Name)) ||
(!string.IsNullOrEmpty(oldEmployee.Name) && !oldEmployee.Name.Equals(employee.Name)))
{
updateColCount++;
query.AppendLine(@" ,[Name] = @Name ");
}
if (((DateTime)oldEmployee.Birthday).CompareTo(employee.Birthday) != 0 && employee.Birthday != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[Birthday] = @Birthday ");
}
if (employee.Sex != oldEmployee.Sex)
{
updateColCount++;
query.AppendLine(@" ,[Sex] = @Sex ");
}
if ((!string.IsNullOrEmpty(employee.CompanyId) && !employee.CompanyId.Equals(oldEmployee.CompanyId)) ||
(!string.IsNullOrEmpty(oldEmployee.CompanyId) && !oldEmployee.CompanyId.Equals(employee.CompanyId)))
{
updateColCount++;
query.AppendLine(@" ,[CompanyId] = @CompanyId ");
}
if ((!string.IsNullOrEmpty(employee.DepartmentId) && !employee.DepartmentId.Equals(oldEmployee.DepartmentId)) ||
(!string.IsNullOrEmpty(oldEmployee.DepartmentId) && !oldEmployee.DepartmentId.Equals(employee.DepartmentId)))
{
updateColCount++;
query.AppendLine(@" ,[DepartmentId] = @DepartmentId ");
}
if ((!string.IsNullOrEmpty(employee.PositionId) && !employee.PositionId.Equals(oldEmployee.PositionId)) ||
(!string.IsNullOrEmpty(oldEmployee.PositionId) && !oldEmployee.PositionId.Equals(employee.PositionId)))
{
updateColCount++;
query.AppendLine(@" ,[PositionId] = @PositionId ");
}
if (employee.Rand != oldEmployee.Rand)
{
updateColCount++;
query.AppendLine(@" ,[Rand] = @Rand ");
}
if (employee.Status != oldEmployee.Status)
{
updateColCount++;
query.AppendLine(@" ,[Status] = @Status ");
}
if ((!string.IsNullOrEmpty(employee.CreaterId) && !employee.CreaterId.Equals(oldEmployee.CreaterId)) ||
(!string.IsNullOrEmpty(oldEmployee.CreaterId) && !oldEmployee.CreaterId.Equals(employee.CreaterId)))
{
updateColCount++;
query.AppendLine(@" ,[CreaterId] = @CreaterId ");
}
if (oldEmployee.CreateTime.CompareTo(employee.CreateTime) != 0 && employee.CreateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[CreateTime] = @CreateTime ");
}
if ((!string.IsNullOrEmpty(employee.UpdatorId) && !employee.UpdatorId.Equals(oldEmployee.UpdatorId)) ||
(!string.IsNullOrEmpty(oldEmployee.UpdatorId) && !oldEmployee.UpdatorId.Equals(employee.UpdatorId)))
{
updateColCount++;
query.AppendLine(@" ,[UpdatorId] = @UpdatorId ");
}
if (oldEmployee.UpdateTime.CompareTo(employee.UpdateTime) != 0 && employee.UpdateTime != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[UpdateTime] = @UpdateTime ");
}
if (((DateTime)oldEmployee.StartWorkDate).CompareTo(employee.StartWorkDate) != 0 && employee.StartWorkDate != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[StartWorkDate] = @StartWorkDate ");
}
if (((DateTime)oldEmployee.JoinDate).CompareTo(employee.JoinDate) != 0 && employee.JoinDate != DateTime.MinValue)
{
updateColCount++;
query.AppendLine(@" ,[JoinDate] = @JoinDate ");
}
query.AppendLine(@" ,[RVersion] = @RVersion ");
query.AppendLine(@"WHERE ");
query.AppendLine(@" [EmployeeId] = @EmployeeId ");
if (updateColCount == 0)
{
return;
}
employee.UpdateTime = DateTime.Now;
DBParamCollection <DBParam> paramCollection = new DBParamCollection <DBParam>();
paramCollection.Add(new DBParam("@EmployeeId", employee.EmployeeId, DbType.String, 40));
paramCollection.Add(new DBParam("@EmployeeCode", employee.EmployeeCode, DbType.String, 15));
paramCollection.Add(new DBParam("@Name", employee.Name, DbType.String, 50));
paramCollection.Add(new DBParam("@Birthday", employee.Birthday, DbType.DateTime));
paramCollection.Add(new DBParam("@Sex", employee.Sex, DbType.Int32));
paramCollection.Add(new DBParam("@CompanyId", employee.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentId", employee.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@PositionId", employee.PositionId, DbType.String, 40));
paramCollection.Add(new DBParam("@Rand", employee.Rand, DbType.Int32));
paramCollection.Add(new DBParam("@RVersion", employee.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", employee.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", employee.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", employee.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", employee.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", employee.UpdateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@StartWorkDate", employee.StartWorkDate, DbType.DateTime));
paramCollection.Add(new DBParam("@JoinDate", employee.JoinDate, DbType.DateTime));
try
{
int effectCount = 0;
if (employee != null)
{
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
}
// 抛出一个异常
if (effectCount == 0)
{
throw new ResponseException((int)ResultCode.NoDataUpdate, employee.EmployeeCode);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
/// <summary>
/// 新建员工
/// </summary>
/// <param name="employee">员工实体对象</param>
/// <param name="tran">中间事务对象</param>
/// <returns>返回处理后的员工实体对象</returns>
public Employee Add(Employee employee, ICTransaction tran)
{
if (employee == null)
{
throw new ArgumentNullException("employee");
}
employee.EmployeeId = KeyGenerator.GenNewGuidKey();
StringBuilder query = new StringBuilder();
query.AppendLine(@"INSERT INTO ");
query.AppendLine(@" [Employee] ( ");
query.AppendLine(@" [EmployeeId] ");
query.AppendLine(@" ,[EmployeeCode] ");
query.AppendLine(@" ,[Name] ");
query.AppendLine(@" ,[Birthday] ");
query.AppendLine(@" ,[Sex] ");
query.AppendLine(@" ,[CompanyId] ");
query.AppendLine(@" ,[DepartmentId] ");
query.AppendLine(@" ,[PositionId] ");
query.AppendLine(@" ,[Rand] ");
query.AppendLine(@" ,[RVersion] ");
query.AppendLine(@" ,[Status] ");
query.AppendLine(@" ,[CreaterId] ");
query.AppendLine(@" ,[CreateTime] ");
query.AppendLine(@" ,[UpdatorId] ");
query.AppendLine(@" ,[UpdateTime] ");
query.AppendLine(@" ,[StartWorkDate] ");
query.AppendLine(@" ,[JoinDate] ");
query.AppendLine(@" ) ");
query.AppendLine(@"VALUES (");
query.AppendLine(@" @EmployeeId ");
query.AppendLine(@" ,@EmployeeCode ");
query.AppendLine(@" ,@Name ");
query.AppendLine(@" ,@Birthday ");
query.AppendLine(@" ,@Sex ");
query.AppendLine(@" ,@CompanyId ");
query.AppendLine(@" ,@DepartmentId ");
query.AppendLine(@" ,@PositionId ");
query.AppendLine(@" ,@Rand ");
query.AppendLine(@" ,@RVersion ");
query.AppendLine(@" ,@Status ");
query.AppendLine(@" ,@CreaterId ");
query.AppendLine(@" ,@CreateTime ");
query.AppendLine(@" ,@UpdatorId ");
query.AppendLine(@" ,@UpdateTime ");
query.AppendLine(@" ,@StartWorkDate ");
query.AppendLine(@" ,@JoinDate ");
query.AppendLine(@"); ");
DBParamCollection <DBParam> paramCollection = new DBParamCollection <DBParam>();
paramCollection.Add(new DBParam("@EmployeeId", employee.EmployeeId, DbType.String, 40));
paramCollection.Add(new DBParam("@EmployeeCode", employee.EmployeeCode, DbType.String, 15));
paramCollection.Add(new DBParam("@Name", employee.Name, DbType.String, 50));
paramCollection.Add(new DBParam("@Birthday", employee.Birthday, DbType.DateTime));
paramCollection.Add(new DBParam("@Sex", employee.Sex, DbType.Int32));
paramCollection.Add(new DBParam("@CompanyId", employee.CompanyId, DbType.String, 40));
paramCollection.Add(new DBParam("@DepartmentId", employee.DepartmentId, DbType.String, 40));
paramCollection.Add(new DBParam("@PositionId", employee.PositionId, DbType.String, 40));
paramCollection.Add(new DBParam("@Rand", employee.Rand, DbType.Int32));
paramCollection.Add(new DBParam("@RVersion", employee.RVersion, DbType.Int32));
paramCollection.Add(new DBParam("@Status", employee.Status, DbType.Int32));
paramCollection.Add(new DBParam("@CreaterId", employee.CreaterId, DbType.String, 40));
paramCollection.Add(new DBParam("@CreateTime", employee.CreateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@UpdatorId", employee.UpdatorId, DbType.String, 40));
paramCollection.Add(new DBParam("@UpdateTime", employee.UpdateTime, DbType.DateTime));
paramCollection.Add(new DBParam("@StartWorkDate", employee.StartWorkDate, DbType.DateTime));
paramCollection.Add(new DBParam("@JoinDate", employee.JoinDate, DbType.DateTime));
try
{
int effectCount = 0;
if (tran != null)
{
DbTransaction dbTran = ((MssqlTransaction)tran).CurrentTransaction;
effectCount = MssqlHelper.ExecuteNonQuery(dbTran, CommandType.Text, query.ToString(), paramCollection);
}
else
{
effectCount = MssqlHelper.ExecuteNonQuery(this.CurrentConnectionString, CommandType.Text, query.ToString(), paramCollection);
}
if (effectCount == 0)
{
employee.EmployeeId = string.Empty;
throw new ResponseException((int)ResultCode.NoDataInsert, employee.EmployeeCode);
}
}
catch (Exception ex)
{
employee.EmployeeId = string.Empty;
throw new Exception(ex.Message, ex);
}
return(employee);
}
