前端之家

  1. 首页
  2. 问答

尝试在C＃中执行存储过程参数实体框架EDMX模型时出错

• 问答

我正在尝试执行带有13个参数的存储过程，其中一些可以接受空值，而某些则始终是必需的。我收到两个确实接受null的日期参数的错误。

我遇到以下错误

  

System.Data.SqlClient.SqlException
  参数化查询'（（@recordType nvarchar（12），@ lotCreation bit，@ licensePlateCreation'）需要参数'@lotManufactureDate'，但未提供。

这是当我从SQL Server数据库调用存储过程时edmx模型创建的存储过程代码，这里存储过程中名为licensePlateLookupCode的参数也允许为空，但是在代码中创建的emdx模型却不允许不能显示它可以为空-您知道为什么吗？

应该像Nullable<string> licensePlateLookupCode，而不是string licensePlateLookupCode。

  public virtual int AddFeedbackRequestsAgentInsert(string recordType,Nullable<bool> lotCreation,Nullable<bool> licensePlateCreation,Nullable<int> finishedGoodLineId,Nullable<int> lotid,string 
     lotLookupCode,Nullable<System.DateTime> lotManufactureDate,Nullable<System.DateTime> lotExpirationDate,Nullable<decimal> 
     packagedAmount,Nullable<int> packagingId,string 
     licensePlateLookupCode,Nullable<int> licensePlateId,Nullable<int> 
     licensePlateLocationId)
    {
        var recordTypeParameter = recordType != null ?
            new ObjectParameter("recordType",recordType) :
            new ObjectParameter("recordType",typeof(string));

        var lotCreationParameter = lotCreation.HasValue ?
            new ObjectParameter("lotCreation",lotCreation) :
            new ObjectParameter("lotCreation",typeof(bool));

        var licensePlateCreationParameter = licensePlateCreation.HasValue ?
            new ObjectParameter("licensePlateCreation",licensePlateCreation) :
            new ObjectParameter("licensePlateCreation",typeof(bool));

        var finishedGoodLineIdParameter = finishedGoodLineId.HasValue ?
            new ObjectParameter("finishedGoodLineId",finishedGoodLineId) :
            new ObjectParameter("finishedGoodLineId",typeof(int));

        var lotidParameter = lotid.HasValue ?
            new ObjectParameter("lotid",lotid) :
            new ObjectParameter("lotid",typeof(int));

        var lotLookupCodeParameter = lotLookupCode != null ?
            new ObjectParameter("lotLookupCode",lotLookupCode) :
            new ObjectParameter("lotLookupCode",typeof(string));

        var lotManufactureDateParameter = lotManufactureDate.HasValue ?
            new ObjectParameter("lotManufactureDate",lotManufactureDate) :
            new ObjectParameter("lotManufactureDate",typeof(System.DateTime));

        var lotExpirationDateParameter = lotExpirationDate.HasValue ?
            new ObjectParameter("lotExpirationDate",lotExpirationDate) :
            new ObjectParameter("lotExpirationDate",typeof(System.DateTime));

        var packagedAmountParameter = packagedAmount.HasValue ?
            new ObjectParameter("packagedAmount",packagedAmount) :
            new ObjectParameter("packagedAmount",typeof(decimal));

        var packagingIdParameter = packagingId.HasValue ?
            new ObjectParameter("packagingId",packagingId) :
            new ObjectParameter("packagingId",typeof(int));

        var licensePlateLookupCodeParameter = licensePlateLookupCode != null ?
            new ObjectParameter("licensePlateLookupCode",licensePlateLookupCode) :
            new ObjectParameter("licensePlateLookupCode",typeof(string));

        var licensePlateIdParameter = licensePlateId.HasValue ?
            new ObjectParameter("licensePlateId",licensePlateId) :
            new ObjectParameter("licensePlateId",typeof(int));

        var licensePlateLocationIdParameter = licensePlateLocationId.HasValue ?
            new ObjectParameter("licensePlateLocationId",licensePlateLocationId) :
            new ObjectParameter("licensePlateLocationId",typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("AddFeedbackRequestsAgentInsert",recordTypeParameter,lotCreationParameter,licensePlateCreationParameter,finishedGoodLineIdParameter,lotidParameter,lotLookupCodeParameter,lotManufactureDateParameter,lotExpirationDateParameter,packagedAmountParameter,packagingIdParameter,licensePlateLookupCodeParameter,licensePlateIdParameter,licensePlateLocationIdParameter);
    }

在这里我要调用该存储过程，然后在用户单击“提交”按钮后执行该存储过程，我认为lotmanufacturer和lotexpiration日期参数出现错误，因为它们是NULL，但是在实际的数据库存储过程中可以为空

    private void Btn_Submit_Click(object sender,EventArgs e)
    {
        // db context variable
        var context = _manufacturingDbContext;

        // ** Variables to insert to FootPrint stored procedure datex_footprint_integration.AddFeedbackRequestsAgentInsert with Record Type (FinishedGood)
        const string recordType = "FinishedGood";
        const bool lotCreation = false;
        const bool licensePlateCreation = true;           
        var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text,Cmb_FgLookupCode.Text,Cmb_LotLookupCode.Text).FirstOrDefault();         
        var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
        var doNotCreateLot = null;
        DateTime? lotManufactureDate = null;
        DateTime? lotExpirationDate = null;
        var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
        const int packagedId = 3;
        var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
        int? licensePlateId = null;
        const int licensePlateLocationId = 51372;            

        // Call SQL Server SPROC dbo.AddFeedbackRequestsAgentInsert and enter data to FootPrint Task

        context.Database.ExecuteSqlCommand("EXEC dbo.AddFeedbackRequestsAgentInsert " +
                                           "@recordType,@lotCreation,@licensePlateCreation,@finishedGoodLineId,@lotid,@lotLookupCode,@lotManufactureDate," +
                                           "@lotExpirationDate,@packagedAmount,@packagingId,@licensePlateLookupCode,@licensePlateId,@licensePlateLocationId",new SqlParameter("@recordType",recordType),new SqlParameter("@lotCreation",lotCreation),new SqlParameter("@licensePlateCreation",licensePlateCreation),new SqlParameter("@finishedGoodLineId",finishedGoodLineId),new SqlParameter("@lotid",lotId),new SqlParameter("@lotLookupCode",doNotCreateLot),new SqlParameter("@lotManufactureDate",lotManufactureDate),new SqlParameter("@lotExpirationDate",lotExpirationDate),new SqlParameter("@packagedAmount",packagedAmount),new SqlParameter("@packagingId",packagedId),new SqlParameter("@licensePlateLookupCode",licensePlateLookupCode),new SqlParameter("@licensePlateId",licensePlateId),new SqlParameter("@licensePlateLocationId",licensePlateLocationId)
                                            );

        context.SaveChanges();
}

这是实际的存储过程-如您所见@lotManufactureDate和@lotExpirationDate确实允许空值：

CREATE PROCEDURE [dbo].[AddFeedbackRequestsAgentInsert]
         @recordType NVARCHAR(30),@lotCreation BIT,@licensePlateCreation BIT,@finishedGoodLineId INT,@lotid INT NULL,@lotLookupCode NVARCHAR(256) NULL,@lotManufactureDate DATETIME NULL,@lotExpirationDate DATETIME NULL,@packagedAmount DECIMAL(28,8),@packagingId INT,@licensePlateLookupCode NVARCHAR(256) NULL,@licensePlateId INT NULL,@licensePlateLocationId INT NULL

所以，我不明白为什么我传递带有空日期的那两个日期参数时会出现预期的错误，如果lotlookupcode参数传递了空值，也会发生相同的错误lotlookupcode。您能在这里看到问题所在吗？

我对代码进行了新的更改，现在我以前的请求描述没有得到该错误，但是现在当我调用存储过程执行时，我在数据库上看不到任何内容，可以在下面的内容中查找emdx模型确定我提供的参数正确吗？

我是从模型浏览器导入函数存储过程中调用此函数的，当我按下“提交”按钮时，数据库上什么都没收到，基于上述emdx模型和存储过程，参数看起来是否正确？

    var context = _manufacturingDbContext;


    const string recordType = "FinishedGood";
    const bool lotCreation = false;
    const bool licensePlateCreation = true;           
    var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text,Cmb_LotLookupCode.Text).FirstOrDefault();         
    var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
    var doNotCreateLot = null;
    DateTime? lotManufactureDate = null;
    DateTime? lotExpirationDate = null;
    var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
    const int packagedId = 3;
    var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
    int? licensePlateId = null;
    const int licensePlateLocationId = 51372;    


        //calling stored procedure and send data to sproc based on the variables above
        context.AddFeedbackRequestsAgentInsert(recordType,lotCreation,licensePlateCreation,finishedGoodLineId,lotId,lot,lotManufactureDate,lotExpirationDate,packagedAmount,packagedId,licensePlateLookupCode,licensePlateId,licensePlateLocationId);

}

hippo101 回答：尝试在C＃中执行存储过程参数实体框架EDMX模型时出错

因此，您更新了数据库中的Stored Proc，但是您的数据模型不知道这些更改。您需要做的是也更新.net应用程序中的Entity FW。 1-从数据库刷新（更新）EDMX。

enter image description here

2-右键单击概念模型（mode.context.tt）和（model.tt），然后单击（运行自定义工具）..这将更新您的C＃映射数据模型以查看这些添加的新参数。 / p>

enter image description here

,

您可以直接调用EF生成的AddFeedbackRequestsAgentInsert方法并将参数传递给它。您可能不需要调用ontext.Database.ExecuteSqlCommand

此外，如果您想使用ontext.Database.ExecuteSqlCommand，则可以使用以下代码在可为空的日期参数中传递null 

        new SqlParameter("@lotManufactureDate",lotManufactureDate.HasValue ? lotManufactureDate : DBNull.Value),new SqlParameter("@lotManufactureDate",lotExpirationDate.HasValue ? lotExpirationDate : DBNull.Value),

OR 

        new SqlParameter("@lotManufactureDate",lotManufactureDate.HasValue ? lotManufactureDate : default(DateTime)),lotExpirationDate.HasValue ? lotExpirationDate : default(DateTime)),
,

这是我的答案，我所要做的就是修复我的sql存储过程的某些部分，最重要的是，我只需要调用从emdx模型创建的导入函数，并在检查数据库数据后向其中添加正确的参数即可。正在根据代码解决方案正确插入，如果您有任何疑问，如果您看到更好的方法，请告诉我。

    public void ExecuteStoredProcedure()
    {
        try
        {
            // db context variable
            var context = _manufacturingDbContext;

            const string recordType = "FinishedGood";
            const bool lotCreation = false;
            const bool licensePlateCreation = true;
            var finishedGoodLineId = context.FinishedGoodLineIdByOrderAndFinishedGoodAndLot(Cmb_MfgOrder.Text,Cmb_FgLookupCode.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
            var lotId = context.LotIdByManufacturingOrderAndFinishedGood(Cmb_MfgOrder.Text,Cmb_LotLookupCode.Text).FirstOrDefault();
            string lot = null;
            DateTime? lotManufactureDate = null;
            DateTime? lotExpirationDate = null;
            var packagedAmount = Convert.ToDecimal(Txt_PackagedAmount.Text);
            const int packagedId = 3;
            var licensePlateLookupCode = Txt_LicensePlateLookupCode.Text;
            int? licensePlateId = null;
            const int licensePlateLocationId = 51372;


            // Call SQL Server SPROC datex_footprint_integration.AddFeedbackRequestsAgentInsert and enter data to FootPrint Task
            var run = context.AddFeedbackRequestsAgentInsert(recordType,lotCreation,licensePlateCreation,finishedGoodLineId,lotId,"",lotManufactureDate,lotExpirationDate,packagedAmount,packagedId,licensePlateLookupCode,licensePlateId,licensePlateLocationId);
            context.SaveChanges();
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
            throw;
        }       
    }
c#edmx-designerentity-framework-4sql-serverstored-procedures
本文链接：https://www.f2er.com/3148385.html

大家都在问