CODEWITHSUNDEEP
c#sqlite
user2363676
user2363676

Reputation: 341

C# with System.Data.SQLite - closing connections

I'm well aware that I should do SELECT query like that:

System.Data.SQLite.SQLiteConnection scrsql_con = new System.Data.SQLite.SQLiteConnection("Data Source=db.db;Version=3;New=False;Compress=True;");
scrsql_con.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = "Select something FROM something";
cmd.Connection = scrsql_con;
SQLiteDataReader dr = cmd.ExecuteReader();
//reading stuff from datareader...
dr.Close();
scrsql_con.Close();

However, there are a lot of SELECT queries in my application, so I decided to make a method for that. Right now it looks like the following:

public static SQLiteDataReader GenericSelect(String query)
{
        System.Data.SQLite.SQLiteConnection scrsql_con = new System.Data.SQLite.SQLiteConnection("Data Source=SCRdb.db;Version=3;New=False;Compress=True;");
        scrsql_con.Open();
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.CommandText = query;
        cmd.Connection = scrsql_con;
        SQLiteDataReader dr = cmd.ExecuteReader();
         return dr; 
}

But it's not very good, since it leaves scrsql_con hanging.I can't close it from inside of GenericSelect method, because it means that it will always return empty datareader or error, and I can't close it from the outside. Any suggestions how should I do GenericSelect properly so it keeps returning datareader?

I know I can use datatable, but aside from performance,this method is used in a lot of places, so I will save a lot of time if it keeps returning what he returns now.

Upvotes: 3

Views: 2188

Answers (1)

Steve
Steve

Reputation: 216363

A first fix is 

SQLiteDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

and this according to MSDN

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Of course it is now of paramount importance to be sure to call SQLiteDataReader.Close.

Upvotes: 2

Related Questions