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. Jun 12 '14 at 07:39 — CL., Jun 12 '14 at 07:39

Steve · Accepted Answer · 2020-10-23T15:52:15.350

44

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 '20 at 15:52

answered Jun 12 '14 at 07:46

Steve

213,761
22
232
286

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 – zzzzz Jun 12 '14 at 07:54
Hope this helps http://stackoverflow.com/questions/13028069/unable-to-load-dll-sqlite-interop-dll – Steve Jun 12 '14 at 07:56
Weird thing is sqlite connection creation was working a few minutes ago but now I get exceptions – zzzzz Jun 12 '14 at 08:00
What version of the Sqlite provider are you using. The message is strange because I have the 1.0.82 and in this version the Interop is bundled together to the main assembly. No separate file is required. Perhaps you need to upgrade or reinstall the assembly. I am sorry but I don't use very often this database system and it has always worked without issues – Steve Jun 12 '14 at 08:06
This works after I manually copied the dll file to the directory. You forgot to open the DB otherwise your answer was correct.Thanks – zzzzz Jun 12 '14 at 08:12
Yes the initial version of the answer was without the call to Open, but I had fixed it. – Steve Jun 12 '14 at 08:13
1

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. – The Senator Sep 27 '16 at 19:30
@Steve Your last two sentences should be bold or moved to the top of the answer. :-) – erikvimz Oct 13 '17 at 05:05
1

**var** sqlite2 – Lei Yang May 16 '19 at 11:36
1

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: https://www.beekeeperstudio.io/blog/sqlite-create-database – Matthew Rathbone May 31 '22 at 14:48

Programmatically create sqlite db if it doesn't exist?

1 Answers1

Linked