/// <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 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 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> /// 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()); } }
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> /// 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> /// 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> /// <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> /// 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> /// 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> /// 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> /// <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> /// 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> /// Gets an instance builder usable to construct the given type. /// /// If a surrogate is registered, the surrogate will be used for discovery - the returned /// constructor will be mapped from the surrogate to forType. /// /// If a surrogate is not registered, either an exception will be thrown or forType will /// be passed to TypeDescriber.GetInstanceBuilder depending on the value of /// ThrowOnNoRegisteredSurrogate. /// </summary> public InstanceBuilder GetInstanceBuilder(TypeInfo forType) { if (!SurrogateTypes.TryGetValue(forType, out var proxy)) { if (ThrowOnNoRegisteredSurrogate) { Throw.InvalidOperationException($"No surrogate registered for {forType}"); } return(TypeDescriber.GetInstanceBuilder(forType)); } var fromProxy = TypeDescriber.GetInstanceBuilder(forType); return(Map(forType, fromProxy)); }
/// <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> /// 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> /// Enumerate all the members on forType to serialize. /// /// If a surrogate is registered, the surrogate will be used for discovery - the returned /// members will be mapped from the surrogate to forType. /// /// If a surrogate is not registered, either an exception will be thrown or forType will /// be passed to TypeDescriber.EnumerateMembersToSerialize depending on the value of /// ThrowOnNoRegisteredSurrogate. /// </summary> public IEnumerable <SerializableMember> EnumerateMembersToSerialize(TypeInfo forType) { if (!SurrogateTypes.TryGetValue(forType, out var proxy)) { if (ThrowOnNoRegisteredSurrogate) { Throw.InvalidOperationException($"No surrogate registered for {forType}"); } foreach (var member in TypeDescriber.EnumerateMembersToSerialize(forType)) { yield return(member); } yield break; } var fromProxy = TypeDescriber.EnumerateMembersToSerialize(proxy); foreach (var member in fromProxy) { yield return(Map(forType, member)); } }
public void HasType() { TypeDescriber type = TypeHandler.Get <Person>(); Assert.Equal(typeof(Person), type.Type); }