SQLite + ADO.NET + C# 4.0

Getting SQLite to run (the ADO.NET version) under C# 4.0 is a bit tricky. You can follow the steps below to get it to work.

First, download SQLite (the ADO.NET version) from here. This is the Source Forge page for the official project. Install it.

Second, download SQLiteAdmin, a free, awesome GUI tool for SQLite. You can get it here.

Next, create a database using SQLiteAdmin. Add a single table with a single row, so that you can test that things work.

Then, launch up Visual Studio 2010 and create a new project. Add a reference to the System.Data.SQLite.dll file (located in \SQLite.NET\\bin). I recommend copying the DLL into an “extlib” folder in your project and referencing that.

Finally, run a query and see how things go! Here’s a quick method I hacked together:


public static DataTable ExecuteQuery(string sql)
{
// Validate SQL
if (string.IsNullOrWhiteSpace(sql))
{
return null;
}
else
{
if (!sql.EndsWith(";"))
{
sql += ";";
}
SQLiteConnection connection = new SQLiteConnection("Data Source=blah.db");
connection.Open();
SQLiteCommand cmd = new SQLiteCommand(connection);
cmd.CommandText = sql;
DataTable dt = new DataTable();
SQLiteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
reader.Close();
connection.Close();
return dt;
}
}

The only wierd thing is that the function returns a DataTable, which essentially makes it easy for you to iterate.

Then, call this function, and trace out the results:


DataTable table = ExecuteQuery("SELECT * FROM someTable");
foreach (DataRow row in table.Rows) {
Debug.Trace("Record: id=" + row["id"] + " name=" + row["name"]);
}

But wait! You get this annoying and un-googlable error:

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.

Ouch! It seems like you’re running a .NET 2.0 library, which is ok, but it has unmanaged code in it, which may or may not be ok; we need to tell our project explicitly that we can run it.

Right-click on your project, and add a new file; select “Application Config File” (depicted below):

How to add app.config to your C# 4.0 application

Paste the following XML inside the configuration tag:





And viola! The exception should disappear. You should see something like (assuming you inserted two names):


Record: id=1 Name=CSharpCity
Record: id=2 Name=Google

That wasn’t so hard, now was it?

About Ashiq Alibhai, PMP

Ashiq has been coding C# since 2005. A desktop, web, and RIA application developer, he's touched ASP.NET MVC, ActiveRecord, Silverlight, NUnit, and all kinds of exciting .NET technologies. He started C# City in order to accelerate his .NET learning.
This entry was posted in Libraries, Tools, Wndows Forms and tagged , , , , , . Bookmark the permalink.

16 Responses to SQLite + ADO.NET + C# 4.0

  1. Jacky says:

    Thanks a lot for this page ! It is exactly what I needed as beginner with C# and SQLite !
    Best regards from Switzerland
    Jacky :)

  2. Ashiq Alibhai, PMP says:

    @Jacky great! It’s surprising sometimes that even basic tutorials are missing. Glad it helped.

  3. Mostafa says:

    Thanks!
    The fix for the last error mentioned worked for me.

  4. Manoj says:

    Thanx!

    I am in this problem from last week This works for me !!

  5. Ashiq Alibhai, PMP says:

    @Manoj @Mostafa glad it helped!

  6. Erik says:

    Thanks! I did not know there was a property like useLegacyV2RuntimeActivationPolicy….

    Works like a charm now!

  7. Mike says:

    Thanks for the tip worked like a charm.

  8. Tomas Halasz says:

    Or you can use this version of System.Data.SQLite which seem’s OK, because it is compiled for VS2010 and .NET4
    http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

  9. Very useful. Thanks a lot!

  10. Ifelere says:

    The solution worked for me!

  11. Pingback: La gran odisea » Blog Archive » Usar SQLite con .net client profile 4 ?

  12. Huahu says:

    Amazing! Very very useful!

  13. jim k. says:

    Thank you! This ended hours of config. hell. The other tips were all missing the bit about ADDING the application config file before pasting into it. The bit about the configuration tag is just extra precision and diligence. A rare completeness, and yet so simple.

  14. moumita says:

    Hi,

    I am getting error the following error when I am trying to run the application

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

    I have follow all step which are specified in the page….
    Please help me in this regards.

    Thanks,
    Moumita

  15. Ashiq Alibhai, PMP says:

    @moumita try the link mentioned by Thomas earlier. Also, make sure your config fille is named app.config, and sits in the root of your solution (not in any directory).

  16. moumita says:

    Thanks a lot…problem solved…

Leave a Reply

Your email address will not be published. Required fields are marked *