Programmatically create sqlite db if it doesn't exist?

Question

I am trying to create an sqlite db programmatically if it doesn't exist. I have written the following code but I am getting an exception at the last line.

if (!System.IO.File.Exists("C:\\Users\\abc\\Desktop\\1\\synccc.sqlite"))
{
    Console.WriteLine("Just entered to create Sync DB");
    SQLiteConnection.CreateFile("C:\\Users\\abc\\Desktop\\1\\synccc.sqlite");
    string sql = "create table highscores (name varchar(20), score int)";
    SQLiteCommand command = new SQLiteCommand(sql, sqlite2);
    command.ExecuteNonQuery();
}
sqlite2 = new SQLiteConnection("Data Source=C:\\Users\\abc\\Desktop\\1\\synccc.sqlite");

I get the exception at the line command.ExecuteNonQuery(); The exception is Invalid operation exception was unhandled. Is there any other way to add an sqlite file to your project? Can I do it manually? If not then how can I solve the above issue?

What is the value of sqlite2 at that point?

CL.
– CL.

2014-06-12 07:39:48 +00:00
Commented Jun 12, 2014 at 7:39 — CL.
– CL., Commented Jun 12, 2014 at 7:39

Steve · Accepted Answer · 2020-10-23 15:52:15Z

48

To execute any kind of data definition command on the database you need an open connection to pass the command. In your code you create the connection AFTER the execution of the query.

Of course, after that creation, you need to open the connection

if (!System.IO.File.Exists(@"C:\Users\abc\Desktop\1\synccc.sqlite"))
{
    Console.WriteLine("Just entered to create Sync DB");
    SQLiteConnection.CreateFile(@"C:\Users\abc\Desktop\1\synccc.sqlite");
    
    using(var sqlite2 = new SQLiteConnection(@"Data Source=C:\Users\abc\Desktop\1\synccc.sqlite"))
    {
        sqlite2.Open();
        string sql = "create table highscores (name varchar(20), score int)";
        SQLiteCommand command = new SQLiteCommand(sql, sqlite2);
        command.ExecuteNonQuery();
    }
}

However, if you use the version 3 of the provider, you don't have to check for the existence of the file. Just opening the connection will create the file if it doesn't exists.

edited Oct 23, 2020 at 15:52

answered Jun 12, 2014 at 7:46

Steve

217k22 gold badges242 silver badges296 bronze badges

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

10 Comments

zzzzz Over a year ago

Now Iam getting this exception "Unable to load DLL 'SQLite.Interop.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" at the line where I open the connection

Steve Over a year ago

Hope this helps stackoverflow.com/questions/13028069/…

The Senator Over a year ago

Minor gotcha with the version 3 of the provider is that it will indeed create the file if it doesn't exist, but only if the full directory path already exists ahead of the file.

Lei Yang Over a year ago

var sqlite2

Matthew Rathbone Over a year ago

To clarify for those reading, SQLite3, which is the current supported version, does not need you to create the file first, just open a connection to whatever path you want. You can create a zero byte file if you like. Here's a guide: beekeeperstudio.io/blog/sqlite-create-database

|

Collectives™ on Stack Overflow

Programmatically create sqlite db if it doesn't exist?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related