Use Sqlite together wtih ASP.Net MVC

Posted on

Visual Studio 2008 integrates Sql server 2008 express nicely into App_Data folder. You just create the database using Server Explorer, and it inserts the connection string into web.config for you. I used it when I worked on my toy project – ToastManager. I finished coding and testing on my machine and deployed it to an integration server which belongs to my SCRUM team.

A problem, as usual, arose on the integration box. My windows login had sysadmin access to my local Sql Server express, but the process that runs ToastManager did not on the box.

I blindly thought that Sql Server express is a stand-alone DB and it can be deployed together with the applicaiton. It was misunderstanding. Sql Server Express is still a server application that needs to be installed on a remote machine. I looked for an alternative and soon found that many people use Sqlite.

Sqlite is an open source database and people testify that it is really fast. In order to use with .Net, you need to install System.Data.SQLite, which is another open source ADO.NET provider for SQLite database engine.

Once you download Sqlite, you can use its own command-prompt based tool, but probably, your first reaction is to try to find some GUI management tool. Fortunately, there is one too. It is FireFox Add-on: SQLite Manager.

So, to summarise, you need to have 3 programs to use SQLite. SQLite, System.Data.SQLite, and SQLite Manager.

If you have all necessary programs, please be careful with the folloing points.

  1. Add “SQLite Data Provider” to your web.config. You can add it to your machine.config or web.config in .Net 2′s config folder, but I prefere adding it to project’s web.config.
  2. Do not use “Link To Sql” as it does not support SQLite. Instead, use Entity Framework. It works nicely.
  3. Make sure you have System.Data.SQLite.dll and System.Data.SQLite.Linq.dll in your application’s bin folder. For some reason, though I referenced those two dlls in the project, they were copied into the folder. If those dlls are not in bin folder, you will have “Failed to find or load the registered .Net Framework Data Provider”. (http://sqlite.phxsoftware.com/forums/t/251.aspx)
  4. When you compare data using LINK, do not use .Date property. For some reason, it works with Sql Express, but not with Sqlite. I use “where o.OrderDate >= DateTime.Today” instead of “where o.OrderDate.Date == DateTime.Today”

This is my repository codes I wrote  against SQLite. Hope it helps.

public class OrderRepositorySqlite : IOrderRepository
{
    ToastMasterSqlite _entities = new ToastMasterSqlite(); 
    #region IOrderRepository Members

    public IEnumerable<Order> ListTodayOrders()
    {
        return (from o in _entities.Order
                where o.OrderDate >= DateTime.Today
                select o).ToList();
    }

    public Order GetOrder(long id)
    {
        return (from order in _entities.Order
                where order.OrderId == id
                select order).FirstOrDefault();
    }

    public void Add(Order order)
    {
        _entities.AddToOrder(order);
        _entities.SaveChanges();
    }

    public void Delete(Order order)
    {
        var originalOrder = GetOrder(order.OrderId);
        _entities.DeleteObject(originalOrder);
        _entities.SaveChanges();
    }

    public Order Update(Order orderToUpdate)
    {
        var originalOrder = GetOrder(orderToUpdate.OrderId);
        _entities.ApplyPropertyChanges(originalOrder.EntityKey.EntitySetName, orderToUpdate);
        _entities.SaveChanges();

        return orderToUpdate;
    }

    #endregion
}

About these ads

4 thoughts on “Use Sqlite together wtih ASP.Net MVC

    fax software said:
    August 12, 2010 at 6:50 am

    Rather excellent idea

    Aliraza said:
    October 29, 2010 at 4:57 pm

    Thanks i was thinking about it.

    asava samuel said:
    March 1, 2013 at 11:36 pm

    Andy

    This may help, here is Linq to SQLite.
    https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

      Andy responded:
      March 22, 2013 at 10:37 am

      Many thanks. I’ll try.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s