Thursday, June 28, 2012

Using SQLite in Visual Studio 2010, with new and improved provider part1

Introduction

Almost a year ago I have written a blog post about SQLite with VS2010, and the old blog post can be found here, http://munnaondotnet.blogspot.com/2011/07/using-sqlite-in-visual-studio-2010.html. Lot of things has changed since then and its time to update the users and take everyone up to the speed. In fact may be all are up to the speed, I am the one who is doing the catch up any way, Previously the System.Data.SQLite provider was taken care by “http://sqlite.phxsoftware.com/” now SQLite.Org has taken the control of managing the provider and further development.

I have already listed a blog on who to install and the some features on on http://munnaondotnet.blogspot.com/2012/06/new-and-improved-systemdatasqlite-also.html and http://munnaondotnet.blogspot.com/2012/06/new-features-for-systemdatasqlite-build.html. in this blog I would simply show adding a data connection to Visual Studio 2010.

Adding the data connection

After installing the installation package if you open visual studio and click on Add connection it will simply lunch the following window.

image

Click on Change and this will again popup a new window like bellow dialog. You would see that there is a “System.Data.SQLite Database File”  option in Data Source List. Select the option and click ok.

image

After that you would get another option where you can either browse or create a new SQLite database file. For Now I have selected “New” and a file Open dialog box would popup where I have created a new SQLite database file, alternately you can also browse and choose your existing database file.

image

In the above window you can also choose to set password, for encryption and other necessary settings if you choose to have different setting rather than default.

For now we have the option to choose

  • Encoding
  • Date/Time Format
  • Synchronization

When you are done ok button will be enabled and click ok to add the connection.

image

Creating Tables And Views

WALAH! a SQLite data connection is been added in the data connections node of server explorer. Now its time to create a database table, the process is simple and just like the previous blog, right click on the Table node and you would see that there is a option says “Add New Table”, selecting the option will start the table designer, note that you would see a Warning message, fear not it just a bug reporting email address message.

image

while Creating the table  you would see that there is a huge set of database data type you have in SQLite, and almost everything is supported now including “nvarchar”. 

image

Alternately you can also create views and in conjunction with Table. Which is yet another great support. I am looking forward to get some Store procedure support in near future.

image

Conclusion

In this short article we have seen how we can use Visual Studio 2010 to edit and create a simple SQLite database, and from now on there is no need for another third-party SQLite database manager tool. Note that the support is only available in Standard or upper version of visual studio 2010, so no support in express editions, which is kind of bad for open source developers, but there is a licensing issue, so SQLite.Org has to go in this direction.

Hope to show some designer support option in next blog.  

No comments:

Post a Comment