How can I use a SQL Scripts in a Database Project with the System.Data.SQLite data provider?

Question

I've got a project where I'm attempting to use SQLite via System.Data.SQLite. In my attempts to keep the database under version-control, I went ahead and created a Database Project in my VS2008. Sounds fine, right?

I created my first table create script and tried to run it using right-click->Run on the script and I get this error message:

This operation is not supported for the provider or data source you are using.

Does anyone know if there's an automatic way to use scripts that are part of database project against SQLite databases referenced by the databases, using the provider supplied by the System.Data.SQLite install?

I've tried every variation I can think of in an attempt to get the script to run using the default Run or Run On... commands. Here's the script in it's most verbose and probably incorrect form:

USE Characters
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'Skills')
  BEGIN
    DROP Table Skills
  END
GO

CREATE TABLE Skills
(
   SkillID INTEGER PRIMARY KEY AUTOINCREMENT,
   SkillName TEXT,
   Description TEXT
)
GO

Please note, this is my first attempt at using a Database, and also the first time I've ever touched SQLite. In my attempts to get it to run, I've stripped any and everything out except for the CREATE TABLE command.

UPDATE: Ok, so as Robert Harvey points out below, this looks like an SQL Server stored procedure. I went into the Server Explorer and used my connection (from the Database project) to get do what he suggested regarding creating a table. I can generate SQL from to create the table and it comes out like thus:

CREATE TABLE [Skills] (
    [SkillID] integer PRIMARY KEY NOT NULL,
    [SkillName] text NOT NULL,
    [Description] text NOT NULL
);

I can easily copy this and add it to the project (or add it to another project that handles the rest of my data-access), but is there anyway to automate this on build? I suppose, since SQLite is a single-file in this case that I could also keep the built database under version-control as well.

Thoughts? Best practices for this instance?

UPDATE: I'm thinking that, since I plan on using Fluent NHibernate, I may just use it's auto-persistence model to keep my database up-to-snuff and effectively in source control. Thoughts? Pitfalls? I think I'll have to keep initial population inserts in source-control separately, but it should work.

Answer 1

Well, your script looks like a SQL Server stored procedure. SQLite most likely doesn't support this, because

1. It doesn't support stored procedures, and
2. It doesn't understand SQL Server T-SQL

SQL is actually a pseudo-standard. It differs between vendors and sometimes even between different versions of a product within the same vendor.

That said, I don't see any reason why you can't run any (SQLite compatible) SQL statement against the SQLite database by opening up connection and command objects, just like you would with SQL Server.

Since, however, you are new to databases and SQLite, here is how you should start. I assume you already have SQLite installed

1. Create a new Windows Application in Visual Studio 2008. The database application will be of no use to you.

2. Open the Server Explorer by pulling down the View menu and selecting Server Explorer.

3. Create a new connection by right-clicking on the Data Connections node in Server Explorer and clicking on Add New Connection...

4. Click the Change button

5. Select the SQLite provider

6. Give your database a file name.

7. Click OK.

A new Data Connection should appear in the Server Explorer. You can create your first table by right-clicking on the Tables node and selecting Add New Table.