/// <summary>
/// Insert objects in table. Will not populate generated fields.
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>Rows affected</returns>
public static Task <int> InsertAsync <T>(this DbConnection conn, IEnumerable <T> dataList, DbTransaction transaction = null, CancellationToken cancellationToken = default)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
return(conn.InsertAsync(td.Table, dataList, transaction, cancellationToken));
}
/// <summary>
/// Insert objects in table. Will not populate generated fields.
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Insert <T>(this DbConnection conn, IEnumerable <T> dataList, DbTransaction transaction = null)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
return(conn.Insert(td.Table, dataList, transaction));
}
/// <summary>
/// Get a object of T by its ID
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="ids">Id(s) in same order as marked with KeyAttribute</param>
/// <returns>T</returns>
public static T Get <T>(this IDbConnection conn, params object[] ids)
where T : class, new()
{
TypeDescriber td = TypeHandler.Get <T>();
PropertyDescriber[] keys = td.Keys.ToArray();
if (keys.Length == 0)
{
throw new ArgumentException("T must be a type with properties decorated with KeyAttribute");
}
else if (keys.Length != ids.Length)
{
throw new ArgumentException(string.Format("KeyAttribute-count ({0}) and argument-count ({1}) must match", keys.Length, ids.Length));
}
else
{
string where = string.Join(",", keys.Select(x => x.DbName + "=@" + x.Property.Name));
string sql = $"SELECT * FROM {td.Table} WHERE {where}";
IDbCommand cmd = conn.Prepare(sql);
for (var i = 0; i < ids.Length; i++)
{
cmd.ApplyParameter(keys[i].Property.Name, ids[i]);
}
return(conn.Query <T>(cmd).FirstOrDefault());
}
}
/// <summary>
/// Insert object to table and populates key properties with generated values from database
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="data">Object containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Insert <T>(this IDbConnection conn, T data, IDbTransaction transaction = null)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
IEnumerable <PropertyDescriber> generated = td.Generated;
if (generated.Count() > 0)
{
string columns = td.WriteableColumns.Select(x => x.DbName).Aggregate((a, b) => a + "," + b);
string values = td.WriteableColumns.Select(x => "@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string returns = string.Join(",", generated.Select(x => x.DbName));
string sql = $"INSERT INTO {td.Table} ({columns}) VALUES ({values}) RETURNING {returns}";
var result = conn.QueryAssoc(sql, data, transaction).FirstOrDefault();
foreach (var prop in generated)
{
td.SetValue(prop.Property.Name, data, result[prop.DbName]);
}
return(1);
}
else
{
return(conn.Insert(td.Table, data, transaction));
}
}
/// <summary>
/// Insert object in table. Will populate generated fields with values from database.
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="data">Object containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>Rows affected</returns>
public static async Task <int> InsertAsync <T>(this DbConnection conn, T data, DbTransaction transaction = null, CancellationToken cancellationToken = default)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
IEnumerable <PropertyDescriber> generated = td.Generated;
if (generated.Count() > 0)
{
string columns = td.WriteableColumns.Select(x => x.DbName).Aggregate((a, b) => a + "," + b);
string values = td.WriteableColumns.Select(x => "@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string returns = string.Join(",", generated.Select(x => x.DbName));
string sql = $"INSERT INTO {td.Table} ({columns}) VALUES ({values}) RETURNING {returns}";
var result = (await conn.QueryAssocAsync(sql, data, transaction, cancellationToken).FirstOrDefaultAsync().ConfigureAwait(false));
foreach (var prop in generated)
{
td.SetValue(prop.Property.Name, data, result[prop.DbName]);
}
return(1);
}
else
{
return(await conn.InsertAsync(td.Table, data, transaction, cancellationToken).ConfigureAwait(false));
}
}
/// <summary>
/// Read data from database
/// </summary>
/// <typeparam name="T">class to map data to</typeparam>
/// <param name="conn">A connection</param>
/// <param name="cmd">Command to be executed</param>
/// <returns>List of T</returns>
public static IEnumerable <T> Query <T>(this IDbConnection conn, IDbCommand cmd) where T : new()
{
using (var reader = cmd.ExecuteReader())
{
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
var td = TypeHandler.Get <T>();
while (reader.Read())
{
var result = new T();
for (var i = 0; i < columns.Count; i++)
{
object value = reader[columns[i]];
td.SetValue(columns[i], result, value is DBNull ? null : value);
}
yield return(result);
}
}
}
public void ReadableProperties()
{
TypeDescriber person = TypeHandler.Get <Person>();
TypeDescriber document = TypeHandler.Get <Document>();
Assert.Equal(4, person.WriteableColumns.Count());
Assert.Equal(4, document.WriteableColumns.Count());
}
public void PropertiesRegistered()
{
TypeDescriber person = TypeHandler.Get <Person>();
TypeDescriber document = TypeHandler.Get <Document>();
Assert.Equal(7, person.Count);
Assert.Equal(4, document.Count);
}
public void HasTable()
{
TypeDescriber person = TypeHandler.Get <Person>();
TypeDescriber document = TypeHandler.Get <Document>();
Assert.Equal("persons", person.Table);
Assert.Equal("document", document.Table);
}
public void KeysAssigned()
{
TypeDescriber person = TypeHandler.Get <Person>();
TypeDescriber document = TypeHandler.Get <Document>();
Assert.Single(person.Keys);
Assert.Empty(document.Keys);
Assert.Equal(6, person.NonKeys.Count());
Assert.Equal(4, document.NonKeys.Count());
}
/// <summary>
/// Insert row in a table and returns generated id
/// </summary>
/// <typeparam name="Tid">type of primary key field</typeparam>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to insert into</param>
/// <param name="data">Object containing the data</param>
/// <param name="pk">Name of primary key field</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>value of generated id</returns>
public static Tid Insert <Tid>(this IDbConnection conn, string table, object data, string pk, IDbTransaction transaction = null)
where Tid : struct
{
TypeDescriber td = TypeHandler.Get(data);
string columns = td.WriteableColumns.Select(x => x.DbName).Aggregate((a, b) => a + "," + b);
string values = td.WriteableColumns.Select(x => "@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string sql = $"INSERT INTO {table} ({columns}) VALUES ({values}) RETURNING {pk}";
return(conn.Scalar <Tid>(sql, data, transaction));
}
/// <summary>
/// Delete object from table
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="data">Object containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>Rows affected</returns>
public static Task <int> DeleteAsync <T>(this DbConnection conn, T data, DbTransaction transaction = null, CancellationToken cancellationToken = default)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
IEnumerable <PropertyDescriber> keys = td.Keys;
if (keys.Count() > 0)
{
return(conn.DeleteAsync(td.Table, string.Join(" AND ", keys.Select(x => x.DbName + "=@" + x.Property.Name)), data, transaction, cancellationToken));
}
else
{
throw new ArgumentException("Invalid object. Atleast one property must be marked with KeyAttribute on type " + data.GetType().Name);
}
}
/// <summary>
/// Update row(s) in a table matching the where clause
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to update</param>
/// <param name="data">Object containing the data</param>
/// <param name="where">Where clause e.g. "id=@id"</param>
/// <param name="args">Additional arguments to apply other then data e.g. new { id = 1 }</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>Rows affected</returns>
public static async Task <int> UpdateAsync(this DbConnection conn, string table, object data, string where, object args = null, DbTransaction transaction = null, CancellationToken cancellationToken = default)
{
TypeDescriber td = TypeHandler.Get(data);
string set = td.WriteableColumns.Select(x => x.DbName + "=@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string sql = $"UPDATE {table} SET {set} WHERE {where}";
DbCommand cmd = await conn.PrepareAsync(sql, transaction, cancellationToken).ConfigureAwait(false);
cmd.ApplyParameters(data);
cmd.ApplyParameters(args);
return(await cmd.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false));
}
/// <summary>
/// Insert rows in table
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to insert into</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>Rows affected</returns>
public static async Task <int> InsertAsync(this DbConnection conn, string table, IEnumerable <object> dataList, DbTransaction transaction = null, CancellationToken cancellationToken = default)
{
TypeDescriber td = TypeHandler.Get(dataList.First());
string columns = string.Join(",", td.WriteableColumns.Select(x => x.DbName));
string values = string.Join(",", dataList.Select((data, i) => $"({string.Join(",", td.WriteableColumns.Select(x => "@" + x.Property.Name + i))})"));
string sql = $"INSERT INTO {table} ({columns}) VALUES {values}";
DbCommand cmd = await conn.PrepareAsync(sql, transaction, cancellationToken).ConfigureAwait(false);
cmd.ApplyParameters(dataList);
return(await cmd.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false));
}
/// <summary>
/// Insert row in a table
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to insert into</param>
/// <param name="data">Object containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Insert(this IDbConnection conn, string table, object data, IDbTransaction transaction = null)
{
TypeDescriber td = TypeHandler.Get(data);
string columns = td.WriteableColumns.Select(x => x.DbName).Aggregate((a, b) => a + "," + b);
string values = td.WriteableColumns.Select(x => "@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string sql = $"INSERT INTO {table} ({columns}) VALUES ({values})";
IDbCommand cmd = conn.Prepare(sql, transaction);
cmd.ApplyParameters(data);
return(cmd.ExecuteNonQuery());
}
/// <summary>
/// Update object in table
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="data">Object containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Update <T>(this IDbConnection conn, T data, IDbTransaction transaction = null)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
IEnumerable <PropertyDescriber> keys = td.Keys;
if (keys.Count() > 0)
{
return(conn.Update(td.Table, data, string.Join(",", keys.Select(x => x.DbName + "=@" + x.Property.Name)), transaction));
}
else
{
throw new ArgumentException("Invalid object. Atleast one property must be marked with KeyAttribute on type " + data.GetType().Name);
}
}
/// <summary>
/// Update row(s) in a table matching the where clause
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to update</param>
/// <param name="data">Object containing the data</param>
/// <param name="where">Where clause e.g. "id=@id"</param>
/// <param name="args">Additional arguments to apply other then data e.g. new { id = 1 }</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Update(this IDbConnection conn, string table, object data, string where, object args = null, IDbTransaction transaction = null)
{
TypeDescriber td = TypeHandler.Get(data);
string set = td.WriteableColumns.Select(x => x.DbName + "=@" + x.Property.Name).Aggregate((a, b) => a + "," + b);
string sql = $"UPDATE {table} SET {set} WHERE {where}";
IDbCommand cmd = conn.Prepare(sql, transaction);
cmd.ApplyParameters(data);
cmd.ApplyParameters(args);
return(cmd.ExecuteNonQuery());
}
/// <summary>
/// Add parameters to a command
/// </summary>
/// <param name="cmd">Command to add parameters to</param>
/// <param name="args">Object that holds the parameters</param>
public static void ApplyParameters(this DbCommand cmd, object args = null)
{
if (args == null)
{
return;
}
var td = TypeHandler.Get(args);
foreach (var property in td.Arguments)
{
var value = td.GetValue(property.Property.Name, args);
cmd.ApplyParameter(property.Property.Name, value ?? DBNull.Value);
}
}
/// <summary>
/// Insert objects in table or updates if exists
/// </summary>
/// <typeparam name="T">type of object</typeparam>
/// <param name="conn">A connection</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>IEnumerable of true when inserted, false when updated</returns>
public static Task <IEnumerable <bool> > UpsertAsync <T>(this DbConnection conn, IEnumerable <T> dataList, DbTransaction transaction = null, CancellationToken cancellationToken = default)
where T : class
{
TypeDescriber td = TypeHandler.Get <T>();
IEnumerable <PropertyDescriber> keys = td.Keys;
if (keys.Any())
{
return(conn.UpsertAsync(td.Table, dataList, string.Join(",", keys.Select(x => x.DbName)), transaction, cancellationToken));
}
else
{
throw new ArgumentException("Invalid object. Atleast one property must be marked with KeyAttribute on type " + dataList.First().GetType().Name);
}
}
/// <summary>
/// Insert rows in table
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to insert into</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>Rows affected</returns>
public static int Insert(this DbConnection conn, string table, IEnumerable <object> dataList, DbTransaction transaction = null)
{
TypeDescriber td = TypeHandler.Get(dataList.First());
string columns = string.Join(",", td.WriteableColumns.Select(x => x.DbName));
string values = string.Join(",", dataList.Select((data, i) => $"({string.Join(",", td.WriteableColumns.Select(x => "@" + x.Property.Name + i))})"));
string sql = $"INSERT INTO {table} ({columns}) VALUES {values}";
DbCommand cmd = conn.Prepare(sql, transaction);
cmd.ApplyParameters(dataList);
return(cmd.ExecuteNonQuery());
}
/// <summary>
/// Insert rows in table or updates if exists
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to upsert into</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="pk">Name of primary key field</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <returns>IEnumerable of true when inserted, false when updated</returns>
public static IEnumerable <bool> Upsert(this DbConnection conn, string table, IEnumerable <object> dataList, string pk, DbTransaction transaction = null)
{
TypeDescriber td = TypeHandler.Get(dataList.First());
string columns = string.Join(",", td.WriteableColumns.Select(x => x.DbName));
string values = string.Join(",", dataList.Select((data, i) => $"({string.Join(",", td.WriteableColumns.Select(x => "@" + x.Property.Name + i))})"));
string set = string.Join(",", td.WriteableColumns.Select(x => x.DbName + "=excluded." + x.DbName));
string sql = $@"
INSERT INTO {table} ({columns}) VALUES {values}
ON CONFLICT ({pk}) DO UPDATE SET {set} RETURNING (xmax = 0) as inserted";
DbCommand cmd = conn.Prepare(sql, transaction);
cmd.ApplyParameters(dataList);
return(conn.ScalarList <bool>(cmd));
}
/// <summary>
/// Insert rows in table or updates if exists
/// </summary>
/// <param name="conn">A connection</param>
/// <param name="table">Name of table to upsert into</param>
/// <param name="dataList">List of objects containing the data</param>
/// <param name="pk">Name of primary key field</param>
/// <param name="transaction">Transaction to associate with the command</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>IEnumerable of true when inserted, false when updated</returns>
public static async Task <IEnumerable <bool> > UpsertAsync(this DbConnection conn, string table, IEnumerable <object> dataList, string pk, DbTransaction transaction = null, CancellationToken cancellationToken = default)
{
TypeDescriber td = TypeHandler.Get(dataList.First());
string columns = string.Join(",", td.WriteableColumns.Select(x => x.DbName));
string values = string.Join(",", dataList.Select((data, i) => $"({string.Join(",", td.WriteableColumns.Select(x => "@" + x.Property.Name + i))})"));
string set = string.Join(",", td.WriteableColumns.Select(x => x.DbName + "=excluded." + x.DbName));
string sql = $@"
INSERT INTO {table} ({columns}) VALUES {values}
ON CONFLICT ({pk}) DO UPDATE SET {set} RETURNING (xmax = 0) as inserted";
DbCommand cmd = await conn.PrepareAsync(sql, transaction, cancellationToken).ConfigureAwait(false);
cmd.ApplyParameters(dataList);
return(await conn.ScalarListAsync <bool>(cmd, cancellationToken).ToListAsync().ConfigureAwait(false));
}
/// <summary>
/// Read data from database
/// </summary>
/// <typeparam name="T">class to map data to</typeparam>
/// <param name="conn">A connection</param>
/// <param name="cmd">Command to be executed</param>
/// <param name="cancellationToken">Cancellationtoken</param>
/// <returns>IAsyncEnumerable of T</returns>
public static async IAsyncEnumerable <T> QueryAsync <T>(this DbConnection conn, DbCommand cmd, [EnumeratorCancellation] CancellationToken cancellationToken = default) where T : new()
{
using (var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
var td = TypeHandler.Get <T>();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
var result = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
td.SetValue(reader.GetName(i), result, reader.GetValueWithNull(i));
}
yield return(result);
}
}
}
/// <summary>
/// Read data from database
/// </summary>
/// <typeparam name="T">class to map data to</typeparam>
/// <param name="conn">A connection</param>
/// <param name="cmd">Command to be executed</param>
/// <returns>IEnumerable of T</returns>
public static IEnumerable <T> Query <T>(this DbConnection conn, DbCommand cmd) where T : new()
{
using (var reader = cmd.ExecuteReader())
{
var td = TypeHandler.Get <T>();
while (reader.Read())
{
var result = new T();
for (var i = 0; i < reader.FieldCount; i++)
{
td.SetValue(reader.GetName(i), result, reader.GetValueWithNull(i));
}
yield return(result);
}
}
}
/// <summary>
/// Add parameters to a command
/// </summary>
/// <param name="cmd">Command to add parameters to</param>
/// <param name="args">Object that holds the parameters</param>
public static void ApplyParameters(this IDbCommand cmd, object args = null)
{
if (args == null)
{
return;
}
var typeDescriber = TypeHandler.Get(args);
foreach (var property in typeDescriber.Arguments)
{
var value = typeDescriber.GetValue(property.Property.Name, args);
if (property.Property.PropertyType.IsEnum)
{
value = value.ToString();
}
cmd.ApplyParameter(property.Property.Name, value ?? DBNull.Value);
}
}
/// <summary>
/// Add parameters to an indexed bulk command
/// </summary>
/// <param name="cmd">Command to add parameters to</param>
/// <param name="argsList">List of objects that holds the parameters</param>
public static void ApplyParameters(this DbCommand cmd, IEnumerable <object> argsList = null)
{
if (argsList == null)
{
return;
}
var td = TypeHandler.Get(argsList.First());
int i = 0;
foreach (var args in argsList)
{
foreach (var property in td.Arguments)
{
var value = td.GetValue(property.Property.Name, args);
cmd.ApplyParameter(property.Property.Name + i, value ?? DBNull.Value);
}
i++;
}
}
public void HasType()
{
TypeDescriber type = TypeHandler.Get <Person>();
Assert.Equal(typeof(Person), type.Type);
}
public void ReadFromCache()
{
Assert.Same(TypeHandler.Get <Person>(), TypeHandler.Get(new Person()));
}
public void ReadByObject()
{
Assert.NotNull(TypeHandler.Get(new Person()));
}
public void Different()
{
Assert.NotSame(TypeHandler.Get <Person>(), TypeHandler.Get <Document>());
}
