存储过程在数据库中工作，但在 C# 代码中不起作用
本文关键字：代码 不起作用 但在 工作 数据库 存储过程 | 更新日期: 2023-09-27 18:35:49
>我有一个存储过程，它有 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 ,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
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 检查行中重复值的不必要的开销。UNION 和 UNION ALL 有什么区别？ - 避免使用
WITH(NOLOCK)提示，因为这些提示可能会导致数据一致性问题。 仅当您完全了解副作用，并且您的应用程序可以进行脏读并且可能多次返回同一行（何时应使用"with （nolock）"）时，才使用此提示。
下面是一个包含上述反馈的示例：
ALTER PROC dbo.usp_YourProcName
@pi_PId INT
, @pi_returnOnly1stRow BIT
, @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,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,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, header_evoc.text Attr_Name, body_evoc.Text Attr_Value, 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
ELSE
BEGIN
SELECT NULL section_name ,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,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,header_evoc.text Attr_Name, body_evoc.Text Attr_Value,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
启动 SQL 探查器并执行代码。检查命中即将进入探查器。从那里复制并在编辑器窗口中运行以检查统计信息。
使用这个
SqlConnection con=new SqlConnection("ConnecttionString");
con.Open();
SqlCommand cmd = new SqlCommand("ProcedureName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("StoredProcedureParameter", Value);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp .Fill(dt);
con.Close();
if (dt.Rows.Count > 0)
{
// get the data table field value here
}
else
{
// Table is Empty
}