public void CSV_DB_WithBatchChanges() { TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); stagingTable.CreateTable(); CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv"); DBDestination <string[]> dest = new DBDestination <string[]>(batchSize: 2) { DestinationTableDefinition = stagingTable, BeforeBatchWrite = rowArray => { rowArray[0][0] = "NewValue"; return(rowArray); } }; source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging where Col1 Like '%ValueRow%' and Col2 <> 1")); Assert.AreEqual(2, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging where Col1 = 'NewValue'")); }
public void DB_DB() { TableDefinition sourceTableDefinition = new TableDefinition("test.Source", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); sourceTableDefinition.CreateTable(); SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)"); SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)"); SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)"); TableDefinition destinationTableDefinition = new TableDefinition("test.Destination", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); destinationTableDefinition.CreateTable(); DBSource <MySimpleRow> source = new DBSource <MySimpleRow>() { SourceTableDefinition = sourceTableDefinition }; DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>() { DestinationTableDefinition = destinationTableDefinition }; source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); }
public void TestCleanUpSchema() { string schemaName = "s" + TestHelper.RandomString(9); SqlTask.ExecuteNonQuery("Create schema", $"create schema {schemaName}"); SqlTask.ExecuteNonQuery("Create schema", $"create table {schemaName}.Table1 ( Nothing int null )"); SqlTask.ExecuteNonQuery("Create schema", $"create view {schemaName}.View1 as select * from {schemaName}.Table1"); SqlTask.ExecuteNonQuery("Create schema", $"create procedure {schemaName}.Proc1 as select * from {schemaName}.Table1"); var objCountSql = new SqlTask("Count object", $@"select count(*) from sys.objects obj inner join sys.schemas sch on sch.schema_id = obj.schema_id where sch.name = '{schemaName}'"); Assert.AreEqual(3, objCountSql.ExecuteScalar <int>()); CleanUpSchemaTask.CleanUp(schemaName); Assert.AreEqual(0, objCountSql.ExecuteScalar <int>()); }
public void DB_RowTrans_DB() { TableDefinition sourceTableDefinition = CreateDBSourceTableForSimpleRow(); TableDefinition destinationTableDefinition = CreateDBDestinationTableForSimpleRow(); DBSource <MySimpleRow> source = new DBSource <MySimpleRow>() { SourceTableDefinition = sourceTableDefinition }; RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>(myRow => { myRow.Value2 += 1; return(myRow); }); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>() { DestinationTableDefinition = destinationTableDefinition }; source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); Assert.AreEqual(9, SqlTask.ExecuteScalar <int>("Check destination table", "select sum(Col2) from test.Destination")); }
public void CSV_RowTrans_DB() { TableDefinition destinationTableDefinition = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); destinationTableDefinition.CreateTable(); CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv"); RowTransformation <string[], MySimpleRow> trans = new RowTransformation <string[], MySimpleRow>( csvdata => { return(new MySimpleRow() { Value1 = csvdata[0], Value2 = int.Parse(csvdata[1]) }); }); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>() { DestinationTableDefinition = destinationTableDefinition }; source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check staging table", "select count(*) from test.Staging")); }
public void DB_RowTrans_DB_WithInitAction() { TableDefinition sourceTableDefinition = CreateDBSourceTableForSimpleRow(); TableDefinition destinationTableDefinition = CreateDBDestinationTableForSimpleRow(); RowTransformationTestClass testClass = new RowTransformationTestClass(); DBSource <MySimpleRow> source = new DBSource <MySimpleRow>() { SourceTableDefinition = sourceTableDefinition }; RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>( "RowTransformation testing init Action", testClass.TestTransformationFunc, testClass.SetAddValue ); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>() { DestinationTableDefinition = destinationTableDefinition }; source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); Assert.AreEqual(9, SqlTask.ExecuteScalar <int>("Check destination table", "select sum(Col2) from test.Destination")); }
public void CSV_DB_WithKey(int keyPosition) { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }; columns.Insert(keyPosition, new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true) { IsIdentity = true }); TableDefinition stagingTable = new TableDefinition($"test.Staging{keyPosition}", columns); stagingTable.CreateTable(); CSVSource source = new CSVSource("src/DataFlow/Simple_CSV2DB.csv"); DBDestination <string[]> dest = new DBDestination <string[]>() { DestinationTableDefinition = stagingTable }; source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging{keyPosition} where Col1 Like '%ValueRow%' and Col2 <> 1")); }
public void UsingRowTransformation() { Prepare(); var orderSource = new MemorySource <Order>(); orderSource.DataAsList.Add(new Order() { OrderNumber = 815, CustomerName = "John" }); orderSource.DataAsList.Add(new Order() { OrderNumber = 4711, CustomerName = "Jim" }); var rowTrans = new RowTransformation <Order>( row => { int?id = SqlTask.ExecuteScalar <int>(SqlConnection, sql: $"SELECT Id FROM CustomerTable WHERE Name='{row.CustomerName}'"); row.CustomerId = id; return(row); }); /* Delete below here */ var dest = new MemoryDestination <Order>(); orderSource.LinkTo(rowTrans).LinkTo(dest); orderSource.Execute(); }
public void TestAddDefaultFileGroup() { string fgName = TestHelper.RandomString(10) + "_FG"; Assert.AreEqual(0, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.filegroups where name = '{fgName}' and is_default = 1")); AddFileGroupTask.AddFileGroup(fgName, DBNameParameter, "5MB", "5MB", true); Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.filegroups where name = '{fgName}' and is_default = 1")); Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("FileGroup", $"select count(*) from sys.sysfiles where name = '{fgName}'")); }
public void TestSimpleDataflow() { CreateTableTask.Create("test.Staging", new List <TableColumn>() { keyCol, col1, col2, col3 }); DataFlowTask.Execute("Test dataflow task", "DataFlow/InputData.csv", "test.Staging", 3, RowTransformation, BatchTransformation); Assert.AreEqual(4, SqlTask.ExecuteScalar <int>("Check staging table", "select count(*) from test.Staging")); }
public void TestExecuteNonQuery() { string propName = TestHelper.RandomString(10); SqlTask.ExecuteNonQuery("Test add extended property", $"exec sp_addextendedproperty @name = N'{propName}', @value = 'Test';"); string asisCollation = SqlTask.ExecuteScalar("Get reference result", $"select value from fn_listextendedproperty('{propName}', default, default, default, default, default, default)").ToString(); Assert.AreEqual("Test", asisCollation); SqlTask.ExecuteNonQuery("Drop extended property", $"exec sp_dropextendedproperty @name = N'{propName}'"); }
public void WaitForLongRunningProcedure() { //Arrange CreateProcedureTask.CreateOrAlter(SqlConnection, new ProcedureDefinition() { Name = "longrunning", Definition = @" WAITFOR DELAY '00:00:01'; SELECT @input + 10;", Parameter = new List <ProcedureParameter>() { new ProcedureParameter() { Name = "input", DataType = "INT" } // new ProcedureParameter() { Name ="result", Out = true, DataType="INT"} } }); var source = new MemorySource <MySimpleRow>(); source.DataAsList.Add(new MySimpleRow() { Col1 = 1, Col2 = "Test1" }); source.DataAsList.Add(new MySimpleRow() { Col1 = 2, Col2 = "Test2" }); source.DataAsList.Add(new MySimpleRow() { Col1 = 3, Col2 = "Test3" }); var dest = new MemoryDestination <MySimpleRow>(); var trans = new RowTransformation <MySimpleRow>( row => { row.Col1 = SqlTask.ExecuteScalar <int>(SqlConnection, "Read from procedure", $"EXEC longrunning @input = {row.Col1}") ?? 0; return(row); } ); //Act source.LinkTo(trans); trans.LinkTo(dest); source.Execute(); dest.Wait(); //Assert Assert.Collection <MySimpleRow>(dest.Data, row => Assert.Equal(11, row.Col1), row => Assert.Equal(12, row.Col1), row => Assert.Equal(13, row.Col1) ); }
public void ExecuteScalar(IConnectionManager connection) { //Arrange //Act object result = SqlTask.ExecuteScalar(connection, "Test execute scalar", $@"SELECT 'Hallo Welt' AS ScalarResult"); //Assert Assert.Equal("Hallo Welt", result.ToString()); }
public void TestExecuteNonQueryWithParameter() { string propName = TestHelper.RandomString(10); var parameter = new List <QueryParameter> { new QueryParameter("propName", "nvarchar(100)", propName) }; SqlTask.ExecuteNonQuery("Test add extended property", $"exec sp_addextendedproperty @name = @propName, @value = 'Test';", parameter); string asisCollation = SqlTask.ExecuteScalar("Get reference result", $"select value from fn_listextendedproperty(@propName, default, default, default, default, default, default)", parameter).ToString(); Assert.AreEqual("Test", asisCollation); SqlTask.ExecuteNonQuery("Drop extended property", $"exec sp_dropextendedproperty @name = N'{propName}'"); }
public void CustSource_DB() { TableDefinition destinationTableDefinition = CreateDestinationTable("test.Destination"); CustomRowReader rowReaderClass = new CustomRowReader(); CustomSource <MySimpleRow> source = new CustomSource <MySimpleRow>(rowReaderClass.ReadData, rowReaderClass.EndOfData); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>(destinationTableDefinition); source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); }
public void TestCreatProcedureWithParameter() { List <ProcedureParameter> pars = new List <ProcedureParameter>() { new ProcedureParameter("Par1", "varchar(10)"), new ProcedureParameter("Par2", "int", "7"), }; CRUDProcedureTask.CreateOrAlter("test.Test2", "select 1 as Test", pars); Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if proc exists", "select count(*) from sys.objects where type = 'P' and object_id = object_id('test.Test2')")); Assert.AreEqual(SqlTask.ExecuteScalar <int>("Check if parameter exists" , "select count(*) from sys.parameters where object_id = object_id('test.Test2')"), 2); }
public void TestCreateTableWithPrimaryKey() { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true), new TableColumn("value2", "datetime", allowNulls: true) }; CreateTableTask.Create("test.Table3", columns); Assert.AreEqual(2, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table3')")); Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if primary key exists", $"select count(*) from sys.key_constraints where parent_object_id = object_id('test.Table3')")); Assert.AreEqual("pk_Table3_Key", SqlTask.ExecuteScalar("Check if primary key has correct naming", "select name from sys.key_constraints where parent_object_id = object_id('test.Table3')")); Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if column is nullable", $"select case when is_nullable = 1 then 1 else 0 end from sys.columns where object_id = object_id('test.Table3') and name='value2'")); }
public void TestCreateTableWithComputedColumn() { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("value1", "int", allowNulls: false), new TableColumn("value2", "int", allowNulls: false), new TableColumn("compValue", "bigint", allowNulls: true) { ComputedColumn = "value1 * value2" } }; CreateTableTask.Create("test.Table7", columns); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table7')")); }
public void TestCreateTableOnlyNVarChars() { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("value1", "int", allowNulls: false), new TableColumn("value2", "datetime", allowNulls: true) }; new CreateTableTask("test.Table4", columns.Cast <ITableColumn>().ToList()) { OnlyNVarCharColumns = true }.Execute(); Assert.AreEqual(2, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table4')")); Assert.AreEqual(2, SqlTask.ExecuteScalar <int>("Check if columns are nvarchar", $@"select count(*) from sys.columns cols inner join sys.types t on t.system_type_id = cols.system_type_id where object_id = object_id('test.Table4') and t.name = 'nvarchar'")); }
public void TestCreateTableWithIdentity() { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("value1", "int", allowNulls: false) { IsIdentity = true, IdentityIncrement = 1000, IdentitySeed = 50 } }; CreateTableTask.Create("test.Table5", columns); Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table5')")); Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if column has identity" , $@"select case when is_identity = 1 then 1 else 0 end from sys.columns cols inner join sys.types t on t.system_type_id = cols.system_type_id where object_id = object_id('test.Table5') and cols.name = 'value1'")); }
public void Sql_Tablename() { TableDefinition destinationTableDefinition = new TableDefinition("test.Destination", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); destinationTableDefinition.CreateTable(); DBSource <MySimpleRow> source = new DBSource <MySimpleRow>( $@"select * from (values ('Test1',1), ('Test2',2), ('Test',3)) AS MyTable(Col1,Col2)"); DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("test.Destination"); source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); }
public void BigData_CSV_DB(int numberOfRows) { Stopwatch watch = new Stopwatch(); TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "nchar(1000)", allowNulls: false), new TableColumn("Col2", "nchar(1000)", allowNulls: false), new TableColumn("Col3", "nchar(1000)", allowNulls: false), new TableColumn("Col4", "nchar(1000)", allowNulls: false), }); stagingTable.CreateTable(); string fileName = "src/DataFlow/BigData_CSV2DB.csv"; BigDataHelper bigData = new BigDataHelper() { FileName = fileName, NumberOfRows = numberOfRows, TableDefinition = stagingTable }; watch.Start(); LogTask.Info($"Create .csv file {fileName} with {numberOfRows} Rows"); bigData.CreateBigDataCSV(); LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to create .csv file"); watch.Reset(); CSVSource source = new CSVSource(fileName); DBDestination <string[]> dest = new DBDestination <string[]>(1000) { DestinationTableDefinition = stagingTable }; source.LinkTo(dest); watch.Start(); source.Execute(); LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to read everything into memory (while constantly writing)"); LogTask.Info($"Already {RowCountTask.Count("test.Staging", RowCountOptions.QuickQueryMode)} inserted into table"); dest.Wait(); //TODO Wait should be part of source LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to write everything into database"); Assert.AreEqual(numberOfRows, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging")); }
public void DB_CustDest() { TableDefinition sourceTableDefinition = CreateSourceTable("test.Source"); TableDefinition destinationTableDefinition = CreateDestinationTable("test.Destination"); DBSource <MySimpleRow> source = new DBSource <MySimpleRow>() { SourceTableDefinition = sourceTableDefinition }; CustomDestination <MySimpleRow> dest = new CustomDestination <MySimpleRow>( row => { SqlTask.ExecuteNonQuery("Insert row", $"insert into test.Destination values('{row.Value1}',{row.Value2})"); } ); source.LinkTo(dest); source.Execute(); dest.Wait(); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check destination table", "select count(*) from test.Destination")); }
public void TestCreateTableWithDefault() { List <TableColumn> columns = new List <TableColumn>() { new TableColumn("value1", "int", allowNulls: false) { DefaultValue = "0" }, new TableColumn("value2", "nvarchar(10)", allowNulls: false) { DefaultValue = "Test" }, new TableColumn("value3", "decimal", allowNulls: false) { DefaultConstraintName = "TestConstraint", DefaultValue = "3.12" } }; CreateTableTask.Create("test.Table6", columns); Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check if column exists", $"select count(*) from sys.columns where object_id = object_id('test.Table6')")); }
public void CSV_DB() { TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "nvarchar(100)", allowNulls: false), new TableColumn("Col2", "int", allowNulls: true) }); stagingTable.CreateTable(); CSVSource source = new CSVSource("DataFlow/Simple_CSV2DB.csv"); DBDestination <string[]> dest = new DBDestination <string[]>() { DestinationTableDefinition = stagingTable }; source.LinkTo(dest); source.Execute(); dest.Wait(); //TODO Wait should be part of source Assert.AreEqual(3, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging where Col1 Like '%ValueRow%' and Col2 <> 1")); }
public void BigData_CSV_DB(int numberOfRows, bool useGenericCSVSource = false) { Stopwatch watch = new Stopwatch(); TableDefinition stagingTable = new TableDefinition("test.Staging", new List <TableColumn>() { new TableColumn("Col1", "nchar(1000)", allowNulls: false), new TableColumn("Col2", "nchar(1000)", allowNulls: false), new TableColumn("Col3", "nchar(1000)", allowNulls: false), new TableColumn("Col4", "nchar(1000)", allowNulls: false), }); stagingTable.CreateTable(); string fileName = "src/DataFlow/BigData_CSV2DB.csv"; BigDataHelper bigData = new BigDataHelper() { FileName = fileName, NumberOfRows = numberOfRows, TableDefinition = stagingTable }; watch.Start(); LogTask.Info($"Create .csv file {fileName} with {numberOfRows} Rows"); bigData.CreateBigDataCSV(); LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to create .csv file"); watch.Reset(); if (useGenericCSVSource) { StartGenericCSVLoad(watch, stagingTable, fileName); } else { StartDefaultCSVLoad(watch, stagingTable, fileName); } LogTask.Info($"Needed {watch.Elapsed.TotalMinutes} to write everything into database"); Assert.AreEqual(numberOfRows, SqlTask.ExecuteScalar <int>("Check staging table", $"select count(*) from test.Staging")); }
public void ExecuteScalarWithCasting(IConnectionManager connection) { if (connection.GetType() == typeof(SQLiteConnectionManager)) { //Arrange //Act double result = (double)(SqlTask.ExecuteScalar(connection, "Test execute scalar with datatype", $@"SELECT CAST(1.343 AS NUMERIC(4,3)) AS ScalarResult")); //Assert Assert.Equal(1.343, result); } else { //Arrange //Act decimal result = (decimal)(SqlTask.ExecuteScalar(connection, "Test execute scalar with datatype", $@"SELECT CAST(1.343 AS NUMERIC(4,3)) AS ScalarResult")); //Assert Assert.Equal(1.343m, result); } }
public void ExecuteScalarWithCasting(IConnectionManager connection) { if (connection.GetType() == typeof(SQLiteConnectionManager)) { //Arrange //Act double result = (double)(SqlTask.ExecuteScalar(connection, "Test execute scalar with datatype", $@"SELECT CAST(1.343 AS NUMERIC(4,3)) AS ScalarResult")); //Assert Assert.Equal(1.343, result); } else { //Arrange //Act DateTime result = (DateTime)(SqlTask.ExecuteScalar(connection, "Test execute scalar with datatype", $@"SELECT CAST('2020-02-29' AS DATE) AS ScalarResult")); //Assert Assert.Equal(DateTime.Parse("2020-02-29"), result); } }
public void TestExecuteScalar() { object result = SqlTask.ExecuteScalar("Test execute scalar", "select cast('Hallo Welt' as nvarchar(100)) as ScalarResult"); Assert.AreEqual(result.ToString(), "Hallo Welt"); }
public void TestExecuteScalarDatatype() { decimal result = (decimal)(SqlTask.ExecuteScalar("Test execute scalar with datatype", "select cast(1.343 as numeric(4,3)) as ScalarResult")); Assert.AreEqual(result, 1.343m); }