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); }