public void EndToEndInsert()
{
// Arrange
using (var connection = DatabaseHelper.CreateAndOpenConnection())
{
var targetSchema = SqlTableSchema.LoadFromDatabase(connection, "TestUpsert", "ident");
var upserter = new DataTableUpserter(targetSchema);
upserter.RetrieveIdentity = true;
var dataTable = new DataTable("TestUpsert");
dataTable.Columns.Add("ident");
dataTable.Columns.Add("key_part_1");
dataTable.Columns.Add("key_part_2");
dataTable.Columns.Add("nullable_text");
dataTable.Columns.Add("nullable_number");
for (int i = 1; i <= 10; i++)
{
dataTable.Rows.Add(DBNull.Value, "TEST", i, String.Format("some text here {0}", i), i * 11);
}
// Act
IEnumerable <int> result = upserter.Upsert(connection, dataTable);
var idents = new List <int>(result);
// Assert
Assert.AreEqual(10, idents.Count);
foreach (DataRow row in dataTable.Rows)
{
Assert.AreEqual(row["key_part_2"], row["ident"]);
}
}
}
public void RetrieveTableSchemaNotExist()
{
using (var connection = DatabaseHelper.CreateAndOpenConnection())
{
// Arrange
// Act
SqlTableSchema.LoadFromDatabase(connection, "DoesNotExist", null);
// Assert
}
}
public void EndToEnd()
{
using (var connection = DatabaseHelper.CreateAndOpenConnection())
{
// Arrange
var targetSchema = SqlTableSchema.LoadFromDatabase(connection, "TestUpsert", "ident");
var columnMappings = new Dictionary <string, Func <TestDto, object> >
{
{ "ident", d => d.Ident },
{ "key_part_1", d => d.KeyPart1 },
{ "key_part_2", d => d.KeyPart2 },
{ "nullable_text", d => d.Text },
{ "nullable_number", d => d.Number },
{ "nullable_datetimeoffset", d => d.Date },
};
Action <TestDto, int> identUpdater = (d, i) => d.Ident = i;
var upserter = new TypedUpserter <TestDto>(targetSchema, columnMappings, identUpdater);
var items = new List <TestDto>();
for (int i = 1; i <= 10; i++)
{
items.Add(
new TestDto
{
Ident = null,
KeyPart1 = "TEST",
KeyPart2 = (short)i,
Text = String.Format("some text here {0}", i),
Number = i,
Date = new DateTimeOffset(new DateTime(2010, 11, 14, 12, 0, 0), TimeSpan.FromHours(i))
});
}
// Act
upserter.Upsert(connection, items);
// Assert
foreach (var testDto in items)
{
Assert.AreEqual(testDto.Number, testDto.Ident);
}
}
}
public void EndToEndInsertUpdate()
{
// Arrange
using (var connection = DatabaseHelper.CreateAndOpenConnection())
{
connection.ExecuteCommands(Resources.PopulateTable);
var targetSchema = SqlTableSchema.LoadFromDatabase(connection, "TestUpsert", "ident");
var upserter = new DataTableUpserter(targetSchema);
upserter.RetrieveIdentity = true;
var dataTable = new DataTable("TestUpsert");
dataTable.Columns.Add("ident");
dataTable.Columns.Add("key_part_1");
dataTable.Columns.Add("key_part_2");
dataTable.Columns.Add("nullable_text");
dataTable.Columns.Add("nullable_number");
for (int i = 1; i <= 10; i++)
{
dataTable.Rows.Add(i, "TEST", i, String.Format("some text here {0}", i), i);
dataTable.Rows.Add(DBNull.Value, "BLAH", i, String.Format("some text here {0}", i), i + 10);
}
// Act
IEnumerable <int> result = upserter.Upsert(connection, dataTable);
var idents = new List <int>(result);
// Assert
Assert.AreEqual(10, idents.Count);
foreach (DataRow row in dataTable.Rows)
{
Assert.AreEqual(row["nullable_number"], row["ident"]);
}
Assert.AreEqual(20, connection.ExecuteScalar("SELECT COUNT(1) FROM [TestUpsert]"));
Assert.AreEqual(10, connection.ExecuteScalar("SELECT COUNT(1) FROM [TestUpsert] WHERE [key_part_1] != 'BLAH'"));
Assert.AreEqual(10, connection.ExecuteScalar("SELECT COUNT(1) FROM [TestUpsert] WHERE [key_part_1] = 'BLAH'"));
}
}
public void RetrieveTableSchema()
{
using (var connection = DatabaseHelper.CreateAndOpenConnection())
{
// Arrange
#region Columns
var expectedColumns = new List <Column>
{
new NumericColumn
{
Name = "ident",
OrdinalPosition = 1,
Nullable = false,
DataType = "int",
Precision = 10,
Radix = 10,
Scale = 0,
},
new TextColumn
{
Name = "key_part_1",
OrdinalPosition = 2,
Nullable = false,
DataType = "nchar",
CharLength = 4,
ByteLength = 8,
},
new NumericColumn
{
Name = "key_part_2",
OrdinalPosition = 3,
Nullable = false,
DataType = "smallint",
Precision = 5,
Radix = 10,
Scale = 0,
},
new TextColumn
{
Name = "nullable_text",
OrdinalPosition = 4,
Nullable = true,
DataType = "nvarchar",
CharLength = 50,
ByteLength = 100,
},
new NumericColumn
{
Name = "nullable_number",
OrdinalPosition = 5,
Nullable = true,
DataType = "int",
Precision = 10,
Radix = 10,
Scale = 0,
},
new DateColumn
{
Name = "nullable_datetimeoffset",
OrdinalPosition = 6,
Nullable = true,
DataType = "datetimeoffset",
Precision = 7,
},
new NumericColumn
{
Name = "nullable_money",
OrdinalPosition = 7,
Nullable = true,
DataType = "money",
Precision = 19,
Radix = 10,
Scale = 4,
},
new TextColumn
{
Name = "nullable_varbinary",
OrdinalPosition = 8,
Nullable = true,
DataType = "varbinary",
CharLength = -1,
ByteLength = -1,
},
new TextColumn
{
Name = "nullable_image",
OrdinalPosition = 9,
Nullable = true,
DataType = "image",
CharLength = 2147483647,
ByteLength = 2147483647,
},
new Column
{
Name = "nullable_xml",
OrdinalPosition = 10,
Nullable = true,
DataType = "xml",
},
};
var expectedKeyColumns = new List <Column>
{
new TextColumn
{
Name = "key_part_1",
OrdinalPosition = 2,
Nullable = false,
DataType = "nchar",
CharLength = 4,
ByteLength = 8,
},
new NumericColumn
{
Name = "key_part_2",
OrdinalPosition = 3,
Nullable = false,
DataType = "smallint",
Precision = 5,
Radix = 10,
Scale = 0,
},
};
#endregion
// Act
SqlTableSchema schema = SqlTableSchema.LoadFromDatabase(connection, "TestUpsert", "ident");
// Assert
Assert.AreEqual("TestUpsert", schema.TableName);
CollectionAssert.AreEqual(expectedColumns, schema.Columns, new ColumnComparer());
CollectionAssert.AreEqual(expectedKeyColumns, schema.PrimaryKeyColumns, new ColumnComparer());
}
}