public override void BulkInsert(ITableData data, string tableName)
{
var TN = new TableNameDescriptor(tableName, ConnectionManagerType.Postgres);
var sourceColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.SourceColumn).ToList();
var destColumnNames = data.ColumnMapping.Cast <IColumnMapping>().Select(cm => cm.DataSetColumn).ToList();
var quotedDestColumns = destColumnNames.Select(col => TN.QB + col + TN.QE);
using (var writer = DbConnection.BeginBinaryImport($@"
COPY {TN.QuotatedFullName} ({string.Join(", ", quotedDestColumns)})
FROM STDIN (FORMAT BINARY)"))
{
while (data.Read())
{
writer.StartRow();
foreach (var destCol in destColumnNames)
{
TableColumn colDef = DestinationColumns[destCol];
int ordinal = data.GetOrdinal(destCol);
object val = data.GetValue(ordinal);
if (val != null)
{
object convertedVal = System.Convert.ChangeType(data.GetValue(ordinal), colDef.NETDataType);
writer.Write(convertedVal, colDef.DataType);
}
else
{
writer.WriteNull();
}
}
}
writer.Complete();
}
}
public void MySqlQuotatedTableName()
{
TableNameDescriptor desc = new TableNameDescriptor("`Test`", ConnectionManagerType.MySql);
Assert.Equal(@"", desc.QuotatedSchemaName);
Assert.Equal(@"`Test`", desc.QuotatedTableName);
Assert.Equal(@"`Test`", desc.QuotatedFullName);
}
public void PostgresWithSchema()
{
TableNameDescriptor desc = new TableNameDescriptor("public.Test", ConnectionManagerType.Postgres);
Assert.Equal(@"""public""", desc.QuotatedSchemaName);
Assert.Equal(@"""Test""", desc.QuotatedTableName);
Assert.Equal(@"""public"".""Test""", desc.QuotatedFullName);
}
public void SqlServerNoSchema()
{
TableNameDescriptor desc = new TableNameDescriptor("Test", ConnectionManagerType.SqlServer);
Assert.Equal("", desc.QuotatedSchemaName);
Assert.Equal("[Test]", desc.QuotatedTableName);
Assert.Equal("[Test]", desc.QuotatedFullName);
}
void InsertDestinationData(IConnectionManager connection, TableNameDescriptor TN)
{
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'XXX')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'D','Delete', 'Test4')");
}
void InsertSourceData(IConnectionManager connection, TableNameDescriptor TN)
{
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'I','Insert', 'Test1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'U','Update', 'Test2')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'E','NoChange', 'Test3')");
}
void InsertTestData(IConnectionManager connection, string tableName)
{
TableNameDescriptor TN = new TableNameDescriptor(tableName, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1, 10 ,'Test1')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(2, 10 ,'Test2')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(3, 10 ,'Test3')");
}
void AddFKConstraint(IConnectionManager connection, string sourceTableName, string referenceTableName)
{
TableNameDescriptor TN = new TableNameDescriptor(sourceTableName, connection);
TableNameDescriptor TNR = new TableNameDescriptor(referenceTableName, connection);
SqlTask.ExecuteNonQuery(connection, "Add FK constraint",
$@"ALTER TABLE {TN.QuotatedFullName}
ADD CONSTRAINT constraint_fk
FOREIGN KEY ({TN.QB}Key2{TN.QE})
REFERENCES {TNR.QuotatedFullName}({TNR.QB}Id{TNR.QE})
ON DELETE CASCADE;");
}
void ReCreateTable(IConnectionManager connection, TableNameDescriptor TN)
{
DropTableTask.DropIfExists(connection, TN.FullName);
CreateTableTask.Create(connection, TN.FullName,
new List <TableColumn>()
{
new TableColumn("ColKey1", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("ColKey2", "CHAR(1)", allowNulls: false, isPrimaryKey: true),
new TableColumn("ColValue1", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false),
new TableColumn("ColValue2", "NVARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
}
private void InsertTestData(IConnectionManager connection, string tableName)
{
var TN = new TableNameDescriptor(tableName, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(1,'\0 \"" \b \n \r \t \Z \\ \% \_ ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(2,' '' """" ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(3,' !""§$%&/())='' ')");
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(4,NULL)");
}
void ReCreateOtherTable(IConnectionManager connection, string tablename)
{
DropTableTask.DropIfExists(connection, tablename);
CreateTableTask.Create(connection, tablename,
new List <TableColumn>()
{
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true),
new TableColumn("Other", "VARCHAR(100)", allowNulls: true, isPrimaryKey: false),
});
TableNameDescriptor TN = new TableNameDescriptor(tablename, connection);
SqlTask.ExecuteNonQuery(connection, "Insert demo data"
, $@"INSERT INTO {TN.QuotatedFullName} VALUES(10,'TestX')");
}
public void MergeWithCompositeKey(IConnectionManager connection)
{
//Arrange
TableNameDescriptor TNS = new TableNameDescriptor("DBMergeSource", connection);
TableNameDescriptor TND = new TableNameDescriptor("DBMergeDestination", connection);
ReCreateTable(connection, TNS);
ReCreateTable(connection, TND);
InsertSourceData(connection, TNS);
InsertDestinationData(connection, TND);
//Act
DBSource <MyMergeRow> source = new DBSource <MyMergeRow>(connection, "DBMergeSource");
DBMerge <MyMergeRow> dest = new DBMerge <MyMergeRow>(connection, "DBMergeDestination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDestination"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'E' and {TND.QB}ColValue2{TND.QE} = 'Test3'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'U' and {TND.QB}ColValue2{TND.QE} = 'Test2'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDestination", $"{TND.QB}ColKey2{TND.QE} = 'I' and {TND.QB}ColValue2{TND.QE} = 'Test1'"));
}
