The Data Provider for SQLite is stable enough for me to publish it here. If you want to use it you will need to download and install the ADO.NET 2.0 SQLite Data Provider which in fact does all the heavy lifting. Then download subsonic’s source code from Google Code, add the file SQLiteDataProvider.cs to the Subsonic project under the DataProviders folder and add the line:
public const string SQLITE = “SQLiteDataProvider”;
To the class DataProviderTypeName in the Constants.cs file:
public class DataProviderTypeName
{
..
public const string SQL_SERVER = “SqlDataProvider”;
public const string SQLITE = “SQLiteDataProvider”;
}
Then add a reference to System.Data.SQLite to the SubSonic project.
To test the provider you will need the sample database, an adapted version of the pubs database which contains all the data except that I changed the date format of all the dates to YYYY-mm-dd by hand so they may be different from the original data.
And finally we have the configuration. First add the line:
<add name=“Pubs“
connectionString=“Data Source=C:\…\pubs.db“/>
To the connectionstrings.config file adjusting the connection string to point to the database location. Then open the Web.config and, in the /configuration/SubSonicService/providers node add a new provider:
<add name=“Pubs“
type=“SubSonic.SQLiteDataProvider, SubSonic“
connectionStringName=“Pubs“
generatedNamespace=“Pubs“/>
I also created several unit tests and added them to the SubSonic.Tests project but I don’t think it is compatible with all the others tests which are not provide specific so unless you want to modify the provider ignore it.
The code can be downloaded from:
2007/07/24/the-sqlite-data-provider-is-now-part-of-subsonic/
(newer download)
Dependencies:
ADO.NET 2.0 SQLite Data Provider
Enjoy and let me know how it goes. If you need help leave a comment here.
P.S.This code is released code under the same license as SubSonic ( http://www.codeplex.com/actionpack/Project/License.aspx ).
Hey wow! This is good stuff!
Does it work with version 2.0.3 or do I need to use a more current version?
Version 2.0.3 of Subsonic does not include this provider as it was created after that release. You will need to get the code from source control.
Any idea when this will become a standard part of SubSonic? Meaning, I won’t need to manually add in support, as you outlined above.
-Josh
No idea Josh, I think your best bet is to ask this same question in the Subsonic’s forums. I know the guys there are keen on doing just that but they have quite a few other things on the move.
I guess that if enough people start asking for it, using it (and filling bugs) the priority will go up. So far I only had one very helpfull user that filled several bugs but that was it.
Has anyone got/done a VistaDB provider for Subsonic?
Not that I know of. I don’t know VistaDB at all but you can pick the Subsonic database driver with the closest SQL and go from there. It is quite easy to get something going for basic scenarios.