将数据库加载到对象中(Load database into an object)

将数据库加载到对象中(Load database into an object)

我想问一下是否可以将数据库加载到数组或列表中，然后对其运行查询？ 我有以下代码。

string cs = "Data Source=dataBase.sqlite;Version=3;"; SQLiteConnection con; SQLiteDataAdapter adapt; DataTable dt; private void textBox1_TextChanged(object sender, EventArgs e) { con = new SQLiteConnection(cs); con.Open(); adapt = new SQLiteDataAdapter("select * from Table1 where CnName1 like '" + textBox1.Text + "%'", con); dt = new DataTable(); adapt.Fill(dt); dataGridView1.DataSource = dt; con.Close(); }

这有效，但每当运行查询时它会创建一个新的dataTable，问题代码是：

dt = new DataTable();

该程序意味着不断运行，因此效率低下，因为它会占用大量内存。 如何将数据库加载到对象中，然后对该对象运行查询？ 该表仅包含1列，运行的查询仅用作搜索功能。 我只想加载数据库一次，也就是程序启动时，然后关闭连接，其他一切都将通过程序完成，而不是数据库。

编辑：我想声明查看此问题的其他人也查看saab669的答案，因为它提供了有用的信息，但是我不能选择两个答案。

I would like to ask if it is possible to load a database into an array or list, and then run queries on it? I have the following code.

string cs = "Data Source=dataBase.sqlite;Version=3;"; SQLiteConnection con; SQLiteDataAdapter adapt; DataTable dt; private void textBox1_TextChanged(object sender, EventArgs e) { con = new SQLiteConnection(cs); con.Open(); adapt = new SQLiteDataAdapter("select * from Table1 where CnName1 like '" + textBox1.Text + "%'", con); dt = new DataTable(); adapt.Fill(dt); dataGridView1.DataSource = dt; con.Close(); }

This works, but it creates a new dataTable whenever a query is run, the problem code is:

dt = new DataTable();

The program is meant to be constantly running, so this is inefficient, since it will eat up a lot of memory. How do I load the database into an object, and then run queries on that object? The table is only meant to have 1 column, and the queries run will only serve as a search function. I want to load the database only once, that is when the program is started, then the connection will be closed, and everything else will be done with the program, not with the database.

Edit: I would like to state for anyone else viewing this question to also view saab669's answer, as it provides useful information as well, however I can not choose two answers.

最满意答案

假设您有一个包含文本框的表单。 声明一个类级变量来存储数据表。

private DataTable _data;

创建一个类来封装数据库连接和数据检索。

public class MyDataBaseConnection { public DataTable ReturnMyData(string valueFromTextBox) { var cs = "Data Source=dataBase.sqlite;Version=3;"; SQLiteConnection con; SQLiteDataAdapter adapt; DataTable dt; try { con = new SQLiteConnection(cs); con.Open(); adapt = new SQLiteDataAdapter("select * from Table1 where CnName1 like '" + textBox1.Text + "%'", con); dt = new DataTable(); adapt.Fill(dt); con.Close(); return dt; } catch (Exception ex) { //Log here. throw; } finally { con = null; adapt = null; //Or Dispose. I dont have SQL lite so dont know if they implement IDispose } } }

在文本框更改事件中，调用db代码并分配给类级别var

private void textBox1_TextChanged(object sender, EventArgs e) { var myDBConnection = new MyDataBaseConnection(); _data = myDBConnection.ReturnMyData(textBox1.Text); dataGridView1.DataSource = null; dataGridView1.DataSource = _data; }

当文本更改事件再次触发时，数据将在网格中更改。

希望有所帮助。

Assuming you have a form containing your text box. Declare a class level variable to store you datatable.

private DataTable _data;

Create a class to encapsulate your database connection and retrieval of data.

public class MyDataBaseConnection { public DataTable ReturnMyData(string valueFromTextBox) { var cs = "Data Source=dataBase.sqlite;Version=3;"; SQLiteConnection con; SQLiteDataAdapter adapt; DataTable dt; try { con = new SQLiteConnection(cs); con.Open(); adapt = new SQLiteDataAdapter("select * from Table1 where CnName1 like '" + textBox1.Text + "%'", con); dt = new DataTable(); adapt.Fill(dt); con.Close(); return dt; } catch (Exception ex) { //Log here. throw; } finally { con = null; adapt = null; //Or Dispose. I dont have SQL lite so dont know if they implement IDispose } } }

In your textbox change event, call the db code and assign to your class level var

private void textBox1_TextChanged(object sender, EventArgs e) { var myDBConnection = new MyDataBaseConnection(); _data = myDBConnection.ReturnMyData(textBox1.Text); dataGridView1.DataSource = null; dataGridView1.DataSource = _data; }

When the text changed event fires again, the data will be changed in the grid.

Hope that helps.