LINQPad create sqlite database in C# query

Question

The IQ Connection plugin for LINQPad that allows one to use SQLite has a checkbox for "Create database if missing" but that will only create an empty file. Is there anyway to build the tables automatically when the file doesn't exist?

Shouldn't there be a way to get the DataContext and create tables using that interface? Hopefully causing LINQPad to update its DataContext at the same time.

The best I've been able to do so far is below, creating DbCommands and executing them on the first run after deleting the sqlite file, then I have to refresh the database, and run it again.

void Main()
{
    if (!File.Exists(this.Connection.ConnectionString.Split('=')[1]))
    {
        "CREATING DATABASE TABLES".Dump();
        CREATE_TABLES();
    }
    else
    {
        "RUNNING CODE".Dump();
        //Code goes here...
    }
}

public void CREATE_TABLES()
{
    this.Connection.Open();
    System.Data.Common.DbCommand sup = this.Connection.CreateCommand();
    sup.CommandText = @"create table MainTable
(
    MainTableID INTEGER not null PRIMARY KEY AUTOINCREMENT,
    FileName nvarchar(500) not null
)";
    sup.ExecuteNonQuery();
    sup.CommandText = @"create table SubTable
(
    SubTableID int not null,
    MainTableID int not null,
    Count int not null,
    primary key (SubTableID, MainTableID),
    FOREIGN KEY(MainTableID) REFERENCES MainTable(MainTableID)
)";
    //Apparently this version of sqlite doesn't support foreign keys really
    sup.ExecuteNonQuery();
    this.Connection.Close();
}

Joe Albahari · Accepted Answer · 2011-11-17 08:09:01Z

3

Just set the query language dropdown to 'SQL', type in the DDL and hit F5. For instance:

PRAGMA foreign_keys = ON
GO

create table Customer
(
    ID int not null primary key,
    Name nvarchar(30) not null
)
GO

create table Purchase
(
    ID int not null primary key,
    CustomerID int null references Customer (ID),
    Date datetime not null,
    Description varchar(30) not null,
    Price decimal not null
)

(Note the syntax for creating foreign key constraints.)

Once you're done, right-click the connection in the Schema Explorer and choose 'Refresh'. You can then switch the query language back to C# Expression or C# Statements and start querying in a proper query language :)

edited Nov 17, 2011 at 8:09

answered Nov 17, 2011 at 7:54

Joe Albahari

31.2k7 gold badges85 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thymine Over a year ago

I was hoping that I could have it all combined into one .linq file that wouldn't need modifying anytime the sqlite database doesn't exist. But I know I'm asking a lot there :)

Collectives™ on Stack Overflow

LINQPad create sqlite database in C# query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related