public static async Task <SqlServerCmd> FromTableSchemaAsync(IDbConnection connection, string schemaName, string tableName, IEnumerable <string> keyColumns, IDbTransaction txn = null)
{
string identityCol = await connection.QuerySingleOrDefaultAsync <string>(
@"SELECT [col].[name]
FROM [sys].[columns] [col]
INNER JOIN [sys].[tables] [t] ON [col].[object_id]=[t].[object_id]
WHERE
SCHEMA_NAME([t].[schema_id])=@schemaName AND
[t].[name]=@tableName AND
[col].[is_identity]=1", new { schemaName, tableName }, txn);
SqlServerCmd result = new SqlServerCmd($"{schemaName}.{tableName}", identityCol);
var columns = await connection.QueryAsync <string>(
@"SELECT [col].[name]
FROM [sys].[columns] [col]
INNER JOIN [sys].[tables] [t] ON [col].[object_id]=[t].[object_id]
WHERE
SCHEMA_NAME([t].[schema_id])=@schemaName AND
[t].[name]=@tableName AND
[col].[is_computed]=0 AND
[col].[is_identity]=0", new { schemaName, tableName }, txn);
foreach (var col in keyColumns)
{
result.Add(KeyColumnPrefix + col, null);
}
foreach (var col in columns.Except(keyColumns))
{
result.Add(col, null);
}
return(result);
}
public static async Task <SqlServerCmd> FromQueryAsync(IDbConnection connection, string sql, object parameters = null, string omitIdentityColumn = null, IDbTransaction txn = null)
{
// help from https://stackoverflow.com/a/26661203/2023653
var row = await connection.QuerySingleOrDefaultAsync(sql, parameters, txn);
var dictionary = row as IDictionary <string, object>;
var result = new SqlServerCmd();
foreach (var kp in dictionary)
{
if (!kp.Key.Equals(omitIdentityColumn))
{
result.Add(kp.Key, kp.Value);
}
}
return(result);
}