C# 从查询中检索单个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10820270/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving single value from query
提问by Cindy Brozyno
I'm attempting to retrieve an integer value from a single table, based on the string field username. I've tried it using a stored proc, and direct text. When I execute the stored proc, I get the proper return value; however, the proper result doesn't come through.
我试图根据字符串字段用户名从单个表中检索整数值。我已经尝试过使用存储过程和直接文本。当我执行存储过程时，我得到了正确的返回值；然而，正确的结果并没有通过。
Here are both sets of code - Direct text -
这是两套代码 - 直接文本 -
public int GetUserRole(string CUSER)
{
try
{
SQLCON = new SqlConnection(connectionString);
SQLCON.Open();
SQLCommand = new SqlCommand();
SQLCommand.CommandType = CommandType.Text;
SQLCommand.Parameters.Add("USUsername", SqlDbType.VarChar).Value = CUSER;
SQLCommand.CommandText = "SELECT USRole FROM tblUser WHERE USUsername = CUSER";
Int32 USRole = (Int32) SQLCommand.ExecuteScalar();
return USRole;
}
catch
{
HttpContext.Current.Response.Redirect("~/ErrorRedirect.aspx", false);
return 0;
}
}
SQL query:
SQL查询：
ALTER PROCEDURE [dbo].[spGetUserRole]
-- Add the parameters for the stored procedure here
@username VARCHAR(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @USRole as int
-- Add the T-SQL statements to compute the return value here
SELECT @USRole = tblUser.USRole FROM tblUser WHERE USUsername = @username
-- Return the result of the function
RETURN @USRole
END
采纳答案by Chris
You are not referencing your parameter correctly. If you are adding a parameter named USUsername then in the command text you should use @USUsername:
您没有正确引用您的参数。如果要添加名为 USUsername 的参数，则在命令文本中应使用 @USUsername：
public int GetUserRole(string CUSER)
{
try
{
SQLCON = new SqlConnection(connectionString);
SQLCON.Open();
SQLCommand = new SqlCommand();
SQLCommand.CommandType = CommandType.Text;
SQLCommand.Parameters.Add("USUsername", SqlDbType.VarChar).Value = CUSER;
SQLCommand.CommandText = "SELECT USRole FROM tblUser WHERE USUsername = @USUsername";
Int32 USRole = (Int32) SQLCommand.ExecuteScalar();
return USRole;
}
catch (Exception)
{
HttpContext.Current.Response.Redirect("~/ErrorRedirect.aspx", false);
return 0;
}
}
Your stored procedure will also need updating as the parameter name here should also match and you don't need the return variable.
您的存储过程也需要更新，因为这里的参数名称也应该匹配，并且您不需要返回变量。
ALTER PROCEDURE [dbo].[spGetUserRole]
-- Add the parameters for the stored procedure here
@USUsername VARCHAR(50)
AS
BEGIN
-- Add the T-SQL statements to compute the return value here
SELECT tblUser.USRole FROM tblUser WHERE USUsername = @USUsername
END
You should also look at using the "using" syntax to automatically close your database connections. See Scott Hanselman's example here - http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx
您还应该考虑使用“使用”语法来自动关闭数据库连接。在这里看到 Scott Hanselman 的例子 - http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx
回答by Thomas
Instead of using the return value of the stored procedure (RETURN @USRole), send the results back using a Select statement (e.g. Select @USRole). What is going on is that the return value of a stored procedure isn't the same as what is used by ExecuteScalar. ExecuteScalar returns the first column and row of the output. The return value is different and must accessed using the specially named parameter @RETURN_VALUEor the special ParameterDirection.ReturnValueproperty.
不使用存储过程的返回值 ( RETURN @USRole)，而是使用Select 语句 (例如Select @USRole)将结果发回。发生的事情是存储过程的返回值与 ExecuteScalar 使用的不同。ExecuteScalar 返回输出的第一列和第一行。返回值不同，必须使用特殊命名的参数@RETURN_VALUE或特殊ParameterDirection.ReturnValue属性访问。
A revised version of your procedure would look like:
程序的修订版如下所示：
ALTER PROCEDURE [dbo].[spGetUserRole]
-- Add the parameters for the stored procedure here
@USUsername VARCHAR(50)
AS
BEGIN
-- Add the T-SQL statements to compute the return value here
Select tblUser.USRole
FROM tblUser
WHERE USUsername = @USUsername
END
回答by hkutluay
Use parameter correctly. And dont forget to close connection on finally statement.
正确使用参数。并且不要忘记在 finally 语句中关闭连接。
public int GetUserRole(string CUSER)
{
try
{
SQLCON = new SqlConnection(connectionString);
SQLCON.Open();
SQLCommand = new SqlCommand();
SQLCommand.CommandType = CommandType.Text;
SQLCommand.CommandText = "SELECT USRole FROM tblUser WHERE USUsername = @USUsername ";
SQLCommand.Parameters.Add("USUsername", SqlDbType.VarChar).Value = CUSER;
Int32 USRole = (Int32) SQLCommand.ExecuteScalar();
return USRole;
}
catch (Exception)
{
HttpContext.Current.Response.Redirect("~/ErrorRedirect.aspx", false);
return 0;
}
finally { close connection here.. }
}
回答by YavgenyP
I dont know how you called your stored procedure, but theres a bug in the query that you posted:
我不知道你是如何调用你的存储过程的，但是你发布的查询中有一个错误：
"SELECT USRole FROM tblUser WHERE USUsername = CUSER"
should be replaced with
应该替换为
SQLCommand.Parameters.Add("@USUsername", SqlDbType.VarChar).Value = CUSER;
"SELECT USRole FROM tblUser WHERE USUsername = @USUsername"
You are currently not really making the parameter part of the query, but trying to find the value CUSER within the column
您目前并未真正将参数作为查询的一部分，而是尝试在列中查找值 CUSER
回答by Conrad Frix
If you insist on using the return value you can do it by setting the Parameter direction and using ExecuteNonQuery
如果您坚持使用返回值，您可以通过设置参数方向并使用 ExecuteNonQuery 来实现
SqlParameter p = cmd.Parameters.Add("@USRole", SqlDbType.Int);
p.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int returnvalue = (int)cmd.Parameters["@USRole"].Value;
If you want to use ExecuteScalar then just change your proc to selectthe variable instead of Return
如果您想使用 ExecuteScalar 则只需将您的 proc 更改select为变量而不是Return
You should note that what you put in for the parameter name is arbitrary.
您应该注意，您为参数名称输入的内容是任意的。
