Using SQLite in Visual Studio 2010

Introduction

Every now and then we need a simple database solution which is light, small and pretty easy to use. What more can be useful other than SQLite, A simple yet very powerful tool to support database for any kind of application needs. When we started this research work we had a particular focus to choose right kind of database depending on few criteria.

• We don’t want to use a service based database rather a file system based database.
• Client have to be light, fast and support blob data.
• We don’t want to install any client or framework in client machine

 

Background

After few minutes of research in the internet we came up with few embedded file system database that can be used in .net and has support, necessary tool and provider.

1. SQLite
2. SQL Compact Database
3. Firebird

Among those SQL Compact DB is really have good support with visual studio IDE and very easy to use but it has its own limitations and we would definitely discuss in future about it. But the end result was that we found that SQLite is winner in every case and that’s why we choose to recommend it in this short article.

Adding the provider

Next step is to add a data connection in server explorer in Visual studio 2010. When you click on add connection by default you don’t have provider added in visual studio, so we have to add it using a third-party tool. You can download the provider for SQLite from http://sqlite.phxsoftware.com/ site. “SQLite-1.0.66.0-setup” this is the version we used for this particular case. When you install this provider you would be given an option to integrate this in vs2008 and vs2010.

After installing the provider when you get back to the add data connection in server explorer you would see an new provider is available for SQLite.

Adding the connection is Server Explorer

Go ahead and click on after choosing SQLite Database File. Next we will be presented with a screen where you would either choose an existing database or created a new database file.

That’s it your connection string and database connection is set up and you are good to go with design of the database. Few interesting note on this particular case. My SQL database have no database diagram support yet. But we hope that this will be provided soon. After adding connection we can created tables and add necessary relations to the table.

Conclusion

In this short article we have seen that how easily SQLite can be used in visual studio 2010 using a provider to create connection in server explorer. Next we would discuss how to use SQL Lite to create a data layer using Entity Framework.

Update

New article with new provider by SQLite org is published here http://munnaondotnet.blogspot.com/2012/06/using-sqlite-in-visual-studio-2010-with.html