10,893,051 members (93,465 online) Sign in home quick answers discussions features community help Search for articles, questions, tips Articles » Web Development » ASP.NET » Howto Rate this:Rahman Masudur, 6 Aug 2011 Using SQLite Embedded Database with Entity Framework and Linq-to-SQL Using SQLite embedded database with entity framework and Linq-to-SQL Download PEM - 231.96 KB 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 Summary 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 the right kind of database depending on few criteria. We don’t want to use a service based database rather a file system based database. Clients 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 concerned, and some people prefer 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 learnings 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 The 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” 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 a new provider is available for SQLite. 4.79 (44 votes) ×Sign up for our free weekly Web Developer Newsletter. articles 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 the design of the database. Few interesting notes on this particular case. SQLite database has 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 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 has 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 concerned. A complete list of SQLite management tools can be found at “http://sqlite.com/cvstrac/wiki?p=ManagementTools”. Download whatever best suits you. Here, I am going to talk about only SQLite Administrator. It can be downloaded from the following site: http://sqliteadmin.orbmu2k.de/ Note This tool needs no installations whatsoever. The whole package is in a zip file and needs zero installation, just unzip and open the EXE, that’s it, and all of the functionality is pretty easy and it has a very intuitive UI. I would recommend it to everyone who works with SQLite. Edit SQLite Database using Visual Studio 2010 or SQLite Administrator In the previous section, I have shared little useful information about SQLite Embedded database and how to use its provider to create a 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 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 expand 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 below figure. I am sure you guys have done this hundreds of time in SQL server, this time it's 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 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 an option to add auto increment column. Using SQLite with Entity Framework in Visual Studio 2010 In the previous section, we have seen how using a simple “Data provider” provided by http://sqlite.phxsoftware.com/ can make our life easier in the 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 a 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 bring 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 the 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 automatically be synchronized in data model, that’s it. We are good to jump in the code to retrieve data and do all types of necessary stuff that we must do in a database. Best of luck and happy coding. Using LINQ-to-SQL with SQLite Database In the previous section, we have discussed about how to use ADO.NET Entity Framework with SQLite database. And we have witnessed that it's almost as similar to 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 cannot drag and drop tables from server explorer or from data connections. It's because we don’t have any built in provider that supports 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 won't please you but it certainly serves our purpose to use Linq with SQLite. There is no magic or rocket engineering behind this, we have a DBLinq open source distribution that comes with various sorts of database support. For this particular case, let's stick to SQLite. The binaries can be found here, http://code.google.com/p/dblinq2007/, please download latest binaries from this site. Now it's time to generate the OurDatabase.dbml file. The steps are not that much complex, Locate 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: Collapse | Copy Code DbMetal /provider:Sqlite /conn "Data Source=File.db3" /dbml:File.dbml Step 2: Create code file for the dbml file: Collapse | Copy Code DbMetal /code:File.cs File.dbml Copy those files to the Visual Studio. That’s it! You are good to go. Make sure that everything compiles okay by hitting build. Below I have put a simple code snippet to demonstrate how to use this dbml to retrieve data. Collapse | Copy Code 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 titles 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: Collapse | Copy Code //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() Article Browse Code Stats Revisions (7) Alternatives Comments (37) Tagged as C# ASP.NET .NET Dev LINQ Beginner Framework entity Sqlite Go to top Rahman Masudur Software Developer (Senior) Satori Tech Solutions, Inc. Australia About Md. Masudur Rahman { 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 takes relative location from app_data. Summary In this short article, we have seen how to administer SQLite database with various tools. and how we can use some useful tools 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 operations on database. License This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL) Share About the Author EMAIL Masudur currently works at Satori Tech Solutions, Inc. as a software developer. Masudur Lives in Sydney, Australia. Awards 26 May 2009: Monthly competition: Best ASP.NET article of April 2009 24 Mar 2009: Monthly competition: Best ASP.NET article of February 2009 Masudur Blog Masudur put down his interesting learning experiences in his blog at http://munnaondotnet.blogspot.com/. Search Comments Go You may also be interested in... Gartner: Magic Quadrant for On-Premises Application Platforms IDC: How Red Hat's JBoss Enterprise Application Platform is Extending Business Value Comments and Discussions You must Sign In to use this message board. Profile popups Spacing Relaxed Noise Medium Layout Normal Per page 25 Update First Prev Next leiyangge 1-Sep-14 4:12 Rahman Masudur 1-Sep-14 12:51 rongchaua 9-Mar-14 0:57 Antonio Garcia 22-Jan-14 7:46 Rahman Masudur 23-Jan-14 10:15 M Rayhan 30-Oct-13 16:45 softwareguy74 26-May-13 14:07 Rahman Masudur 26-May-13 15:23 softwareguy74 26-May-13 17:00 softwareguy74 27-May-13 3:07 Rahman Masudur 27-May-13 10:23 softwareguy74 28-May-13 4:42 Rahman Masudur 28-May-13 20:51 The links in the article are not accessible for me Re: The links in the article are not accessible for me Sqlite Tool how to use this with Click Once Re: how to use this with Click Once My vote of 5 I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? Permalink | Advertise | Privacy | Mobile Web04 | 2.8.140925.1 | Last Updated 7 Aug 2011 Article Copyright 2011 by Rahman Masudur Everything else Copyright © CodeProject, 1999-2014 Terms of Service Layout: fixed | fluid Rahman Masudur 26-May-13 15:34 softwareguy74 26-May-13 13:58 softwareguy74 25-May-13 9:00 saman.ziamolki 22-May-13 7:08 Ruther Ang 30-Apr-13 5:26 asavasamuel 1-Mar-13 12:06 csharpbd 21-Nov-12 6:49 Rahman Masudur 21-Nov-12 19:42 csharpbd 21-Nov-12 22:25 Member 8717634 17-Oct-12 21:23 H. S. Masud 2-Jul-12 7:50 TrangNguyen1990 2-Jul-12 3:53 Last Visit: 31-Dec-99 18:00 Last Update: 25-Sep-14 20:01 Refresh 1 2 Next » General News Suggestion Question Bug Answer Joke Rant Admin Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages. Re: I'm confused. Why do we need DbMetal and dbml file when it works without it?? How do you easily update the model when data structure changes? Nice job! [modified] My vote of 5 Unsupported type Date Message Automatically Removed My vote of 5 Re: My vote of 5 Re: My vote of 5 Thanks My vote of 5 Error Dblinq could not be found in C# application using Linq to Sqlite