使用 C# 和存储过程从 SQL 数据库检索数据
本文关键字:数据库 检索 数据 SQL 存储过程 使用 | 更新日期: 2023-09-27 17:56:57
每当我尝试从数据库中检索数据时,我总是得到空值。我使用的代码如下:
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection(GetConnectionString());
SqlCommand cmd = new SqlCommand("spSelectCustomer", myConnection);
cmd.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlParameter custId = cmd.Parameters.Add("@CustomerId", SqlDbType.Int);
custId.Direction = ParameterDirection.Input;
custId.Value = 10;
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Label1.Text = dr["FirstName"].ToString();
Label2.Text = dr["LastName"].ToString();
Label3.Text = dr[3].ToString();
Label4.Text = dr["Email"].ToString();
}
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["Lab3ConnectionString"].ConnectionString;
}
您需要先调用Read
才能访问数据,您的代码应该是
While (dr.Read())
{
Label1.Text = dr["FirstName"].ToString();
Label2.Text = dr["LastName"].ToString();
Label3.Text = dr[3].ToString();
Label4.Text = dr["Email"].ToString();
}
//close DataReader
dr.Close();
在从 DataReader 读取列值之前,必须从数据读取器调用 Read() 方法。
if (dr.Read())
{
Label1.Text = dr["FirstName"].ToString();
Label2.Text = dr["LastName"].ToString();
Label3.Text = dr[3].ToString();
Label4.Text = dr["Email"].ToString();
}
您也可以尝试:
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
您错过了对阅读器的Read()
调用。我还建议您将IDisposable
对象包装在using
语句中,如下所示。
从SqlDataReader
检索值时,您似乎也使用了列名和序号位置的奇怪组合。
protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection myConnection = new SqlConnection(GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand("spSelectCustomer", myConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
myConnection.Open();
SqlParameter custId = cmd.Parameters.AddWithValue("@CustomerId", 10);
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
Label1.Text = dr["FirstName"].ToString();
Label2.Text = dr["LastName"].ToString();
Label3.Text = dr[3].ToString();
Label4.Text = dr["Email"].ToString();
}
}
}
}
}
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
文档:SqlCommand.ExecuteReader Method