, MemberData(nameof(Access))] //If access fails with "Internal OLE Automation error", download and install: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50040
//see also: https://stackoverflow.com/questions/54632928/internal-ole-automation-error-in-ms-access-using-oledb
public void StringArray(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture destTable = new TwoColumnsTableFixture(connection, "BulkInsert2Columns");
TableData <string[]> data = new TableData <string[]>(
destTable.TableDefinition,
new List <object[]>
{
new [] { "1", "Test1" },
new [] { "2", "Test2" },
new [] { "3", "Test3" }
}
);
//Act
SqlTask.BulkInsert(connection, "Bulk insert demo data", data, "BulkInsert2Columns");
//Assert
destTable.AssertTestData();
}
public void DBDestinationWithColumnMapping()
{
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE test.Destination
(ID int not null identity (1,1) primary key,
Col1 nvarchar(30) null, Col2 nvarchar(30) null)");
int index = 1;
CustomSource <ColumnMapRow> source = new CustomSource <ColumnMapRow>(() => {
return(new ColumnMapRow()
{
Col1 = "Test" + index++,
B = "Test2",
});
}, () => index > 3);
DBDestination <ColumnMapRow> dest = new DBDestination <ColumnMapRow>("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination", "Col2 = 'Test2'"));
}
public void IfTableExists(IConnectionManager connection)
{
//Arrange
if (connection.GetType() != typeof(AccessOdbcConnectionManager) &&
connection.GetType() != typeof(OracleConnectionManager))
{
SqlTask.ExecuteNonQuery(connection, "Drop table if exists"
, $@"DROP TABLE IF EXISTS existtable_test");
}
//Act
var existsBefore = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
SqlTask.ExecuteNonQuery(connection, "Create test data table"
, $@"CREATE TABLE {connection.QB}existtable_test{connection.QE} ( Col1 INT NULL )");
var existsAfter = IfTableOrViewExistsTask.IsExisting(connection, "existtable_test");
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
}
public void DboTableName_DboTablename()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into Source values('Test3',3)");
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE Destination
(Col1 nvarchar(30) null, Col2 bigint null)");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>("Source");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("Destination"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("Destination", "Col1 = 'Test3' AND Col2=3"));
}
private TimeSpan GetBulkInsertTime(IConnectionManager connection, int numberOfRows)
{
TimeSpan result = TimeSpan.FromMilliseconds(0);
if (connection.GetType() == typeof(SqlConnectionManager))
{
result = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with rows of data using BulkInsert",
() =>
{
SqlTask.ExecuteNonQuery(connection, "Insert with BulkInsert",
$@"BULK INSERT [dbo].[CsvDestinationBulkInsert]
FROM '{BigDataCsvSource.GetCompleteFilePath(numberOfRows)}'
WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
");
});
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationBulkInsert"));
output.WriteLine("Elapsed " + result.TotalSeconds + " seconds for bulk insert.");
}
return(result);
}
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 TestLoadProcessKeyIfRestarted()
{
StartLoadProcessTask.Start("Test process 6");
int?processKey1 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.AreEqual(2, new SqlTask("Check if load process key is set", $"select count(*) from etl.Log where Message='Test Task' and LoadProcessKey = {processKey1}")
{
DisableLogging = true
}.ExecuteScalar <int>());
StartLoadProcessTask.Start("Test process 7");
int?processKey2 = ControlFlow.CurrentLoadProcess.LoadProcessKey;
Assert.AreNotEqual(processKey1, processKey2);
SqlTask.ExecuteNonQuery("Test Task", "Select 1 as test");
Assert.AreEqual(2, new SqlTask("Check if load process key is set", $"select count(*) from etl.Log where Message='Test Task' and LoadProcessKey = {processKey2}")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TextTypes()
{
//Arrange
SqlTask.ExecuteNonQuery(SqlConnection, "Create table", @"
CREATE TABLE TextTypes (
Col1 TEXT,
Col2 NTEXT,
Col3 IMAGE
)"
);
//Act
var result = TableDefinition.GetDefinitionFromTableName(SqlConnection, "TextTypes");
//Assert
Assert.Collection(result.Columns,
tc => Assert.True(tc.DataType == "TEXT" && tc.NETDataType == typeof(string)),
tc => Assert.True(tc.DataType == "NTEXT" && tc.NETDataType == typeof(string)),
tc => Assert.True(tc.DataType == "IMAGE" && tc.NETDataType == typeof(string))
);
}
public async Task TaskListTaskShouldReturnAllTasks()
{
serviceHostMock.AddEventHandling(TaskCreatedNotification.Type, null);
serviceHostMock.AddEventHandling(TaskStatusChangedNotification.Type, null);
DatabaseOperationStub operation = new DatabaseOperationStub();
SqlTask sqlTask = service.TaskManager.CreateTask(taskMetaData, operation.FunctionToRun);
sqlTask.Run();
ListTasksParams listParams = new ListTasksParams
{
};
await RunAndVerify <ListTasksResponse>(
test : (requestContext) => service.HandleListTasksRequest(listParams, requestContext),
verify : ((result) =>
{
Assert.True(result.Tasks.Any(x => x.TaskId == sqlTask.TaskId.ToString()));
}));
operation.Stop();
}
public void DBSourceWithStringArray()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 int null)");
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)");
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE test.Destination
(Col1 nvarchar(30) null, Col2 bigint null)");
DBSource source = new DBSource("test.Source");
DBDestination dest = new DBDestination("test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' AND Col2=3"));
}
public void IsControlFlowStageLogged()
{
//Arrange
CreateLogTableTask.Create(SqlConnection, "test_log_stage");
ControlFlow.AddLoggingDatabaseToConfig(SqlConnection, NLog.LogLevel.Debug, "test_log_stage");
//Act
ControlFlow.STAGE = "SETUP";
SqlTask.ExecuteNonQuery(SqlConnection, "Test Task", "Select 1 as test");
//Assert
Assert.Equal(2, new RowCountTask("test_log_stage",
$"message='Test Task' and stage = 'SETUP'")
{
DisableLogging = true,
ConnectionManager = SqlConnection
}.Count().Rows);
//Cleanup
DropTableTask.Drop(SqlConnection, ControlFlow.LogTable);
}
public void DBMergeWithDeltaDestination()
{
CreateSourceTable();
CreateDestinationTable();
SqlTask.ExecuteNonQuery("Create delta table", @"CREATE TABLE test.Delta
(ColKey int not null, ColValue nvarchar(30) null,
ChangeDate datetime null, ChangeAction char(1) not null)");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>("test.Source");
DBMerge <MySimpleRow> merge = new DBMerge <MySimpleRow>("test.Destination");
DBDestination <MySimpleRow> dest = new DBDestination <MySimpleRow>("test.Delta");
source.LinkTo(merge);
merge.LinkTo(dest);
source.Execute();
merge.Wait();
dest.Wait();
//dest.Wait();
}
public void InsertTestData()
{
if (IsSQLiteConnection)
{
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $"INSERT INTO {TN.QuotatedFullName} (Col1, Col2, Col3, Col4) VALUES(NULL, 'Test1', NULL, '1.2')");
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $"INSERT INTO {TN.QuotatedFullName} (Col1, Col2, Col3, Col4) VALUES(NULL, 'Test2', 4711, '1.23')");
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $"INSERT INTO {TN.QuotatedFullName} (Col1, Col2, Col3, Col4) VALUES(NULL, 'Test3', 185, '1.234')");
}
else
{
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} ({QB}Col2{QE}, {QB}Col3{QE}, {QB}Col4{QE}) VALUES('Test1', NULL, '1.2')");
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} ({QB}Col2{QE}, {QB}Col3{QE}, {QB}Col4{QE}) VALUES('Test2', 4711, '1.23')");
SqlTask.ExecuteNonQuery(Connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} ({QB}Col2{QE},{QB}Col3{QE}, {QB}Col4{QE}) VALUES('Test3', 185, '1.234')");
}
}
public void IfDatabaseExists(IConnectionManager connection)
{
if (connection.GetType() == typeof(OracleConnectionManager))
{
return;
}
//Arrange
string dbName = ("ETLBox_" + TestHashHelper.RandomString(10)).ToLower();
var existsBefore = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Act
SqlTask.ExecuteNonQuery(connection, "Create DB", $"CREATE DATABASE {dbName}");
var existsAfter = IfDatabaseExistsTask.IsExisting(connection, dbName);
//Assert
Assert.False(existsBefore);
Assert.True(existsAfter);
//Cleanup
DropDatabaseTask.Drop(connection, dbName);
}
private TableDefinition RecreateAccessTestTable()
{
try
{
SqlTask.ExecuteNonQuery(AccessOdbcConnection, "Try to drop table",
@"DROP TABLE TestTable;");
}
catch { }
TableDefinition testTable = new TableDefinition("TestTable", new List <TableColumn>()
{
new TableColumn("Field1", "NUMBER", allowNulls: true),
new TableColumn("Field2", "CHAR", allowNulls: true)
});
new CreateTableTask(testTable)
{
ThrowErrorIfTableExists = true,
ConnectionManager = AccessOdbcConnection
}.Execute();
return(testTable);
}
public async Task VerifyCancelBackupTask()
{
using (SqlTaskManager manager = new SqlTaskManager())
{
IBackupOperation backupOperation = new BackupOperationStub();
DisasterRecoveryService service = new DisasterRecoveryService();
TaskMetadata taskMetaData = this.CreateTaskMetaData(backupOperation);
SqlTask sqlTask = manager.CreateTask <SqlTask>(taskMetaData);
Assert.NotNull(sqlTask);
Task taskToVerify = sqlTask.RunAsync().ContinueWith(Task =>
{
Assert.Equal(SqlTaskStatus.Canceled, sqlTask.TaskStatus);
Assert.Equal(sqlTask.IsCancelRequested, true);
((BackupOperationStub)backupOperation).BackupSemaphore.Release();
manager.Reset();
});
manager.CancelTask(sqlTask.TaskId);
await taskToVerify;
}
}
/// <summary>
/// Handles a backup request
/// </summary>
internal static async Task HandleBackupRequest(
BackupParams backupParams,
RequestContext <BackupResponse> requestContext)
{
try
{
ConnectionInfo connInfo;
DisasterRecoveryService.ConnectionServiceInstance.TryFindConnection(
backupParams.OwnerUri,
out connInfo);
if (connInfo != null)
{
DatabaseTaskHelper helper = AdminService.CreateDatabaseTaskHelper(connInfo, databaseExists: true);
SqlConnection sqlConn = GetSqlConnection(connInfo);
if ((sqlConn != null) && !connInfo.IsSqlDW && !connInfo.IsAzure)
{
BackupOperation backupOperation = DisasterRecoveryService.Instance.SetBackupInput(helper.DataContainer, sqlConn, backupParams.BackupInfo);
// create task metadata
TaskMetadata metadata = new TaskMetadata();
metadata.ServerName = connInfo.ConnectionDetails.ServerName;
metadata.DatabaseName = connInfo.ConnectionDetails.DatabaseName;
metadata.Name = SR.Backup_TaskName;
metadata.IsCancelable = true;
metadata.Data = backupOperation;
// create backup task and perform
SqlTask sqlTask = SqlTaskManager.Instance.CreateTask(metadata, Instance.BackupTaskAsync);
sqlTask.Run();
}
}
await requestContext.SendResult(new BackupResponse());
}
catch (Exception ex)
{
await requestContext.SendError(ex.ToString());
}
}
private static void RunTestDataCreationTask(IConnectionManager connection)
{
int startNumber = 1;
int days = 1;
Random rnd = new Random();
var t = Task.Run(() => {
while (true)
{
var pastDate = DateTime.Now.AddDays(days);
QueryParameter par1 = new QueryParameter()
{
Name = "number", Value = "OD" + startNumber
};
QueryParameter par2 = new QueryParameter()
{
Name = "details", Value = GetDetailsText(startNumber)
};
QueryParameter par3 = new QueryParameter()
{
Name = "date", Value = pastDate
};
var sql = new SqlTask(
"INSERT INTO orders (Number, Details, Date) VALUES (@number, @details, @date)",
new[] { par1, par2, par3 }
)
{
ConnectionManager = connection,
DisableLogging = true
}
.ExecuteNonQuery();
if (startNumber % 8 == 0)
{
days++;
}
startNumber++;
Task.Delay(rnd.Next(0, 4) * 1000).Wait();
}
});
}
public void Sql_DB()
{
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>()
{
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 ExecuteReaderSingleColumn(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture tc = new TwoColumnsTableFixture(connection, "ExecuteReader");
tc.InsertTestData();
List <int> asIsResult = new List <int>();
List <int> toBeResult = new List <int>()
{
1, 2, 3
};
//Act
SqlTask.ExecuteReader(connection,
"Test execute reader",
$"SELECT {tc.QB}Col1{tc.QE} FROM {tc.QB}ExecuteReader{tc.QE}",
colA => asIsResult.Add(int.Parse(colA.ToString()))
);
//Assert
Assert.Equal(toBeResult, asIsResult);
}
public async Task FailedOperationShouldFailTheTask()
{
SqlTaskStatus expectedStatus = SqlTaskStatus.Failed;
DatabaseOperationStub operation = new DatabaseOperationStub();
operation.TaskResult = new TaskResult
{
};
SqlTask sqlTask = new SqlTask(new TaskMetadata(), operation.FunctionToRun, operation.FunctionToCancel);
Assert.Equal(sqlTask.TaskStatus, SqlTaskStatus.NotStarted);
Task taskToVerify = sqlTask.RunAsync().ContinueWith(task => {
Assert.Equal(sqlTask.TaskStatus, expectedStatus);
Assert.True(sqlTask.Duration > 0);
});
Assert.Equal(sqlTask.TaskStatus, SqlTaskStatus.InProgress);
Thread.Sleep(1000);
operation.FailTheOperation();
await taskToVerify;
}
public async Task <TaskResult> FunctionToRun(SqlTask sqlTask)
{
return(await Task.Factory.StartNew(() =>
{
while (!IsStopped)
{
//Just keep running
if (sqlTask.TaskStatus == SqlTaskStatus.Canceled)
{
break;
}
if (Failed)
{
throw new InvalidOperationException();
}
sqlTask.AddMessage("still running", SqlTaskStatus.InProgress, true);
}
sqlTask.AddMessage("done!", SqlTaskStatus.Succeeded);
return TaskResult;
}));
}
public void DBSourceWithColumnMapping()
{
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 nvarchar(100) null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1','Test2')");
DBSource <ColumnMapRow> source = new DBSource <ColumnMapRow>()
{
TableName = "test.Source"
};
CustomDestination <ColumnMapRow> dest = new CustomDestination <ColumnMapRow>(
input => {
Assert.AreEqual("Test1", input.Col1);
Assert.AreEqual("Test2", input.B);
});
source.LinkTo(dest);
source.Execute();
dest.Wait();
}
public static void Prepare()
{
Console.WriteLine("Starting DataFlow example - preparing database");
TableDefinition OrderDataTableDef = new TableDefinition("orders",
new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Number", "nvarchar(100)", allowNulls: false),
new TableColumn("Item", "nvarchar(200)", allowNulls: false),
new TableColumn("Amount", "money", allowNulls: false),
new TableColumn("CustomerKey", "int", allowNulls: false)
});
TableDefinition CustomerTableDef = new TableDefinition("customer",
new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Name", "nvarchar(200)", allowNulls: false),
});
TableDefinition CustomerRatingTableDef = new TableDefinition("customer_rating",
new List <TableColumn>()
{
new TableColumn("Key", "int", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("CustomerKey", "int", allowNulls: false),
new TableColumn("TotalAmount", "decimal(10,2)", allowNulls: false),
new TableColumn("Rating", "nvarchar(3)", allowNulls: false)
});
//Create demo tables & fill with demo data
OrderDataTableDef.CreateTable();
CustomerTableDef.CreateTable();
CustomerRatingTableDef.CreateTable();
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO customer values('Sandra Kettler')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO customer values('Nick Thiemann')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO customer values('Zoe Rehbein')");
SqlTask.ExecuteNonQuery("Fill customer table", "INSERT INTO customer values('Margit Gries')");
}
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 ControlFlowLoggingInDifferentDB()
{
//Arrange
//Act
ControlFlow.CurrentDbConnection = NoLogConnection;
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE CFLogSource
(Col1 INT NOT NULL, Col2 NVARCHAR(50) NULL)");
SqlTask.ExecuteNonQuery("Insert demo data", "INSERT INTO CFLogSource VALUES(1,'Test1')");
ControlFlow.CurrentDbConnection = LoggingConnection;
SqlTask.ExecuteNonQuery(NoLogConnection, "Insert demo data", "INSERT INTO CFLogSource VALUES(2,'Test2')");
SqlTask.ExecuteNonQuery(NoLogConnection, "Insert demo data", "INSERT INTO CFLogSource VALUES(3,'Test3')");
//Assert
Assert.Equal(4, new RowCountTask("etl.Log", "TaskType = 'SQL' ")
{
DisableLogging = true,
ConnectionManager = LoggingConnection
}.Count().Rows);
}
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 BigIntIdentity()
{
//Arrange
SqlTask.ExecuteNonQuery(SqlConnection, "Create table", @"
CREATE TABLE BigIntIdentity (
Id BIGINT NOT NULL PRIMARY KEY IDENTITY(100,10)
)"
);
//Act
var result = TableDefinition.FromName(SqlConnection, "BigIntIdentity");
//Assert
Assert.Collection(result.Columns,
tc => Assert.True(tc.DataType == "BIGINT" &&
tc.NETDataType == typeof(Int64) &&
tc.IsIdentity == true &&
tc.IdentityIncrement == 10 &&
tc.IdentitySeed == 100
)
);
}
public void DateTimeTypes()
{
//Arrange
SqlTask.ExecuteNonQuery(SqlConnection, "Create table", @"
CREATE TABLE ReadTableDefinition (
Col11 DATE,
--Col12 DATETIMEOFFSET,
Col13 DATETIME2,
Col14 SMALLDATETIME,
Col15 DATETIME,
Col16 TIME
)"
);
//Act
var result = TableDefinition.FromName(SqlConnection, "ReadTableDefinition");
//Assert
Assert.True(result.Columns.All(tc => tc.NETDataType == typeof(DateTime)));
Assert.True(result.Columns.Count == 5);
}
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, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' AND Col2=3"));
}
