public void TestSqLiteStatementBuilderCreateMergeAllWithIdentity() { // Setup var builder = StatementBuilderMapper.Get <SQLiteConnection>(); // Act var query = builder.CreateMergeAll(new QueryBuilder(), "Table", Field.From("Id", "Name", "Address"), null, 3, new DbField("Id", true, false, false, typeof(int), null, null, null, null), new DbField("Id", false, true, false, typeof(int), null, null, null, null)); var expected = "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id, @Name, @Address ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id) AS INT) AS [Result] ; " + "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_1, @Name_1, @Address_1 ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id_1) AS INT) AS [Result] ; " + "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_2, @Name_2, @Address_2 ) ; SELECT CAST(COALESCE(last_insert_rowid(), @Id_2) AS INT) AS [Result] ;"; // Assert Assert.AreEqual(expected, query); }
public void TestMySqlStatementBuilderCreateMergeAllWithPrimaryAsQualifier() { // Setup var builder = StatementBuilderMapper.Get <MySqlConnection>(); // Act var query = builder.CreateMergeAll(new QueryBuilder(), "Table", Field.From("Id", "Name", "Address"), Field.From("Id"), 3, new DbField("Id", true, false, false, typeof(int), null, null, null, null), null); var expected = "INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id, @Name, @Address ) ON DUPLICATE KEY UPDATE `Id` = @Id, `Name` = @Name, `Address` = @Address ; SELECT COALESCE(@Id, LAST_INSERT_ID()) AS `Result` ; " + "INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id_1, @Name_1, @Address_1 ) ON DUPLICATE KEY UPDATE `Id` = @Id_1, `Name` = @Name_1, `Address` = @Address_1 ; SELECT COALESCE(@Id_1, LAST_INSERT_ID()) AS `Result` ; " + "INSERT INTO `Table` ( `Id`, `Name`, `Address` ) VALUES ( @Id_2, @Name_2, @Address_2 ) ON DUPLICATE KEY UPDATE `Id` = @Id_2, `Name` = @Name_2, `Address` = @Address_2 ; SELECT COALESCE(@Id_2, LAST_INSERT_ID()) AS `Result` ;"; // Assert Assert.AreEqual(expected, query); }
public void TestPostgreSqlStatementBuilderCreateMergeAllWithIdentity() { // Setup var builder = StatementBuilderMapper.Get <NpgsqlConnection>(); // Act var query = builder.CreateMergeAll(new QueryBuilder(), "Table", Field.From("Id", "Name", "Address"), null, 3, new DbField("Id", true, false, false, typeof(int), null, null, null, null), new DbField("Id", false, true, false, typeof(int), null, null, null, null)); var expected = "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id, @Name, @Address ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name, \"Address\" = @Address RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_0 AS \"OrderColumn\" ; " + "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id_1, @Name_1, @Address_1 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_1, \"Address\" = @Address_1 RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_1 AS \"OrderColumn\" ; " + "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) OVERRIDING SYSTEM VALUE VALUES ( @Id_2, @Name_2, @Address_2 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_2, \"Address\" = @Address_2 RETURNING CAST(\"Id\" AS INTEGER) AS \"Id\", @__RepoDb_OrderColumn_2 AS \"OrderColumn\" ;"; // Assert Assert.AreEqual(expected, query); }
public void TestSqLiteStatementBuilderCreateMergeAllWithPrimaryAsQualifier() { // Setup var builder = StatementBuilderMapper.Get <SqliteConnection>(); // Act var query = builder.CreateMergeAll(new QueryBuilder(), "Table", Field.From("Id", "Name", "Address"), Field.From("Id"), 3, new DbField("Id", true, false, false, typeof(int), null, null, null, null), null); var expected = "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id, @Name, @Address ) ; SELECT CAST(@Id AS BIGINT) AS [Result] ; " + "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_1, @Name_1, @Address_1 ) ; SELECT CAST(@Id_1 AS BIGINT) AS [Result] ; " + "INSERT OR REPLACE INTO [Table] ( [Id], [Name], [Address] ) VALUES ( @Id_2, @Name_2, @Address_2 ) ; SELECT CAST(@Id_2 AS BIGINT) AS [Result] ;"; // Assert Assert.AreEqual(expected, query); }
public void TestPostgreSqlStatementBuilderCreateMergeAllWithPrimaryAsQualifier() { // Setup var builder = StatementBuilderMapper.Get <NpgsqlConnection>(); // Act var query = builder.CreateMergeAll(new QueryBuilder(), "Table", Field.From("Id", "Name", "Address"), Field.From("Id"), 3, new DbField("Id", true, false, false, typeof(int), null, null, null, null), null); var expected = "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id, @Name, @Address ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name, \"Address\" = @Address RETURNING @Id AS \"Result\" ; " + "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id_1, @Name_1, @Address_1 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_1, \"Address\" = @Address_1 RETURNING @Id AS \"Result\" ; " + "INSERT INTO \"Table\" ( \"Id\", \"Name\", \"Address\" ) VALUES ( @Id_2, @Name_2, @Address_2 ) ON CONFLICT (\"Id\") DO UPDATE SET \"Name\" = @Name_2, \"Address\" = @Address_2 RETURNING @Id AS \"Result\" ;"; // Assert Assert.AreEqual(expected, query); }
public void TestBaseStatementBuilderCreateExistsWithWhereExpression() { // Setup var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>(); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var where = new QueryGroup(new QueryField("Id", 1)); // Act var actual = statementBuilder.CreateExists(queryBuilder: queryBuilder, tableName: tableName, where : where); var expected = $"" + $"SELECT TOP (1) 1 AS [ExistsValue] " + $"FROM [Table] " + $"WHERE ([Id] = @Id) ;"; // Assert Assert.AreEqual(expected, actual); }
private void Setup(IDbSetting settings) { if (_isInitialized) { return; } lock (_mutex) { DbSettingMapper.Add(typeof(SQLiteConnection), settings, true); // Map the DbHelper DbHelperMapper.Add(typeof(SQLiteConnection), new SqLiteDbHelper(settings, new SdsSqLiteDbTypeNameToClientTypeResolver()), true); // Map the Statement Builder StatementBuilderMapper.Add(typeof(SQLiteConnection), new SqLiteStatementBuilder(settings), true); // Set the flag _isInitialized = true; } }
public void TestDbHelperForQueryAll() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.QueryAll <DataEntityForDbHelper>(); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <string>(s => s == connection.ConnectionString), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>())), Times.Exactly(1)); }
public void TestSqlServerStatementBuilderCreateCountWithWhereExpression() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var where = new QueryGroup(new QueryField("Id", 1)); // Act var actual = statementBuilder.CreateCount(queryBuilder: queryBuilder, tableName: tableName, where : where); var expected = $"" + $"SELECT COUNT_BIG (*) AS [CountValue] " + $"FROM [Table] " + $"WHERE ([Id] = @Id) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateQueryAllWithOrderBy() { // Setup var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>(); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var orderBy = OrderField.Parse(new { Field1 = Order.Ascending, Field2 = Order.Descending }); // Act var actual = statementBuilder.CreateQueryAll(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 TestBaseStatementBuilderCreateCountWithWhereExpressionAndWithHints() { // Setup var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>(); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var where = new QueryGroup(new QueryField("Id", 1)); var hints = "WITH (NOLOCK)"; // Act var actual = statementBuilder.CreateCount(queryBuilder: queryBuilder, tableName: tableName, where : where, hints: hints); var expected = $"" + $"SELECT COUNT (*) AS [CountValue] " + $"FROM [Table] WITH (NOLOCK) " + $"WHERE ([Id] = @Id) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateQueryWithWhereExpression() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var where = new QueryGroup(new QueryField("Id", 1)); // Act var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder, tableName: tableName, fields: fields, where : where); var expected = $"" + $"SELECT [Field1], [Field2], [Field3] " + $"FROM [Table] " + $"WHERE ([Id] = @Id) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateInsertWithUnquotedTableSchema() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection)); var queryBuilder = new QueryBuilder(); var tableName = "dbo.Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); // Act var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder, tableName: tableName, fields: fields, primaryField: null, identityField: null); var expected = $"" + $"INSERT INTO [dbo].[Table] " + $"( [Field1], [Field2], [Field3] ) " + $"VALUES " + $"( @Field1, @Field2, @Field3 ) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateSumWithWhereExpression() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var field = new Field("Value"); var where = new QueryGroup(new QueryField("Id", 1)); // Act var actual = statementBuilder.CreateSum(queryBuilder: queryBuilder, tableName: tableName, field: field, where : where); var expected = $"" + $"SELECT SUM ([Value]) AS [SumValue] " + $"FROM [Table] " + $"WHERE ([Id] = @Id) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestDbHelperForInsert() { // Prepare var dbHelper = new Mock <IDbHelper>(); var connection = new CustomDbConnectionForDbHelper(); // Act CommandTextCache.Flush(); DbFieldCache.Flush(); StatementBuilderMapper.Add(typeof(CustomDbConnectionForDbHelper), new SqlServerStatementBuilder(), true); DbHelperMapper.Add(typeof(CustomDbConnectionForDbHelper), dbHelper.Object, true); // Act connection.Insert <DataEntityForDbHelper>(new DataEntityForDbHelper { Id = 1, Name = "Name" }); // Assert dbHelper.Verify(builder => builder.GetFields( It.Is <IDbConnection>(s => s == connection), It.Is <string>(s => s == ClassMappedNameCache.Get <DataEntityForDbHelper>()), It.IsAny <IDbTransaction>()), Times.Exactly(1)); }
public void TestBaseStatementBuilderCreateInsertWithPrimary() { // Setup var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>(); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var primaryField = new DbField("Field1", true, false, false, typeof(int), null, null, null, null); // 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 ) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestSqlServerStatementBuilderCreateInsertAllWithIdentityForThreeBatches() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var identityField = new DbField("Field1", false, true, false, typeof(int), null, null, null, null); // 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 ) ; " + $"SELECT CONVERT(INT, SCOPE_IDENTITY()) ; " + $"INSERT INTO [Table] " + $"( [Field2], [Field3] ) " + $"VALUES " + $"( @Field2_1, @Field3_1 ) ; " + $"SELECT CONVERT(INT, SCOPE_IDENTITY()) ; " + $"INSERT INTO [Table] " + $"( [Field2], [Field3] ) " + $"VALUES " + $"( @Field2_2, @Field3_2 ) ; " + $"SELECT CONVERT(INT, SCOPE_IDENTITY()) ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateUpdateWithUnquotedTableSchema() { // Setup var statementBuilder = StatementBuilderMapper.Get <BaseStatementBuilderDbConnection>(); var queryBuilder = new QueryBuilder(); var tableName = "dbo.Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var where = (QueryGroup)null; // Act var actual = statementBuilder.CreateUpdate(queryBuilder: queryBuilder, tableName: tableName, fields: fields, where : where, primaryField: null, identityField: null); var expected = $"" + $"UPDATE [dbo].[Table] " + $"SET [Field1] = @Field1, [Field2] = @Field2, [Field3] = @Field3 ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestSqlServerStatementBuilderCreateInsert() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(SqlConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); // Act var actual = statementBuilder.CreateInsert(queryBuilder: queryBuilder, tableName: tableName, fields: fields, primaryField: null, identityField: null); var expected = $"" + $"INSERT INTO [Table] " + $"( [Field1], [Field2], [Field3] ) " + $"VALUES " + $"( @Field1, @Field2, @Field3 ) ; " + $"SELECT NULL AS [Result] ;"; // Assert Assert.AreEqual(expected, actual); }
public void TestBaseStatementBuilderCreateQueryAllWithOrderByAndWithHints() { // Setup var statementBuilder = StatementBuilderMapper.Get(typeof(BaseStatementBuilderDbConnection)); var queryBuilder = new QueryBuilder(); var tableName = "Table"; var fields = Field.From(new[] { "Field1", "Field2", "Field3" }); var orderBy = OrderField.Parse(new { Field1 = Order.Ascending, Field2 = Order.Descending }); var hints = SqlServerTableHints.NoLock; // Act var actual = statementBuilder.CreateQueryAll(queryBuilder: queryBuilder, tableName: tableName, fields: fields, orderBy: orderBy, hints: hints); var expected = $"" + $"SELECT [Field1], [Field2], [Field3] " + $"FROM [Table] WITH (NOLOCK) " + $"ORDER BY [Field1] ASC, [Field2] DESC ;"; // Assert Assert.AreEqual(expected, actual); }
public void Initialize() { DbSettingMapper.Add(typeof(CacheDbConnection), new CustomDbSetting(), true); StatementBuilderMapper.Add(typeof(CacheDbConnection), new CustomStatementBuilder(), true); }
public void Initialize() { DbSettingMapper.Add <CacheDbConnection>(new CustomDbSetting(), true); DbHelperMapper.Add <CacheDbConnection>(new CustomDbHelper(), true); StatementBuilderMapper.Add <CacheDbConnection>(new CustomStatementBuilder(), true); }
public void Initialize() { StatementBuilderMapper.Add <BaseStatementBuilderDbConnection>(new CustomBaseStatementBuilder(), true); StatementBuilderMapper.Add <NonHintsSupportingBaseStatementBuilderDbConnection>(new CustomNonHintsSupportingBaseStatementBuilder(), true); }
public void Initialize() { StatementBuilderMapper.Add(typeof(BaseStatementBuilderDbConnection), new CustomBaseStatementBuilder(), true); }
public void Initialize() { DbSettingMapper.Add(typeof(PropertyHandlerConnection), new CustomDbSetting(), true); DbHelperMapper.Add(typeof(PropertyHandlerConnection), new CustomDbHelper(), true); StatementBuilderMapper.Add(typeof(PropertyHandlerConnection), new CustomStatementBuilder(), true); }
public void Cleanup() { StatementBuilderMapper.Clear(); }
public void Initialize() { StatementBuilderMapper.Add <BaseStatementBuilderDbConnection>(new CustomBaseStatementBuilder(), true); }
public void Initialize() { StatementBuilderMapper.Add(typeof(BaseStatementBuilderDbConnection), new CustomBaseStatementBuilder(), true); StatementBuilderMapper.Add(typeof(DefinedBaseStatementBuilderDbConnection), new CustomDefinedBaseStatementBuilder(), true); StatementBuilderMapper.Add(typeof(NonHintsSupportingBaseStatementBuilderDbConnection), new CustomNonHintsSupportingBaseStatementBuilder(), true); }
static Helper() { StatementBuilder = StatementBuilderMapper.Get <SqlConnection>(); EpocDate = new DateTime(1970, 1, 1, 0, 0, 0); }
public static void ClassInitialize(TestContext context) { DbSettingMapper.Add(typeof(TraceDbConnection), new CustomDbSetting(), true); DbHelperMapper.Add(typeof(TraceDbConnection), new CustomDbHelper(), true); StatementBuilderMapper.Add(typeof(TraceDbConnection), new CustomStatementBuilder(), true); }