Connect to SQLite database, add relations manually

Knowledgebase English KB
1

To connect to a SQLite database you can normally use the SQLiteConnectionDataProvider directly. However, if there are no relations in the database and you want to add them later, you need to proceed differently:

Use an instance of DbCommandSetDataProvider instead. You can then add the tables and relations as follows:

using (ListLabel LL = new ListLabel())
{
    // use reflection to make sure to create the "right" SQLiteConnection (i.e. the one used internally)
    Type type = DataProviderHelper.GetSQLiteConnectionType();
    IDbConnection conn = Activator.CreateInstance(type, @"data source=c:\temp\BoxBuddy.db") as IDbConnection;

    // create generic DbCommand host provider
    DbCommandSetDataProvider provider = new DbCommandSetDataProvider();

    // add first table
    var cmd1 = conn.CreateCommand();
    cmd1.CommandText = "SELECT * from TableA";
    provider.AddCommand(cmd1, "TableA", "\"{0}\"", "@{0}");

    // add Box table
    var cmd2 = conn.CreateCommand();
    cmd2.CommandText = "SELECT * from TableB";
    provider.AddCommand(cmd2, "TableB", "\"{0}\"", "@{0}");

    // manually add relation
    provider.AddRelation("TableAtoTableB", "TableA", "TableB", "ID", "FK_ID");

    // assign data source
    LL.DataSource = provider;

    // call the designer
    LL.Design();
}