private static string SaveBinary(SqlDataReader reader, int field, string parentFile, bool blobs)
{
if (!blobs)
{
return(string.Empty);
}
var folder = GetDirectoryName(parentFile);
var name = Guid.NewGuid() + ".blob";
var buffer = new byte[0x10000];
Console.WriteLine($"Saving blob to {name}");
using (var logger = new ThrottledLogger())
using (var stream = reader.GetStream(field))
using (var file = new FileStream(Path.Combine(folder, name), FileMode.Create, FileAccess.Write))
{
int got;
var total = 0;
while ((got = stream.Read(buffer, 0, buffer.Length)) != 0)
{
file.Write(buffer, 0, got);
total += got;
logger.Update($"Saved {total} bytes");
}
}
Console.WriteLine($"Finished saving blob to {name}");
return(name);
}
private static void Export(string fileName, IEnumerable <string> tables, SqlConnection conn, bool blobs)
{
var rootElem = new XElement("tables");
foreach (var table in tables)
{
var tableElem = new XElement("table");
tableElem.SetAttributeValue("name", table);
rootElem.Add(tableElem);
Console.WriteLine($"Exporting table: {table}");
var records = 0;
using (var command = new SqlCommand(null, conn))
{
command.CommandText = @"SELECT * FROM " + table;
using (var logger = new ThrottledLogger())
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
records++;
logger.Update($"Exported {records} records");
var recordElem = new XElement("record");
tableElem.Add(recordElem);
for (var f = 0; f < reader.FieldCount; f++)
{
var name = reader.GetName(f);
var type = reader.GetFieldType(f);
var val = reader.IsDBNull(f) ? null :
type == typeof(byte[]) ? SaveBinary(reader, f, fileName, blobs) :
reader.GetValue(f);
var fieldElem = new XElement("field");
recordElem.Add(fieldElem);
fieldElem.SetAttributeValue("name", name);
if (type != null)
{
fieldElem.SetAttributeValue("type", type.FullName);
}
if (val == null)
{
fieldElem.SetAttributeValue("null", "true");
}
else
{
var byteArray = val as byte[];
fieldElem.Value = byteArray != null?Convert.ToBase64String(byteArray) : Convert.ToString(val);
}
}
}
}
Console.WriteLine($"Finished table {table}, {records} records");
}
}
File.WriteAllText(fileName, rootElem.ToString());
}
private static void Import(string fileName, Dictionary <string, string> mappings, bool delete, SqlConnection conn)
{
Console.WriteLine($"Reading import file {fileName}...");
var doc = XDocument.Load(fileName);
var tableElements = doc.Descendants("table").ToList();
string TableName(XElement table)
{
return((string)table.Attribute("name"));
}
XElement TableByName(string name)
{
return(tableElements.FirstOrDefault(e => TableName(e) == name));
}
var dependencies = new Dictionary <string, List <XElement> >();
Console.WriteLine("Analyzing dependencies...");
using (var command = new SqlCommand(null, conn))
{
command.CommandText = SqlGetDependencies;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var source = reader.GetString(0);
var target = reader.GetString(1);
if (!dependencies.TryGetValue(source, out List <XElement> targets))
{
dependencies[source] = targets = new List <XElement>();
}
var targetTable = TableByName(target);
if (targetTable != null)
{
targets.Add(targetTable);
}
}
}
}
IEnumerable <XElement> TableDependencies(XElement table)
{
if (dependencies.TryGetValue(TableName(table), out List <XElement> targets))
{
return(targets);
}
return(Enumerable.Empty <XElement>());
}
var sortedTables = tableElements.TopologicalSort(TableDependencies).ToList();
if (delete)
{
foreach (var tableElem in sortedTables)
{
var table = (string)tableElem.Attribute("name");
Console.WriteLine($"Deleting from table: {table}");
using (var command = new SqlCommand(null, conn))
{
command.CommandText = @"DELETE FROM " + table;
var affected = command.ExecuteNonQuery();
Console.WriteLine("{0} - Deleted {1} rows", table, affected);
}
}
}
sortedTables.Reverse();
var exceptions = new List <(Exception error, string table, XElement record)>();
foreach (var tableElem in sortedTables)
{
var table = (string)tableElem.Attribute("name");
Console.WriteLine($"Reading schema of table: {table}");
if (!mappings.TryGetValue(table, out string mappedTable))
{
mappedTable = table;
}
var columnTypes = new Dictionary <string, SqlDbType>();
var columnSizes = new Dictionary <string, int>();
var columnPrecisions = new Dictionary <string, short>();
var columnScales = new Dictionary <string, short>();
using (var command = new SqlCommand(null, conn))
{
command.CommandText = @"SELECT TOP 1 * FROM " + mappedTable;
using (var reader = command.ExecuteReader())
{
var schemaType = reader.GetSchemaTable();
foreach (var columnInfo in schemaType.Rows.OfType <DataRow>())
{
var columnName = (string)columnInfo["ColumnName"];
columnTypes[columnName] = (SqlDbType)(int)columnInfo["ProviderType"];
columnSizes[columnName] = (int)columnInfo["ColumnSize"];
columnPrecisions[columnName] = (short)columnInfo["NumericPrecision"];
columnScales[columnName] = (short)columnInfo["NumericScale"];
}
}
}
var inserted = 0;
var errors = 0;
Console.WriteLine($"Importing table: {table}");
using (var logger = new ThrottledLogger())
{
foreach (var recordElem in tableElem.Descendants("record"))
{
using (var command = new SqlCommand(null, conn))
{
var columns = new List <string>();
foreach (var destColumn in columnTypes.Keys)
{
string sourceColumn;
if (!mappings.TryGetValue(destColumn, out sourceColumn))
{
sourceColumn = destColumn;
}
var fieldElem = recordElem.Descendants("field").FirstOrDefault(f => (string)f.Attribute("name") == sourceColumn);
if (fieldElem == null)
{
var sqlType = columnTypes[destColumn];
var columnSize = columnSizes[destColumn];
var columnPrecis = columnPrecisions[destColumn];
var columnScale = columnScales[destColumn];
object val = null;
if (sqlType == SqlDbType.Bit)
{
val = 0;
}
else if (sqlType == SqlDbType.DateTime2)
{
val = DateTime.Now;
}
else if (sqlType == SqlDbType.UniqueIdentifier)
{
val = Guid.Empty;
}
if (val != null)
{
columns.Add(destColumn);
command.Parameters.Add(new SqlParameter("@p" + destColumn, sqlType, columnSize)
{
Value = val,
Precision = (byte)columnPrecis,
Scale = (byte)columnScale
});
}
}
else
{
var typeName = (string)fieldElem.Attribute("type");
var isNull = !string.IsNullOrWhiteSpace((string)fieldElem.Attribute("null"));
if (!isNull)
{
var value = FromString(fieldElem.Value, Type.GetType(typeName), fileName);
var sqlType = columnTypes[destColumn];
var columnSize = columnSizes[destColumn];
var columnPrecis = columnPrecisions[destColumn];
var columnScale = columnScales[destColumn];
if (sqlType == SqlDbType.NVarChar)
{
columnSize = value?.ToString().Length ?? 0;
}
if (sqlType != SqlDbType.NVarChar || columnSize != 0)
{
columns.Add(destColumn);
command.Parameters.Add(new SqlParameter("@p" + destColumn, sqlType, columnSize)
{
Value = value,
Precision = (byte)columnPrecis,
Scale = (byte)columnScale
});
}
}
}
}
command.CommandText = @"INSERT INTO " + table + "(" +
string.Join(",", columns.Select(c => "[" + c + "]")) + ") VALUES (" +
string.Join(",", columns.Select(c => "@p" + c)) + ")";
command.Prepare();
try
{
inserted += command.ExecuteNonQuery();
}
catch (Exception x)
{
errors++;
x = x.GetBaseException();
exceptions.Add((x, table, recordElem));
}
logger.Update($"{inserted} records inserted, {errors} failures");
}
}
}
Console.WriteLine($"Finished importing {table}, {inserted} records, {errors} failures");
}
if (exceptions.Count != 0)
{
foreach (var exception in exceptions)
{
Console.WriteLine($"In table {exception.table}: {exception.error.Message} - data was: {exception.record}");
}
}
}