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