C# Detect Database format | DbProviderFactory

Question

My app actually use SqlServerCe (Microsoft SqlServer Compact). Now with my new update I swapped to SQLite.

Now my question: Every time the user wants to import the database, he can import a SqlServerCe file (old backup) or SQLite (a new backup). How can I detect, which DbProviderFactory is needed for my DbConnection?

Old Method (needs an update)

    /// <summary>
    /// Verifies the db if it is not corrupt! If the return value is <see cref="Nullable"/>, the DB is corrupt!
    /// </summary>
    /// <returns><see cref="Array"/> of <see cref="int"/>. The 1. index is Components.Count(), the 2 index is the Recipes.Count()!!</returns>
    [CanBeNull]
    public static int[] ImportDB_Verify()
    {
        try
        {
            SqlCeProviderFactory provider = new SqlCeProviderFactory();
            SqlCeConnectionStringBuilder connectionStringBuilder = new SqlCeConnectionStringBuilder
            {
                DataSource = "Path/to/foo.db"
            };
            int[] val = new int[2];
            using (DbConnection dbConnection = provider.CreateConnection())
            {
                dbConnection.ConnectionString = connectionStringBuilder.ConnectionString;
                dbConnection.Open();

                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM Components"))
                {
                    val[0] = (int)dbCommand.ExecuteScalar();
                }
                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM Recipes"))
                {
                    val[1] = (int)dbCommand.ExecuteScalar();
                }
            }
            return val;
        }
        catch (Exception ex)
        {
            _Logger.Error(ex);
            return null;
        }
    }

Try Catch "solution"

If there is something better, please let me know!

    /// <summary>
    /// Verifies the db if it is not corrupt! If the return value is <see cref="Nullable"/>, the DB is corrupt!
    /// </summary>
    /// <returns><see cref="Array"/> of <see cref="int"/>. The 1. index is components.Count(), the 2 index is the recipes.Count()!!</returns>
    [CanBeNull]
    public static int[] ImportDB_Verify()
    {
        int[] val = new int[2];

        Exception sqLiteException;

        try
        {
            SQLiteFactory provider = new SQLiteFactory();
            SQLiteConnectionStringBuilder connectionStringBuilder = new SQLiteConnectionStringBuilder
            {
                DataSource = Core.CommonAppDataPath + "tmp.HTdb"
            };
            using (DbConnection dbConnection = provider.CreateConnection())
            {

                dbConnection.ConnectionString = connectionStringBuilder.ConnectionString;
                dbConnection.Open();

                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM components;"))
                {
                    val[0] = (int)dbCommand.ExecuteScalar();
                }
                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM recipes;"))
                {
                    val[1] = (int)dbCommand.ExecuteScalar();
                }
            }

            return val;
        }
        catch (Exception ex)
        {
            sqLiteException = ex;
        }

        try
        {
            SqlCeProviderFactory provider = new SqlCeProviderFactory();
            SqlCeConnectionStringBuilder connectionStringBuilder = new SqlCeConnectionStringBuilder
            {
                DataSource = Core.CommonAppDataPath + "tmp.HTdb"
            };
            using (DbConnection dbConnection = provider.CreateConnection())
            {

                dbConnection.ConnectionString = connectionStringBuilder.ConnectionString;
                dbConnection.Open();

                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM Components;"))
                {
                    val[0] = (int)dbCommand.ExecuteScalar();
                }
                using (DbCommand dbCommand = dbConnection.CreateCommand("SELECT Count(*) FROM Recipes;"))
                {
                    val[1] = (int)dbCommand.ExecuteScalar();
                }
            }
            return val;
        }
        catch (Exception ex)
        {
            _Logger.Error(ex, $"Error while verifying the database. The SQLite Exception: {sqLiteException}");
            return null;
        }
    }

Answer 1

Open the file and read its "magic number". SQLite databases start with the ASCII string "SQLite format", SQL Server CE .SDF files start with 0x01 0x0F 0x00 0x00 as far as I can find, that is, if they look like .MDF files.

So something like this:

byte[] buffer = new byte[4];

using (var fileStream = File.OpenRead(databasefile))
{
    fileStream.Read(buffer, 0, 4);  
}

if (buffer[0] == 83 // S
    && buffer[1] == 81 // Q
    && buffer[2] == 76 // L
    && buffer[3] == 105) // i
{
    // SQLite
}
else
{
    // Assume SQL Server CE
}

Note that a user could still feed your code a text file that happens to begin with "SQLi" but isn't an SQLite file, so you cannot and should not remove your exception handling code using this approach.

Answer 2

Using @CodeCaster's answer:

public enum DBType { SQLite, SQLServerCe };

public static DBType GetDatabaseType(string filename)
{
    byte[] buffer = new byte[4];

    using (var fileStream = File.OpenRead(databasefile))
    {
        fileStream.Read(buffer, 0, 4);  
    }

    if (buffer[0] == 83 // S
        && buffer[1] == 81 // Q
        && buffer[2] == 76 // L
        && buffer[3] == 105) // i
    {
        return DBType.SQLite;
    }
    else
    {
        return DBType.SQLServerCe;
    }
}

public static int[] ImportDB_Verify()
{
    string dbFilePath = "someDatabaseFile"
    DBType detectedType = GetDatabaseType(dbFilePath);

    if(detectedType == DBType.SQLite)
        return VerifySQLiteDb(dbFilePath);
    else
        return VerifySQLServerCeDb(dbFilePath);
}

private static int[] VerifySQLiteDb(string dbFilePath)
{
    //...
    // exception handling etc.
}

private static int[] VerifySQLServerCeDb(string dbFilePath)
{
    //...
    // exception handling etc.
}