如何使用C＃中的本地数据库中的数据显示文本框中的列表框项目

Question

编程时，我正在编写一个项目，该项目允许用户使用文本框将其注册数据输入到本地数据库中。该代码的作品，它将项目添加到数据库 和我按下“Show_users”按钮后，它显示在listBox_users列表框中。

我的问题是，当我从listBox_users选择一个名字，应该用我的listBox_users创建的事件dislay大约在上textBox'es选定的用户，我用摆在首位进入 数据的数据， 但即时通讯出现“无法从已关闭的数据库读取数据”的错误。

namespace Userform 
{ 
public partial class Form1: Form 
{ 
    SqlCeDataReader rdr; 

    public Form1() 
    { 
     InitializeComponent(); 
    } 

    // Some code between... 

    private void button_ShowUsers_Click(object sender, EventArgs e) //code that shows users in listBox 
    { 

     var dt = new DataTable(); 
     string connectionString2 = @"Data Source=MyDatabase;Password=xxxxxx;"; 

     using (var cn = new SqlCeConnection(connectionString2)) 
     using (var cmd = new SqlCeCommand("Select * From Users", cn)) 
     { 
      cn.Open(); 

      using (var reader = cmd.ExecuteReader()) 
      { 
       dt.Load(reader); 
       var results = (from row in dt.AsEnumerable() 
           select new 
           { 
            //UserID = row.Field<int>("ID"), 
            FirstName = row.Field<string>("Firsname"), 
            LastName = row.Field<string>("Lastname"), 
            FullName = row.Field<string>("Firstname") + " " + row.Field<string>("Lastname") 
           }).ToList(); 

       listBox_users.DataSource = results; 
       listBox_users.DisplayMember = "FullName"; 

       rdr = cmd.ExecuteReader(); 
       } 
      } 
     } 

    //I made an event for the listBox_users: 

    private void listBox_users_SelectedIndexChanged(object sender, EventArgs e) 
    //event code that should show listbox selected data in the textBoxes 
    {    
     if (listBox_inimesed.SelectedItem != null && rdr != null) 
     { 
      try 
      { 
       if (rdr.Read()) 
       { 

        textBox1_firstname.Text = rdr.GetString(1); 
        textBox2_lastname.Text = rdr.GetString(2); 
        textBox3_email.Text = rdr.GetString(3); 
        textBox4_address.Text = rdr.GetString(4); 
        dateTimePicker1.Value = rdr.GetDateTime(5); 
        richTextBox_info.Text = rdr.GetString(6); 
       } 
       else MessageBox.Show("Object not found"); 
      } 
      finally 
      { 
       rdr.Close(); 
      } 
     } 
    } 
} 

Answer 1

您正在使用的阅读器所依赖的连接已在button_ShowUsers_Click事件中关闭。

尝试跨事件共享连接和数据读取器不是一个好习惯。这将导致打开的连接没有正确处理。更好的做法是在每个事件方法中创建Connection，Command和DataReader。通过使用“使用”语句，它们将在每种方法中被关闭并正确处置。您也可以删除类级变量“rdr”。

namespace Userform 
{ 
    public partial class Form1 : Form 
    { 
     const string connectionString2 = @"Data Source=MyDatabase;Password=xxxxxx;"; 

     public Form1() 
     { 
      InitializeComponent(); 
     } 

     // Some code between... 

     private void button_ShowUsers_Click(object sender, EventArgs e) //code that shows users in listBox 
     { 
      var dt = new DataTable(); 

      using (var cn = new SqlCeConnection(connectionString2)) 
      using (var cmd = new SqlCeCommand("Select * From Users", cn)) 
      { 
       cn.Open(); 

       using (var reader = cmd.ExecuteReader()) 
       { 
        dt.Load(reader); 
        var results = (from row in dt.AsEnumerable() 
            select new 
            { 
             //UserID = row.Field<int>("ID"), 
             FirstName = row.Field<string>("Firsname"), 
             LastName = row.Field<string>("Lastname"), 
             FullName = row.Field<string>("Firstname") + " " + row.Field<string>("Lastname") 
            }).ToList(); 

        listBox_users.DataSource = results; 
        listBox_users.DisplayMember = "FullName"; 
       } 
      } 
     } 

    //I made an event for the listBox_users: 

     private void listBox_users_SelectedIndexChanged(object sender, EventArgs e) 
     //event code that should show listbox selected data in the textBoxes 
     { 
      if (listBox_inimesed.SelectedItem != null) 
      { 
       using (var cn = new SqlCeConnection(connectionString2)) 
       using (var cmd = new SqlCeCommand("Select * From Users", cn)) 
       { 
        cn.Open(); 

        using (var reader = cmd.ExecuteReader()) 
        { 
         if (reader.Read()) 
         { 
          textBox1_firstname.Text = reader.GetString(1); 
          textBox2_lastname.Text = reader.GetString(2); 
          textBox3_email.Text = reader.GetString(3); 
          textBox4_address.Text = reader.GetString(4); 
          dateTimePicker1.Value = reader.GetDateTime(5); 
          richTextBox_info.Text = reader.GetString(6); 
         } 
         else MessageBox.Show("Object not found"); 
        } 
       } 
      } 
     } 
    } 
}