Wednesday, August 3, 2011

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.

AddRelation

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. 

image

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.


Generated DBML


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.

No comments:

Post a Comment