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.
}
