public override bool BulkInsert(DbTable schema, MungedDataReader reader) { using (var cn = _getConnection()) { using (SqlBulkCopy copy = new SqlBulkCopy(cn)) { //copy.ColumnMappings = new SqlBulkCopyColumnMappingCollection(); for (var i = 0; i < reader.ColumnNames.Length; i++) { var column = reader.ColumnNames[i]; var sourceOrdinal = i; var destinationOrdinal = schema.Columns.FindIndex(x => x.Name == column); if (destinationOrdinal == -1) { var msg = string.Format("Unable to resolve column mapping, column: {0} was not found in destination table {1}", column, _table ); throw new Exception(msg); } copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(i, destinationOrdinal)); } copy.DestinationTableName = string.Format("[{0}].[{1}]", _schema, _table); copy.BatchSize = 1000; copy.BulkCopyTimeout = 9999999; copy.WriteToServer(reader); } } return true; }
public override bool CreateTable(DbTable tbl) { StringBuilder ddl = new System.Text.StringBuilder(); ddl.Append(string.Format("CREATE TABLE [{0}].[{1}] (\r\n", Schema, tbl.Name)); foreach (var c in tbl.Columns) { if (c.Name == "_Id_" || c.Name == "_At_") { ddl.Append(string.Format("\t[{0}] {1} NOT NULL,\r\n", c.Name, c.Type.SqlType())); } else { ddl.Append(string.Format("\t[{0}] {1} NULL,\r\n", c.Name, c.Type.SqlType())); } } ddl.Append(string.Format("\tCONSTRAINT [PK_{0}] PRIMARY KEY ([_Id_])\r\n", tbl.Name)); ddl.Append(")"); return ExecuteCommand(ddl.ToString()); }
public override bool ModifySchema(DbTable fromTbl, DbTable toTbl) { foreach (var c in toTbl.Columns) { var existing = fromTbl .Columns .FirstOrDefault(x => SameColumn(x, c)); // Add the new column... if (existing == null) { fromTbl.Columns.Add(c); string ddl = string.Format("ALTER TABLE [{0}].[{1}] ADD [{2}] {3} NULL", Schema, fromTbl.Name, c.Name, c.Type.SqlType()); ExecuteCommand(ddl); } else { if (c.Type != existing.Type) { if (existing.Type == DbType.Varchar) { // Do nothing, your already as generic as you can be. } else if (existing.Type == DbType.Float && c.Type == DbType.Integer) { // Was a float, and we are adding an int, so do nothing } else if (existing.Type == DbType.Integer && c.Type == DbType.Float) { // Change from INT to FLOAT existing.Type = DbType.Float; string ddl = string.Format("ALTER TABLE [{0}].[{1}] ALTER COLUMN [{2}] {3} NULL", Schema, fromTbl.Name, c.Name, DbType.Float.SqlType()); ExecuteCommand(ddl); } else { // Change from ANYTHING else to VARCHAR existing.Type = DbType.Varchar; // Push it out to be VARCHAR string ddl = string.Format("ALTER TABLE [{0}].[{1}] ALTER COLUMN [{2}] {3} NULL", Schema, fromTbl.Name, c.Name, DbType.Varchar.SqlType()); ExecuteCommand(ddl); } } } } return true; }