public bool Update(String userID, String roleID)
{
var hs = new Hashtable();
var strInsertSql = new StringBuilder();
strInsertSql.Append("INSERT INTO tbLOG_Role_User(");
strInsertSql.Append("User_ID,Role_ID)");
strInsertSql.Append(" SELECT @User_ID,B.ID FROM (");
strInsertSql.Append(" SELECT CONVERT(XML, '<v>' + REPLACE(@Role_ID, ',', '</v><v>')+ '</v>') AS ID) A OUTER APPLY (");
strInsertSql.Append(" SELECT ID = N.v.value('.', 'VARCHAR(1000)') FROM A.ID.nodes('/v') N ( v )) B ");
strInsertSql.Append("LEFT JOIN dbo.tbLOG_Role_User RU ON RU.Role_ID = B.ID AND RU.User_ID = @User_ID WHERE RU.Role_ID IS NULL ");
SqlParameter[] parametersInsert =
{
new SqlParameter("@User_ID", SqlDbType.VarChar, 50),
new SqlParameter("@Role_ID", SqlDbType.VarChar, 1000)
};
parametersInsert[0].Value = userID.ToUpper();
parametersInsert[1].Value = roleID;
hs.Add(strInsertSql, parametersInsert);
var strDeleteSql = new StringBuilder();
strDeleteSql.Append("DELETE FROM dbo.tbLOG_Role_User WHERE User_ID = @User_ID AND Role_ID NOT IN (");
strDeleteSql.Append(roleID);
strDeleteSql.Append(")");
SqlParameter[] parametersDelete =
{
new SqlParameter("@User_ID", SqlDbType.VarChar, 50)
};
parametersDelete[0].Value = userID.ToUpper();
hs.Add(strDeleteSql, parametersDelete);
DbHelperSql.ExecuteSqlTran(DbHelperSql.DefaultUpdateConn, hs);
return(true);
}
//public bool Update(int programID, string buttonID,string Url)
//{
// var strDeleteSql = new StringBuilder();
// strDeleteSql.Append("DELETE FROM dbo.tbLOG_Program_Button WHERE Program_ID = @Program_ID AND Button_ID IN (@Button_ID)");
// SqlParameter[] parametersDelete = {
// new SqlParameter("@Program_ID", SqlDbType.VarChar, 50),
// new SqlParameter("@Button_ID", SqlDbType.VarChar, 1000)};
// parametersDelete[0].Value = programID;
// parametersDelete[1].Value = buttonID;
// var strInsertSql = new StringBuilder();
// strInsertSql.Append("INSERT INTO tbLOG_Program_Button(");
// strInsertSql.Append("Program_ID,Button_ID,Url)");
// strInsertSql.Append(" SELECT @Program_ID,B.ID,@Url FROM (");
// strInsertSql.Append(" SELECT CONVERT(XML, '<v>' + REPLACE(@Button_ID, ',', '</v><v>')+ '</v>') AS ID) A OUTER APPLY (");
// strInsertSql.Append(" SELECT ID = N.v.value('.', 'VARCHAR(1000)') FROM A.ID.nodes('/v') N ( v )) B ");
// strInsertSql.Append("LEFT JOIN dbo.tbLOG_Program_Button PB ON PB.Button_ID = B.ID AND PB.Program_ID = @Program_ID WHERE PB.Button_ID IS NULL ");
// SqlParameter[] parametersInsert = {
// new SqlParameter("@Program_ID", SqlDbType.VarChar, 50),
// new SqlParameter("@Button_ID", SqlDbType.VarChar, 200),
// new SqlParameter("@Url", SqlDbType.VarChar, 100)};
// parametersInsert[0].Value = programID;
// parametersInsert[1].Value = buttonID;
// parametersInsert[2].Value = Url;
// var hs = new Hashtable();
// hs.Add(strDeleteSql, parametersDelete);
// hs.Add(strInsertSql, parametersInsert);
// try
// {
// DbHelperSql.ExecuteSqlTran(DbHelperSql.DefaultUpdateConn, hs);
// }
// catch (Exception ex)
// {
// return false;
// }
// return true;
//}
public bool Update(int programID, string buttonID, string Url)
{
var strSql = new StringBuilder();
strSql.Append("DELETE FROM dbo.tbLOG_Program_Button WHERE Program_ID = @Program_ID AND Button_ID IN (@Button_ID);");
strSql.Append("INSERT INTO tbLOG_Program_Button(");
strSql.Append("Program_ID,Button_ID,Url)");
strSql.Append(" SELECT @Program_ID,B.ID,@Url FROM (");
strSql.Append(" SELECT CONVERT(XML, '<v>' + REPLACE(@Button_ID, ',', '</v><v>')+ '</v>') AS ID) A OUTER APPLY (");
strSql.Append(" SELECT ID = N.v.value('.', 'VARCHAR(1000)') FROM A.ID.nodes('/v') N ( v )) B ");
strSql.Append("LEFT JOIN dbo.tbLOG_Program_Button PB ON PB.Button_ID = B.ID AND PB.Program_ID = @Program_ID WHERE PB.Button_ID IS NULL;");
SqlParameter[] parameters =
{
new SqlParameter("@Program_ID", SqlDbType.VarChar, 50),
new SqlParameter("@Button_ID", SqlDbType.VarChar, 200),
new SqlParameter("@Url", SqlDbType.VarChar, 100)
};
parameters[0].Value = programID;
parameters[1].Value = buttonID;
parameters[2].Value = Url;
var hs = new Hashtable();
hs.Add(strSql, parameters);
DbHelperSql.ExecuteSqlTran(DbHelperSql.DefaultUpdateConn, hs);
return(true);
}
/// <summary>
/// 保存码表，涉及增、删、改操作
/// </summary>
/// <param name="hs"></param>
/// <returns></returns>
/// <remarks></remarks>
public bool Save(Hashtable hs)
{
DbHelperSql.ExecuteSqlTran(DbHelperSql.DefaultQueryConn, hs);
return(true);
}