SqlBulkCopy XML mit einem GUID-Fremdschlüssel

c# guid sqlbulkcopy uniqueidentifier xml

Frage

Ich versuche, Daten mit XML-Dateien und SqlBulkCopy einzufügen. Die Zieltabelle ist eine Zeitreihentabelle, die wie folgt eingerichtet ist

create table TimeSeries (
    Id          uniqueidentifier constraint DF_TimeSeries_Id default (newid()) not null,
    ObjectId    uniqueidentifier not null,
    [Date]      datetime not null,
    Value       float(53) not null,
    [Type]      varchar (4) not null,
    [Source]    varchar (4) not null,
    LastUpdate  datetime constraint DF_TimeSeries_LastUpdate default (getdate()) not null,
    TypeIndex   smallint constraint DF_TimeSeries_TypeIndex default (0) not null,
    constraint PK_TimeSeries primary key clustered ([Date] asc, ObjectId asc, [Type] asc, [Source] asc, TypeIndex asc) with (fillfactor = 80)
);

go
create nonclustered index [IX_TimeSeries_ObjectId_Type_Date_Source]
    on TimeSeries(ObjectId asc, [Type] asc, [Date] asc, [Source] asc)
    include(Value) with (fillfactor = 80);


go
create nonclustered index [IX_TimeSeries_ObjectId_Date]
    on TimeSeries(ObjectId asc, [Date] asc)
    include(Value) with (fillfactor = 80);

go
create table Beacons
(
    BeaconId uniqueidentifier not null default newid(), 
    [Description] varchar(50) not null, 
    LocationX float not null,
    LocationY float not null,
    Altitude float not null
    constraint PK_Beacons primary key clustered (BeaconId)
)
go
create index IX_Beacons on Beacons (BeaconId)

go
create table SnowGauges
(
    SnowGaugeId uniqueidentifier not null default newid(), 
    [Description] varchar(50) not null
    constraint PK_SnowGauges primary key clustered (SnowGaugeId)
)
go
create index IX_SnowGauges on SnowGauges (SnowGaugeId)


go
insert into Beacons ([Description], LocationX, LocationY, Altitude)
values ('Dunkery', 51.162, -3.586, 519), ('Prestwich', 53.527, -2.279, 76)
insert into SnowGauges ([Description]) values ('Val d''Isère')

select * from Beacons
select * from SnowGauges

Wie Sie sehen können, möchte ich in TimeSeries jede Art von Zeitreihen speichern. Dies können Temperatur, Druck, biologische Daten usw. sein. In jedem Fall kann ich die Zeitreihe durch eine eindeutige Kennung, eine Quelle und einen Typ identifizieren. In ObjectId ist kein Fremdschlüssel eingerichtet, da sich dieser eindeutige Bezeichner auf jede Tabelle beziehen kann.

Am Ende dieses Skripts habe ich 2 Beacons und einen Snowgauge eingefügt und möchte ihre Zeitreihen füllen. Die XML-Datei hierfür hat folgendes Format:

<?xml version="1.0" encoding="utf-8" ?>
<TimeSeries>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 07:00:00" Value="9.2" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 10:00:00" Value="8.8" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 13:00:00" Value="8.7" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 07:00:00" Value="1" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 10:00:00" Value="3" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="186CA33E-AC1C-4220-81DE-C7CD32F40C1A" Date="09/06/2013 13:00:00" Value="5" Source = "Met Office" Type = "UV"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 07:00:00" Value="5.8" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 10:00:00" Value="6.3" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="AFB81E51-18B0-4696-9C2F-E6E9EEC1B647" Date="09/06/2013 13:00:00" Value="6.5" Source = "Met Office" Type = "Temperature"/>
<TimeSeries ObjectId="50E52A2B-D719-4341-A451-110D0874D26D" Date="07/06/2013 00:00:00" Value="80.5" Source = "Meteo France" Type = "SnowMeter"/>
<TimeSeries ObjectId="50E52A2B-D719-4341-A451-110D0874D26D" Date="08/06/2013 00:00:00" Value="80.5" Source = "Meteo France" Type = "SnowMeter"/>
</TimeSeries>

Wenn Sie das erste Skript ausführen, können Sie eine andere ObjectId erwarten und müssen diese in der XML-Datei aktualisieren. Von daher sollte alles einfach sein und ein einfaches C # -Programm sollte die Aufgabe übernehmen, die Daten einzufügen. Sehen wir uns jetzt den C # -Code an:

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace XMLBulkInsert
{
    class Program
    {
        const string XMLFILE_PATH = @"C:\Workspaces\Ws1\R\TimeSeries\TimeSeries.xml";
        const string CONNECTION_STRING = @"Server=RISK1;Database=DevStat;Trusted_Connection=True;";

        static void Main(string[] args)
        {
            StreamReader xmlFile = new StreamReader(XMLFILE_PATH);
            DataSet ds = new DataSet();

            Console.Write("Read file... ");
            ds.ReadXml(xmlFile);
            DataTable sourceData = ds.Tables[0];
            Console.WriteLine("Done !");

            using (SqlConnection sourceConnection = new SqlConnection(CONNECTION_STRING))
            {
                sourceConnection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sourceConnection.ConnectionString))
                {
                    bulkCopy.ColumnMappings.Add("ObjectId", "ObjectId");
                    bulkCopy.ColumnMappings.Add("Date", "Date");
                    bulkCopy.ColumnMappings.Add("Value", "Value");
                    bulkCopy.ColumnMappings.Add("Source", "Source");
                    bulkCopy.ColumnMappings.Add("Type", "Type");
                    bulkCopy.DestinationTableName = "TimeSeries";

                    try
                    {
                        Console.Write("Insert data... ");
                        bulkCopy.WriteToServer(sourceData);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        xmlFile.Close();
                        sourceConnection.Close();
                    }
                }
            }

            Console.WriteLine("Insertion completed, please Press Enter...");
            Console.ReadLine();
        }

    }
}

Das Ausführen dieses Programms gibt diese Ausnahme zurück: "Der angegebene Wert vom Typ String aus der Datenquelle kann nicht in den Typ uniqueidentifier der angegebenen Zielspalte konvertiert werden.". Es sieht so aus, als gäbe es keine Möglichkeit, wenn ich das Mapping einrichten würde, um die Spalte zu einer eindeutigen Kennung zu zwingen. Ich habe sogar versucht, diesen Code ds.Tables[0].Columns[0].DataType = typeof(Guid); einzufügen. ds.Tables[0].Columns[0].DataType = typeof(Guid); aber mit keinem Erfolg, .Net kann den Typ der Spalte nicht ändern, sobald die Tabelle Zeilendaten enthält.

Ich hatte hohe Ausnahme mit SQlBulkCopy, aber jetzt fühle ich mich ein bisschen fest. Ich habe Millionen und Abermillionen von Daten im XML-Format und kann wegen dieser eindeutigen Kennung keine davon einfügen.

Kann jemand diese Klasse einrichten, um eine eindeutige Kennung zu akzeptieren?

Akzeptierte Antwort

Angesichts der Kommentare über 300 Millionen Zeilen würde ich DataTable vergessen ; Sie möchten diese Daten nicht alle gleichzeitig laden. Das Ideal wäre es, Element für Element zu parsen und die Daten als IDataReader .

Glücklicherweise existieren dafür einige Dienstprogramme. Lasst uns zuerst die Daten analysieren. Jede Zeile ist im Wesentlichen:

class TimeSeries
{
    public Guid ObjectId { get; set; }
    public DateTime Date { get; set; }
    public string Source { get; set; }
    public string Type { get; set; }
    public decimal Value { get; set; }
}

und wir können einen elementbasierten Leser schreiben wie:

static IEnumerable<TimeSeries> ReadTimeSeries(TextReader source)
{
    using (var reader = XmlReader.Create(source, new XmlReaderSettings {
                     IgnoreWhitespace = true }))
    {
        reader.MoveToContent();
        reader.ReadStartElement("TimeSeries");
        while(reader.Read() && reader.NodeType == XmlNodeType.Element
                    && reader.Depth == 1)
        {
            using (var subtree = reader.ReadSubtree())
            {
                var el = XElement.Load(subtree);
                var obj = new TimeSeries
                {
                    ObjectId = (Guid) el.Attribute("ObjectId"),
                    // note: datetime is not xml format; need to parse - this
                    // should probably be more explicit
                    Date = DateTime.Parse((string) el.Attribute("Date")),
                    Source = (string) el.Attribute("Source"),
                    Type = (string)el.Attribute("Type"),
                    Value = (decimal)el.Attribute("Value")
                };
                yield return obj;
            }
        }
    }
}

Beachten Sie, dass dies ein "Iterator-Block" ist, und spoolt langsam - es lädt nicht alle Daten auf einmal.

Als nächstes brauchen wir eine API, die ein IEnumerable<T> konsumieren und es als IDataReader verfügbar IDataReader - FastMember tut genau dies (und viele andere Dinge). Also können wir dann einfach schreiben:

using(var bcp = new SqlBulkCopy(connection))
using(var objectReader = ObjectReader.Create(ReadTimeSeries(source)))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(objectReader);
}

wo source ist ein TextReader , zum Beispiel von File.OpenText :

using(var source = File.OpenText(path))
using(var bcp = new SqlBulkCopy(connection))
using(var objectReader = ObjectReader.Create(ReadTimeSeries(source)))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(objectReader);
}

Wenn Sie die Spaltenreihenfolge steuern möchten, können Sie bcp.ColumnMappings - aber vielleicht ist es bequemer, IDataReader intern dazu zu veranlassen:

using(var objectReader = ObjectReader.Create(
    ReadTimeSeries(source, "ObjectId", "Date", "Value" /* etc */)))
{
    bcp.DestinationTableName = "SomeTable";
    bcp.WriteToServer(objectReader);
}

Ich benutze dies für einige meiner eigenen Code - selbst dann , wenn die Daten in den Speicher passen tut, es ist viel schneller als über gehen DataTable .

Der entscheidende Punkt ist jedoch, dass wir jetzt die Kontrolle darüber haben, was vor sich geht.



Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow
Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow