#SQLite Database #NuGet Package: Common Problems Solved



SQLite is a file based database, or an embedded database that you can use without need for any special installation from your side. It’s a bit popular for non-LOB (line of business) desktop applications and even mobile applications like Android apps. It also has an "In-Memory" mode where there is no physical file used as a database, which is usually used in testing.

It has a similar syntax (sort of) to SQL Server. In Microsoft .NET world,, those using NHibernate are usually using the in-memory mode for applying unit testing or trying out NHibernate stuff, although Microsoft has its own embedded database called SQL Compact Edition (CE), which doesn’t seem to support in-memory databases (v4.0).


You can download the latest Sqlite database (v3.0) from:


Since it’s not SQL Server, you need a special ADO.NET provider too. It’s called "System.Data.Sqlite"., which is very similar to the normal System.Data.SqlClient stuff. It’s included in the above download link.



If you don’t know what NuGet is, go to www.nuget.org or check my old posts mentioning it, or if you are an Arab maybe check this post in Arabic.

NuGet has become the No. 1 way I use to find libraries, and I usually really get mad if I don’t find the library in there or I have to get a more recent version from library source code, which luckily doesn’t happen that often.

Today I discovered that NuGet already has a NuGet package:



I was willing to make some test to what I believed was a bug in NHibernate 3.2, but this is another story.



I thought (like most other NuGet packages) the package will do configuration needed to up and running with SQLite, but I found that I was still getting different configuration issues.

Here is the basic code I was trying to get to work:



All the code in NHibernate code (even the dialect thing). Don’t bother yourself with it if you don’t know NHibenrate. I just wanted to show the exact path I went into when facing this.

You do NOT need NHibernate to use SQLite, you can use it just like SQL Server under System.Data.Sqlite same as you use System.Data.SqlClient for SQL Server. I’m just covering usual use of it and part of my actual real situation.

The only relevant SQLite part is the connection string, which, as this is for testing, was using in-memory database.


Configuration: Problems & Solutions


I have updated the NuGet package to include the required config entries.

This configuration section is now obsolete and for reference only.


It should be helpful though if you download the SQLite library manually not from NuGet or modified your configuration after installing the package.


The updated packages start from version




When I tried to run this, I got the following error:

Unhandled Exception: NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.SQLite20Driver. --->

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.

The first 2 lines are NHibernate thing as I know (don’t bother the "2" part in there, just because I tell you not to). The following 2 lines are just generic stuff.

The last two are what really matter, specifically:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

SQLite has a similar ADO.NET provider like SqlClient for MS SQL Server, we know we have it, but .NET is unable to find it.

To Solve this problem, I created an app.config file (if it’s a website, you’ll use web.config), and I added the following directly under <Configuration> root node:



And then tried to run again…

This above problem "might" happen with the download file included in the website directly also, not only the NuGet package.


Mixed Mode Assembly

Next time the Console had the following errors

Unhandled Exception: NHibernate.HibernateException: Could not create the driver from NHibernate.Driver.SQLite20Driver. --->

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->

System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

As usual the important bit is the last bit:

System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

To understand what’s happening, let’s read the SQLite package description in NuGet:

System.Data.SQLite is the original SQLite database engine and a complete ADO.NET 2.0/3.5 provider all rolled into a single mixed mode assembly. It is a complete drop-in replacement for the original sqlite3.dll (you can even rename it to sqlite3.dll if you’re using it natively).

OK, now we know where this "mixed mode assembly" thing is coming from. It’s an assembly that merges both native and .NET assemblies. To be honest I didn’t even know this was possible before. One more thing learned, now time to see how to get this working in .NET 4.0 – I am expecting it’s strongly attached to the version of the .NET part of the assembly.


As you expect, those kinds of things are solved in configuration also. Here is the related configuration part, also added directly under <configuration> node in app.config:



That was all. It did work afterwards.


Bonus: Using SQLite In Web / ASP.NET On Windows x64 bit


I was scared of the "<startup>" tag that it may not work nicely with ASP.NET, so, I tested the configuration and NHibernate-based test in a new web application and it worked nicely in VS 2010 and IIS.

The Problem

However, the x86 (32 bit) of SQLite (which is the one on NuGet) does not work in IIS on Windows x64 (63 bit) directly. You must enable 32-bit in the application pool.

The error:

Could not load file or assembly 'System.Data.SQLite' or one of its dependencies. An attempt was made to load a program with an incorrect format.


The Solution

As mentioned, you need to set the app pool to support 3-bit assemblies, this, go to IIS, open Application pools:


Right click the application pool you use in your website and pick "Advanced Settings…"


Then change the 32-bit setting…


And now you are done.


On a shared hosting they should be taking care of this already although I’m not sure if you’ll have more severe issues with shared hosting supporting only medium trust security. This always brings problems.


The complete Standard SQLite Configuration

For convenience, here is my complete app.config file:with required config sections.

As mentioned above, you can use those sections in any app.config or web.config files.



I intestinally added the connection string, just to show you how you should be able to do it. Of course the connection string name is up to you and you can choose a file instead of in-memory and I haven’t covered anything about SQLite really. Gogle is your friend if you are interested in more, and you can ask me also as I "might" know!


The Code On Github


As usual, you can browse or download or clone the almost-empty sample application used here from:


If you just want to download it locally I highly advise you get "git" source control and clone the code, however, if you insist on something for direct download, here it is:


I hope this has been helpful to someone trying to get started with this NuGet package.

Thank you very much for attending CairoCodeCamp`10 – Session Slides

My first DotNetwork Cairo Code Camp in 2009 was a great experience, because it was one of the biggest events I’ve spoken at (in terms of audience count, similar to SilverKey Demo Day II). However, Cairo Code Camp 10 had a much different taste!


Not just that I met Scott Hanselman, one of the most popular Microsoft guys, and hold him down as you see in the picture (which I’ll never forget), the great person and popular guru…


Not just that I met so many great other speakers, many of them are my friends and some of my friends talking for the first time in such event or after long pause…


But also because we all (speakers and organizers – both volunteers) met so many great attendees. Some of them were interesting people I’ve followed on Twitter for some time and haven’t seen them yet. Some of them were old lovely faces that we meet very rarely (especially I no longer work  in Egypt). Some of them were totally new faces, bringing a lot of ideas and very useful discussions….

Thanks a lot everybody. You made Cairo Code Camp 10 the sweetest speaking experience yet for me. I really hope you had as much fun.

Give Me Those Slides

Now to the part you are probably reading this for! I promised the great guys who asked that the slides will be up on this blog in 3 days maximum after the event closing although probably they’ll be available by DotNEtwork pretty soon. So here they’re below.

It’s really sad we couldn’t get recording this event also. You can’t imagine how hard the setup for this can be. I may consider next time to buy an extra microphone and record the talk as a webcast, but usually things are not that easy.

Using jQuery To Survive In ASP.NET Webforms World

here are the slides hosted on SlideShare:

Session Downloads:

Download: Using jQuery To Survive In ASP.NET Webforms World.pptx

Download: Using jQuery To Survive In ASP.NET Webforms World.Demos.zip

It’s interesting how we went through jQuery all from beginning. I’m so happy the goal from this session was achieved, which is to show that jQuery can does regular tasks we see in many websites without much of the pain we picture in our heads once we think about JavaScript.

Introduction To ORM Patterns With Code Samples In NHibernate

Here are the slides hosted on SlideShare:

Session Downloads:

Download: Introduction to ORM Patterns with Code Samples in NHibernate.pptx

This session had three goals, one is to open minds about patterns and practices and choices instead of follows, which was accomplished. the second goal was to introduce some common ORM patterns, which was  partially accomplished. The 3rd goal was introducing NHibernate, which we didn’t have time to since the open talk was getting much more benefiting information.

I hope most of you liked how this went, and for the rest and everybody, would you like me to refer to existing NHibernate resources that I find most useful? (most of them are in the last slide in presentation), or create my own article on it? Or Screencast? And if so, would you like them to be in English or Arabic? (which reserving terms in English of course).

Tell me and I’ll work on something…

Thanks to all of you…

This event would never have been so great without the participation of every organizer, speaker and attendee. You guys created all the great atmosphere that I will be missing probably until the next big event – only if it has you or people as great as you were. Thank you very much for everything.

Related Resources

Usually the twitter accounts are the most updated of course (maybe more than you ask for sometimes), and the facebook group/page profiles are used for emailing members/fans with the more important news. By the way, my twitter username is @Meligy.

Thanks a  lot.

Which ORM? LINQ To SQL, Entity Framework? LLBLGen? NHibernate?…?

While I was planning to write about the same topic and have the draft ready in my Windows Live Writer waiting to complete, I found an interesting question in StackOVerflow and couldn’t just resist to answer:

ORM/Persistence layer AdviceORM

The question starts with:

I’m starting a new project and I’m looking around for either a very good ORM or for a non-SQL-based persistence layer.

Then follows up with a REALLY GOOD summary of what he believes about each known ORM he knew out of his own findings and search. I advice you to go read it.

However, all this investigation didn’t get him to a single choice answer. And I can’t blame him. This is one fo the questions that will remain for so long without a single answer, or maybe having the popular “It depends” answer.

I have had a LONG research in this topic as well. I have read for so long (and watched videos/casts) to make sure of the best usage of many ORMs and then used them sometimes in test projects sometimes in production, and I wanted to share my thoughts based on this. I posted a long answer there on the question in StackOverflow, and I want to share this answer with you here. I may also have a second part of this post based on my existing Windows Live Writer draft, but, based on my previous times, I think I won’t!

Let me first quote some parts from the question itself:

I also want to avoid at all cost having to mess with string-based queries so tools supporting LINQ or otherwise intuitive and possibly strongly typed queries get a big bonus.
Finally working with POCO objects is another thing I’d really want to do
Here’s a list of products I’ve evaluated and why they don’t fit, just so that I don’t see any advice about using those:

  • NHibernate: crazy xml stuff, too much set up, high maintenance complexity and cost for model changes, session factories are messy and don’t fit well with my needs
  • Castle ActiveRecord: NHibernate based, little documentation plus some problems related to NHibernate still apply. Furthermore, to get decent models it takes so many attributes that one is better off creating the schema manually, and the way relations are handled is a shame.
  • Linq To SQL: missing POCO objects and according to MS it won’t improve much overtime (EF is what they’re committed to)
  • Entity Framweork: although in v4 POCO objects are possible, they’re still pretty hacky and force you into doing too much manual work to set things up. Besides, v4 is just a beta
  • LLBLGen Pro: good, especially with SelfServicing adapters, but not POCO. Also, the LINQ provider isn’t perfect yet. Finally, deleting a group of objects is not possible via LINQ which results in mixing APIs (one of which is far from intuitive) and that I don’t like.
  • SubSonic SimpleRepository: for a few minutes I thought I was dreaming. The deam came to an end as I figured out how the thing didn’t handle relationships
  • Here was my answer:

    If you can afford LLBLGen license, go for it.

    I seriously don’t like LINQ query-syntax the more I work with it (although I LOVE the language features related to it like Extension Methods and Expression Trees).

    I loved it at first like everybody else, but being uncertain whether [[ where employee.Name.StartsWith(“John Smit”) ]] in that XYZ LINQ provider will be done in SQL statement or in LINQ to Objects (after the SQL returns all results), and whether [[ user.Roles.Contains(role) ]] will at all work or not is a big step behind.

    LLBLGen can make deleting all items without loading them as easy as

    This is pretty simple and I like it. You get lazy loading and you set eager/deep loading by default and/or per query using Prefetch API. You can compose and construct dynamically (and easily) any filter/sort/loading at infinite levels. It’s very nice.

    There are only two problems about LLBLGen: first, it’s price not all companies would love to pay especially given the hype Microsoft alternatives have. Second, the naming convention although standard in RDBMS theories) like PredicateFactory instead of “where” or “filter” and Prefetch instead of deep loading and even SortExpression instead of orderby, those all are a little scary to a developer working with it for the first times, but soon you learn to love them, given the power and ease they give. There are talks about POCO support in LLBLGen 3.0. I cannot tell about it because I don’t know.

    Now given I no longer work in a company that uses LLBLGen, the company uses LINQ to SQL mainly because it’s “proven” in so many projects without big failures (unlike EF 1, which is lacking even LINQ features in LINQ to SQL and has very bad performance and can be quite limiting in advanced mapping – which it should have been best for!). This website StackOVerflow itself runs on top of it!!! I used both in this company (EF after L2S) and hated both. The decision for new projects remained LINQ to SQL, and doing all we can to overcome it’s limitations. You can work around it to do SEMI-POCO (you still need to use some L2S related types when it comes to associations).

    I also do some small projects at home. Since I nolonger have LLBLGen license, I decided to learn NHibernate and use it along with Fluent NHibernate and LINQ To NHibernate. I have learned through this that NHibernate is VERY strong. It changed how I work by some features like updating DB schema automatically (I never touched the DB almost when using it). LINQ provider (in NHibernate Contrib project) is quite lacking sometimes but there is the unreleased source code of NHibernate itself contains a better LINQ provider (haven’t tried it yet). The “Session” in NHibernate has problems when you are doing web development similar to those related to DataContext in L2S or ObjectContext in EF (LLBLGen doesn’t suffer from those thanks to self tracking entities).

    The biggest problems I had with NHibernate though was ability to find information. Too many pieces that should be put together in certain way and not much guidance can include advanced information for both mapping and querying. If not I had a friend (Tuna Toksoz , @tehlike on twitter) who happended to be a committer in NHibernate project source code, I’d really be in serious trouble.

    The moral I learned was: If you want something that just works and a bit basic use Linq To Sql or SubSonic, if you want something in the middle and your production environment can afford BETA .NET version (given golive exists) use Entity Framework 4.0, if you want something very powerful and can afford the hard learning process go to NHibernate, AND, BEST OF ALL, if you can afford LLBLGen, USE IT.

    Let me know your own thoughts on the topic.

    Listen to me talking about ORM in Arabic – DotNetArabi Podcast

    Emad Ashi (@splashup on twitter) interviewed me in the 5th episode of his first Arabic podcast series DotNetArabi to talk about Object Relational Mapping in .NET in Arabic.
    السلام عليكم
    أصدقائي العرب ممن يتابعون هذه المدونة.. يسعدني أن أعلن عن أول حديث لي على الانترنت – و كذلك أول حديث لي على الانترنت بالعربية، عن الـ Object Relational Mappers – ORMs
    شكرا جزيلا لـ “عماد العشي” (splashup@ على تويتر) على استضافته لي في موقع “دوت نت عربي DotNetArabi”، و هو موقع عربي يتضمن لقاءات صوتية مع العديد من المطورين في مجال الدوت نت، تماما على غرار DotNetRocks ، HanselMinutes و سواها، و هو في حد ذاته فخر لي أن أكون ضيفا للحلقة الخامسة في برنامج كهذا، خاصة عندما يكون ضيف أولى الحلقات هو عمر قعدان (omarq@ على تويتر).

    الحلقة 5: محمد مليجي يتكلم عن الـ ORM (Object Relational Mapping)

    وصف الحلقة من دوت نت عربي:
    محمد مليجي تكلم عن الـ ORM (Object Relational Mapping) و هي برامج مساعدة تستطيع من خلالها نقل المعلومات و تحويلها من طبيعة قاعدة البيانات إلى طبيعة البرامج المبنية بأسلوب الـ Object Oriented. حلقة غنية بالتفاصيل و المعلومات القيمة جدا.
    Listen to the episode now (in Arabic):
    الشكر خاص جدا لعماد الذي أتى لي في وقت قل فيه اتصالي بمجتمع المطورين في مصر، و اقتصر نشاطي في مجتمع المطورين بصفة عامة على بعض المحاضرات خارج نطاق الالتزامات الرسمية في شركتي الحالية، و مشاركات قليلة في المنتديات و المجموعات البريدية خاصة ALT.NET و مدونتي هذه، (و هو ما يقل مع الوقت) و متابعة أخبار المجتمعات العربية على تويتر كـ Mohamed_Meligy@ و على Google Reader.

    Related Links: