UsingSQLiteProviderWithVS2005

Introduction

Using SQLite OLE DB Provider in Visual Studio 2005 IDE is really easy. But some of you may or may know how to set it up. We are going to show you how to set it up and what problems you may run into.

Setup SQLite Provider with VS 2005

We will guide you through step by step to complete the setup process

1. Find Server Explorer in VS 2005, right click Data Connections and select Add Connection... on context menu. Then you shall see pop up dialog below
   
2. Click Change... button to change Data Source.
   
3. Select in the Data source list, select .NET Framework Data Provider for OLE DB in the Data Provider drop down list and then click OK button.
   
4. Select SQLite Provider in OLE DB Provider, Click Data Links... button so that you can fill SQLite database file name (must be full qualified path) into the Data Source field. Click OK on the dialog above. Now your data connection shall be added into Server Explorer panel.

Running SQLite QUeries

You setup SQLite OLE DB provider with VS 2005 IDE. Now you can run SQLite queries by right clicking the data connection you just added and the select New Query on the context menu.

Select a table and click add and close buttons. You shall be able run queries now.

Problems

Visual Studio 2005 verifies the query syntax before it calls OLE DB Provider. However, SQLite query syntax is not complete the same as that for SQL Server. For example, 'SELECT * FROM CherryLog4Net order by ID desc limit 5' is a valid SQLite query. But VS 2005 think it is in valid and it pops up a error message box as following:

Fortunately, Microsoft® does not prevent us from running this query. We can simply click continue to let the query run.

Currently, SQLite OLE DB provider does not support query syntax validation before running the query. It would be a very nice feature if we had it implemented.

One other thing we have to point out is that it is always a good idea to set a limit for number of records you want to retrieve from SQLite database because it may take very long if you have millions of records.