Make SQLite connection fail if database is missing? (deleted/moved)

Question

I have the following method inside class DBConnection. I call the method like this: SQLiteConnection conn = DBConnection.OpenDB(); when I want to open an connection, so that I can execute my queries. I can call a similar method when I want to close the connection.

The method:

public static SQLiteConnection OpenDB()
{
    try
    {
        //Gets connectionstring from app.config
        string myConnectString =
            ConfigurationManager.ConnectionStrings[
                "LegMedSQLLite.Properties.Settings.LegMedSQLLiteDBConnectionString"].ConnectionString;

        var conn = new SQLiteConnection(myConnectString);

        conn.Open();
        return conn;
    }
    catch (SQLiteException e)
    {
        MessageBox.Show(e.ToString(), "TEST");
        return null;
    }
}

This all works fine and dandy. The problem is the try-catch though. Let us imagine the following scenario:

• The database file has been moved/delete.

The exception will never be thrown. Actually, the first catch I stumble upon is when I execute my first query - where it figures that there is no such table(s) and it throws its own exception. I was stunned by this weird phenomenon, but I soon found out that SQLite creates a new empty database. By empty is mean no tables, nothing, just an SQLite database file with the same name as the old database which was supposed to be there.

This is an issue, I want the application to know if there is something wrong (database not found, corrupted, being used by another process etc.) as soon as I try to call SQLiteConnection conn = DBConnection.OpenDB();.

Naturally, I could try call a File.Exists in my method, but that doesn't seem like a proper solution. Any help?

Answer 1

If you're using System.Data.SQLite, you can add "FailIfMissing=True" to your connection string. SQLiteConnection.Open() will throw a SQLiteException if the database file does not exist.

string ConnectString = "Data Source=file.sdb; FailIfMissing=True";
DbConnection db = new SQLiteConnection(ConnectString);
db.Open(); // Fails if file.sdb does not exist

See SQLite Connection String Samples for another example, look for "Disable create database behaviour".

If you're using Microsoft.Data.Sqlite, you can specify an open mode with Mode=ReadWrite instead of Mode=ReadWriteCreate.

Answer 2

I haven't used SQLite but that is pretty bizarre behaviour to auto create a brand new database.

You could just adjust your try block to do a Select top 1 * From Table immediately after you open the connection, if it works, throw away the result and continue to return your conn object. If it fails, the exception handler should fire.

Answer 3

If you want to detect database corruption issues on start up , you can execute the command

pragma integrity_check;

or

pragma quick_check; ( which is faster, but less thorough )

This returns a single row with the value "ok".

Otherwise it will report errors that it encounters.

Answer 4

For sqlite use this: Suppose you have connection string in textbox txtConnSqlite

     Using conn As New System.Data.SQLite.SQLiteConnection(txtConnSqlite.Text)
            Dim FirstIndex As Int32 = txtConnSqlite.Text.IndexOf("Data Source=")
            If FirstIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Dim SecondIndex As Int32 = txtConnSqlite.Text.IndexOf("Version=")
            If SecondIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Dim FilePath As String = txtConnSqlite.Text.Substring(FirstIndex + 12, SecondIndex - FirstIndex - 13)
            If Not IO.File.Exists(FilePath) Then MsgBox("Database file not found", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Try
                conn.Open()
                Dim cmd As New System.Data.SQLite.SQLiteCommand("SELECT * FROM sqlite_master WHERE type='table';", conn)
                Dim reader As System.Data.SQLite.SQLiteDataReader
                cmd.ExecuteReader()
                MsgBox("Success", MsgBoxStyle.Information, "Sqlite")
            Catch ex As Exception
                MsgBox("Connection fail", MsgBoxStyle.Exclamation, "Sqlite")
            End Try
         End Using

I think you can easilly convert it to c# code

Answer 5

This is specific to .NET Core.

Entity Framework Core 5.0 will throw this exception:

System.ArgumentException: Connection string keyword 'failifmissing' is not supported. For a possible alternative, see https://go.microsoft.com/fwlink/?linkid=2142181.

The alternative is to use Mode=ReadWrite in the Connection String.

string connectString = "Data Source=DbFileName.sdb; Mode=ReadWrite;";

ReadWriteCreate - Opens the database for reading and writing, and creates it if it doesn't exist. This is the default.

ReadWrite - Opens the database for reading and writing.

Refer the alternative link provided in the Exception message - https://go.microsoft.com/fwlink/?linkid=2142181

Types of Connection Mode - https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings

Answer 6

Don't catch at that level. Instead, SQLiteConnection should implement IDisposable, meaning you should just return the open connection and allow calling code to handle any exceptions, as well as rely on the Dispose method to close the connection.

Answer 7

If there is no way to change the default SQLite behavior, then you might have to do a File.Exists. That would be better than connecting and creating a new file, checking to see if it's the database you want, then deleting the new file in the catch block.