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 Features for System.Data.SQLite build 3.7.12.1

Couldn’t help sharing the new features of System.Data.SQLite, really impressive. the following text is been taken from the read me file what I saw after a successful installation of New .net provider provided by http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki.

Features

• Written from scratch on Visual Studio 2008 specifically for ADO.NET, implementing all the base classes and features recently introduced in the framework, including automatic transaction enlistment.
• Supports the Full and Compact .NET Framework, and native C/C++ development. 100% binary compatible with the original sqlite3.dll.
• Full support for Mono via a "managed only" provider that runs against the official SQLite 3.6.1 or higher library.
• Full Entity Framework support (ADO.NET 3.5 SP1).
• On the Compact Framework, it is faster than SQL Server Mobile. SQLite's installed size is a fraction of SQL Mobile's. It uses less memory at runtime, runs queries faster, and has a smaller database file size as well.
• Encrypted database support. Encrypted databases are fully encrypted and support both binary and cleartext password types.
• Visual Studio design-time Support, works with all versions of Visual Studio 2005/2008/2010. You can add a SQLite database to the Servers list, design queries with the Query Designer, drag-and-drop tables onto a Typed DataSet, etc.
  Due to Visual Studio licensing restrictions, the Express Editions can no longer be supported.
• Full SQLite schema editing inside Visual Studio. You can create/edit tables, views, triggers, indexes, check constraints and foreign keys.
• Single file redistributable (except on Compact Framework). The core SQLite native code and the ADO.NET managed wrapper are combined into one mixed-mode assembly.
• Binaries included for x86, x64, Itanium, and ARM processors.
  Itanium processor support not currently included.
• DbProviderFactory support.
• Full support for ATTACH'ed databases. Exposed as Catalogs in the schema. When cloning a connection, all attached databases are automatically re-attached to the new connection.
• DbConnection.GetSchema(...) support includes ReservedWords, MetaDataCollections, DataSourceInformation, DataTypes, Columns, Tables, Views, ViewColumns, Catalogs, Indexes, IndexColumns, ForeignKeys and Triggers.
• Enhanced DbDataReader.GetSchemaTable() functionality returns catalog, namespace and detailed schema information even for complex queries.
• Named and unnamed parameters.
• Full UTF-8 and UTF-16 support, each with optimized pipelines into the native database core.
• Multiple simultaneous DataReaders (one DataReader per Command however).
• Full support for user-defined scalar and aggregate functions, encapsulated into an easy-to-use base class in which only a couple of overrides are necessary to implement new SQL functions.
• Full support for user-defined collating sequences, every bit as simple to implement as user-defined functions and uses the same base class.
• Full source for the entire engine and wrapper. No copyrights. Public Domain. 100% free for commercial and non-commercial use.

Hope this time SQLite will be more popular among developer of .net platform. Happy coding.

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.

Using LINQ to SQL with SQLite database

In my previous short article we have discussed about how to use Ado.net Entity Framework with SQLite database. And we have witnessed that its almost as like as SQL server in context of ease and usability when you have the right provider.

Today we are going to see how we can use LINQ to SQL with SQLite database. We always looks for ways that would make our life easy and most important no more re-inventing the wheel. By default you can not drag and drop tables from server explorer or from data connections. Its because we don’t have any built in provider that support LINQ and if you try to do so you would certainly encounter with the following error.

Now, I know what am I going to show you wont please you but it certainly serve our purpose to use LINQ with SQLite. There is no magic or rocket engineering behind this, we have a DBLinq open source distribution that come with various sort of database support. For this particular case lets stick to SQLite. The binaries can be found here,  http://code.google.com/p/dblinq2007/, please download latest binaries from this site.

Now its time to generate the OurDatabase.dbml file. the steps are not that much complex, Located the DBLinq package that you downloaded from Google code, then unzip in a suitable folder location where you can access it easily.

Note that you need to copy the System.Data.SQLite dll in the DBLinq directory. 

Step 1: Create the dbml file

DbMetal /provider:Sqlite /conn "Data Source=File.db3" /dbml:File.dbml

Step 2: Create the code file for the dbml file

DbMetal /code:File.cs File.dbml

Copy those files to the files. That’s it you are good to go.

Make sure that every thing compiles okay by hitting build. Bellow I have put done a simple code snippet to demonstrate how to use this dbml to retrieve data.

 protected void Page_Load(object sender, EventArgs e)
        {
            string ConStr = "Data Source=" + HttpContext.Current.Server.MapPath("App_Data\\File.s3db") + ";Version=3;";
            var connection = new SQLiteConnection(
                    ConStr
                );
            connection.Open();
            var db = new Main(connection,new SqliteVendor());
            var users = db.User;
            var item = users.Take(10).ToList();
        }

I hope this above code is self explanatory. Best of luck and happy coding.

Using SQLite with Entity Framework in Visual Studio 2010

Introduction

In my previews blog post we have seen how using a simple “data provider” provided by http://sqlite.phxsoftware.com/ can make our life easier in context of SQLite in .net. In this short article we are going to see how we can use SQLite to generate Entity Framework Data model.

Choosing the Provider

First thing first create any type of project in Visual studio 2010. But I would prefer an web application or windows forms or perhaps a WPF application. Now click on project and select add new item to add a new item type. From Add new item wizard select “Ado.net Entity Data Model” and click on add this will brought up the Entity data model wizard.

• Step 1: Select Generate from Database
• Step 2: In next window select New connection
• Step 3: Connection properties window will popup and the select right kind of provider for SQLite.
• Step 4: After that the Connection properties will have browser or New db,

• Step 5: Now select existing db if you have using browser or New to create new db.
• Step 6: Continue with the wizard to select database table items to include in model.
• Step 7: Click finish to close the wizard and a data model will be generated for you.

If you had any relationship in the database those will be automatically be synchronized in data model, that’s it we are good to jump in the code to retrieve data and do all type of necessary stuff that we must do in a database. Best of luck and 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.

SQLite management tool.

SQLite have lots of management tools, some of them are open source and some of them are trial ware and some of them are commercial. I found “SQLite Administrator” Pretty easy to use and handy tool as far as SQLite database file is concern.

A complete list of SQLite management tools can be found here “http://sqlite.com/cvstrac/wiki?p=ManagementTools” download the use what ever best suits you.

Today I am going to talk about only in SQLite Administrator, It can be downloaded from the following site.

http://sqliteadmin.orbmu2k.de/

A Complete list of feature is given In that site.

Features:

• Create / Modify / Delete Tables by Wizard
• Create / Modify / Delete Indices by Wizard
• Create / Modify / Delete Views by Wizard
• Create / Modify / Delete Triggers by Wizard
• SQL Code Completion that supports table aliases
• SQL Code Highlighting
• SQL Error Locating
• Import Data from CSV Files
• Export Data ( XLS / CSV / HTML / XML )
• Store User Queries into Database
• Search for User Queries
• Store Images into Blob Fields ( JPG / BMP )
• Show SQL of each Database Item
• Migrate SQLite2 Databases to SQLite3
• Try to keep Indices and Triggers after modifying a Table

 

Note

This tool need no installations what so ever. The whole package is in a zip file and need zero installation, just unzip and open the exe that’s it, and All of the functionality is pretty easy and it has very intuitive UI. I would recommend it to every one who work with SQLite.

Limitations of SQLite version 3

Found a good source of Limitations of SQLite embedded database. And I am quite impressed about the numbers that have as a limit in any category. I my opinion those are not limitations . Rather those are supports. You can take a look at this http://www.sqlite.org/limits.html where all the max stuff is listed. Here are few examples, and all are taken from the original site.

Maximum Number Of Rows In A Table

The theoretical maximum number of rows in a table is 2⁶⁴ (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 14 terabytes will be reached first. A 14 terabytes database can hold no more than approximately 1e+13 rows, and then only if there are no indices and if each row contains very little data.

Maximum length of a string or BLOB

The maximum number of bytes in a string or BLOB in SQLite is defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value of this macro is 1 billion (1 thousand million or 1,000,000,000). You can raise or lower this value at compile-time using a command-line option like this:

-DSQLITE_MAX_LENGTH=123456789

The current implementation will only support a string or BLOB length up to 2³¹-1 or 2147483647. And some built-in functions such as hex() might fail well before that point. In security-sensitive applications it is best not to try to increase the maximum string and blob length. In fact, you might do well to lower the maximum string and blob length to something more in the range of a few million if that is possible.

During part of SQLite's INSERT and SELECT processing, the complete content of each row in the database is encoded as a single BLOB. So the SQLITE_MAX_LENGTH parameter also determines the maximum number of bytes in a row.

The maximum string or BLOB length can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface.

Maximum Number Of Columns

The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767. On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

In most applications, the number of columns is small - a few dozen. There are places in the SQLite code generator that use algorithms that are O(N²) where N is the number of columns. So if you redefine SQLITE_MAX_COLUMN to be a really huge number and you generate SQL that uses a large number of columns, you may find that sqlite3_prepare_v2() runs slowly.

The maximum number of columns can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COLUMN,size) interface.

Maximum Number Of Tables In A Join

SQLite does not support joins containing more than 64 tables. This limit arises from the fact that the SQLite code generator uses bitmaps with one bit per join-table in the query optimizer.

SQLite uses a very efficient O(N²) greedy algorithm for determining the order of tables in a join and so a large join can be prepared quickly. Hence, there is no mechanism to raise or lower the limit on the number of tables in a join.

Note: I am sure those are pretty good numbers as far as an embedded database can offer.