|
|
using System;using System.Data;using System.Text;using System.Data.SqlClient;using OT.IDAL;using DBUtility;//Please add references
using System.Collections.Generic;namespace OT.SQLServerDAL{ /// <summary>
/// 資料訪問類:OTB_SYS_Authorize
/// </summary>
public partial class OTB_SYS_Authorize : IOTB_SYS_Authorize { public OTB_SYS_Authorize() { } #region Method
/// <summary>
/// 是否存在該記錄
/// </summary>
public bool Exists( string RuleID, string ProgramID) { int rowsAffected; StringBuilder strSql = new StringBuilder(); strSql.Append("select count(1) from OTB_SYS_Authorize"); strSql.Append(" where RuleID=@RuleID and ProgramID=@ProgramID "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200) }; parameters[0].Value = RuleID; parameters[1].Value = ProgramID;
return DbHelperSQL.Exists(strSql.ToString(), parameters); //int result = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Exists", parameters, out rowsAffected);
//if (result == 1)
//{
// return true;
//}
//else
//{
// return false;
//}
}
/// <summary>
/// 增加一條資料
/// </summary>
public bool Add(OT.Model.OTB_SYS_Authorize model) { int rowsAffected; StringBuilder strSql = new StringBuilder(); strSql.Append(" INSERT INTO [OTB_SYS_Authorize]( "); strSql.Append(" [RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] "); strSql.Append(" )VALUES( "); strSql.Append(" @RuleID,@ProgramID,@AllowRight,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE() ) "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200), new SqlParameter("@AllowRight", SqlDbType.VarChar,200), new SqlParameter("@Memo", SqlDbType.Text), 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.RuleID; parameters[1].Value = model.ProgramID; parameters[2].Value = model.AllowRight; parameters[3].Value = model.Memo; parameters[4].Value = model.CreateUser; parameters[5].Value = model.CreateDate; parameters[6].Value = model.ModifyUser; parameters[7].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_Authorize model) { int rowsAffected = 0; StringBuilder strSql = new StringBuilder(); strSql.Append(" update [OTB_SYS_Authorize] set "); strSql.Append("[ProgramID]=@ProgramID,[AllowRight]=@AllowRight,[Memo]=@Memo,[ModifyUser]=@ModifyUser,[ModifyDate]=GETDATE() "); strSql.Append(" where [RuleID]=@RuleID "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200), new SqlParameter("@AllowRight", SqlDbType.VarChar,200), new SqlParameter("@Memo", SqlDbType.Text), 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.RuleID; parameters[1].Value = model.ProgramID; parameters[2].Value = model.AllowRight; parameters[3].Value = model.Memo; parameters[4].Value = model.CreateUser; parameters[5].Value = model.CreateDate; parameters[6].Value = model.ModifyUser; parameters[7].Value = model.ModifyDate;
//DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Update", parameters, out rowsAffected);
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 刪除一條資料
/// </summary>
public bool Delete( string RuleID, string ProgramID) { int rowsAffected = 0; StringBuilder strSql = new StringBuilder(); strSql.Append(" DELETE [TB_SYS_Authorize] "); strSql.Append(" WHERE RuleID=@RuleID and ProgramID=@ProgramID "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200) }; parameters[0].Value = RuleID; parameters[1].Value = ProgramID; //DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Delete", parameters, out rowsAffected);
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 得到一個對象實體
/// </summary>
public OT.Model.OTB_SYS_Authorize GetModel( string RuleID, string ProgramID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" DECLARE @AllowRight NVARCHAR(4000) "); strSql.Append(" SET @AllowRight='' "); strSql.Append(" SELECT @AllowRight=@AllowRight+'|'+RTRIM(AllowRight) "); //看看是不是能去掉重複的
strSql.Append(" FROM [OTB_SYS_Authorize] "); strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) "); strSql.Append(" and ProgramID=@ProgramID "); strSql.Append(" PRINT @AllowRight "); strSql.Append(" SELECT DISTINCT "); strSql.Append(" @RuleID AS RuleID,A.ProgramID,@AllowRight AS AllowRight,'' AS Memo,'' AS CreateUser,'' AS CreateDate,'' AS ModifyUser,'' AS ModifyDate,P.Effective ");//Add by Alina 20141029 添加查詢出來一個程式的狀態欄位
strSql.Append(" FROM [OTB_SYS_Authorize] A "); strSql.Append(" INNER JOIN "); strSql.Append(" [OTB_SYS_ProgramList] P "); strSql.Append(" ON A.ProgramID = P.ProgramID "); strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) "); strSql.Append(" and A.ProgramID=@ProgramID AND P.ProgramType <> 'S' "); SqlParameter[] parameters = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200) }; parameters[0].Value = RuleID; parameters[1].Value = ProgramID;
OT.Model.OTB_SYS_Authorize model = new OT.Model.OTB_SYS_Authorize(); //DataSet ds = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_GetModel", parameters, "ds");
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["RuleID"] != null && ds.Tables[0].Rows[0]["RuleID"].ToString() != "") { model.RuleID = ds.Tables[0].Rows[0]["RuleID"].ToString(); } if (ds.Tables[0].Rows[0]["ProgramID"] != null && ds.Tables[0].Rows[0]["ProgramID"].ToString() != "") { model.ProgramID = ds.Tables[0].Rows[0]["ProgramID"].ToString(); } if (ds.Tables[0].Rows[0]["AllowRight"] != null && ds.Tables[0].Rows[0]["AllowRight"].ToString() != "") { model.AllowRight = ds.Tables[0].Rows[0]["AllowRight"].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()); } if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "") { model.ProgramStatus = ds.Tables[0].Rows[0]["Effective"].ToString(); } return model; } else { return null; } }
/// <summary>
/// 儲存權限表
/// </summary>
public bool Save(OT.Model.OTB_SYS_Authorize model) { List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); CommandInfo o_CommandInfo;
//把該角色下所有的權限給刪除掉先
StringBuilder strSql = new StringBuilder(); strSql.Append(" DELETE [OTB_SYS_Authorize] "); strSql.Append(" WHERE RuleID=@RuleID "); SqlParameter[] parametersDel = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200) }; parametersDel[0].Value = model.RuleID; parametersDel[1].Value = model.ProgramID; //DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Delete", parametersDel);
o_CommandInfo = new CommandInfo(); //o_CommandInfo.CommandText = "OSP_OTB_SYS_Authorize_Delete";
o_CommandInfo.CommandText = strSql.ToString(); o_CommandInfo.Parameters = parametersDel; lstCommandInfo.Add(o_CommandInfo);
//然後再插入新的值
foreach (OT.Model.OTB_SYS_Authorize ChildModel in model.AuthorizeList) { StringBuilder strSql2 = new StringBuilder(); strSql2.Append(" INSERT INTO [OTB_SYS_Authorize]( "); strSql2.Append(" [RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] "); strSql2.Append(" )VALUES( "); strSql2.Append(" @RuleID,@ProgramID,@AllowRight,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE() ) "); SqlParameter[] parametersAdd = { new SqlParameter("@RuleID", SqlDbType.VarChar,20), new SqlParameter("@ProgramID", SqlDbType.VarChar,200), new SqlParameter("@AllowRight", SqlDbType.VarChar,200), new SqlParameter("@Memo", SqlDbType.Text), new SqlParameter("@CreateUser", SqlDbType.VarChar,50), new SqlParameter("@CreateDate", SqlDbType.DateTime), new SqlParameter("@ModifyUser", SqlDbType.VarChar,50), new SqlParameter("@ModifyDate", SqlDbType.DateTime)}; parametersAdd[0].Value = ChildModel.RuleID; parametersAdd[1].Value = ChildModel.ProgramID; parametersAdd[2].Value = ChildModel.AllowRight; parametersAdd[3].Value = ChildModel.Memo; parametersAdd[4].Value = ChildModel.CreateUser; parametersAdd[5].Value = ChildModel.CreateDate; parametersAdd[6].Value = ChildModel.ModifyUser; parametersAdd[7].Value = ChildModel.ModifyDate; o_CommandInfo = new CommandInfo(); //o_CommandInfo.CommandText = "OSP_OTB_SYS_Authorize_Add";
o_CommandInfo.CommandText = strSql2.ToString(); o_CommandInfo.Parameters = parametersAdd; lstCommandInfo.Add(o_CommandInfo); }
//if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0) { return true; } else { return false; } }
public bool GetDirectRoleByRoleSource(string strRoleSource, string strRoleDirect, string CreateUser) { int effectrow = 0; StringBuilder strSql = new StringBuilder(); strSql.Append(" DELETE [OTB_SYS_Authorize] WHERE [RuleID]=@DirectRoleID "); strSql.Append(" INSERT INTO [OTB_SYS_Authorize] "); strSql.Append(" ([RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]) "); strSql.Append(" SELECT @DirectRoleID,[ProgramID],[AllowRight],[Memo],@CreateUser,GETDATE(),[ModifyUser],[ModifyDate] "); strSql.Append(" FROM [OTB_SYS_Authorize] "); strSql.Append(" WHERE [RuleID]=@RoleSourceID "); SqlParameter[] parameter = { new SqlParameter("@RoleSourceID",SqlDbType.VarChar,20), new SqlParameter("@DirectRoleID",SqlDbType.VarChar,20), new SqlParameter("@CreateUser",SqlDbType.VarChar,20) }; parameter[0].Value = strRoleSource; parameter[1].Value = strRoleDirect; parameter[2].Value = CreateUser; //DbHelperSQL.RunProcedure("OSP_GetDirectRoleSourceID", parameter, out effectrow);
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameter); if (rows > 0) { return true; } else { return false; } }
/// <summary>
/// 根據登入者判斷權限大小
/// </summary>
public DataSet GetListByRuleID(string strMemberID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT RuleID,ProgramID,AllowRight "); strSql.Append(" FROM [OTB_SYS_Authorize] "); strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) "); SqlParameter[] parameters = { new SqlParameter("@RuleID",SqlDbType.NVarChar,50)}; parameters[0].Value = strMemberID; return DbHelperSQL.Query(strSql.ToString(), parameters); //return DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_GetListByRuleID", parameter, "ds");
}
#endregion Method
#region MethodEx
#endregion MethodEx
}}
|
