从存储过程中获取 C# asp.net 中的返回值(语法问题) [英] Getting a Return Value in C# asp.net from a stored procedure (syntax issue)

问题描述

我在尝试为我的 C# 2008 asp.net 代码获取正确的语法时遇到问题.我需要从我的存储过程中获取一个返回值(选择@@Identity)

I am having issues trying to get the syntax correct for my C# 2008 asp.net code. I need to get a return value (Select @@Identity) from my stored procedure

我的 C# 代码是:

        SqlConnection conn = new SqlConnection(strConn);
        string sql = "usp_ClientProfile_Header";
        SqlCommand cmdHeader = new SqlCommand(sql, conn);

        cmdHeader.CommandType = CommandType.StoredProcedure;

        cmdHeader.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@FName"].Value = txtFName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@LName"].Value = txtLName.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@EmailAddress", SqlDbType.VarChar, 100));
        cmdHeader.Parameters["@Email"].Value = txtEMail.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@Address1", SqlDbType.VarChar, 255));
        cmdHeader.Parameters["@Address1"].Value = txtAddress1.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@City", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@City"].Value = txtCity.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@State", SqlDbType.VarChar, 50));
        cmdHeader.Parameters["@State"].Value = txtState.Text.Trim();

        cmdHeader.Parameters.Add(new SqlParameter("@ZipCode", SqlDbType.VarChar, 12));
        cmdHeader.Parameters["@Postal_Code"].Value = txtZip.Text.Trim();

我的代码中的下一行需要是ParameterDirection.ReturnValue"，但我似乎无法获得正确的语法.

The next line in my code needs to be the "ParameterDirection.ReturnValue" but I can't seem to get the syntax correct.

有什么想法吗?

推荐答案

要捕获 RETURN VALUE(由 SQL 使用 RETURN({number}) 语法返回)，请使用:

To capture a RETURN VALUE (returned by SQL using the RETURN({number}) syntax) use:

cmdHeader.Parameters.Add("@ReturnValue", SqlDbType.Int, 4).Direction = ParameterDirection.ReturnValue;

此外，您可能应该使用 SCOPE_IDENTITY() 而不是@@身份

Also, you should probably be using SCOPE_IDENTITY() instead of @@IDENTITY

所以你的 sproc 会做这样的事情:

So your sproc would do something like:

DECLARE @NewId INTEGER
INSERT SomeTable(FieldA) VALUES ('Something')
SELECT @NewId = SCOPE_IDENTITY()
RETURN (@NewId)

您用来检索该值的 C# 代码将是:

And your C# code to retrieve that value would be:

int newId = cmdHeader.Parameters[@ReturnValue].value;

编辑 2:
好的，最初的问题混淆了这个问题，因为返回值"与您实际执行的返回单列结果集的操作不同.

Edit 2:
Ok, the original question confused the issue as the "return value" is a different thing to what you're actually doing which is returning a single column resultset.

因此，完全不要添加 ReturnValue 参数.只需使用 ExecuteScalar() 使用您的原始 SqlCommand 设置如下:

So, instead DON'T add a ReturnValue parameter at all. Just use ExecuteScalar() using your original SqlCommand setup as below:

int newId = Convert.ToInt32(cmdHeader.ExecuteScalar());

这篇关于从存储过程中获取 C# asp.net 中的返回值(语法问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！