public void TestSqlStatementBuilderCreateInsertAllWithPrimary()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var primaryField = new DbField("Field1", true, false, false, typeof(int), null, null, null);
// Act
var actual = statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: primaryField,
identityField: null);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateInsertAllWithIdentityAsBigInt()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var identityField = new DbField("Field1", false, true, false, typeof(long), null, null, null);
// Act
var actual = statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: null,
identityField: identityField);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SET @Field1 = CONVERT(BIGINT, SCOPE_IDENTITY()) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertAllIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
// Act
statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
batchSize: 1,
primaryField: null,
identityField: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertAllIfThePrimaryIsNotReallyAPrimary()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var primaryField = new DbField("Field1", false, false, false, typeof(int), null, null, null);
// Act
statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: primaryField,
identityField: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertAllIfTheNonIdentityPrimaryIsNotCovered()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var primaryField = new DbField("Id", true, false, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: primaryField,
identityField: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertAllIfTheIdentityIsNotReallyAnIdentity()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var qualifiers = Field.From("Field1", Helper.DbSetting);
var identifyField = new DbField("Field2", false, false, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: null,
identityField: identifyField);
}
public void TestSqlStatementBuilderCreateInsertAllWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
var actual = statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 1,
primaryField: null,
identityField: null);
var expected = $"" +
$"INSERT INTO [dbo].[Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateInsertAllWithIdentityForThreeBatches()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var identityField = new DbField("Field1", false, true, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateInsertAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
batchSize: 3,
primaryField: null,
identityField: identityField);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SET @Field1 = CONVERT(INT, SCOPE_IDENTITY()) ; " +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2_1, @Field3_1 ) ; " +
$"SET @Field1_1 = CONVERT(INT, SCOPE_IDENTITY()) ; " +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2_2, @Field3_2 ) ; " +
$"SET @Field1_2 = CONVERT(INT, SCOPE_IDENTITY()) ;";
// Assert
Assert.AreEqual(expected, actual);
}
