public void TempTableShouldMatch() { // arrange var instance = new TSqlGenerator <StandardModel>(); var expected = $@"#StandardModels"; // act var actual = instance.TableName(true); // assert Assert.Equal(expected, actual); }
/// <summary> /// Upsert (insert if not matched, update if matched) a generic collection of objects. /// </summary> /// <typeparam name="TEntity">The type of entities to be updated.</typeparam> /// <param name="conn">The <see cref="IDbConnection"/> instance we're hanging off.</param> /// <param name="entities">An IEnumerable of objects to be upserted into the connection.</param> /// <param name="options">Bulk Upsert Options that describe how this upsert should work.</param> /// <returns></returns> public static bool Upsert <TEntity>( this IDbConnection conn, IEnumerable <TEntity> entities, IUpsertOptions <TEntity> options) { // setup var sqlGenerator = new TSqlGenerator <TEntity>(); var createScript = sqlGenerator.CreateTable(); var mergeScript = sqlGenerator.Merge(options); var cleanUpScript = $"DROP TABLE {sqlGenerator.TableName(true)};"; // execute using (var createTableCmd = conn.CreateCommand()) { createTableCmd.CommandType = CommandType.Text; createTableCmd.CommandText = createScript; createTableCmd.ExecuteNonQuery(); } // ==> bulk insert to temp table using (var copy = new SqlBulkCopy((SqlConnection)conn)) using (var reader = FastMember.ObjectReader.Create(entities, sqlGenerator.GetProperties().ToArray())) { // setup mapping foreach (var prop in sqlGenerator.GetProperties()) { // force one<=>one mapping copy.ColumnMappings.Add(prop, prop); } // set destination table (insert into TEMP table) copy.DestinationTableName = sqlGenerator.TableName(true); // write data from reader to server copy.WriteToServer(reader); // run the bulk insert to temp table copy.Close(); } // <== end bulk insert to temp table // ==> merge upsert using (var trans = conn.BeginTransaction()) using (var mergeTempAndRealCmd = conn.CreateCommand()) { // ==> merge temp table to target table mergeTempAndRealCmd.Transaction = trans; mergeTempAndRealCmd.CommandType = CommandType.Text; mergeTempAndRealCmd.CommandText = mergeScript; mergeTempAndRealCmd.ExecuteNonQuery(); // <== end merge temp table to target table trans.Commit(); } // <== end merge upsert // cleanup using (var dropTableCmd = conn.CreateCommand()) { dropTableCmd.CommandText = cleanUpScript; dropTableCmd.ExecuteNonQuery(); } // finalize return(true); // maybe update this to indicate kinds of failure instead of simple true/false. }