|
|
using System;using System.Data;using System.Text;using System.Data.SqlClient;using OT.IDAL;using DBUtility;using System.Collections.Generic;//Please add references
namespace OT.SQLServerDAL{ /// <summary>
/// 資料訪問類:OTB_SYS_Jobtitle
/// </summary>
public partial class OTB_SYS_Jobtitle : IOTB_SYS_Jobtitle { public OTB_SYS_Jobtitle() { } #region Method
/// <summary>
/// 是否存在該記錄
/// </summary>
public bool Exists(string JobtitleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_Jobtitle"); strSql.Append(" where JobtitleID=@JobtitleID "); SqlParameter[] parameters = { new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) }; parameters[0].Value = JobtitleID;
return DbHelperSQL.Exists(strSql.ToString(), parameters); } /// <summary>
/// 是否存在該記錄在人員表中
/// </summary>
public bool ExistsByMember(string JobtitleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_Members"); strSql.Append(" where JobtitleID=@JobtitleID "); SqlParameter[] parameters = { new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) }; parameters[0].Value = JobtitleID;
return DbHelperSQL.Exists(strSql.ToString(), parameters); } /// <summary>
/// 增加一條資料
/// </summary>
public bool Add(OT.Model.OTB_SYS_Jobtitle model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into OTB_SYS_Jobtitle("); strSql.Append("JobtitleID,JobtitleName,DepartID,Isleader,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate)"); strSql.Append(" values ("); strSql.Append("@JobtitleID,@JobtitleName,@DepartID,@Isleader,@Effective,@Memo,@CreateUser,getdate(),@CreateUser,getdate())"); SqlParameter[] parameters = { new SqlParameter("@JobtitleID", SqlDbType.VarChar,10), new SqlParameter("@JobtitleName", SqlDbType.NVarChar,10), new SqlParameter("@DepartID", SqlDbType.VarChar,10), new SqlParameter("@Isleader", SqlDbType.Char,1), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.NVarChar), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime)}; parameters[0].Value = model.JobtitleID; parameters[1].Value = model.JobtitleName; parameters[2].Value = model.DepartID; parameters[3].Value = model.Isleader; parameters[4].Value = model.Effective; parameters[5].Value = model.Memo; parameters[6].Value = model.CreateUser; parameters[7].Value = model.CreateDate; parameters[8].Value = model.ModifyUser; parameters[9].Value = model.ModifyDate;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 更新一條資料
/// </summary>
public bool Update(OT.Model.OTB_SYS_Jobtitle model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update OTB_SYS_Jobtitle set "); strSql.Append("JobtitleName=@JobtitleName,"); strSql.Append("DepartID=@DepartID,"); strSql.Append("Isleader=@Isleader,"); strSql.Append("Effective=@Effective,"); strSql.Append("Memo=@Memo,"); strSql.Append("ModifyUser=@ModifyUser,"); strSql.Append("ModifyDate=getdate()"); strSql.Append(" where JobtitleID=@JobtitleID "); SqlParameter[] parameters = { new SqlParameter("@JobtitleName", SqlDbType.NVarChar,10), new SqlParameter("@DepartID", SqlDbType.VarChar,10), new SqlParameter("@Isleader", SqlDbType.Char,1), new SqlParameter("@Effective", SqlDbType.Char,1), new SqlParameter("@Memo", SqlDbType.NVarChar), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime), new SqlParameter("@JobtitleID", SqlDbType.VarChar,10)}; parameters[0].Value = model.JobtitleName; parameters[1].Value = model.DepartID; parameters[2].Value = model.Isleader; parameters[3].Value = model.Effective; parameters[4].Value = model.Memo; parameters[5].Value = model.CreateUser; parameters[6].Value = model.CreateDate; parameters[7].Value = model.ModifyUser; parameters[8].Value = model.ModifyDate; parameters[9].Value = model.JobtitleID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 刪除一條資料
/// </summary>
public bool Delete(string JobtitleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from OTB_SYS_Jobtitle "); strSql.Append(" where JobtitleID=@JobtitleID "); SqlParameter[] parameters = { new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) }; parameters[0].Value = JobtitleID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } } /// <summary>
/// 批量刪除資料
/// </summary>
public bool DeleteList(string JobtitleIDlist) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from OTB_SYS_Jobtitle "); strSql.Append(" where JobtitleID in (" + JobtitleIDlist + ") "); int rows = DbHelperSQL.ExecuteSql(strSql.ToString()); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_Jobtitle GetModel(string JobtitleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 JobtitleID,JobtitleName,DepartID,Isleader,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate from OTB_SYS_Jobtitle "); strSql.Append(" where JobtitleID=@JobtitleID "); SqlParameter[] parameters = { new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) }; parameters[0].Value = JobtitleID;
OT.Model.OTB_SYS_Jobtitle model = new OT.Model.OTB_SYS_Jobtitle(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["JobtitleID"] != null && ds.Tables[0].Rows[0]["JobtitleID"].ToString() != "") { model.JobtitleID = ds.Tables[0].Rows[0]["JobtitleID"].ToString(); } if (ds.Tables[0].Rows[0]["JobtitleName"] != null && ds.Tables[0].Rows[0]["JobtitleName"].ToString() != "") { model.JobtitleName = ds.Tables[0].Rows[0]["JobtitleName"].ToString(); } if (ds.Tables[0].Rows[0]["DepartID"] != null && ds.Tables[0].Rows[0]["DepartID"].ToString() != "") { model.DepartID = ds.Tables[0].Rows[0]["DepartID"].ToString(); } if (ds.Tables[0].Rows[0]["Isleader"] != null && ds.Tables[0].Rows[0]["Isleader"].ToString() != "") { model.Isleader = ds.Tables[0].Rows[0]["Isleader"].ToString(); } if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "") { model.Effective = ds.Tables[0].Rows[0]["Effective"].ToString(); } if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "") { model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString(); } if (ds.Tables[0].Rows[0]["CreateUser"] != null && ds.Tables[0].Rows[0]["CreateUser"].ToString() != "") { model.CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString(); } if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "") { model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString()); } if (ds.Tables[0].Rows[0]["ModifyUser"] != null && ds.Tables[0].Rows[0]["ModifyUser"].ToString() != "") { model.ModifyUser = ds.Tables[0].Rows[0]["ModifyUser"].ToString(); } if (ds.Tables[0].Rows[0]["ModifyDate"] != null && ds.Tables[0].Rows[0]["ModifyDate"].ToString() != "") { model.ModifyDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyDate"].ToString()); } return model; } else { return null; } } /// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string DepartID, string JobtitleID, string JobtitleName, string Effective, string SortExpression) {
StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" RowId "); strSql.Append(" ,JobtitleID "); strSql.Append(" ,JobtitleName "); strSql.Append(" ,Effective "); strSql.Append(" ,DepartID "); strSql.Append(" FROM "); strSql.Append(" ( "); strSql.Append(" SELECT "); strSql.Append(" CASE @SortExpression "); strSql.Append(" WHEN N'JobtitleID' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID)) "); strSql.Append(" WHEN N'JobtitleID DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleID DESC)) "); strSql.Append(" WHEN N'DepartID' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY DepartID)) "); strSql.Append(" WHEN N'DepartID DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY DepartID DESC)) "); strSql.Append(" WHEN N'JobtitleName' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleName)) "); strSql.Append(" WHEN N'JobtitleName DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY JobtitleName DESC)) "); strSql.Append(" WHEN N'Effective' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY Effective)) "); strSql.Append(" WHEN N'Effective DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY Effective DESC)) "); strSql.Append(" ELSE "); strSql.Append(" (ROW_NUMBER() OVER(Order BY ModifyDate DESC)) "); strSql.Append(" END AS RowId "); strSql.Append(" ,JobtitleID "); strSql.Append(" ,JobtitleName "); strSql.Append(" ,Effective "); strSql.Append(" ,DepartID "); strSql.Append(" FROM [OTB_SYS_Jobtitle] "); strSql.Append(" WHERE(JobtitleID LIKE @JobtitleID OR @JobtitleID IS NULL OR @JobtitleID='%%') "); strSql.Append(" AND (JobtitleName LIKE @JobtitleName OR @JobtitleName IS NULL OR JobtitleName IS NULL OR @JobtitleName='%%') "); strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') "); strSql.Append(" "); strSql.Append(" ) AS AA "); strSql.Append(" WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex "); strSql.Append(" ORDER BY RowId "); SqlParameter[] parameters = { new SqlParameter("@StartRecordIndex", SqlDbType.Int) ,new SqlParameter("@EndRecordIndex", SqlDbType.Int) ,new SqlParameter("@DepartID", SqlDbType.VarChar,10) ,new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) ,new SqlParameter("@JobtitleName", SqlDbType.NVarChar,50) ,new SqlParameter("@Effective", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = DepartID; parameters[3].Value = "%" + JobtitleID + "%"; parameters[4].Value = "%" + JobtitleName + "%"; parameters[5].Value = Effective; parameters[6].Value = SortExpression; return DbHelperSQL.Query(strSql.ToString(), parameters); } /// <summary>
/// 獲得資料總筆數
/// </summary>
public int GetListCount(string DepartID, string JobtitleID, string JobtitleName, string Effective) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" COUNT(0) "); strSql.Append(" FROM [OTB_SYS_Jobtitle] "); strSql.Append(" WHERE "); strSql.Append(" (JobtitleID LIKE @JobtitleID OR @JobtitleID IS NULL OR @JobtitleID='%%') "); strSql.Append(" AND (JobtitleName LIKE @JobtitleName OR @JobtitleName IS NULL OR JobtitleName IS NULL OR @JobtitleName='%%') "); strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
SqlParameter[] parameters = { new SqlParameter("@DepartID", SqlDbType.VarChar,10) ,new SqlParameter("@JobtitleID", SqlDbType.VarChar,10) ,new SqlParameter("@JobtitleName", SqlDbType.NVarChar,50) ,new SqlParameter("@Effective", SqlDbType.Char,1) }; parameters[0].Value = DepartID; parameters[1].Value = "%" + JobtitleID + "%"; parameters[2].Value = "%" + JobtitleName + "%"; parameters[3].Value = Effective; return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); }
#endregion Method
}}
|
