使用带有存储过程的 C# 从 sql 数据库中检索数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14654064/
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 data from sql database using C# with stored procedure
提问by russian
Whenever I try to retrieve data from my database i keep getting null. The code i'm using is below:
每当我尝试从我的数据库中检索数据时，我总是为空。我正在使用的代码如下：
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;
}
采纳答案by rs.
You need to call Readbefore you can access data, Your code should be
您需要先调用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();
回答by S?awomir Rosiek
Before you read column values from DataReader you must invoke Read()method from data reader.
在从 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();
}
You can also try:
你也可以试试：
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
回答by Adrian10 BEN
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.
// 隐式关闭连接，因为指定了 CommandBehavior.CloseConnection。
Documentation : http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executereader(v=vs.71).aspx
文档：http: //msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executereader(v=vs.71).aspx
回答by Daniel Kelley
You are missing a call to Read()on your reader. I also suggest you wrap your IDisposableobjects in usingstatements as below.
您错过了Read()对阅读器的呼叫。我还建议您将IDisposable对象包装在using如下语句中。
You also seem to be using a strange combination of column names and ordinal positions when retrieving your values from the SqlDataReader.
从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();
}
}
}
}
}
