/// <summary>
/// 以事务方式执行拼接好的sql语句组
/// </summary>
public static void RunSqlStatement_SaveUserMenuButton(int intUserNo)
{
string strSql = @"delete from BS_UserMenuButton where userNo= " + intUserNo + " ";
strSqlLlist.Insert(0, strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 获取存储过程参数列表
/// </summary>
/// <param name="strProcedureName">存储过程名</param>
/// <returns></returns>
public static void CreateProcedureParaList(string strQuerySqlId, string strProcedureName)
{
strSqlLlist.Clear();
string strSql =
"delete from BS_QuerySqlParameter " +
"where convert(varchar(10), querySqlId)='" + strQuerySqlId + "' " +
"and parametersName + parametersCatg not in ( " +
"select a.name + b.name " +
"from syscolumns a " +
"left join systypes b on a.xtype = b.xusertype " +
"where id = (select id from sysobjects where upper(name) = '" + strProcedureName.ToUpper().Trim() + "')" +
"and a.isoutparam = 0) ";
strSqlLlist.Add(strSql);
strSql =
"insert into BS_QuerySqlParameter(QuerySqlId,ParametersText,ParametersName,ParametersCatg) " +
"select '" + strQuerySqlId + "',a.name as paraText,a.name as paraName,b.name paraCatg " +
"from syscolumns a " +
"left join systypes b on a.xtype = b.xusertype " +
"where a.id = (select id from sysobjects where upper(name) = '" + strProcedureName.ToUpper().Trim() + "') " +
"and '" + strQuerySqlId + "'+a.name+b.name not in ( " +
"select convert(varchar(10), querySqlId)+parametersName+parametersCatg from BS_QuerySqlParameter) " +
"and a.isoutparam=0 ";
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 以事务方式执行拼接好的sql语句组
/// </summary>
public static void RunSqlStatement()
{
BaseSQL.ExecuteSqlTran(strSqlLlist);
IDataParameter[] parameters = new System.Data.IDataParameter[1]; //实例化参数对象
parameters[0] = new SqlParameter("@returnMessage", string.Empty); //参数对象赋值
BaseSQL.RunProcedure("FrmRight_RefreshButtonInfo", parameters); //执行存储过程，刷新button相关表
}
/// <summary>
/// 保存角色权限（执行事务）
/// </summary>
public static void RunSqlStatement_SaveRoleMenuButton(string strRoleNo)
{
string strSql = @"delete from BS_RoleMenu where RoleNo= '" + strRoleNo + "' ";
strSqlLlist.Insert(0, strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 删除与【角色】相关数据
/// </summary>
/// <param name="strRoleNo"></param>
public static void DeleteRoleCorrelationData(string strRoleNo)
{
strSqlLlist.Clear();
string strSql = @"delete from BS_RoleMenu where RoleNo like '" + strRoleNo + "'";
strSqlLlist.Add(strSql);
strSql = @"delete from BS_RoleUser where RoleNo like '" + strRoleNo + "'";
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 保存为用户设定的角色
/// </summary>
public static void SaveRoleUser(string strRoleNo, int intUserNo)
{
strSqlLlist.Clear();
string strSql = @"delete from BS_RoleUser where UserNo= " + intUserNo + " ";
strSqlLlist.Insert(0, strSql);
strSql = @"insert into BS_RoleUser(RoleNo,UserNo) values('" + strRoleNo + "'," + intUserNo + ") ";
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
public static ArrayList strSqlLlist = new ArrayList();//存储生成的SQL语句
/// <summary>
/// 删除与【用户信息】相关数据
/// </summary>
/// <param name="strRoleNo"></param>
public static void DeleteUserInfoCorrelationData(int strUserNo)
{
strSqlLlist.Clear();
string strSql = @"delete from BS_UserMenuButton where UserNo= " + strUserNo;
strSqlLlist.Add(strSql);
strSql = @"delete from BS_RoleUser where UserNo= " + strUserNo;
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 保存为用户设定的角色
/// </summary>
public static void SaveRoleUser(string strRoleNo, int intUserNo, int buttonPowerInt)
{
strSqlLlist.Clear();
string strSql = @"delete from BS_RoleUser where UserNo= " + intUserNo + " ";
strSqlLlist.Insert(0, strSql);
strSql = @"insert into BS_RoleUser(RoleNo,UserNo) values('" + strRoleNo + "'," + intUserNo + ") ";
strSqlLlist.Add(strSql);
strSql = string.Format("Update BS_UserInfo set ButtonPower = {1} where AutoId = {0}", intUserNo, buttonPowerInt);
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 删除与【菜单】相关数据
/// </summary>
public static void DeleteMenuCorrelationData(string strMenuName)
{
strSqlLlist.Clear();
string strSql = @"delete from BS_UserMenuButton " +
"where MenuButtonId in " +
"(select a1.AutoId from BS_MenuButton a1 where a1.MenuName like '" + strMenuName + "')";
strSqlLlist.Add(strSql);
strSql = @"delete from BS_MenuButton where MenuName like '" + strMenuName + "'";
strSqlLlist.Add(strSql);
strSql = @"delete from BS_RoleMenu where MenuName like '" + strMenuName + "'";
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 删除查询表相关数据
/// </summary>
/// <param name="iQuerySqlId"></param>
public static void DeleteQueryCorrelationData(int iQuerySqlId)
{
strSqlLlist.Clear();
string strSql =
"delete from BS_DataHeaderSetting where QuerySql=" + iQuerySqlId;
strSqlLlist.Add(strSql);
strSql =
"delete from BS_DataSetting where QuerySql=" + iQuerySqlId;
strSqlLlist.Add(strSql);
strSql =
"delete from BS_QuerySqlParameter where QuerySqlId=" + iQuerySqlId;
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 4.初始化DataHeaderSetting表
/// </summary>
/// <param name="iQuerySqlId"></param>
/// <param name="iTabIndex"></param>
public static void InitHeaderSetting(int iQuerySqlId, int iTabIndex)
{
strSqlLlist.Clear();
string strSql =
"delete from BS_DataHeaderSetting " +
"where DataSetting not in (select AutoId from BS_DataSetting) ";
strSqlLlist.Add(strSql);
//int iTmp = GetDataSettingId(iQuerySqlId, iTabIndex);
//strSql =
//"delete from BS_DataHeaderSetting " +
//"where dataSetting= " + iTmp + " " +
//"and HeaderName not in (select HeaderName from tmpColumns)";
//strSqlLlist.Add(strSql);
//strSql = "insert into BS_DataHeaderSetting(QuerySql,DataSetting,HeaderName,HeaderText) " +
// "select " + iQuerySqlId + "," + iTmp + ",HeaderName,HeaderText from tmpColumns where '" + iQuerySqlId.ToString() + iTmp.ToString() + "'+HeaderName not in (select convert(varchar(10),querySql)+convert(varchar(10),dataSetting)+headerName from BS_DataHeaderSetting) ";
//strSqlLlist.Add(strSql);
strSql = @"drop table tmpColumns ";//删除建立的临时表
strSqlLlist.Add(strSql);
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 以事务方式执行拼接好的sql语句组(保存对界面控件文本的遍历结果)
/// </summary>
public static void TraverseControlTextSave()
{
BaseSQL.ExecuteSqlTran(strSqlLlist);
}
/// <summary>
/// 3.执行事务
/// </summary>
public static void ExecSqlTran()
{
BaseSQL.ExecuteSqlTran(strSqlLlist);
}