如何从SqlDataReader返回单个值?
本文关键字:单个值 返回 SqlDataReader | 更新日期: 2023-09-27 18:19:09
我忘了在单层应用程序中返回值。
public int Studentid()
{
try
{
SqlConnection con = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand("SELECT s_id FROM student where name = + ('" + Request.QueryString.ToString() + "')", con);
con.Open();
SqlDataReader dr = null;
con.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
//Want help hear how I return value
}
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
这是你的方法的一个版本,它实现了你想要的。
public int GetStudentId()
{
var sql = string.Format("SELECT s_id FROM student where name = '{0}'", Request.QueryString);
using (var con = new SqlConnection(connectionStr))
using (var cmd = new SqlCommand(sql, con))
{
con.Open();
var dr = cmd.ExecuteReader();
return dr.Read() ? return dr.GetInt32(0) : -1;
}
}
没有必要使用try/catch当你不做任何异常除了重新抛出(事实上,你失去了原来的堆栈跟踪使用throw ex;
而不是throw;
。此外,c# using
语句会在更少的代码行中为您清理资源。
像这样将查询字符串直接传递给SQL意味着任何人都可以在您的数据库中执行随机SQL,可能会删除所有内容(或者更糟)。阅读SQL注入
您应该使用using
块,以便您确定连接、命令和读取器已正确关闭。然后,您可以从if
语句内部返回值,并且在关闭对象之前不必将其存储在变量中。
你只需要打开一次连接。
您应该使用参数化查询,而不是将值连接到查询中。
public int Studentid() {
try {
using (SqlConnection con = new SqlConnection(connectionStr)) {
using (SqlCommand cmd = new SqlCommand("SELECT s_id FROM student where name = @Name", con)) {
cmd.Parameters.Add("@Name", DbType.VarChar, 50).Value = Request.QueryString.ToString();
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader()) {
if (dr.Read()) {
return dr.GetInt32(0);
} else {
return -1; // some value to indicate a missing record
// or throw an exception
}
}
}
}
} catch (Exception ex) {
throw; // just as this, to rethrow with the stack trace intact
}
}
返回单个值的最简单方法是调用ExecuteScalar
。您还应该修复SQL注入错误。您的意思是对整个查询字符串数组进行编码,还是只挑选一个值?
public int StudentId()
{
string sql = "SELECT s_id FROM student WHERE name = @name";
using (var con = new SqlConnection(connectionStr))
{
using (var cmd = new SqlCommand(sql, con))
{
cmd.Parameters.Add("@name", DbType.VarChar, 256).Value = Request.QueryString["name"];
con.Open();
return (int)cmd.ExecuteScalar();
}
}
}
try this:
int s_id = (int) dr["s_id"];
int studId=0;
if(rdr.Read())
{
studId=rdr.GetInt32(rdr.GetOrdinal("s_id"));
}
if (dr.Read())
{
//Want help hear how i return value
int value = dr.GetInt32("s_id");
}
像这样?
public int Studentid()
{
int studentId = -1;
SqlConnection con = null;
try
{
con = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand("SELECT s_id FROM student where name = + ('" + Request.QueryString.ToString() + "')", con);
SqlDataReader dr = null;
con.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
studentId = dr.GetInt32(0);
}
dr.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if(con != null)
con.Close();
con = null;
}
return studentId;
}