从查询中检索单个值
本文关键字:单个值 检索 查询 | 更新日期: 2023-09-27 18:35:51
我正在尝试根据字符串字段用户名从单个表中检索整数值。我已经尝试过使用存储的过程和直接文本。当我执行存储的过程时,我得到了正确的返回值;但是,正确的结果并没有通过。
这是两组代码 -直接文本 -
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查询:
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
您没有正确引用参数。如果要添加名为 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;
}
}
存储过程也需要更新,因为此处的参数名称也应匹配,并且不需要返回变量。
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
您还应该考虑使用"using"语法自动关闭数据库连接。在这里看到斯科特·汉塞尔曼的例子 - http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx
不使用存储过程的返回值 ( RETURN @USRole
),使用 Select 语句(例如 Select @USRole
)。现在的情况是,存储过程的返回值与 ExecuteScalar 使用的返回值不同。ExecuteScalar 返回输出的第一列和第一行。返回值不同,必须使用特殊命名的参数 @RETURN_VALUE
或特殊 ParameterDirection.ReturnValue
属性进行访问。
过程的修订版本如下所示:
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
RETURN (Transact-SQL)
SqlCommand.ExecuteScalar Method
您如何调用存储过程,但是您发布的查询中存在一个错误:
"SELECT USRole FROM tblUser WHERE USUsername = CUSER"
应替换为
SQLCommand.Parameters.Add("@USUsername", SqlDbType.VarChar).Value = CUSER;
"SELECT USRole FROM tblUser WHERE USUsername = @USUsername"
您当前并没有真正使参数成为查询的一部分,而是尝试在列中查找值 CUSER
正确使用参数。并且不要忘记在最后声明中关闭连接。
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.. }
}
如果您坚持使用返回值,则可以通过设置参数方向并使用 ExecuteNonQuery 来实现
SqlParameter p = cmd.Parameters.Add("@USRole", SqlDbType.Int);
p.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int returnvalue = (int)cmd.Parameters["@USRole"].Value;
如果你想使用ExecuteScalar,那么只需更改你的proc来select
变量而不是Return
您应该注意,您为参数名称输入的内容是任意的。