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.

Long Parameter List: A very long function declaration

Todays topic of discussion is long parameter list, to be sure that we are in same page I must first mention that “long parameter list” is a kind of “code smell”. Wikipedia has nothing to say about it, if anyone get a reference of a good resource please feel free to add in the comment section.

What is long parameter list?

Method or procedure that take too many argument tend to produce complex code and unreadable complex method, developer need to dive big time in a method to find out what it is doing!

But as big things has small beginning no method has this smell at the beginning I guess, as time progresses and developer start putting parameter one after another to accommodate long parameter list in a function and some thing end up with not only long parameter list but also long method.

Bellow a long parameter list method is given!

public SomeDetail GetSomeDetail(string payingId, string receivingId, string paymentStream, double holdingPercentage, int holdingPeriod,int param, int param2, int anotherparam, string moreparam, string andmore)
{
   ---------------------your code goes here
}

Where Long parameter list is allowed?

In some language there is possibilities of adding default value to a parameter, for instance C++, Python and C# (.NET 4.0 onwards) have this ability to have default value for a parameter on the other hand C and Java do not have such ability.

How to avoid long parameter list?

First of all the most easy way out is to use object class not clear ? for instance we have a long parameter method which is having a1,a2 …. a*n parameter, we can form a class named “ourclass” and then add members like “a1,a2…. a*n” and then pass single object to a method.

Secondly we can break the method in few smaller piece of method so that each method have few parameter and do only one thing, as long parameter list methods tend to do lot of work all by it self.

How lets look at a good example!

public IEnumerable<RepositoryItemInfo> GetAllDocumentInfo(string directory, string[] filteredExtensions)
{
    return Service
        .GetAllDocumentInfo(GetProxyCredential(), directory, filteredExtensions)
        .Select(proxyDoc => ConvertProxyDoc(proxyDoc));
}


private static RepositoryItemInfo ConvertProxyDoc(RepositoryItemInfo info)
{
    return new RepositoryItemInfo
               {
                   Created = info.Created,
                   FullName = info.FullName,
                   Id = info.Id,
                   IsFolder = info.IsFolder,
                   LastModified = info.LastModified,
                   MetaData = info.MetaData,
                   ModifiedBy = info.ModifiedBy,
                   Name = info.Name,
                   FileSize = info.FileSize
               };
}

In above code we can see that RepositoryItemInfo is a class which warps 9 parameter in a simple class and is been passed to a service method to get information.

References

• http://sourcemaking.com/refactoring/long-parameter-list
  
• http://c2.com/cgi/wiki?TooManyParameters

Speculative Generality: Build something even if you don’t need it right now!

Today we will have a short discussion about Speculative generality, it yet another kind of code smell, not in case of actual code but in case of design.

Did you ever write code that was not needed right then , rather you wrote it in case you needed it in future?, If the answer is yes then you probably, in fact did introduced Speculative generality with or without knowing it.

Here is advice from the Experts

“Please don’t write anything in your code base unless your needed it!”

Lets be frank we don’t know what client want, infect the preceding statement is not true for all cases for instance when you follow strict water fall, but that is a whole different store as far as software development processes is concern. So in an ideal world I would say that on 80% client don’t know what they want so its obvious that developers can not predict, when we build something and show it to customers, customers will always will have some opinion on average case they want some modification.

So we have to build just what is necessary for reaching a short goal, after that along the way we would start fit whatever is necessary.

An Interesting Example could be, event though we don’t need any Abstract class for a view we spontaneously created an interface and an abstract class.

We can put down lots of stuff like that.

Rules

1. If there is only one class that implements a interface probably we don’t need it.
2. If there is only one sub class of a superclass, again probably we don’t need it.
3. If only single function works okay for a method we don’t need any overload.
4. And so on.

So from now on we would avoid any code that is not necessary.

Switch Statement: Same choice once again.

You have been there and done that thousands of time, but did you know that switch statement is a code smell? I am sure lot of people don’t know about this. I my self got surprise to know that switch statement is a code smell, when I attended my first agile process training.

Why switch statement is code smell?

Where ever there is a need for switch statement there is a good chance of polymorphism. But we must need to consider the context as well. Note that there is another form of switch statement,

(if … else if … else if .. )  is also another from of switch statement where polymorphism can be considered.

One thing we must consider that where converting a switch statement to a polymorphic solution often create a bunch of classes so make sure before creating classes that they worth doing and actually abstract some part of the code and take significant amount of code in a child class so that it does not become a lazy class.