Using SQLite embedded database with entity framework and Linq-to-SQL.
Table of Contents
- Introduction
- Adding the provider
- SQLite management tool
- Edit SQLite database using Visual Studio 2010 or SQLite Administrator
- Using SQLite with Entity Framework in Visual Studio 2010
- Task management example with SQLite
- Summery
Introduction
Every now and then we need a simple database solution which is light, small and pretty easy to use. What more can be useful other than SQLite, a simple yet very powerful tool to support database for any kind of application needs. When we started this research work, our major concern was to choose right kind of database depending on few criteria.
- We don’t want to use a service based database rather a file system based database.
- Client have to be light, fast and support blob data.
- We don’t want to install any client or framework in client machine
SQLite is turning to be a very handy embedded database as far as the web and small scale database is concern, and some people prefers SQLite over SQL compact Edition database because of its size, performance and support for large data. After doing research on few area of the database I have decided to put together the learning's that I had while exploring the uses and ease while working with visual studio 2010. In this short article we are going to see how we can use ado.net entity framework and then how to use Linq to SQL with SQLite database.
Adding the provider
Next step is to add a data connection in server explorer in Visual studio 2010. When you click on add connection by default you don’t have provider added in visual studio, so we have to add it using a third-party tool. You can download the provider for SQLite from http://sqlite.phxsoftware.com/ site. “SQLite-1.0.66.0-setup” this is the version we used for this particular case. When you install this provider you would be given an option to integrate this in vs2008 and vs2010. After installing the provider when you get back to the add data connection in server explorer you would see an new provider is available for SQLite.
Adding the connection in Server Explorer
Go ahead and click on after choosing SQLite Database File. Next we will be presented with a screen, where you would either choose an existing database or create a new database file.
That’s it! Your connection string and database connection is now set up and you are good to go with design of the database. Few interesting note on this particular case. SQLite database have no database diagram support yet, but we hope that this will be provided soon. After adding connection we can created tables and add necessary relations to the table.
In this section we have seen that how easily SQLite can be used in visual studio 2010 using a provider to create connection in server explorer. Next we would discuss how to use SQLite to create a data layer using Entity Framework, but before that let's take a quick look at a simple tool for SQLite db management.
SQLite management tool
SQLite have lots of management tools, some of which are open source, some are trial ware and others 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.
Here, I am going to talk about only in SQLite Administrator, It can be downloaded from the following site.
http://sqliteadmin.orbmu2k.de/
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.
Edit SQLite database using Visual Studio 2010 or SQLite Administrator
In previous section 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, than 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 the 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 pop-up 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.
Using SQLite with Entity Framework in Visual Studio 2010
In previous section 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 section we are going to see how we can use SQLite to generate Entity Framework Data model.
Choosing the Provider
The primary thing is to 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 then we have to 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.
Using LINQ-to-SQL with SQLite database
In previous section 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 code file for the dbml file
DbMetal /code:File.cs File.dbml
Copy those files to the Visual Studio. 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.
public static void GetData() { string ConStr = "Data Source=" + HttpContext.Current.Server.MapPath("App_Data\\PersonalExpenseDB.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(); }
Task management example with SQLite
In this section I have put together a simple task editing code having only two field title and description. This is just to demonstrate the idea of how we can use the above ideas in real coding for this particular example I have used ado.net entity framework. Below the code snippets are given for most important methods.
//load task private void LoadTasks() { var db = new PersonalExpenseDBEntities(); var tasks = db.Tasks.ToList(); RepeaterTasks.DataSource = tasks; RepeaterTasks.DataBind(); } //add new task private void AddNewTask() { var db = new PersonalExpenseDBEntities(); Task task; if (EditTaskId > 0) { task = db.Tasks.Where(p => p.ID == EditTaskId).SingleOrDefault(); if (task != null) { task.Description = TextBoxDescription.Text.Trim(); task.Title = TextBoxTitle.Text.Trim(); } EditTaskId = 0; } else { task = new Task {Description = TextBoxDescription.Text.Trim(), Title = TextBoxTitle.Text.Trim()}; db.AddToTasks(task); } db.SaveChanges(true); } //delete task private static void DeleteTask(CommandEventArgs e) { var db = new PersonalExpenseDBEntities(); var argument = e.CommandArgument; int taskId = Convert.ToInt32(argument); var editingTask = db.Tasks.Where(p => p.ID == taskId).SingleOrDefault(); db.DeleteObject(editingTask); db.SaveChanges(); } //populate task private void PopulateEditingTask(CommandEventArgs e) { var argument = e.CommandArgument; int taskId = Convert.ToInt32(argument); var db = new PersonalExpenseDBEntities(); var editingTask = db.Tasks.Where(p => p.ID == taskId).SingleOrDefault(); if (editingTask != null) { TextBoxTitle.Text = editingTask.Title; TextBoxDescription.Text = editingTask.Description; } EditTaskId = taskId; }
Note that while creating the connection string using the wizard you might end up with database path that is not relative to your application. so please modify the connection string so that it take relative location from app_data.
Summery
In this short article we have seen how to administer SQLite database with various tools. and how we can use some useful tool to get it working with Linq-to-SQL. We have also seen how we can use provider to get some native support with visual studio 2010 to design and manage SQLite database. Lastly we have seen how to use SQLite with entity framework to perform some operation on database.
Post Comment
l9rwqt This excellent website definitely has all of the info I wanted concerning this subject and didn at know who to ask.
VBmcSe You can certainly see your expertise in the work you write. The world hopes for more passionate writers such as you who aren at afraid to mention how they believe. All the time follow your heart.
Tirage en croix du tarot de marseille horoscope femme
There is visibly a lot to identify about this. I consider you made certain good points in features also.
Im thankful for the article.Thanks Again. Much obliged.
Modular Kitchens have changed the idea of kitchen nowadays since it has provided household females with a comfortable yet an elegant place through which they may devote their quality time and space.
Major thanks for the article.Really looking forward to read more. Awesome.
There as a lot of people that I think would really appreciate your content. Please let me know. Many thanks
media is a impressive source of information.
Special Letter Network Technology Co., Ltd., Guangzhou
While I was surfing yesterday I saw a excellent post concerning
Utterly written subject matter, Really enjoyed reading.
Wow, awesome weblog layout! How long have you ever been running a blog for?
Really appreciate you sharing this blog.Really looking forward to read more. Keep writing.
There is clearly a bundle to identify about this. I suppose you made certain nice points in features also.
Thanks so much for the article.Really looking forward to read more. Keep writing.
Of course, what a magnificent website and educative posts, I will bookmark your site.All the Best!
Thanks for sharing your info. I truly appreciate your efforts and I am waiting for your next post thank you once again.
Pink your weblog post and beloved it. Have you ever imagined about visitor posting on other related weblogs equivalent to your blog?
Thank you, I ave recently been searching for info about this topic for ages and yours is the best I ave discovered till now. But, what about the bottom line? Are you sure in regards to the supply?
Very interesting subject , regards for posting. What passes for optimism is most often the effect of an intellectual error. by Raymond Claud Ferdinan Aron.
you can always count on search engine marketing if you want to promote products online.
This particular blog is really awesome additionally informative. I have picked up a bunch of useful advices out of it. I ad love to come back again and again. Thanks!
Very interesting info !Perfect just what I was looking for!
Thanks-a-mundo for the blog post.Much thanks again. Will read on
You generated some decent points there. I looked on-line for that problem and discovered the majority of people will go coupled with with all your internet site.
I wanted to thank you for this great article, I definitely loved each and every little bit of it. I have bookmarked your web site to look at the newest stuff you post.
This is one awesome article post.Really looking forward to read more. Much obliged.
Really appreciate you sharing this blog post.Really thank you! Cool.
There is certainly a great deal to find out about this subject. I love all of the points you have made.
Wonderful work! This is the type of information that should be shared around the net. Shame on the search engines for not positioning this post higher! Come on over and visit my web site. Thanks =)
very good post, i certainly love this web site, carry on it
You are not right. I can defend the position. Write to me in PM.
The handbook submission and work might be billed bigger by the corporation.
It as really a nice and helpful piece of information. I am glad that you shared this useful info with us. Please keep us informed like this. Thanks for sharing.
Souls in the Waves Great Morning, I just stopped in to go to your internet site and assumed I ad say I experienced myself.
This is one awesome article.Much thanks again. Really Cool.
This is a topic close to my heart cheers, where are your contact details though?
that is the end of this write-up. Right here you
This awesome blog is obviously interesting additionally informative. I have discovered helluva interesting tips out of this blog. I ad love to go back every once in a while. Cheers!
I wish my site loaded up as fast as yours lol
Lastly, an issue that I am passionate about. I ave looked for data of this caliber for your last numerous hours. Your internet site is drastically appreciated.
Thanks for sharing this first-class post. Very inspiring! (as always, btw)
Thank you ever so for you blog article.Much thanks again. Want more.
Major thanks for the article.Really thank you!
This web site definitely has all of the info I needed about this subject and didn at know who to ask.
Thankyou for this post, I am a big big fan of this internet site would like to go on updated.
Really enjoyed this blog post.Much thanks again. Fantastic.
Just added your weblog to my list of price reading blogs
Looking forward to reading more. Great blog.Thanks Again. Cool.