I have a postgres database with several schemas.
And schemas are also created at runtime while the app runs.
These schemas have a fixed set of tables, named like table1
, table2
.
In Petopoco i have to decorate by Poco classes with the table attribute.
I do it like
[TableName("schema1.table1")]
[PrimaryKey("id")]
class SomePoco {
[Column("id")]public int Id { get; set; }
//other properties
}
I want the schema part of the attribute to be changed at runtime, since I am creating schemas at runtime.
Is there a possibility for this, or is there any other way I can use a single POCO over multiple schemas.
Although you can't change the attribute, there are a few different ways to get what you want, depending on what info your code has and how you want to write your code.
One approach is to remove the TableName
attribute from your POCO and specify the whole query in your SQL. So instead of just db.Fetch<SomePoco>()
you might do something like:
var schemaName = "schema1";
var tableName = "table1";
db.Fetch<SomePoco>($"select * from {schemaName}.{tableName}");
You could also use a custom mapper to help you get what you want. A PetaPoco mapper interprets between POCO information and table/column names, so you can use it to make changes on the way to the database.
[TableName("table1")]
[PrimaryKey("id")]
class SomePoco {
[Column("id")]public int Id { get; set; }
}
public class SchemaMapper : ConventionMapper
{
private string _schema;
public SchemaMapper(string schema)
{
_schema = schema;
}
public override TableInfo GetTableInfo(Type pocoType)
{
var ti = base.GetTableInfo(pocoType);
// prepend the schema to the table name
ti.TableName = $"{_schema}.{ti.TableName}";
return ti;
}
}
using var db = new Database(myConnection, new SchemaMapper("schema1"));
db.Fetch<SomePoco>();
// SELECT schema1.table1.id from schema1.table1
If you make Schema
a public property instead of a private field, you could even change the schema after you've created the Database
object, by setting ((SchemaMapper)db.DefaultMapper).Schema
later on.
(By the way, instead of having to explicitly set ColumnName
properties on all your POCOs just to lowercase them, you can also have your mapper do that for you by adding InflectColumnName = (i, cn) => cn.ToLower();
to your mapper's constructor. This will automatically turn property Id
into column id
.)