[sqlite] Using |DataDirectory| in connection string (.NET with System.Data.SQLite)

[Lee Gray]

Hello, I'm just starting to experiment with sqlite and System.Data.SQLite. How 
do they make use of the connection string |DataDirectory| macro? I've found 
lots of references online showing that it is indeed used, but I haven't found 
how to extract the file path from it at runtime.

Given a connection string such as "Data 
Source=|DataDirectory|TestDatabase.sqlite;", I want to be able to create the 
database file if it does not exist, and I want to extract that path from the 
connection string.

In SQL CE, I could do this:

public string GetSqlCeDataSource()
{
    var connectionString = 
ConfigurationManager.ConnectionStrings[ConnectionStringName];
    var factory = DbProviderFactories.GetFactory(connectionString.ProviderName);

    using (var connection = factory.CreateConnection())
    {
        if (connection == null)
        {
            throw new InvalidOperationException(String.Format(
                "Failed to create connection for {0}", 
connectionString.ProviderName));
        }

        connection.ConnectionString = connectionString.ConnectionString;
        return connection.Database;
    }
}

But sqlite returns "main" as the database instead of the file name.

So I worked around it by using a ConnectionStringBuilder and parsing the macro 
myself, but is there a better way to do this?

Here's the workaround (null checks removed for simplicity):

public void EnsureDatabase()
{
    var connectionStringSettings = 
ConfigurationManager.ConnectionStrings[ConnectionStringName];
    var factory = 
DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
    var builder = factory.CreateConnectionStringBuilder();
    builder.ConnectionString = connectionStringSettings.ConnectionString;
    var dataDirectory = 
AppDomain.CurrentDomain.GetData("DataDirectory").ToString();

    string databaseFilePath = builder["Data 
Source"].ToString().Replace("|DataDirectory|", null);
    databaseFilePath = Path.Combine(dataDirectory, databaseFilePath);

    if (!Directory.Exists(dataDirectory))
    {
        Directory.CreateDirectory(dataDirectory);
    }

    if (!File.Exists(databaseFilePath))
    {
       SQLiteConnection.CreateFile(databaseFilePath);
    }
}

Thanks,
Lee