存储过程未返回值 - C#

Question

我有这个存储过程:

ALTER PROCEDURE [dbo].[DeleteFromSchoolMain]
    @TblName VARCHAR(50),
    @MainID VARCHAR(10),
    @TblCol VARCHAR(50),
    @rowsCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @RelTbl AS Nvarchar(50)
    DECLARE @ColForFK AS Nvarchar(50)
    DECLARE @TblMaterial AS NVARCHAR(50)
    DECLARE @ColMaterialID AS NVARCHAR(50)
    DECLARE @ColMaterialFK AS NVARCHAR(50)
    DECLARE @cmdForMaterial AS NVARCHAR(max)
    DECLARE @TblSubject AS NVARCHAR(50)
    DECLARE @ColSubjectID AS NVARCHAR(50)
    DECLARE @ColSubjectFK AS NVARCHAR(50)
    DECLARE @cmdForSubject AS NVARCHAR(max)

    Set @ColForFK = SUBSTRING(@TblCol,1, DATALENGTH(@TblCol)-2)
    Set @ColForFK = @ColForFK+'FK'

    DECLARE @cmd AS NVARCHAR(max)
    DECLARE @TempCount int

    SET @MainID = ''''+@MainID+ ''''

    SET @RelTbl = 'ClassSubMatRelation'
    --For Class Material
    SET @TblMaterial = 'ClassMaterial'
    SET @ColMaterialID = 'ClassMaterialID'
    SET @ColMaterialFK = 'ClassMaterialFK'

    SET @cmd = N'UPDATE ' + @RelTbl + ' SET Status_Info = 0 WHERE ' +  @ColForFK + ' = ' + @MainID -- Delete from ClassRelation Table
    EXEC(@cmd)
    SET @TempCount = @@ROWCOUNT
    SET @rowsCount = @TempCount + @rowsCount;

    SET @cmd = N'UPDATE ' + @TblName + ' SET Status_Info = 0 WHERE ' +  @TblCol + ' = ' + @MainID -- Delete from Main Table
    EXEC(@cmd)
    SET @TempCount = @@ROWCOUNT 
    SET @rowsCount = @TempCount + @rowsCount;

    -------------------------------------Board-----------------------------------
    IF (@TblName = 'Board')
    BEGIN

        SET @cmdForMaterial = N'UPDATE '+@TblMaterial+' Set Status_Info = 0 where '+@ColMaterialID+' in ( Select s.'+@ColMaterialFK+' from  '+@RelTbl+' s where s.' +  @ColForFK + ' = ' +  @MainID + ' AND s.ClassSubjectFK IN ( SELECT T.ClassSubjectFK FROM ' + @RelTbl + ' T WHERE  T.'+@ColForFK+' = '+ @MainID + ') and s.ClassSubjectFK NOT IN ( SELECT E.ClassSubjectFK FROM ' +@RelTbl+' E WHERE E.'+@ColForFK+' != '+ @MainID +') and s.'+@ColMaterialFK+'  is not null )'
        EXEC(@cmdForMaterial)   
        SET @TempCount = @@ROWCOUNT 
        SET @rowsCount = @TempCount + @rowsCount;
        SET @TempCount = 0;


        --For Class Subject
        SET @TblMaterial = 'ClassSubject'
        SET @ColMaterialID = 'ClassSubjectID'
        SET @ColMaterialFK = 'ClassSubjectFK'

        SET @cmdForMaterial = N'UPDATE '+@TblMaterial+' Set Status_Info = 0 where '+@ColMaterialID+' in ( Select s.'+@ColMaterialFK+' from  '+@RelTbl+' s where s.' +  @ColForFK + ' = ' +  @MainID + ' AND s.ClassSubjectFK IN ( SELECT T.ClassSubjectFK FROM ' + @RelTbl + ' T WHERE  T.'+@ColForFK+' = '+ @MainID + ') and s.ClassSubjectFK NOT IN ( SELECT E.ClassSubjectFK FROM ' +@RelTbl+' E WHERE E.'+@ColForFK+' != '+ @MainID +') and s.'+@ColMaterialFK+'  is not null )'
        EXEC(@cmdForMaterial)   
        SET @TempCount = @@ROWCOUNT 
        SET @rowsCount = @TempCount + @rowsCount;
        SET @TempCount = 0;


    END

        ------------------------------ Class Subject ------------------------------------
    ELSE IF (@TblName = 'ClassSubject')
    BEGIN                   

        SET @cmdForMaterial = N'UPDATE '+@TblMaterial+' Set '+@TblMaterial+'.Status_Info = 0 from '+@TblMaterial+' tm join '+@RelTbl+' rt on rt.'+@ColMaterialFK+' = tm.'+@ColMaterialID+' where rt.'+@ColForFK+ ' = '+ @MainID
        EXEC(@cmdForMaterial)

        SET @TempCount = @@ROWCOUNT 
        SET @rowsCount = @TempCount + @rowsCount;
        SET @TempCount = 0;
    END

END
GO

我正在使用C#执行它，代码如下：

var rowsAffected = 0;

using (SqlConnection conn = new SqlConnection(Constants.Connection))
{
     conn.Open();

     SqlCommand cmd = new SqlCommand("DeleteFromSchoolMain", conn);
     cmd.CommandType = CommandType.StoredProcedure;

     cmd.Parameters.AddWithValue("@TblName", abundleBoard.TempName);
     cmd.Parameters.AddWithValue("@MainID", abundleBoard.MainID.ToString());
     cmd.Parameters.AddWithValue("@TblCol", abundleBoard.TblCol);

     SqlParameter outputParam = new SqlParameter();
     outputParam.ParameterName = "@rowsCount";
     outputParam.SqlDbType = System.Data.SqlDbType.Int;
     outputParam.Direction = System.Data.ParameterDirection.Output;
     cmd.Parameters.Add(outputParam);

     object o = cmd.ExecuteScalar();

     if (!outputParam.Value.Equals(DBNull.Value))
     {
         rowsAffected = Convert.ToInt32(outputParam.Value);
         string q = o.ToString();
     }

     conn.Close();
}

但我的问题是，我没有得到输出参数的任何东西。我不明白问题所在，而且我感觉我的存储过程并没有优化。有什么我可以做的来加快查询执行速度吗？我已经尽力进行了索引和分区。

编辑：

在@SurjitSD和@PeterB的帮助下，我得到了解决方案：

在我的存储过程顶部添加了以下代码：

SET @rowsCount=0

在C#中，我已经改变了代码，就像这样：

 cmd.ExecuteNonQuery();
 if (!cmd.Parameters["@rowsCount"].Value.Equals(DBNull.Value))
  {
   rowsAffected = Convert.ToInt32(cmd.Parameters["@rowsCount"].Value);
  }

目前这个可以正常工作！

- Deepak

---

尝试使用 SET @rowsCount=0 启动过程。 - Peter B

@PeterB 你是个男人！！那个简单的事情就像一把斧子砍在我的脑袋上。非常感谢！ - Deepak

@PeterB 如果你能再花点时间看一下优化部分就好了。我的查询需要很长时间。 - Deepak

你应该查看我们能否停止使用AddWithValue()了？并停止使用.AddWithValue() - 它可能导致意外和令人惊讶的结果... - marc_s

只是出于好奇，为什么您将 o 声明为一个对象，而输出参数是 int 类型的？如果我们使用 var 声明它，然后将输出转换为 int 类型，会更好吗？ - iSR5

Answer 1

您正在使用c#中的ExecuteScalar方法。

ExecuteScalar方法会返回存储过程中Select语句所返回的第一行第一列的值。

您需要使用ExecuteNonQuery方法。

在执行ExecuteNonQuery方法后，您可以获取返回值，如下：

cmd.Parameters["@rowsCount"].Value

如果您在set @rowsCount之后使用Select @rowsCount，则可以使用ExecuteScalar获取值。然后，在SQL和C#中都不需要提及任何参数的output direction.

使用ExecuteScalar的示例

Sql过程

Alter procedure SomeProcedure
(
@someVariable int
:
:
)
Begin
   /*Some processing logic of procedure*/

    Select @SomeVariable //any variable Or Value from procedure needed as output in c#
End

C#

var result = cmd.ExecuteScalar();

——-编辑——

在c#中未获得值的实际原因是（附有解释）

在使用之前，参数@rowsCount未被初始化。 因此，它是null，当执行SET @rowsCount = @TempCount + @rowsCount时，实际上是将某些内容添加到null中。并且将任何内容添加到null将作为结果返回null。

因此，@rowsCount始终为null。 在过程开始时设置@rowsCount=0有所帮助。

- SSD

---

我也使用了ExecuteNonQuery。它给了我0。 - Deepak

但是我在我的存储过程或C#中没有使用TempRowCount，对吗？ - Deepak

@SurjitSD 好的，我现在明白你的意思了。不需要举例子了。谢谢！ :) - Jorge Y.

@SurjitSD 在 Peter 的帮助下问题已经解决。非常感谢你的帮助。现在我关心的是执行时间。一定会编辑问题。 - Deepak

哇，不错。快速查看您的过程嵌套子查询，告诉我缓慢的原因。多层嵌套子查询并不是很优化的使用方式。 - SSD