qq_32052883的博客

一个不错的治具管理系统 namespace MainDepartment { public class MainDepartmentDAL { private string Module_Desc = "Department Maintenance"; SecAuditTrail.SecAuditTrailDAL SecAuditTrailDAL = new SecAuditTrail.SecAuditTrailDAL(); SQLAPI.SQLAPI SQLAPI = new SQLAPI.SQLAPI(); #region Search public DataTable Search(object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = " SELECT a.DepId,a.DepCd,a.DepDesc,a.Status,a.CreatedBy,a.CreatedDate,a.UpdatedDate,a.UpdatedBy,b.SiteCd +'-'+ b.SiteDesc as Site" + " FROM CR_Department a with (nolock)" + //" inner join CR_Site b with (nolock) on a.CreatedSite = b.SiteId" + " where a.DepDesc like '" + SqlObject.Encode(obj2.DepDesc) + "%'" + " and a.DepCd like '" + SqlObject.Encode(obj2.DepCd) + "%'" + " and a.Status like '" + SqlObject.Encode(obj2.Status) + "%'" + // " and a.CreatedSite in (" + obj2.CreatedSite + ")" + " order by a.DepCd "; return SQLAPI.ExecuteNonQueryReturnDataTable(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.Search : " + ex.Message); } return SQLAPI.ExecuteNonQueryReturnDataTable(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); } #endregion #region add public void add(Object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = "INSERT INTO CR_Department " + "(DepCd,DepDesc,Status,CreatedBy,CreatedDate,CreatedSite) " + "VALUES(" + "'" + SqlObject.Encode(obj2.DepCd) + "'," + "'" + SqlObject.Encode(obj2.DepDesc) + "'," + "'A'," + "'" + SqlObject.Encode(obj2.CreatedBy) + "'," + " Convert(datetime,getdate(),12) , " + "'" + obj2.CreatedSite + "'" + ")"; SQLAPI.ExecuteNonQuery(SQLAPI.ENCRYPT_CONN_STRING(), s,null, CommandType.Text); SecAuditTrailDAL.add(Module_Desc, new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name.ToString(), obj2.CreatedBy.ToString()); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.add : " + ex.Message); } } #endregion #region delete public void delete(Object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = "Delete from CR_Department where DepId = '" + obj2.DepId + "'"; SQLAPI.ExecuteNonQuery(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); SecAuditTrailDAL.add(Module_Desc, new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name.ToString(), obj2.UpdatedBy.ToString()); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.delete : " + ex.Message); } } #endregion #region update public void update(Object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = "UPDATE CR_Department SET " + " DepCd = '" + SqlObject.Encode(obj2.DepCd) + "', " + " DepDesc = '" + SqlObject.Encode(obj2.DepDesc) + "', " + " Status = '" + SqlObject.Encode(obj2.Status) + "', " + " UpdatedBy = '" + SqlObject.Encode(obj2.UpdatedBy) + "', " + " UpdatedDate = Convert(datetime,getdate(),12) " + " where DepId = '" + obj2.DepId + "'"; SQLAPI.ExecuteNonQuery(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); SecAuditTrailDAL.add(Module_Desc, new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name.ToString(), Convert.ToString(obj2.UpdatedBy)); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.update : " + ex.Message); } } #endregion #region CheckDuplicate public string CheckDuplicate(object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = " select (case when count(*)='0' then 'No' else 'Yes' end) FROM CR_Department with (nolock)" + " where DepCd = '" + SqlObject.Encode(obj2.DepCd) + "' and CreatedSite = '" + obj2.CreatedSite + "'"; return SQLAPI.ExecuteScalar(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.CheckDuplicate : " + ex.Message); } } #endregion #region updateStatus public void updateStatus(Object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = "UPDATE CR_Department SET " + " Status ='" + SqlObject.Encode(obj2.Status) + "', " + " UpdatedBy = '" + SqlObject.Encode(obj2.UpdatedBy) + "', " + " UpdatedDate = Convert(datetime,getdate(),12) " + " where DepId = '" + obj2.DepId + "'"; SQLAPI.ExecuteNonQuery(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); SecAuditTrailDAL.add(Module_Desc, new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name.ToString(), obj2.UpdatedBy.ToString()); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.updateStatus : " + ex.Message); } } #endregion #region DDL public DataTable DDL(object obj) { string s = ""; try { if (obj is MainDepartment && obj !=null) { MainDepartment obj2 = (MainDepartment)obj; s = " SELECT DepId,DepCd+'-'+DepDesc as CdDesc " + " FROM CR_Department with (nolock)" + " where Status='A'" + " and CreatedSite in (" + obj2.CreatedSite + ") order by DepCd asc "; return SQLAPI.ExecuteNonQueryReturnDataTable(SQLAPI.ENCRYPT_CONN_STRING(), s, null, CommandType.Text); } else { throw new Exception("MainDepartment Object not found!"); } } catch (Exception ex) { throw new Exception("MainDepartmentDAL.DDL : " + ex.Message); } } #endregion } }