c# - 使用 Linq-to-SQL 从存储过程中检索多个数据

Question

我正在使用 Silverlight 和 Linq-to-SQL 与数据库通信。

我有一个存储过程，它接收 2 个参数(PFOID 和 Quantity)和 Userid 并返回一个产品名称。

如果我们发送多个值，如多个 pfoid 和 quantity，它将返回多个产品名称，如下所示

存储过程看起来是这样的..

ALTER PROCEDURE [PFO].[PFOValidateUpdateData]
    @PfoIDs xml, -- list of PFO ID's
    @UserID uniqueidentifier --The Identity of the User making the call.
AS
BEGIN
    -- SET DEFAULT BEHAVIOR
    SET NOCOUNT ON          -- Performance: stops rows affected messages
    SET DEADLOCK_PRIORITY LOW   -- This SP to be the Deadlock victim

    -- Initialise Lock-Timeout and Deadlock vars for Insert
    DECLARE @iLockTimeoutRetries as int 
    DECLARE @iDeadLockRetries as int
    DECLARE @dtLockTimeoutSleepInterval as datetime 
    DECLARE @dtDeadlockSleepInterval as datetime
    DECLARE @iErrorNumber as int

    SET @iLockTimeoutRetries = 0 
    SET @iDeadLockRetries = 0 
    SET @dtLockTimeoutSleepInterval = sCommon.fnLockTimeoutSleepInterval()
    SET @dtDeadlockSleepInterval= sCommon.fnDeadlockSleepInterval()
    SET @iErrorNumber = 0

    -- procedure specific
    DECLARE @idoc as int
    DECLARE @IsBrightstarUser as bit

    RETRY:
    BEGIN TRY
        --Create Temp table to store stores!
        CREATE TABLE [#PFOList]
        (
            [PFOId] nvarchar(50),
            [Quantity] INT
        )

        --Create Temp table to store User stores!
        CREATE TABLE [#UserStoreList]
        (
            [StoreID_XRef]      nvarchar(50)
        )

       print CONVERT(varchar(1000), @PfoIDs)

        --Create Document
        EXEC sp_xml_preparedocument @idoc OUTPUT, @PfoIDs

        -- Append to new list of Store records
        INSERT INTO [#PFOList] (
                    [PFOId],
                    [Quantity]
                    )
        SELECT  [PFOID],[Quantity]
        FROM OPENXML (@idoc, 'ArrayOfString/string',2)
        WITH( [PFOID] nvarchar(50),[Quantity] [INT]) Stores
        --WHERE [PFOId] Is Not NULL

        -- Clean UP
        exec sp_xml_removedocument @iDoc

        -- are we dealing with a brightstar user?
        SET @IsBrightstarUser = CASE WHEN exists
            (SELECT *
            FROM dbo.aspnet_UsersInRoles AS uir inner join
            dbo.aspnet_Roles  AS roles ON uir.RoleId = roles.roleid
            WHERE roles.rolename = 'Brightstar Employee' and uir.userid = @userid)
            THEN 1 ELSE 0 END

         --Get User Storelist
         INSERT INTO [#UserStoreList] (
                    [StoreID_XRef]
                    )
         SELECT s.StoreId_XRef  
         FROM  PFO.UserStoreLink us(nolock)
         INNER JOIN PFO.Store  s(nolock)
         ON us.StoreId=s.StoreId
         where UserId=@UserID        

         --Select * from [#PFOList]
         --SELECT @IsBrightstarUser AS ISBrightstaruser
         --SELECT * from [#UserStoreList]

    --If BrightstarCustomer Update all the Quantities.
    IF @IsBrightstarUser=1
    BEGIN
               UPDATE 
                    PFO.PFO
                SET
                     IsBrightstarReviewComplete = 1
                    ,[ModifyingUsersID] = @UserID
                    ,[ModifiedDate] = getdate()
                    ,[PlannedQty] = pfol.[Quantity]
                    ,[BrightstarReviewedQty]=pfol.[Quantity]
                FROM
                    PFO.PFO as pfo
                    INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef           
                    INNER JOIN [#PFOList] as pfol on pfo.PFOId =  pfol.PFOId 
                WHERE @IsBrightstarUser = 1     
    END
    ELSE BEGIN    
           --Update Non Contrained Orders
           UPDATE 
                PFO.PFO
            SET
                 [ModifyingUsersID] = @UserID
                ,[ModifiedDate] = getdate()
                ,[PlannedQty] = pfol.[Quantity]
            FROM
                PFO.PFO (nolock) as pfo
                INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef           
                INNER JOIN [#PFOList] as pfol on pfo.PFOId =  pfol.PFOId 
            WHERE pfo.IsBrightstarReviewComplete=1  AND IsConstraint=0

           --SELECT * from PFO.PFO (nolock) where PFOId='04676723-2afb-49ff-9fa1-0131cabb407c'

        --Update Contrained Orders
        --Get Existing quantities for the User
            CREATE TABLE #ExistingProductQuantity
            (
              [PfoID]  nvarchar(100)  
             ,[Product]  nvarchar(255) 
             ,[PlannedQty]  INT 
             ,[BrightstarReviewedQty]  INT    
            )

            CREATE TABLE #CustProductQuantity
            (
              [Product]  nvarchar(255)
             ,[IsUpdatable]  BIT    
            )

            INSERT INTO #ExistingProductQuantity
            ( [PfoID],[Product],[PlannedQty],[BrightstarReviewedQty])
            SELECT PFOId,InventoryId,PlannedQty,BrightstarReviewedQty
            FROM PFO.PFO as pfo
            INNER JOIN [#UserStoreList] as stores on pfo.StoreId_XRef=stores.StoreID_XRef   
            WHERE pfo.IsBrightstarReviewComplete=1 AND IsConstraint=1       

            UPDATE 
            #ExistingProductQuantity
            SET [PlannedQty]=pfol.[Quantity]
            FROM #ExistingProductQuantity eoq
            INNER JOIN [#PFOList] as pfol on eoq.PFOId =  pfol.PFOId 

            INSERT INTO #CustProductQuantity
            (  [Product],[IsUpdatable] )
            SELECT 
            [Product],
            CASE WHEN SUM(PlannedQty)<=SUM(BrightstarReviewedQty) THEN 1 ELSE 0 END
            FROM #ExistingProductQuantity
            GROUP BY [Product]

            --SELECT * from #ExistingProductQuantity
            --SELECT * from #CustProductQuantity

           --Update the products that can be updatable
            UPDATE 
                PFO.PFO
            SET
                 [ModifyingUsersID] = @UserID
                ,[ModifiedDate] = getdate()
                ,[PlannedQty] = pfol.[Quantity]
            FROM
                PFO.PFO as pfo
                INNER JOIN #UserStoreList as stores on pfo.StoreId_XRef=stores.StoreID_XRef         
                INNER JOIN #PFOList as pfol on pfo.PFOId =  pfol.PFOId 
                INNER JOIN #CustProductQuantity as pr on pr.Product=pfo.InventoryId 
            WHERE pfo.IsBrightstarReviewComplete=1 AND pr.IsUpdatable=1 AND IsConstraint=1

           --Return the products that are not updatabele    
             select  [Product]          
             FROM #CustProductQuantity
             where [IsUpdatable]=0
    END 
    END TRY
    BEGIN CATCH
        -- Get the ErrorNumber
        Set @iErrorNumber = ERROR_NUMBER()

        --Handle Deadlock situation (Deletes, Inserts & Updates)
        IF @iErrorNumber = 1205  
        BEGIN
            -- If we have not made enough attempts to break the lock
            IF @iDeadLockRetries < sCommon.fnMaxDeadlockRetries()
            BEGIN
                -- Increment the Attempt count
                SET @iDeadLockRetries = @iDeadLockRetries + 1
                -- Pause to allow the deadlock contention to clear
                WAITFOR DELAY @dtDeadlockSleepInterval
                GOTO RETRY
            END
        END

        -- Handle Lock Timeout situation (Deletes, Inserts & Updates)
        IF @iErrorNumber = 1222  
        BEGIN       
            -- If we have not made enough attempts to break the Deadlock
            IF @iLockTimeoutRetries < sCommon.fnMaxLockTimeoutRetries()
            BEGIN
                -- Increment the Attempt count
                SET @iLockTimeoutRetries = @iLockTimeoutRetries + 1
                -- Pause to allow the lock contention to clear
                WAITFOR DELAY @dtLockTimeoutSleepInterval
                GOTO RETRY
            END
        END

        exec Common.RethrowError 
    END CATCH
END

结果如下..

 Product
 6435LVWK-360-CD819E3
 NSCHI535C1097I360-4C 
 NSCHU485C1819I360-0C

 Return Value
 0

我的Linq-to-SQL连接是这样的

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="PFO.PFOValidateUpdateData")]
    public int PFOValidateUpdateData([global::System.Data.Linq.Mapping.ParameterAttribute(Name = "PfoIDs", DbType = "Xml")] System.Xml.Linq.XElement pfoIDs, [global::System.Data.Linq.Mapping.ParameterAttribute(Name = "UserID", DbType = "UniqueIdentifier")] System.Nullable<System.Guid> userID)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pfoIDs, userID);
        return ((int)(result.ReturnValue));
    }

我试图从存储过程中检索所有数据，但是当我调试它时，返回值为“o”..

如果你能帮我检索存储过程返回的所有数据，我将不胜感激......非常感谢......

Answer 1

如果您的存储过程返回 nvarchar 的集合，那么您的 Linq2Sql 方法的签名不正确。它不应该返回一个整数，而是一个 ISingleResult。 .

所以正确的签名是:

public ISingleResult<string> PFOValidateUpdateData(...)
{
    IExecuteResult result = this....;
    return (ISingleResult<string>)result.ReturnValue;
}

var products = PFOValidateUpdateData(...).ToList();

如果您想在存储过程中返回多个 SELECT 的结果，则必须使用 IMultipleResults .