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'")); }