Dominic Jonas Dominic Jonas - 1 month ago 7
SQL Question

C# Detect Database format | DbProviderFactory

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

Now my question: Everytime 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

Using @CodeCaster's answer:

public enum DBType {SQLite, SQLCe};
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.SQLCe;
    }
}

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

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

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

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