|
|
using System;using System.Data;using System.Text;using System.Data.SqlClient;using OT.IDAL;using DBUtility;//Please add references
namespace OT.SQLServerDAL{ /// <summary>
/// 資料訪問類:OTB_SYS_SystemSetting
/// </summary>
public partial class OTB_SYS_SystemSetting : IOTB_SYS_SystemSetting { public OTB_SYS_SystemSetting() { } #region Method
/// <summary>
/// 是否存在該記錄
/// </summary>
public bool Exists(string SettingItem) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_SystemSetting"); strSql.Append(" where SettingItem=@SettingItem "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50) }; parameters[0].Value = SettingItem; return DbHelperSQL.Exists(strSql.ToString(), parameters); }
/// <summary>
/// 增加一條資料
/// </summary>
public bool Add(OT.Model.OTB_SYS_SystemSetting model) { StringBuilder strSql = new StringBuilder(); strSql.Append(" INSERT INTO [OTB_SYS_SystemSetting]( "); strSql.Append(" [SettingItem],[SettingDescription],[SettingValue],[Memo],[Effective],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] "); strSql.Append(" )VALUES( "); strSql.Append(" @SettingItem,@SettingDescription,@SettingValue,@Memo,@Effective,@CreateUser,GETDATE(),@CreateUser,GETDATE() ) "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50), new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100), new SqlParameter("@SettingValue", SqlDbType.NVarChar,400), new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@Effective", SqlDbType.Char,1), 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.SettingItem; parameters[1].Value = model.SettingDescription; parameters[2].Value = model.SettingValue; parameters[3].Value = model.Memo; parameters[4].Value = model.Effective; parameters[5].Value = model.CreateUser; parameters[6].Value = model.CreateDate; parameters[7].Value = model.ModifyUser; parameters[8].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_SystemSetting model) { StringBuilder strSql = new StringBuilder(); strSql.Append(" UPDATE [OTB_SYS_SystemSetting] SET "); strSql.Append(" [SettingDescription] = @SettingDescription,[SettingValue] = @SettingValue,[Memo] = @Memo,[Effective] = @Effective,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE() "); strSql.Append(" WHERE SettingItem=@SettingItem "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50), new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100), new SqlParameter("@SettingValue", SqlDbType.NVarChar,400), new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@Effective", SqlDbType.Char,1), 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.SettingItem; parameters[1].Value = model.SettingDescription; parameters[2].Value = model.SettingValue; parameters[3].Value = model.Memo; parameters[4].Value = model.Effective; parameters[5].Value = model.CreateUser; parameters[6].Value = model.CreateDate; parameters[7].Value = model.ModifyUser; parameters[8].Value = model.ModifyDate; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 刪除一條資料
/// </summary>
public bool Delete( string SettingItem) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from OTB_SYS_SystemSetting "); strSql.Append(" where SettingItem=@SettingItem "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50) }; parameters[0].Value = SettingItem; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_SystemSetting GetModel(string SettingItem) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" SettingItem,SettingDescription,SettingValue,Memo,Effective,CreateUser,CreateDate,ModifyUser,ModifyDate "); strSql.Append(" FROM [OTB_SYS_SystemSetting] "); strSql.Append(" WHERE SettingItem=@SettingItem "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50) }; parameters[0].Value = SettingItem; OT.Model.OTB_SYS_SystemSetting model = new OT.Model.OTB_SYS_SystemSetting(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["SettingItem"] != null && ds.Tables[0].Rows[0]["SettingItem"].ToString() != "") { model.SettingItem = ds.Tables[0].Rows[0]["SettingItem"].ToString(); } if (ds.Tables[0].Rows[0]["SettingDescription"] != null && ds.Tables[0].Rows[0]["SettingDescription"].ToString() != "") { model.SettingDescription = ds.Tables[0].Rows[0]["SettingDescription"].ToString(); } if (ds.Tables[0].Rows[0]["SettingValue"] != null && ds.Tables[0].Rows[0]["SettingValue"].ToString() != "") { model.SettingValue = ds.Tables[0].Rows[0]["SettingValue"].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]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "") { model.Effective = ds.Tables[0].Rows[0]["Effective"].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 SettingItem, string SettingDescription, string SettingValue, string Effective, string SortExpression) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" RowId,SettingItem,SettingDescription,SettingValue,Memo,Effective "); strSql.Append(" FROM "); strSql.Append(" ( "); strSql.Append(" SELECT "); strSql.Append(" CASE @SortExpression WHEN N'SettingItem' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingItem)) "); strSql.Append(" WHEN N'SettingItem DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingItem DESC)) "); strSql.Append(" WHEN N'SettingDescription' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingDescription)) "); strSql.Append(" WHEN N'SettingDescription DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingDescription DESC)) "); strSql.Append(" WHEN N'SettingValue' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingValue)) "); strSql.Append(" WHEN N'SettingValue DESC' THEN "); strSql.Append(" (ROW_NUMBER() OVER(Order BY SettingValue 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 SettingItem)) "); strSql.Append(" END AS RowId,SettingItem,SettingDescription,SettingValue,Memo,Effective "); strSql.Append(" FROM [OTB_SYS_SystemSetting] "); strSql.Append(" WHERE (SettingItem LIKE @SettingItem OR @SettingItem IS NULL OR @SettingItem='') "); strSql.Append(" AND (SettingDescription LIKE @SettingDescription OR @SettingDescription IS NULL OR SettingDescription IS NULL OR @SettingDescription='') "); strSql.Append(" AND (SettingValue LIKE @SettingValue OR @SettingValue IS NULL OR @SettingValue='' OR SettingValue IS NULL) "); strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') "); 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("@SettingItem", SqlDbType.NVarChar,50) ,new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100) ,new SqlParameter("@SettingValue", SqlDbType.NVarChar,200) ,new SqlParameter("@Effective", SqlDbType.Char,1) ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500) }; parameters[0].Value = StartRecordIndex; parameters[1].Value = EndRecordIndex; parameters[2].Value = "%" + SettingItem + "%"; parameters[3].Value = "%" + SettingDescription + "%"; parameters[4].Value = "%" + SettingValue + "%"; parameters[5].Value = Effective; parameters[6].Value = SortExpression; return DbHelperSQL.Query(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料總筆數
/// </summary>
public int GetListCount(string SettingItem, string SettingDescription, string SettingValue, string Effective) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" COUNT(0) "); strSql.Append(" FROM [OTB_SYS_SystemSetting] "); strSql.Append(" WHERE (SettingItem LIKE @SettingItem OR @SettingItem IS NULL OR @SettingItem='') "); strSql.Append(" AND (SettingDescription LIKE @SettingDescription OR @SettingDescription IS NULL OR SettingDescription IS NULL OR @SettingDescription='') "); strSql.Append(" AND (SettingValue LIKE @SettingValue OR @SettingValue IS NULL OR @SettingValue='' OR SettingValue IS NULL) "); strSql.Append(" AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') "); SqlParameter[] parameters = { new SqlParameter("@SettingItem", SqlDbType.NVarChar,50) ,new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100) ,new SqlParameter("@SettingValue", SqlDbType.NVarChar,200) ,new SqlParameter("@Effective", SqlDbType.Char,1) }; parameters[0].Value = "%" + SettingItem + "%"; parameters[1].Value = "%" + SettingDescription + "%"; parameters[2].Value = "%" + SettingValue + "%"; parameters[3].Value = Effective; return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters); }
/// <summary>
/// 獲得資料列表
/// </summary>
public DataSet GetAllList() { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT "); strSql.Append(" SettingItem,SettingDescription,SettingValue "); strSql.Append(" FROM [OTB_SYS_SystemSetting] "); strSql.Append(" WHERE Effective = 'Y' "); SqlParameter[] parameters = new SqlParameter[0]; return DbHelperSQL.Query(strSql.ToString(), parameters); }
public DataSet GetMitakeInfo() { StringBuilder strSql = new StringBuilder(); strSql.Append(" DECLARE @UserID NVARCHAR(50),"); strSql.Append(" @UserPSWD NVARCHAR(50)"); strSql.Append(" SET @UserID=(SELECT SettingValue FROM OTB_SYS_SystemSetting Where SettingItem='Mitake_UserID' And Effective='Y');"); strSql.Append(" SET @UserPSWD=(SELECT SettingValue FROM OTB_SYS_SystemSetting Where SettingItem='Mitake_UserPSWD' And Effective='Y');"); strSql.Append(" SELECT @UserID As UserID ,@UserPSWD As UserPSWD "); SqlParameter[] parameters = new SqlParameter[0]; return DbHelperSQL.Query(strSql.ToString(), parameters); } #endregion Method
}}
|
