Load database into an object

Question

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.

Answer 1

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.

Answer 2

My response is too long for a comment, but I want to reply to a few things you said:

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.

Sort of. You only ever have one DataTable object. It's just that every time that event fires, it's re-querying the database. So you're not consuming an excessive amount of memory (unless your DB is huge!), or at least not in the way you think you are.

How do I load the database into an object, and then run queries on that object?

As I mentioned in my comment, you're doing that already. Just in a less-than-ideal event handler. Depending on how you want the application to behave, you should move this to the form load event as defined here. Or perhaps it might make more sense to have a button with a click event. The form load could add a long delay to your application's start up and the user might think it froze, depending on how long it takes you to fetch all the records from the DB.

Also, from the code snippet you provide, am I correct in assuming you define DataTable dt; outside of the event handler, on the class level?

Anyways, per the MSDN article I linked in my comment, once you have a DataTable populated then you can simply do things like this:

DataRow[] foundRows;
foundRows = dataSet1.Tables["TableName"].Select("ColumnName LIKE 'your_search_value%'");

Lastly, and I cannot stress this enough about the code you provided: DO NOT concatenate strings for a query which will execute against a database. I don't care if it's for a homework assignment or a little tool only you will use, there's no excuse for not taking the extra time to learn how to do it the right way. There's no reason to build a bad habbit, as this is susceptible to SQL injection which is comically easy to protect against. You absolutely should spend the 30 minutes to learn about parameterized queries.