rolesp.cs

来自「ASP.NET 2.0动态网站设计实例源代码,本书介绍了ASP.NET2.0的基」· CS 代码 · 共 143 行

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;


public partial class StoredProcedures
{
 //根据ID获取可访问栏目ID
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetItemsByRoleID(int pRoleID)
 {
  string SQL = @"SELECT [ItemID] 
                 FROM [RoleItemMap]
                 WHERE [RoleID]=@RoleID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@RoleID", SqlDbType.Int);

  parms[0].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //添加角色名称，并返回角色ID
 [SqlProcedure]
 public static void AddRole(string pRoleName)
 {
  string SQL = @"INSERT INTO [Role]
                       ([RoleName])
                 VALUES
                       (@RoleName);
                 SELECT @@IDENTITY";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@RoleName", SqlDbType.NVarChar,50);

  parms[0].Value = pRoleName;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //添加角色规则
 [SqlProcedure]
 public static void AddRoleItem(int pRoleID, string pItemID)
 {
  string SQL = "";
  string[] ItemID = pItemID.Split(new char[] { ',' });
  for (int Counter = 0; Counter < ItemID.GetLength(0); Counter++)
  {
   SQL += @"INSERT INTO [LocaleBBS].[dbo].[RoleItemMap]
           ([RoleID]
           ,[ItemID])
     VALUES
           (@RoleID
           ," + ItemID[Counter].ToString() + @");";
  }
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@RoleID", SqlDbType.Int);

  parms[0].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //获取角色列表
 [SqlProcedure]
 public static void GetRoleList()
 {
  string SQL = @"SELECT * FROM [Role]
                 WHERE [RoleName]<>'管理员';";
  
  DBTools.CreateStoredProcedure(SQL);
 }
 //修改角色名称
 [SqlProcedure]
 public static void ChangeRoleNameByID(int pRoleID, string pRoleName)
 {
  string SQL = @"UPDATE [Role]
                 SET [RoleName] = @RoleName
                 WHERE [RoleID]=@RoleID";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@RoleID", SqlDbType.Int);
  parms[1] = new SqlParameter("@RoleName", SqlDbType.NVarChar, 50);

  parms[0].Value = pRoleID;
  parms[1].Value = pRoleName;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //修改角色规则
 [SqlProcedure]
 public static void ChangeRoleByID(int pRoleID, string pItemID)
 {
  string SQL = @"DELETE FROM [RoleItemMap]
                 WHERE [RoleID]=@RoleID;";
  SqlParameter[] parms = new SqlParameter[2];
  parms[0] = new SqlParameter("@RoleID", SqlDbType.Int);

  string[] ItemID = pItemID.Split(new char[] { ',' });
  for (int Counter = 0; Counter < ItemID.GetLength(0); Counter++)
  {
   SQL += @"INSERT INTO [LocaleBBS].[dbo].[RoleItemMap]
           ([RoleID]
           ,[ItemID])
     VALUES
           (@RoleID
           ," + ItemID[Counter].ToString() + @");";
  }

  DBTools.CreateStoredProcedure(SQL, parms);
  parms[0].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
 //根据ID获取角色名称
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void GetRoleNameByRoleID(int pRoleID)
 {
  string SQL = @"SELECT [RoleName] 
                 FROM [Role]
                 WHERE [RoleID]=@RoleID";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@RoleID", SqlDbType.Int);

  parms[0].Value = pRoleID;

  DBTools.CreateStoredProcedure(SQL, parms);
 }

 //判断指定的角色名称是否存在
 [Microsoft.SqlServer.Server.SqlProcedure]
 public static void IsRoleNameExist(string pRoleName)
 {
  string SQL = @"SELECT [RoleID] 
                 FROM [Role]
                 WHERE [RoleName]=@RoleName";
  SqlParameter[] parms = new SqlParameter[1];
  parms[0] = new SqlParameter("@RoleName", SqlDbType.NVarChar,50);

  parms[0].Value = pRoleName;

  DBTools.CreateStoredProcedure(SQL, parms);
 }
};