Using SQLite with C# winforms application - a few basic questions??

Question

Hi,

Would be great to get some direction re trying to use sqlite for my WinForms application I'm building in VS2008.

1. Installation - Is it just drop the "System.Data.SQLite.DLL" file into some folder in my VS2008 project (e.g. create a folder for it), and then create a "reference" to it? I've made the property of the reference CopyGlobal = TRUE. Is the idea that when I deploy my application this should work (e.g. deploy the DLL for the application)

2. Initial Database - Do I have to create an initial database or not? I see the below mentioned code in the Help file but what is the DB it actually connects to and where would the DB file be?

   DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
   using (DbConnection cnn = fact.CreateConnection())
   {
     cnn.ConnectionString = "Data Source=test.db3";
     cnn.Open();
   }
   

3. What methods to use - Is this typically how I would use/make calls?

   DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
   using (DbConnection myconnection = fact.CreateConnection())
   {
     myconnection.ConnectionString = "Data Source=test.db3";
     myconnection.Open();
     SQLiteTransaction mytransaction = SQLiteTransaction)myconnection.BeginTransaction();
     SQLiteCommand mycommand = new SQLiteCommand((SQLiteConnection)myconnection);
     mycommand.CommandText = "SELECT * FROM SYSTEM";  
     mycommand.ExecuteNonQuery();
     mytransaction.Commit();
     myconnection.Close();
   }
   

4. How would I setup the database tables? Would I do this and store it in my VS2008 project as a template? Or would I want to automatic the creation of the database in code if it wasn't there?

5. If the idea from 4 is to setup tables prior, where would I store this initial database file? such that when I run the project to test it and then I use the database file, the one I'm testing with gets scrapped afterwards. I guess I'm asking how to ensure I have a separate blank but configured (with tables) database as "source" in my VS2008 project, but then when I run/debug it, it would take a copy of this for use in testing?

Thanks

Answer 1

1. Yes. SQLite is an xcopy-deployment database; there's no registration and your app only needs the dll to use it.

2. The database is specified in the connection string. In this particular case, it's in the test.db3 file in the application working folder.

3. You might want to cache the connection to the database to avoid the expensive operation of opening it every time you need to access it. Also, I am not sure why you need a transaction, since all you do is just read from the database.

4. You have two choices - either store the empty database with the precreated schema and copy it to the output dir during your build process; or create a set of SQL scripts to execute against the database from your code on the first connection to the database. Which one you choose depends on whether you want your application to be responsible for creating the schema or your build process.

5. If you create an empty database with precreated schema, you can add it as a file along your sources and instruct VS to copy it to the output dir (as I already mentioned).

Answer 2

I almost always use using so:

using (DbConnection conn = new SQLiteConnection(...)) {
   using (DbTransaction tran = conn.BeginTransaction()) {
      using (DbCommand comm = conn.CreateCommand()) {
          ...
      }
      tran.Commit();
   }
   conn.Close();
}

To make sqlite perform you have to use transactions for insert, update and delete and you have to use paramaterized queries. Concatenated queries are a lot slower: http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251