Create table with custom name dynamically and insert with custom table name

Question

I want to create the table with custom name but I cannot find the sample code. I notice the only way to create table is by generic type like db.CreateTable(). May I know if there is a way to create the table name dynamically instead of using Alias? The reason is because sometime we want to store the same object type into different tables like 2015_january_activity, 2015_february_activity.

Apart from this, the db.Insert also very limited to object type. Is there anyway to insert by passing in the table name?

I think these features are very important as it exists in NoSQL solution for long and it's very flexible. Thanks.

Answer 1

OrmLite is primarily a code-first ORM which uses typed POCO's to create and query the schema of matching RDMBS tables. It also supports executing Custom SQL using the Custom SQL API's.

One option to use a different table name is to change the Alias at runtime as seen in this previous answer where you can create custom extension methods to modify the name of the table, e.g:

public static class GenericTableExtensions 
{
    static object ExecWithAlias<T>(string table, Func<object> fn)
    {
        var modelDef = typeof(T).GetModelMetadata();
        lock (modelDef) {
            var hold = modelDef.Alias;
            try {
                modelDef.Alias = table;
                return fn();
            }
            finally {
                modelDef.Alias = hold;
            }
        }
    }

    public static void DropAndCreateTable<T>(this IDbConnection db, string table) {
        ExecWithAlias<T>(table, () => { db.DropAndCreateTable<T>(); return null; });
    }

    public static long Insert<T>(this IDbConnection db, string table, T obj, bool selectIdentity = false) {
        return (long)ExecWithAlias<T>(table, () => db.Insert(obj, selectIdentity));
    }

    public static List<T> Select<T>(this IDbConnection db, string table, Func<SqlExpression<T>, SqlExpression<T>> expression) {
        return (List<T>)ExecWithAlias<T>(table, () => db.Select(expression));
    }

    public static int Update<T>(this IDbConnection db, string table, T item, Expression<Func<T, bool>> where) {
        return (int)ExecWithAlias<T>(table, () => db.Update(item, where));
    }
}

These extension methods provide additional API's that let you change the name of the table used, e.g:

var tableName = "TableA"'
db.DropAndCreateTable<GenericEntity>(tableName);

db.Insert(tableName, new GenericEntity { Id = 1, ColumnA = "A" });

var rows = db.Select<GenericEntity>(tableName, q =>
    q.Where(x => x.ColumnA == "A"));

rows.PrintDump();

db.Update(tableName, new GenericEntity { ColumnA = "B" },
    where: q => q.ColumnA == "A");

rows = db.Select<GenericEntity>(tableName, q => 
    q.Where(x => x.ColumnA == "B"));

rows.PrintDump();

This example is also available in the GenericTableExpressions.cs integration test.