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;
}