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.

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.

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.

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.

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.

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.

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”. 

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.

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.  

New and improved “System.Data.SQLite” also has vs2010 designer support!

Hi today I am going to share some valuable information about “System.Data.SQLite” here is the few important thing you must know before moving on,

Previous development is been discontinued by http://sqlite.phxsoftware.com/. And the development is been continued in the http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki. Well there are huge set of distribution in here for all kind of customized need.

But I want to focus on a particular distribution. You can download it from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki. Note that you have to download the package “Setups for 32-bit Windows (.NET Framework 4.0)” and in particular which says “This setup package is capable of installing the design-time components for Visual Studio 2010.”

I have downloaded it and here is the installation screen after two next :D. Can you see some awesome changes two particular thing captured my eye. It has “Linq Support” and “Visual studio designer support”  a supper WOW! to the development team. You guys really did made a huge difference to the force. May the force be with you forever.

In the installation process don’t forget to check the option where it says “Install the assemblies into global assembly cache”. after that you also must check “Install the designer component for Visual Studio 2010.”

There are lots of command line window is going to popup after the installation starts don’t accidently close the windows let the installation continue it will finish and then redirect the user to a new readme and changes html file.

You are good to go with the new and improved support for SQLite. Go a head build some killer application using System.Data.SQLite. Until next time Happy coding.

Edit SQLite database using Visual Studio 2010 or SQLite Administrator

Introduction

In my previous blog I have shared few useful information about SQLite Embedded database and how to use its provider to create an connection using System.Data.SQLite. We have also seen a simple tool named “SQLite Administrator” which is very handy to use with SQLite database. In this short article we will see that how we can create and manipulate  data using Visual Studio 2010 or SQLite Administrator.

Creating Table in Visual Studio 2010

Select the SQLite connection that you created and expend the Tables node, then right click and select “Add New Table” that’s it you are good to go to add a table just like the bellow figure. I am sure you guys have done this hundreds of time in SQL server, this time its just SQLite. you might see a warning when you first enter into edit of table editor.

Using SQLite Administrator

Double click on the SQLite database file and right click on tables and click "Create Table”, rest of the UI is fanatic to Create the table. Set field as primary key or assign auto increment to primary key all can be done via simple clicks. So no custom hand written SQL any more.

Creating Relations in Visual Studio 2010

Creating table relations are easy in visual studio, just click on relation icon, the icon beside primary key, in fact to be precise right side icon of primary key button. Or perhaps you can select a table and then click on relations, this will popup an widget to create relation.

Creating auto increment primary key is a not that much difficult also, now a days in every table we use auto increment key so we must discuss about it. at first glance we didn’t find any button or option to assign it directly. You can use SQLite Administrator to add it in few clicks, but to add in Visual Studio 2010 you got to bring the indexes window, just click on Indexes after that you will find option to add auto increment column.

I hope this information's will be helpful to us to work better with SQLite. Hope to write more information's about SQLite in near future. Happy coding.