gpt4 book ai didi

c# - 存储过程不返回值 - C#

转载 作者:行者123 更新时间:2023-11-30 12:20:59 25 4
gpt4 key购买 nike

我有这个存储过程:

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);
}

目前这一切正常!

最佳答案

您正在用 c# 调用 ExecuteScalar

ExecuteScalar 将为您提供过程中 Select 语句返回的 firstRow 和 FirstColumn 值。

你需要使用ExecuteNonQuery

ExecuteNonQuery之后,你可以得到类似的值

cmd.Parameters["@rowsCount"].Value

You can also get value using ExecuteScalar if you use Select @rowsCount after set @rowsCount. Then you dont need to mention output direction to any parameter, both in sql and c#

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# 中没有获得值(value)的真正原因是(有解释)

参数@rowsCount 在使用前没有初始化。原来如此null 并且在执行 SET @rowsCount = @TempCount + @rowsCount 时,它实际上向 null 添加了一些内容。并且向 null 添加任何内容都将是 null 结果。

所以 @rowsCount 总是 null。在程序开始时设置 @rowsCount=0 有帮助。

关于c# - 存储过程不返回值 - C#,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48962089/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com