存储过程在DB中有效,但在C#代码中无效

我有一个存储过程,它有3个输入参数. Id和2个VarChar条件.当我执行存储过程的干运行时,它工作正常,但是当我从C#代码调用相同的存储过程时,它会失败.我传递了与存储过程的干运行中使用的完全相同的参数,但存储过程保持挂起.

这有什么理由发生吗？

我使用的是sql Server 2008 R2快速版.

这是存储过程的干运行：

EXEC    @return_value = [dbo].[GetAttributes]
        @pi_PId = 95102,@pi_returnOnly1stRow = 0,@pi_returnExtAttr = 1

SELECT  'Return Value' = @return_value

这是来自C#代码的调用：

sqlDataAdapter da = new sqlDataAdapter();
da.SelectCommand = new sqlCommand("GetAttributes",conn);
da.SelectCommand.Parameters.AddWithValue("@pi_PId",95102); 
da.SelectCommand.Parameters.AddWithValue("@pi_returnOnly1stRow",0);
da.SelectCommand.Parameters.AddWithValue("@pi_returnExtAttr",1);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
da.Fill(ds,"result_name");
DataTable dt = ds.Tables["result_name"];
conn.Close();

编辑：问题返回….

我以为我已经通过杀死引起这个问题的过程解决了这个问题,但不幸的是它又回来了.我说我对这个问题有了更好的理解,我可以看到问题,但不知道是什么导致了这个问题.

问题是一样的.如果直接在sql服务器上运行但是在从C#程序调用时无法执行,则存储过程运行正常….

我跑了Sp_lock.我使用的存储过程有Spid：’59’,其锁定如下所示.我不知道为什么这些表只有在从c#调用存储过程时才被锁定,而不是在SP的干运行期间被锁定.

59  2   0           0   DB  [ENCRYPTION_SCAN]                   S   GRANT
59  5   1802489500  0   PAG 1:169937                            S   GRANT
59  5   1914489899  0   TAB                                     IS  GRANT
59  5   1898489842  0   TAB                                     IS  GRANT
59  5   1177771253  0   TAB                                     IS  GRANT
59  5   1786489443  0   TAB                                     IS  GRANT
59  5   1802489500  0   TAB                                     IS  GRANT
59  5   1882489785  0   TAB                                     IS  GRANT
59  5   0           0   DB                                      S   GRANT

最新编辑：我还通过在SP中涉及的每个表上对NOLOCK进行了介绍来编辑我的Sp

我也在这里粘贴存储过程供您参考(添加了最新的NOLOCK)….

@pi_PId                 INT,@pi_returnOnly1stRow    BIT,@pi_returnExtAttr       BIT
AS

BEGIN 
    IF(@pi_returnOnly1stRow=1)
        BEGIN
            SELECT TOP 1 NULL section_name,header_mvoc.text Attr_Name,body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee WITH (NOLOCK)
            JOIN   cds_mvocee header_mvoc WITH (NOLOCK)ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc WITH (NOLOCK) ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod WITH (NOLOCK)ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   ProductVariant revpro WITH (NOLOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            UNION
            SELECT section_evoc.Text section_name,header_evoc.text Attr_Name,body_evoc.Text Attr_Value,cds_Especee.DisplayOrder
            FROM   cds_Especee WITH (NOLOCK) 
            JOIN   cds_Evocee section_evoc WITH (NOLOCK) ON (cds_Especee.SectID = section_evoc.ID)
            JOIN   cds_Evocee header_evoc WITH (NOLOCK) ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc WITH (NOLOCK) ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   ProductVariant revpro WITH (NOLOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            AND    @pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
    ELSE
        BEGIN
            SELECT NULL section_name,cds_mspecee.DisplayOrder
            FROM   cds_mspecee WITH (NOLOCK) 
            JOIN   cds_mvocee header_mvoc WITH (NOLOCK) ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc WITH (NOLOCK) ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   productVariant revpro WITH (NOLOCK) On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            UNION
            SELECT section_evoc.Text section_name,cds_Especee.DisplayOrder
            FROM   cds_Especee WITH (NOLOCK) 
            JOIN   cds_Evocee section_evoc WITH (NOLOCK) ON (cds_Especee.sectid = section_evoc.id)
            JOIN   cds_Evocee header_evoc WITH (NOLOCK) ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc WITH (NOLOCK) ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   productVariant revpro WITH (NOLOCK) On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            AND    @pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
END

解决方法

这听起来像是“参数嗅探”( http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx)的问题,这意味着可以根据过程的先前执行针对不同的参数值优化为您的过程缓存的执行计划.此缓存计划可能对参数值x有效,但对参数值y无效.我之前遇到过同样的问题,当我在SSMS中运行查询时,它立即运行,但如果我从我的应用程序运行,它会“挂起”.这是因为我在SSMS中运行时的查询文本与来自应用程序的查询文本略有不同,因此它使用不同的缓存执行计划.

解决此问题的一种解决方法是在proc中创建本地范围的变量,这些变量充当过程参数的副本.

我还建议：

>如果没有从UNION的每一侧返回重复行,则将UNION替换为UNION ALL以删除 sql Server检查行中重复值的不必要开销. What is the difference between UNION and UNION ALL?
>避免使用WITH(NOLOCK)提示,因为这些可能会导致数据一致性问题.如果您完全理解副作用,则只使用此提示,并且您的应用程序可以使用脏读取并且可能具有多次返回相同的行(When should you use “with (nolock)”).

以下是结合上述反馈的示例：

ALTER PROC dbo.usp_YourProcName
    @pi_PId                 INT,@pi_returnExtAttr       BIT
AS
BEGIN 
    --these local variables are used to address "parameter sniffing" issues which may cause an ineffient plan cache.
    --Use these local variables below instead of the direct parameter values.
    DECLARE @local_pi_PId             INT = @pi_PId,@local_pi_returnOnly1stRow    BIT = @pi_returnOnly1stRow,@local_pi_returnExtAttr       BIT = @pi_returnExtAttr
    ;

    IF(@local_pi_returnOnly1stRow=1)
        BEGIN
            SELECT TOP 1 NULL section_name,cds_mspecee.DisplayOrder
            FROM   cds_mspecee 
            JOIN   cds_mvocee header_mvoc ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name,cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.SectID = section_evoc.ID)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,cds_mspecee.DisplayOrder
            FROM   cds_mspecee  
            JOIN   cds_mvocee header_mvoc  ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name,cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.sectid = section_evoc.id)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
END

原文链接：https://www.f2er.com/csharp/100420.html

存储过程在DB中有效,但在C#代码中无效

解决方法

猜你在找的C#相关文章