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

Introduction

In previous post I have shown how to add a data connection in server explorer for a SQLite database file, and along the way also showed the process to create a new SQLite database file using visual studio 2010. if you haven’t read it or wish to read it please visit http://munnaondotnet.blogspot.com/2012/06/using-sqlite-in-visual-studio-2010-with.html farther more you can visit http://munnaondotnet.blogspot.com/search/label/SQLite for other SQLite post.

In this post we are going to discuss about few basics about SQLite designer in Visual Studio 2010, we have already see some basics of how to create table and view in previous post in this post we would look some of the related staff.

The designer tools

All the tool related to the SQLite designer is accessible from the Visual studio toolbar when you select data table or any database object. Note that the tool specific for each purpose is context sensitive, so upon selecting right object the tool button will get enabled.

After creating the table first thing we want to do is to create a primary key from for the table, hopefully we have id column for our tables, to do this we can select the column the either click on the tool bar or right click and select set primary key from context menu. Pretty simple.

You can also see the generated script if in case you needed it, for this you have to select the table then select generate change script from context menu. Bellow I have given a screen show which shows the table “user”’s generated script in a dialog window.

To setup unique ness you have to set it from the table designer properties, which is situated down the table designer column editor. Bellow a screenshot is shown for this purpose.

setting up auto increment column for primary key

In this section we would see how to setup a primary key which is auto increment or identity column. To do this simply click on index editor from context menu or tool menu. You would see a screen dialog named Index Editor, where you have the option to set up Constrains, The top most is “Auto Increment”.   

However if you have data type other than “Integer” for your table primary key you would end up with the above error message, work around is simple and straight change the  primary key column to integer and then set auto increment to true and finally save.

Retrieving the data

Most easy part, just right click on the table and the select Retrieve data, note that you can also use this to insert data in design view, there is a editable grid shown upon selecting the “Retrieve Data” option.

Conclusion

In this short article we have see some basic uses of SQLite Visual Studio designer, and some obvious tips and tricks that would help us while working with it.

Until next me Md. Masudur Rahman Signing out, Happy coding.