public void TestEveryType() { // arrange var instance = new TSqlGenerator <EachTypeWeHave>(); var expected = $@"CREATE TABLE EachTypeWeHave ( [PK] [int] NOT NULL, [FK] [int] NULL, [theGuid] [uniqueidentifier] NOT NULL, [nullGuid] [uniqueidentifier] NULL, [Name] [nvarchar](max) NOT NULL, [Dt] [datetime] NOT NULL, [NullableDt] [datetime] NULL, [d] [real] NOT NULL, [dNull] [real] NULL, [longNumber] [bigint] NOT NULL, [i64Number] [bigint] NOT NULL, [er] [float] NOT NULL, [dec] [decimal] NOT NULL, [theBIT] [bit] NOT NULL, [theBOOLEAN] [bit] NOT NULL, [chewer] [tinyint] NOT NULL, [bigChewer] [tinyint] NOT NULL, [binary] [binary] NOT NULL, [offset] [datetimeoffset] NOT NULL )"; // act var actual = instance.CreateTable(false); // assert Assert.Equal(expected, actual); }
public void TestNullAndNotNullInts() { // arrange var instance = new TSqlGenerator <TestNullAndNotNullInt>(); var expected = $@"CREATE TABLE TestNullAndNotNullInts ( [PK] [int] NOT NULL, [FK] [int] NULL )"; // act var actual = instance.CreateTable(false); // assert Assert.Equal(expected, actual); }
public void TestTempTableGenerationExplicit() { // arrange var instance = new TSqlGenerator <StandardModel>(); var expected = $@"CREATE TABLE #StandardModels ( [PK] [int] NOT NULL, [FK] [int] NULL, [Name] [nvarchar](max) NOT NULL )"; // act var actual = instance.CreateTable(true); // assert Assert.Equal(expected, actual); }
public void TestTempTableGeneration() { // arrange var instance = new TSqlGenerator <StandardModel>(); var expected = $@"CREATE TABLE #StandardModels (" + Environment.NewLine + "[PK] [int] NOT NULL," + Environment.NewLine + "[FK] [int] NULL," + Environment.NewLine + "[Name] [nvarchar](max) NOT NULL" + Environment.NewLine + ")"; // act var actual = instance.CreateTable(); // 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. }