public void TestSqlStatementBuilderCreateUpdateAllWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var qualifiers = Field.From("Field1", Helper.DbSetting);
// Act
var actual = statementBuilder.CreateUpdateAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
batchSize: 1,
primaryField: null,
identityField: null);
var expected = $"" +
$"UPDATE [dbo].[Table] " +
$"SET [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Field1] = @Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithOrderBy()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending,
Field2 = Order.Descending
});
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateBatchQueryWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "[dbo].[Table]";
var fields = Field.From("Field1", "Field2");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null,
hints: SqlServerTableHints.NoLock);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [dbo].[Table] WITH (NOLOCK) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateMergeAllWithUncoveredIdentity()
{
// 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 identityField = new DbField("Id", false, true, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateMergeAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
batchSize: 1,
primaryField: null,
identityField: identityField);
var expected = $"" +
$"MERGE [Table] AS T " +
$"USING ( SELECT @Field1 AS [Field1], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[Field1] = T.[Field1] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [Field1], [Field2], [Field3] ) " +
$"VALUES ( S.[Field1], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field1] = S.[Field1], [Field2] = S.[Field2], [Field3] = S.[Field3] " +
$"OUTPUT INSERTED.[Id] AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateInsertWithIdentity()
{
// 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(int), null, null, null);
// Act
var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: null,
identityField: identityField);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field2, @Field3 ) ; " +
$"SELECT CONVERT(INT, SCOPE_IDENTITY()) AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// Initializes all the necessary settings for SQL Server.
/// </summary>
public static void Initialize()
{
// Skip if already initialized
if (IsInitialized == true)
{
return;
}
// Map the DbSetting
var dbSetting = new SqlServerDbSetting();
DbSettingMapper.Add <SqlConnection>(dbSetting, true);
// Map the DbHelper
var dbHelper = new SqlServerDbHelper();
DbHelperMapper.Add <SqlConnection>(dbHelper, true);
// Map the Statement Builder
var statementBuilder = new SqlServerStatementBuilder(dbSetting);
StatementBuilderMapper.Add <SqlConnection>(statementBuilder, true);
// Set the flag
IsInitialized = true;
}
public void TestSqlStatementBuilderCreateBatchQuerySecondBatch()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2" }, Helper.DbSetting);
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending }, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 1,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [Table] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 11 AND 20) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithWhereAndWithOrderByAndWithTopAndWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var where = new QueryGroup(new QueryField("Id", 1));
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending,
Field2 = Order.Descending
});
var top = 100;
var hints = SqlServerTableHints.NoLock;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
orderBy: orderBy,
top: top,
hints: hints);
var expected = $"" +
$"SELECT TOP (100) [Field1], [Field2], [Field3] " +
$"FROM [Table] WITH (NOLOCK) " +
$"WHERE ([Id] = @Id) " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateInsertWithPrimary()
{
// 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("Field1", true, false, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
primaryField: primaryField,
identityField: null);
var expected = $"" +
$"INSERT INTO [Table] " +
$"( [Field1], [Field2], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field2, @Field3 ) ; " +
$"SELECT @Field1 AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateInsertAllWithPrimaryAndIdentityAsBigInt()
{
// 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);
var identityField = new DbField("Field2", 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] " +
$"( [Field1], [Field3] ) " +
$"VALUES " +
$"( @Field1, @Field3 ) ; " +
$"SET @Field2 = CONVERT(BIGINT, SCOPE_IDENTITY()) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateUpdateAllWithCoveredPrimaryField()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
var field = new DbField("Field1", true, true, false, typeof(int), null, null, null, null, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateUpdateAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
batchSize: 1,
primaryField: field,
identityField: null);
var expected = $"" +
$"UPDATE [Table] " +
$"SET [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Field1] = @Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateUpdateWithUncoveredWhereExpressionButWithCoveredIdentity()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var where = new QueryGroup(new QueryField("Id", 1));
var field = new DbField("Field1", true, true, false, typeof(int), null, null, null);
// Act
var actual = statementBuilder.CreateUpdate(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
primaryField: null,
identityField: field);
var expected = $"" +
$"UPDATE [Table] " +
$"SET [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Id] = @_Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateUpdateWithWhereExpression()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var where = new QueryGroup(new QueryField("Field1", 1));
// Act
var actual = statementBuilder.CreateUpdate(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
primaryField: null,
identityField: null);
var expected = $"" +
$"UPDATE [Table] " +
$"SET [Field1] = @Field1, [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Field1] = @_Field1) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateUpdateAllForThreeBatches()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var qualifiers = Field.From("Field1");
// Act
var actual = statementBuilder.CreateUpdateAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
batchSize: 3,
primaryField: null,
identityField: null);
var expected = $"" +
$"UPDATE [Table] " +
$"SET [Field2] = @Field2, [Field3] = @Field3 " +
$"WHERE ([Field1] = @Field1) ; " +
$"UPDATE [Table] " +
$"SET [Field2] = @Field2_1, [Field3] = @Field3_1 " +
$"WHERE ([Field1] = @Field1_1) ; " +
$"UPDATE [Table] " +
$"SET [Field2] = @Field2_2, [Field3] = @Field3_2 " +
$"WHERE ([Field1] = @Field1_2) ;";
// Assert
Assert.AreEqual(expected, actual);
}
/// <summary>
/// Initializes all necessary settings for SqlServer.
/// </summary>
public static void Initialize()
{
// Skip if already initialized
if (IsInitialized == true)
{
return;
}
// Map the DbSetting
var dbSetting = new SqlServerDbSetting();
DbSettingMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), dbSetting, true);
DbSettingMapper.Add(typeof(System.Data.SqlClient.SqlConnection), dbSetting, true);
// Map the DbHelper
var dbHelper = new SqlServerDbHelper();
DbHelperMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), dbHelper, true);
DbHelperMapper.Add(typeof(System.Data.SqlClient.SqlConnection), dbHelper, true);
// Map the Statement Builder
var statementBuilder = new SqlServerStatementBuilder(dbSetting);
StatementBuilderMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), statementBuilder, true);
StatementBuilderMapper.Add(typeof(System.Data.SqlClient.SqlConnection), statementBuilder, true);
// Set the flag
IsInitialized = true;
}
public void TestSqlStatementBuilderCreateMergeWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From("Field1", "Field2", "Field3");
var qualifiers = Field.From("Field1");
// Act
var actual = statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: qualifiers,
primaryField: null,
identityField: null);
var expected = $"" +
$"MERGE [dbo].[Table] AS T " +
$"USING ( SELECT @Field1 AS [Field1], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[Field1] = T.[Field1] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [Field1], [Field2], [Field3] ) " +
$"VALUES ( S.[Field1], S.[Field2], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field1] = S.[Field1], [Field2] = S.[Field2], [Field3] = S.[Field3] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateMergeWithCoveredPrimaryAndWithCoveredIdentityButWithoutQualifiers()
{
// 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);
var identityField = new DbField("Field2", false, true, false, typeof(int), null, null, null);
// Act
var actual = statementBuilder.CreateMerge(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
qualifiers: null,
primaryField: primaryField,
identityField: identityField);
var expected = $"" +
$"MERGE [Table] AS T " +
$"USING ( SELECT @Field1 AS [Field1], @Field2 AS [Field2], @Field3 AS [Field3] ) " +
$"AS S ON ( S.[Field1] = T.[Field1] ) " +
$"WHEN NOT MATCHED THEN " +
$"INSERT ( [Field1], [Field3] ) " +
$"VALUES ( S.[Field1], S.[Field3] ) " +
$"WHEN MATCHED THEN " +
$"UPDATE SET [Field3] = S.[Field3] " +
$"OUTPUT INSERTED.[Field2] AS [Result] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void Init(string connectionString, DatabaseProvider databaseType)
{
DatabaseProvider = databaseType;
conn = DatabaseProvider.GetAndConfigureConnection(connectionString, (connection, dbType) =>
{
switch (dbType)
{
case DatabaseProvider.MySqlData:
MySqlBootstrap.Initialize();
break;
case DatabaseProvider.Npgsql:
PostgreSqlBootstrap.Initialize();
break;
case DatabaseProvider.SystemData:
{
var dbSetting = new SqlServerDbSetting();
DbSettingMapper.Add(typeof(System.Data.SqlClient.SqlConnection), dbSetting, true);
// Map the DbHelper
var dbHelper = new SqlServerDbHelper();
DbHelperMapper.Add(typeof(System.Data.SqlClient.SqlConnection), dbHelper, true);
// Map the Statement Builder
var statementBuilder = new SqlServerStatementBuilder(dbSetting);
StatementBuilderMapper.Add(typeof(System.Data.SqlClient.SqlConnection), statementBuilder, true);
break;
}
case DatabaseProvider.MicrosoftData:
{
var dbSetting = new SqlServerDbSetting();
DbSettingMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), dbSetting, true);
// Map the DbHelper
var dbHelper = new SqlServerDbHelper();
DbHelperMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), dbHelper, true);
// Map the Statement Builder
var statementBuilder = new SqlServerStatementBuilder(dbSetting);
StatementBuilderMapper.Add(typeof(Microsoft.Data.SqlClient.SqlConnection), statementBuilder, true);
break;
}
case DatabaseProvider.MySqlConnector:
MySqlConnectorBootstrap.Initialize();
break;
default:
throw new ArgumentOutOfRangeException();
}
connection.Open();
return(connection);
});
}
public void ThrowExceptionOnSqlStatementBuilderCreateDeleteAllIfTheTableIsEmpty()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "";
// Act
statementBuilder.CreateDeleteAll(queryBuilder: queryBuilder,
tableName: tableName);
}
public void ThrowExceptionOnSqlStatementBuilderCreateTruncateIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
// Act
statementBuilder.CreateTruncate(queryBuilder: queryBuilder,
tableName: tableName);
}
public void ThrowExceptionOnSqlStatementBuilderCreateDeleteIfTheTableIsNull()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = (string)null;
// Act
statementBuilder.CreateDelete(queryBuilder: queryBuilder,
tableName: tableName);
}
public void ThrowExceptionOnSqlStatementBuilderCreateCountIfTheTableIsEmpty()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "";
// Act
statementBuilder.CreateCount(queryBuilder: queryBuilder,
tableName: tableName,
hints: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfThereAreNoFields()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryAllIfTheTableIsNull()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = (string)null;
var fields = Field.From("Field1", "Field2", "Field3");
// Act
statementBuilder.CreateQueryAll(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheTableIsEmpty()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
// Act
statementBuilder.CreateInsert(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
primaryField: null,
identityField: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheOrderFieldIsNotCovered()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new { Id = Order.Ascending, Field1 = Order.Ascending });
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
}
public void TestSqlStatementBuilderCreateTruncateWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
// Act
var actual = statementBuilder.CreateTruncate(queryBuilder: queryBuilder,
tableName: tableName);
var expected = "TRUNCATE TABLE [dbo].[Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlStatementBuilderCreateInsertAllIfTheTableIsEmpty()
{
// 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);
}
