更新交叉表的数据
有三个表:
表:TT_Roles
字段iRoleID sRoleName
表:TT_Users
字段iUserID sLoginName sPassword
表: TT_User_Role
字段iUserID iRoleID bHave
一个交叉查询的存储过程:
CREATE PROCEDURE dbo.GetUserRole
AS
BEGIN
--声明变量
DECLARE @SqlString nvarchar (4000) --放SQL语句
DECLARE @TempRoleID int --放RoleID
DECLARE @TempRoleName varchar(50) --放RoleName
SET @SqlString = N'select TT_Users.sLoginName 用户名,' --赋值
DECLARE Cursor_UserRole CURSOR FOR --声明游标
SELECT DISTINCT TT_User_Role.iRoleID,sRoleName
FROM TT_User_Role,TT_Roles
WHERE TT_User_Role.iRoleID = TT_Roles.iRoleID
OPEN Cursor_UserRole --打开游标
FETCH NEXT FROM Cursor_UserRole INTO @TempRoleID,@TempRoleName --赋值
WHILE @@FETCH_STATUS = 0 --如果下确执行,则循环下去
BEGIN
SET @SqlString = @SqlString + N'SUM(CASE iRoleID WHEN ' +
CAST(@TempRoleID AS varchar) + N'THEN bHave END) AS " '+
@TempRoleName + '",'
FETCH NEXT FROM Cursor_UserRole INTO @TempRoleID,@TempRoleName
END
CLOSE Cursor_UserRole
SET @SqlString = LEFT(@SqlString,LEN(@SqlString)-1)
SET @SqlString = @SqlString + N'FROM TT_User_Role,TT_Users WHERE TT_User_Role.iUserID = TT_Users.iUserID GROUP BY TT_Users.sLoginName'
EXEC sp_executesql @SqlString
END
在一个DataGrid中显示
问题是可以显示,但是怎么在DataGrid中更新和删除?