EF调用sp,EF自动生成返回类型

时间:2023-01-10 12:25:50

在sp中添加下面的红色部分,就是执行sp时的返回类型,后面在EF中添加该sp后,EF会在DBContext文件中,自动生成调用该sp的代码,包括返回类型等,如下:

public virtual ObjectResult<SP_CustomerRequestWriteoff_Result> SP_CustomerRequestWriteoff(Nullable<int> qrTransID, Nullable<int> campaignID, string openID, string buyList, string giftList)
        {
            var qrTransIDParameter = qrTransID.HasValue ?
                new ObjectParameter("qrTransID", qrTransID) :
                new ObjectParameter("qrTransID", typeof(int));
   
            var campaignIDParameter = campaignID.HasValue ?
                new ObjectParameter("campaignID", campaignID) :
                new ObjectParameter("campaignID", typeof(int));
   
            var openIDParameter = openID != null ?
                new ObjectParameter("openID", openID) :
                new ObjectParameter("openID", typeof(string));
   
            var buyListParameter = buyList != null ?
                new ObjectParameter("buyList", buyList) :
                new ObjectParameter("buyList", typeof(string));
   
            var giftListParameter = giftList != null ?
                new ObjectParameter("giftList", giftList) :
                new ObjectParameter("giftList", typeof(string));
   
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_CustomerRequestWriteoff_Result>("SP_CustomerRequestWriteoff", qrTransIDParameter, campaignIDParameter, openIDParameter, buyListParameter, giftListParameter);
        }

然后直接调用即可,如下:

public static Result ExportPeriodDetailReport(DateTime? beginDate, DateTime? endDate, string activityId, string storeCode, string flag)
        {
            using (var db = new SAISC_4S_PotentialEntities())
            {
                //调用存储过程
                if (flag == "1")
                {
                    var query = db.sp_Rpt_Act_Weekly_List(beginDate, endDate, activityId, storeCode).ToList();
                    return new Result(true, "", query);
                }
               
            }
        }

USE [FrisoFidpDB]
GO
/****** Object:  StoredProcedure [dbo].[SP_CustomerRequestWriteoff]    Script Date: 12/26/2016 14:45:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_CustomerRequestWriteoff]
    @qrTransID INT ,
    @campaignID INT ,
    @openID NVARCHAR ,
 --购买明细列表,格式:商品编号:数量,多个以英文逗号隔开,如51234:2,34532:1,9876:3
    @buyList NVARCHAR ,
 --赠品明细列表,格式:商品编号:数量,多个以英文逗号隔开,如51234:2,34532:1,9876:3
    @giftList NVARCHAR
AS
    BEGIN
        IF ( @qrTransID = NULL
             OR @qrTransID = 0
           )
            BEGIN
                DECLARE @sp_Result TABLE
                    (
                      errCode INT ,
                      errMsg NVARCHAR(200) ,
                      proCode NVARCHAR(50)
                    )
                SELECT  *
                FROM    @sp_Result
                RETURN
            END
   
        DECLARE @errcode INT ,
            @errmsg NVARCHAR(500) ,
            @giftCount INT ,
            @idx INT ,
            @customerID INT ,
            @qty INT ,
            @stockType INT ,
            @shopCode NVARCHAR(50) ,
            @cityID INT ,
            @proCode NVARCHAR(50) ,
            @stockQty INT ,
            @giftName NVARCHAR(50) ,
            @fmcCode NVARCHAR(50) ,
            @distributorCode NVARCHAR(200) ,
            @writeOffUserCode NVARCHAR(50) ,
            @writeOffTime DATETIME
        DECLARE @table TABLE
            (
              ID INT ,
              ProCode NVARCHAR(200) ,
              ProCount NVARCHAR(50) ,
              Qty INT ,
              Flag INT
            )
   
        SET @errcode = 0;
        SELECT  @shopCode = ShopCode ,
                @cityID = SalesCityId ,
                @fmcCode = GenUserCode ,
                @writeOffTime = WriteOffTime ,
                @writeOffUserCode = WriteOffUserCode
        FROM    dbo.IDreamTransaction
        WHERE   id = @qrTransID
 
        SELECT TOP 1
                @customerID = Id
        FROM    dbo.Customer
        WHERE   OpenId = @openID
  
  --核销条件检查
        IF ( LEN(@buyList) = 0
             OR LEN(@giftList) = 0
           )
            BEGIN
                SET @errcode = 1000;
                SET @errmsg = '购买明细和赠品明细不能为空';
            END
           
        IF ( @writeOffTime != NULL
             AND @writeOffUserCode != NULL
           )
            BEGIN
                SET @errcode = 9000;
                SET @errmsg = '此二维码已被使用过';
            END
 
  --设置当前会话的数据库锁超时时间为20秒
        SET LOCK_TIMEOUT 20000
 
        BEGIN TRY
            BEGIN TRAN trans
  
   --赠品库存检查
            IF ( @errcode = 0 )
                BEGIN
     --购买明细
                    INSERT  INTO @table
                            ( ID ,
                              ProCode ,
                              ProCount ,
                              Flag
                            )
                            SELECT  ID ,
                                    [key] ,
                                    value ,
                                    1
                            FROM    dbo.[Fun_GetKeyValueList](@buyList, ',',
                                                              ':')
   
     --赠品明细
                    INSERT  INTO @table
                            ( ID ,
                              ProCode ,
                              ProCount ,
                              Flag
                            )
                            SELECT  ID ,
                                    [key] ,
                                    value ,
                                    2
                            FROM    dbo.[Fun_GetKeyValueList](@giftList, ',',
                                                              ':')
   
                    UPDATE  @table
                    SET     Qty = CAST(ProCount AS INT)
                    WHERE   ISNUMERIC(ProCount) = 1
   
                    UPDATE  @table
                    SET     Qty = 0
                    WHERE   ISNUMERIC(ProCount) = 0
   
                    SELECT  @giftCount = COUNT(*)
                    FROM    @table
                    WHERE   Flag = 2
                    IF ( @giftCount > 0 )
                        BEGIN
                            SET @idx = 1;
                            WHILE ( @idx <= @giftCount )
                                BEGIN
                                    SELECT  @proCode = ProCode ,
                                            @qty = Qty
                                    FROM    @table
                                    WHERE   ID = @idx
     
                                    IF ( @qty > 0 )
                                        BEGIN
                                            SELECT  @stockType = m.StockType ,
                                                    @giftName = n.NameCN
                                            FROM    dbo.CampaignGift m
                                                    LEFT JOIN dbo.Product n ON m.GiftCode = n.Code
                                            WHERE   m.CampaignId = @campaignID
                                                    AND m.GiftCode = @proCode
      
           --库存类型,1-门店库存 2-城市库存 3-全国库存
                                            IF ( @stockType = 1 )
                                                BEGIN
                                                    SELECT  @stockQty = ISNULL(StockQty,
                                                              0)
                                                    FROM    dbo.IDreamStock
                                                            WITH ( UPDLOCK )
                                                    WHERE   CampaignId = @campaignID
                                                            AND GiftCode = @proCode
                                                            AND ShopCode = @shopCode
                                                            AND SalesCityId = @cityID
                                                END
                                            ELSE
                                                IF ( @stockType = 2 )
                                                    BEGIN
                                                        SELECT
                                                              @stockQty = ISNULL(StockQty,
                                                              0)
                                                        FROM  dbo.IDreamStock
                                                              WITH ( UPDLOCK )
                                                        WHERE CampaignId = @campaignID
                                                              AND GiftCode = @proCode
                                                              AND SalesCityId = @cityID
                                                    END
                                                ELSE
                                                    IF ( @stockType = 3 )
                                                        BEGIN
                                                            SELECT
                                                              @stockQty = ISNULL(StockQty,
                                                              0)
                                                            FROM
                                                              dbo.IDreamStock
                                                              WITH ( UPDLOCK )
                                                            WHERE
                                                              CampaignId = @campaignID
                                                              AND GiftCode = @proCode
                                                        END
      
           --库存不足直接返回调用方
                                            IF ( @qty > @stockQty )
                                                BEGIN
                                                    SET @errcode = 2000;
                                                    SET @errmsg = '赠品'
                                                        + @giftName + '库存不足';
                                                    BREAK;
                                                END
                                        END
     
                                    SET @idx += 1;
                                END
                        END
                END
  
   --核销操作
            IF ( @errcode = 0 )
                BEGIN
     --添加核销流水并更新库存
                    BEGIN TRY
                        SET @giftCount = 0;
                        SELECT  @giftCount = COUNT(*)
                        FROM    @table
                        WHERE   Flag = 2
                        IF ( @giftCount > 0 )
                            BEGIN
                                SET @idx = 1;
                                WHILE ( @idx <= @giftCount )
                                    BEGIN
                                        SELECT  @proCode = ProCode ,
                                                @qty = Qty
                                        FROM    @table
                                        WHERE   Flag = 2
                                                AND ID = @idx
      
                                        SELECT  @stockType = m.StockType ,
                                                @giftName = n.NameCN
                                        FROM    dbo.CampaignGift m
                                                LEFT JOIN dbo.Product n ON m.GiftCode = n.Code
                                        WHERE   m.CampaignId = @campaignID
                                                AND m.GiftCode = @proCode
      
          --更新库存,库存类型,1-门店库存 2-城市库存 3-全国库存
                                        IF ( @stockType = 1 )
                                            BEGIN
                                                UPDATE  IDreamStock
                                                SET     StockQty = StockQty
                                                        - @qty
                                                WHERE   CampaignId = @campaignID
                                                        AND GiftCode = @proCode
                                                        AND ShopCode = @shopCode
                                                        AND SalesCityId = @cityID
      
                                                SELECT TOP 1
                                                        @distributorCode = DistributorCode
                                                FROM    dbo.IDreamStock
                                                WHERE   CampaignId = @campaignID
                                                        AND GiftCode = @proCode
                                                        AND ShopCode = @shopCode
                                                        AND SalesCityId = @cityID
                                            END
                                        ELSE
                                            IF ( @stockType = 2 )
                                                BEGIN
                                                    UPDATE  IDreamStock
                                                    SET     StockQty = StockQty
                                                            - @qty
                                                    WHERE   CampaignId = @campaignID
                                                            AND GiftCode = @proCode
                                                            AND SalesCityId = @cityID
      
                                                    SELECT TOP 1
                                                            @distributorCode = DistributorCode
                                                    FROM    dbo.IDreamStock
                                                    WHERE   CampaignId = @campaignID
                                                            AND GiftCode = @proCode
                                                            AND SalesCityId = @cityID
                                                END
                                            ELSE
                                                IF ( @stockType = 3 )
                                                    BEGIN
                                                        UPDATE
                                                              IDreamStock
                                                        SET   StockQty = StockQty
                                                              - @qty
                                                        WHERE CampaignId = @campaignID
                                                              AND GiftCode = @proCode
      
                                                        SELECT TOP 1
                                                              @distributorCode = DistributorCode
                                                        FROM  dbo.IDreamStock
                                                        WHERE CampaignId = @campaignID
                                                              AND GiftCode = @proCode
                                                    END
      
          --添加核销流水
                                        IF ( @qty > 0 )
                                            BEGIN
                                                INSERT  INTO dbo.IDreamWriteOff
                                                        ( TransactionId ,
                                                          AppId ,
                                                          CampaignId ,
                                                          SalesCityId ,
                                                          GiftCode ,
                                                          GiftQuantity ,
                                                          ShopCode ,
                                                          FMCCode ,
                                                          CustomerId ,
                                                          OpenId ,
                                                          Remark ,
                                                          CreateTime ,
                                                          UpdateTime ,
                                                          DistributorCode ,
                                                          WriteOffTime ,
                                                          WriteOffType
                                )
                                                        SELECT
                                                              @qrTransID ,
                                                              0 ,
                                                              @campaignID ,
                                                              @cityID ,
                                                              @proCode ,
                                                              @qty ,
                                                              @shopCode ,
                                                              @fmcCode ,
                                                              ISNULL(@customerID,
                                                              0) ,
                                                              @openID ,
                                                              'idream核销' ,
                                                              GETDATE() ,
                                                              GETDATE() ,
                                                              @distributorCode ,
                                                              GETDATE() ,
                                                              '线上兑换'
                                            END
      
                                        SET @idx += 1;
                                    END
                            END
                    END TRY
                    BEGIN CATCH
                        SET @errcode = 3000;
                        SET @errmsg = '添加核销流水并更新库存时异常,信息为' + ERROR_MESSAGE()
                    END CATCH
   
     --添加购买记录
                    IF ( @errcode = 0 )
                        BEGIN TRY
                            SET @giftCount = 0;
                            SELECT  @giftCount = COUNT(*)
                            FROM    @table
                            WHERE   Flag = 1
                            IF ( @giftCount > 0 )
                                BEGIN
                                    SET @idx = 1;
                                    WHILE ( @idx <= @giftCount )
                                        BEGIN
                                            SELECT  @proCode = ProCode ,
                                                    @qty = Qty
                                            FROM    @table
                                            WHERE   Flag = 1
                                                    AND ID = @idx
      
                                            SELECT TOP 1
                                                    @distributorCode = DistributorCode
                                            FROM    dbo.IDreamStock
                                            WHERE   CampaignId = @campaignID
                                                    AND GiftCode = @proCode
     
                                            IF ( @qty > 0 )
                                                BEGIN
                                                    INSERT  INTO dbo.IDreamBuyDetail
                                                            ( TransactionId ,
                                                              ProductCode ,
                                                              Qty ,
                                                              Remark ,
                                                              CreateTime
                                 )
                                                            SELECT
                                                              @qrTransID ,
                                                              @proCode ,
                                                              @qty ,
                                                              'idream核销' ,
                                                              GETDATE()
                                                END
      
                                            SET @idx += 1;
                                        END
                                END
                        END TRY
                        BEGIN CATCH
                            SET @errcode = 4000;
                            SET @errmsg = '添加购买记录发生异常,信息为' + ERROR_MESSAGE()
                        END CATCH
   
     --更新二维码事物表
                    IF ( @errcode = 0 )
                        BEGIN TRY
                            UPDATE  dbo.IDreamTransaction
                            SET     WriteOffTime = GETDATE() ,
                                    WriteOffUserCode = @openID ,
                                    UpdateTime = GETDATE() ,
                                    ExpireTime = GETDATE() + 10
                            WHERE   Id = @qrTransID;
                        END TRY
                        BEGIN CATCH
                            SET @errcode = 5000;
                            SET @errmsg = '添加购买记录发生异常,信息为' + ERROR_MESSAGE()
                        END CATCH
                END
  
            IF ( @errcode = 0 )
                BEGIN
                    COMMIT TRAN trans
                END
            ELSE
                BEGIN
                    ROLLBACK TRAN trans
                    SET @errcode = 8000;
                    SET @errmsg = '核销过程中异常,操作回滚';
                END
        END TRY
        BEGIN CATCH
            ROLLBACK TRAN trans
            SET @errcode = 6000;
            SET @errmsg = '核销过程中异常,信息为' + ERROR_MESSAGE();
        END CATCH
 
        SELECT  @errcode AS errCode ,
                @errmsg AS errMsg ,
                @proCode AS proCode;
        RETURN 1;
    END