asp.net - 在gridview中,在存储过程中循环并填充

  显示原文与译文双语对照的内容

目的:根据特定的因素/权限选择属性并在Gridview中显示

错误/问题:

我需要'循环'来只显示Gridview中用户的有效属性

SQL中的代码


USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[spSafeguardingActionPropertyByPermission]

@RegionID bigint
,@EmployeeID varchar(max)
,@PropertyID varchar(max)

AS

BEGIN

-- Easier one first
--Head Office, they see all

If @RegionID = 1 --then -- Head office users
 BEGIN
 SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE 
 FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
 WHERE TblA.RegionID> 0 and TblA.PropertyID LIKE '%' + @PropertyID + '%'
 END
ELSE 
--Regional Manager who is not Region 1 (Head office) 
If @PropertyID = 99 --then

BEGIN 
 ---- First we want to see what properties the employee has permission to view
 -- Note @RegionID is used here to filter 

 SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE 
 FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
 WHERE TblA.RegionID = @RegionID and TblA.PropertyID LIKE '%' + @PropertyID + '%' 
END 

ELSE


--Not regional manager so only has access to specific sites
If @RegionID> 1 and @PropertyID <> 99 --then
--99 means they are not a regional manager

 BEGIN 
 ---- First we want to see what properties the employee has permission to view
 SELECT EmployeeID, PropertyID as tbPD_PropertyID 
 FROM tblPropertyViewPermissions
 WHERE EmployeeID = @EmployeeID 

 --loop here?? to get only those the user has access to
 SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE 
 FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
 -- END here??
 -- what to do here???!!! 
 -- Need to filter by PropertyID(s) that the user can see
 WHERE TblA.RegionID> 0 and TblA.PropertyID LIKE '%' + @PropertyID + '%' 
 END

END


 -- If SQL can/can not have If else (????) then capture all and show nothing with error message?

我已经看过一些我曾经使用过的but,我也看到了控制参数


<asp:SqlDataSource ID="SqlDataSource2" runat="server"
 ConnectionString="<%$ ConnectionStrings:GWmySQL %>"
 SelectCommand="spSafeguardingActionPropertyByPermission"
 SelectCommandType="StoredProcedure">

 <SelectParameters> 
 <asp:SessionParameter DefaultValue="" Name="EmployeeID"
 SessionField="EmployeeID" Type="string"/>
 <asp:SessionParameter DefaultValue="" Name="PropertyID"
 SessionField="PropertyID" Type="string"/>
 <asp:SessionParameter DefaultValue="" Name="RegionID"
 SessionField="RegionID" Type="string"/>
 </SelectParameters>
</asp:SqlDataSource>

时间: 作者:

我可以告诉你,你不需要循环,你可以使用一个查询来执行这里操作:


SELECT TblA.PropertyID as PId, 
 TblA.Propertyname as PNa, 
 TblB.FireSafety as FireSafety1, 
 TblB.DisplayScreenEquipment as DSE 
FROM TbPropertyDetails as TblA 
 INNER JOIN TbPropertyDetailsSafeguarding as TblB 
 ON TblA.PropertyID = TblB.PropertyID 
WHERE TblA.RegionID> 0 
AND TblA.PropertyID LIKE '%' + @PropertyID + '%' 
AND EXISTS
 ( SELECT 1
 FROM tblPropertyViewPermissions AS pvp
 WHERE pvp.EmployeeID = @EmployeeID 
 AND pvp.PropertyID = TblA.PropertyID
 );

子句只是检查用户是否拥有查看作为参数传递的属性的权限。

作者:
...