public void WriteRecordNoHeaderTest()
{
var cont = new PipelineContext()
{
FirstLineContainsHeaders = false,
SourceFilePath = @"..\..\..\D2S.LibraryTests\TestDataFiveColumns.txt",
Delimiter = "||",
TotalObjectsInSequentialPipe = 10,
DestinationTableName = "dbo.SimpleSqlTableLoaderTest"
};
DestinationTableCreator table = new DestinationTableCreator(cont);
table.CreateTable();
SimpleSqlTableLoader loader = new SimpleSqlTableLoader(cont);
for (int i = 0; i < 5; i++)
{
var row = loader.GetEmptyRow();
for (int j = 0; j < cont.ColumnNamesSelection.Count(); j++)
{
row[j] = $"col{j}";
}
loader.PostRecord(row);
}
try
{
loader.WriteRecords();
}
finally
{
int rowCount = DropTableAndReturnRows(cont.DestinationTableName);
Assert.IsTrue(rowCount == 5);
}
}
public void InserterTest()
{
PipeLineContext c = new PipeLineContext();
c.PathToSourceFile = @"C:\Users\C51188\Documents\CSAHC_Tradedetails_20180228\blabla.txt";
c.ConnectionString = @"Data Source=NLGSPIDCS34019\S0QRMSN;Initial Catalog=QRM_TDM_DMT_LIQ_DEV_DWH;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
c.FieldDelimiter = '|';
c.DestinationTableName = "MurexTradeDetails";
c.IsSuggestingDataTypes = false;
c.LinesToScan = 2000;
ConcurrentQueue <List <string> > RowsCollection = new ConcurrentQueue <List <string> >();
SourceFileReader Reader = new SourceFileReader(c, RowsCollection);
Inserter Writer = new Inserter(c, RowsCollection);
DestinationTableCreator TableMaker = new DestinationTableCreator(c);
Reader.ReadFinished += Writer.StopWriting;
Writer.FinishedWriting += IsDone;
TableMaker.CreateTable();
Reader.StartReading();
Writer.CreateConcurrentWriter();
Writer.CreateConcurrentWriter();
while (!Done)
{
Thread.Sleep(2000);
}
}
public void WriteDbNullValuesTest()
{
var cont = new PipelineContext()
{
FirstLineContainsHeaders = false,
SourceFilePath = @"..\..\..\D2S.LibraryTests\TestDataFiveColumns.txt",
Delimiter = "||",
TotalObjectsInSequentialPipe = 10,
DestinationTableName = "dbo.WriteDbNullValuesTest",
DbNullStringValue = "nullerino"
};
DestinationTableCreator table = new DestinationTableCreator(cont);
table.CreateTable();
SimpleSqlTableLoader loader = new SimpleSqlTableLoader(cont);
var row = loader.GetEmptyRow();
for (int j = 0; j < cont.ColumnNamesSelection.Count(); j++)
{
row[j] = "nullerino";
}
loader.PostRecord(row);
try
{
loader.WriteRecords();
}
finally
{
var val = ReadScalar(1, 1, "dbo.WriteDbNullValuesTest");
Assert.AreEqual(expected: DBNull.Value, actual: val);
DropTableAndReturnRows("dbo.WriteDbNullValuesTest");
}
}
public void CreateTableTest()
{
PipeLineContext c = new PipeLineContext();
c.PathToSourceFile = @"C:\Users\C51188\Documents\Axiom ultimo Jan 2018.txt";
c.ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Liquidity;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
c.FieldDelimiter = '\t';
DestinationTableCreator creator = new DestinationTableCreator(c);
creator.CreateTable();
}
public void CreateTableWithContextTest()
{
/* result should look like this without spaces:
* string 167 6
* integer 56 4
* decimal 106 5
* char 175 1
*/
DestinationTableCreator creater = new DestinationTableCreator(pipelineContext);
try
{
creater.CreateTable();
string[] nameTypeDataType = new string[4];
using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
{
con.Open();
string commtext = "select name, CONVERT(nvarchar,system_type_id),CONVERT(nvarchar, max_length) from sys.columns where object_id = (select object_id from sys.tables where name = 'CreationTest')";
using (SqlCommand comm = new SqlCommand(commtext, con))
{
SqlDataReader result = comm.ExecuteReader();
for (int i = 0; i < 4; i++)
{
result.Read();
nameTypeDataType[i] = result.GetString(0) + result.GetString(1) + result.GetString(2);
}
}
}
Assert.IsTrue(nameTypeDataType[0] == "string23112");
Assert.IsTrue(nameTypeDataType[1] == "integer564");
Assert.IsTrue(nameTypeDataType[2] == "decimal10617");
Assert.IsTrue(nameTypeDataType[3] == "char1751");
}
finally
{
using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
{
con.Open();
string commtext = "drop table dbo.CreationTest";
using (SqlCommand comm = new SqlCommand(commtext, con))
{
comm.ExecuteNonQuery();
}
}
}
}
public void TestInit()
{
DestinationTableCreator d = new DestinationTableCreator(pipelineContext);
d.CreateTable();
using (SqlConnection con = new SqlConnection(ConfigVariables.Instance.ConfiguredConnection))
{
using (SqlCommand com = new SqlCommand())
{
com.Connection = con;
con.Open();
com.CommandText = "insert into dbo.TruncatorTest ([string], [integer], [decimal], [char]) values ('s', 1, 1, 's')";
com.ExecuteNonQuery();
}
}
}
private static void CreateOrRecreateTargetTable(DataColumnCollection header, string connectionString, string schemaAndTable)
{
//drop (if exists) and recreate
string[] cols = new string[header.Count];
string[] datatypes = new string[header.Count];
for (int i = 0; i < header.Count; i++)
{
cols[i] = header[i].ColumnName;
datatypes[i] = DataTypeMapping.SystemToSql[header[i].DataType];
}
DestinationTableCreator dtc = new DestinationTableCreator(schemaAndTable, cols, datatypes);
//using d2s class for this
ConfigVariables.Instance.ConfiguredConnection = connectionString; //this is where the d2s class get their connstring from
DestinationTableDropper dtd = new DestinationTableDropper(schemaAndTable);
dtd.DropTable();
dtc.CreateTable();
}
#pragma warning disable S3241 // Methods should not return values that are never used
private async Task StartFileTransfer()
#pragma warning restore S3241 // Methods should not return values that are never used
{
//build staging table
if (!Context.IsAppendingDataToExistingTable)
{
DestinationTableCreator TableMaker = new DestinationTableCreator(Context);
TableMaker.CreateTable();
}
//create buffer
ConcurrentQueue <List <string> > Queu = new ConcurrentQueue <List <string> >();
//create threads to transfer file
SourceFileReader Reader = new SourceFileReader(Context, Queu);
Inserter Writer = new Inserter(Context, Queu);
Writer.done = false;
Reader.ReadFinished += Writer.StopWriting;
Writer.FinishedWriting += OnWriterFinishing;
// start everything up and monitor for finish
Reader.StartReading();
for (int i = 0; i < NumberOfWriterThreads; i++)
{
Writer.CreateConcurrentWriter();
}
await Task.Run(() =>
{
while (NumberOfWriterThreads > NumberOfFinishedThreads)
{
Task.Delay(1000).Wait();
}
});
btnStartTransfer.Enabled = true;
OnLoadStageOneFinished();
}
public void WriteRecordsTest()
{
DestinationTableCreator destinationTableCreator = new DestinationTableCreator(context);
destinationTableCreator.CreateTable();
SimpleSqlTableLoader loader = new SimpleSqlTableLoader(context);
int numberOfRows = 100;
for (int i = 0; i < numberOfRows; i++)
{
var row = loader.GetEmptyRow();
foreach (var column in context.ColumnNames)
{
row[column] = $"Value{i}";
}
loader.PostRecord(row);
}
loader.WriteRecords();
int rowCount = DropTableAndReturnRows(context.DestinationTableName);
Assert.AreEqual(expected: numberOfRows, actual: rowCount);
}
public static void ExportToSqlTable(this DataTable source, string connectionString, string schemaAndTable, bool AppendIfTableExists = false)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
//does table exist?
bool tableExists = DoesTableExist(schemaAndTable, conn);
//error if append is not allowed and table exists
if (tableExists && !AppendIfTableExists)
{
Console.Error.WriteLine($"{schemaAndTable} already exists and appending is set to false");
return;
}
if (!tableExists)
{
//create table if it doesn't exist
string[] cols = new string[source.Columns.Count];
string[] datatypes = new string[source.Columns.Count];
for (int i = 0; i < source.Columns.Count; i++)
{
cols[i] = source.Columns[i].ColumnName;
datatypes[i] = DataTypeMapping.SystemToSql[source.Columns[i].DataType];
}
DestinationTableCreator dtc = new DestinationTableCreator(schemaAndTable, cols, datatypes);
//using d2s class for this
ConfigVariables.Instance.ConfiguredConnection = connectionString; //this is where the d2s class get their connstring from
dtc.CreateTable();
}
//fill table
using (SqlBulkCopy bulk = new SqlBulkCopy(conn))
{
bulk.BatchSize = 10000;
bulk.DestinationTableName = schemaAndTable;
bulk.WriteToServer(source);
}
}
}
/// <summary>
/// creates a table based on the given context
/// </summary>
/// <param name="context"></param>
protected void CreateTable(PipelineContext context)
{
var tableMaker = new DestinationTableCreator(context);
tableMaker.CreateTable();
}
public void TestInit()
{
DestinationTableCreator d = new DestinationTableCreator(pipelineContext);
d.CreateTable();
}
