Using ASP.NET tables generated by aspnet_regsql.exe in a SQLite database

Question

I'm building a small ASP.NET MVC site where I want to use SQLite. While I already know how I will be connecting to the database (using DbLinq), I don't understand how to put the ASP.NET tables generated by aspnet_regsql.exe into an SQLite database.

I've used the regsql tool before with SQL Server, but never with SQLite. How do I create them for the SQLite DB?

One strategy that I think might somehow work is:

1. Use aspnet_regsql.exe to create the tables into an empty SQL Server database
2. Script all the objects in the database into T-SQL
3. Apply the T-SQL to the SQLite DB (how?)

Answer 1

Unfortunately the aspnet_regsql will not work with SQLite, so your strategy is essentially the correct approach. However, I would suggest looking at something like Roger Martin's Gallery Server Pro, which uses SQLite and already has the Membership, Role, and Profile provider tables scripted. You can make use of the script. See Roger's Code Project article from more information.

Answer 2

Take a look at http://salient.codeplex.com

the Web.SQLite directory contains a drop in replacement for the default SQL providers.

It was a proof of concept that turned out ok. It uses EF, so is not as performant as it could be but should service low/medium traffic sites without issue.

You should be able to exclude all other files except that subdirectory and build it in isolation if desired. This will allow you to also trim the references.

Let me know if you have any issues.

Answer 3

You could try it with SQL Server Compact Edition 4 which is an embeddable SQL engine like SQLite but has an easier upgrade path if you need to grow it up to SQL Express or SQL Server.