C#Windows窗体应用程序-在文本框中显示sql server值
本文关键字:显示 sql server 文本 窗体 应用程序 C#Windows | 更新日期: 2023-09-27 18:29:34
我需要帮助在windows窗体中显示sql server值。在下面的应用程序中,如果查询返回一行,则显示值。我知道我要使用sqldatareader,但到目前为止,我还没有成功地添加它
SqlConnection ChuoDB_Connection = new SqlConnection("Data Source=test-PC''tester;Initial Catalog=Chuo;Integrated Security=True;Pooling=False");
SqlDataAdapter select_adapt;
private void btn_guardian_student_search_Click(object sender, EventArgs e)
{
if (rd_btn_guardian_student_no.Checked == true)
{
DataSet ds = new DataSet();
SqlDataReader dr;
ChuoDB_Connection.Open();
select_adapt = new SqlDataAdapter("SELECT * FROM Guardian WHERE STUDENT_NO = @student_no", ChuoDB_Connection);
select_adapt.SelectCommand.Parameters.Add("@student_no", SqlDbType.Int).Value = Convert.ToInt32(txt_bx_guardian_student_search.Text);
select_adapt.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
lbl_guardian_student_search.Text = "No Guardian record exists for this student. Please enter the Guardian Information";
ChuoDB_Connection.Close();
}
if (ds.Tables[0].Rows.Count > 0)
{
lbl_guardian_student_search.Text = "";
while (dr.read())
{
txtBox1.Text = rdr.Item["DBFieldName1"].ToString();
txtBox2.Text = rdr.Item["DBFieldName2"].ToString();
}
}
}
}
我认为这里不需要DataAdapter和DataSet。尝试使用DataReader:
string _connectionString = "Data Source=test-PC''tester;Initial Catalog=Chuo;Integrated Security=True;Pooling=False";
string _selectCommand = @"SELECT * FROM Guardian WHERE STUDENT_NO = @student_no";
点击处理程序的代码:
SqlParameter parameter = new SqlParameter("@student_no", SqlDbType.Int);
parameter.Value = Convert.ToInt32(txt_bx_guardian_student_search.Text);
using (IDbConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = _selectCommand;
command.Parameters.Add("@student_no", SqlDbType.Int).Value = Convert.ToInt32(txt_bx_guardian_student_search.Text);
IDataReader reader = command.ExecuteReader();
if (reader.Read())
{
txtBox1.Text = reader["DBFieldName1"].ToString();
txtBox2.Text = reader["DBFieldName2"].ToString();
}
else
{
lbl_guardian_student_search.Text = "No Guardian record exists for this student. Please enter the Guardian Information";
}
}
}
要使用SqlDataReader,需要使用SqlCommand的ExecuteReader方法对其进行初始化。因此,在您的代码中,您可以丢弃与SqlDataAdapter 相关的所有部分
private void btn_guardian_student_search_Click(object sender, EventArgs e)
{
if (rd_btn_guardian_student_no.Checked == true)
{
using(SqlConnection cnn = new SqlConnection(......))
using(SqlCommand cmd = new SqlCommand(@"SELECT * FROM Guardian
WHERE STUDENT_NO = @student_no", cnn))
{
cnn.Open();
cnn.Add("@student_no", SqlDbType.Int).Value = Convert.ToInt32(txt_bx_guardian_student_search.Text);
using(SqlDataReader rd = new cmd.ExecuteReader())
{
if(!rd.HasRows)
lbl_guardian_student_search.Text = "No Guardian record exists for this student. Please enter the Guardian Information";
else
{
rdr.Read();
txtBox1.Text = rdr.Item["DBFieldName1"].ToString();
txtBox2.Text = rdr.Item["DBFieldName2"].ToString();
}
}
}
}
}
注意,我已经在代码中移动了全局连接对象,使其成为一个局部变量,在using块以及命令和读取器中初始化。它们是一次性物品,应该在用完后处理。使用声明确保正确处理这些对象
还要注意,在数据库服务器应用程序中,在应用程序的所有时间内保持连接打开实际上是一个很大的禁忌。如果您的连接一直处于打开状态,则会阻碍服务器提供更多请求的能力。恢复服务器连接不会带来太大的损失,因为ADO.NET有一个名为连接池的基础设施,它允许您立即恢复连接