• Download PEM - 278.67 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
• 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.