Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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());
        }
Ejemplo n.º 3
0
        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}");
                }
            }
        }