DevAndy | Bulkupsert oder einfach Update, wenn bereits vorhanden ansonsten Insert in MSSQL

Lange habe ich nach einer vernünftigen Lösung gesucht, um riesige Datenmengen schnell in die Datenbank zu schreiben.

Als Quelle steht uns eine List<Class> mit der Klasse zur Verfügung, die der Datenbanktabelle gleicht und jede Menge Inhalte enthält. Versucht man diese Liste nun über das Entity Framework in die Datenbank zu jagen, merkt man schnell, dass das Entity Framework an seine Grenzen stößt. Alternativ haben wir das von der SQLCopy Klasse die BulkInsert() Methode, die aber nur Inserten kann. Findet das BulkInsert einen Index/Primärschlüssel, der bereits vorhanden ist, wird die ganze Show abgebrochen. Wie toll wäre es nun, wenn es nicht abbrechen würde, sondern diese Zeile einfach updated. Und das ganze Superschnell. Ich habe Testweise 10.000 Einträge in 400ms und 1.000.000 in 1 min in die Datenbank bekommen.

Methode Upsert:

/// <summary>
        /// example:
        /// UpdateData(
        /// list: new List<Personal>(), 
        /// index: t => new { t.PersNr,t.Address},
        /// insert: t => new {t.PersNr,t.Birthday,t.FirstName},
        /// update: t => t.FirstName);
        /// </summary>
        /// <typeparam name="T">
        /// </typeparam>
        /// <typeparam name="TIndexResult"></typeparam>
        /// <typeparam name="TInsertResult"></typeparam>
        /// <typeparam name="TUpdateResult"></typeparam>
        /// <param name="list"></param>
        /// <param name="index"></param>
        /// <param name="insert"></param>
        /// <param name="update"></param>
        public void UpsertData<T, TIndexResult, TInsertResult, TUpdateResult>(List<T> list, Func<T, TIndexResult> index, Func<T, TInsertResult> insert, Func<T, TUpdateResult> update, string schema = "dbo") where T : class
        {
            if (list.Count == 0) return;
            // get Properties over Reflection
            string TableName = list[0].GetType().Name;
            var Index =
                list.Select(index).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();
            var Insert =
                list.Select(insert).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();
            var Update =
                list.Select(update).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();
            // Create Temp Table
            StringBuilder sb = new StringBuilder();
            sb.Append("IF OBJECT_ID('#");
            sb.Append(TableName);
            sb.Append("') IS NULL ");
            sb.Append("BEGIN ");
            sb.Append("SELECT TOP(0) ");
            for (int i = 0; i < Insert.Length; i++)
            {
                if (i != Insert.Length -1)
                    sb.Append(Insert[i] + ",");
                else
                    sb.Append(Insert[i]);
            }
            sb.Append(" INTO #" + TableName + " FROM " + schema + "." + TableName);
            sb.Append(" END");
            Exec(sb.ToString());

            //Create Datatable and Bulkinsert the List as Bulkinsert
            DataTable dt = list.Select(insert).ToList().AsDataTable();
            dt.TableName = "#" + TableName;

            BulkInsert(dt, "#" + TableName);
            // Now Merge the From the Temptable to your Table
            sb.Clear();
            sb.Append("MERGE INTO ");
            sb.Append(schema + "." + TableName);
            sb.Append(" AS Target ");
            sb.Append("USING #");
            sb.Append(TableName);
            sb.Append(" AS Source ");
            sb.Append(" ON ");


            for (int i = 0; i < Index.Length; i++)
            {
                sb.Append("Target.");
                sb.Append(Index[i]);
                sb.Append("=Source.");
                sb.Append(Index[i]);

                if (i != Index.Length - 1)
                {
                    sb.Append(" AND ");
                }
            }

            sb.Append("\n WHEN MATCHED THEN ");
            sb.Append("UPDATE SET ");
            for (int i = 0; i < Update.Length; i++)
            {
                sb.Append("Target.");
                sb.Append(Update[i]);
                sb.Append("=Source.");
                sb.Append(Update[i]);

                if (i != Update.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(" WHEN NOT MATCHED THEN ");
            sb.Append(" INSERT(");
            for (int i = 0; i < Insert.Length; i++)
            {
                sb.Append(Insert[i]);
                if (i != Insert.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(") VALUES (");
            for (int i = 0; i < Insert.Length; i++)
            {
                sb.Append("Source.");
                sb.Append(Insert[i]);
                if (i != Insert.Length - 1)
                {
                    sb.Append(",");
                }
            }
            sb.Append(");");
            Exec(sb.ToString());

            Exec("DROP TABLE #" + TableName);

        }

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

/// <summary>

/// example:

/// UpdateData(

/// list: new List<Personal>(),

/// index: t => new { t.PersNr,t.Address},

/// insert: t => new {t.PersNr,t.Birthday,t.FirstName},

/// update: t => t.FirstName);

/// </summary>

/// <typeparam name="T">

/// </typeparam>

/// <typeparam name="TIndexResult"></typeparam>

/// <typeparam name="TInsertResult"></typeparam>

/// <typeparam name="TUpdateResult"></typeparam>

/// <param name="list"></param>

/// <param name="index"></param>

/// <param name="insert"></param>

/// <param name="update"></param>

public void UpsertData<T, TIndexResult, TInsertResult, TUpdateResult>(List<T> list, Func<T, TIndexResult> index, Func<T, TInsertResult> insert, Func<T, TUpdateResult> update, string schema = "dbo") where T : class

{

if (list.Count == 0) return;

// get Properties over Reflection

string TableName = list[0].GetType().Name;

var Index =

list.Select(index).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();

var Insert =

list.Select(insert).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();

var Update =

list.Select(update).FirstOrDefault().GetType().GetProperties().Select(x => x.Name).ToArray();

// Create Temp Table

StringBuilder sb = new StringBuilder();

sb.Append("IF OBJECT_ID('#");

sb.Append(TableName);

sb.Append("') IS NULL ");

sb.Append("BEGIN ");

sb.Append("SELECT TOP(0) ");

for (int i = 0; i < Insert.Length; i++)

{

if (i != Insert.Length -1)

sb.Append(Insert[i] + ",");

else

sb.Append(Insert[i]);

}

sb.Append(" INTO #" + TableName + " FROM " + schema + "." + TableName);

sb.Append(" END");

Exec(sb.ToString());

//Create Datatable and Bulkinsert the List as Bulkinsert

DataTable dt = list.Select(insert).ToList().AsDataTable();

dt.TableName = "#" + TableName;

BulkInsert(dt, "#" + TableName);

// Now Merge the From the Temptable to your Table

sb.Clear();

sb.Append("MERGE INTO ");

sb.Append(schema + "." + TableName);

sb.Append(" AS Target ");

sb.Append("USING #");

sb.Append(TableName);

sb.Append(" AS Source ");

sb.Append(" ON ");

for (int i = 0; i < Index.Length; i++)

{

sb.Append("Target.");

sb.Append(Index[i]);

sb.Append("=Source.");

sb.Append(Index[i]);

if (i != Index.Length - 1)

{

sb.Append(" AND ");

}

sb.Append("\n WHEN MATCHED THEN ");

sb.Append("UPDATE SET ");

for (int i = 0; i < Update.Length; i++)

{

sb.Append("Target.");

sb.Append(Update[i]);

sb.Append("=Source.");

sb.Append(Update[i]);

if (i != Update.Length - 1)

{

sb.Append(",");

}

sb.Append(" WHEN NOT MATCHED THEN ");

sb.Append(" INSERT(");

for (int i = 0; i < Insert.Length; i++)

{

sb.Append(Insert[i]);

if (i != Insert.Length - 1)

{

sb.Append(",");

}

sb.Append(") VALUES (");

for (int i = 0; i < Insert.Length; i++)

{

sb.Append("Source.");

sb.Append(Insert[i]);

if (i != Insert.Length - 1)

{

sb.Append(",");

}

sb.Append(");");

Exec(sb.ToString());

Exec("DROP TABLE #" + TableName);

}

Methode BulkInsert:

        public bool BulkInsert(DataTable PocoClass, string InsertInTable, int BatchSize = 100)
        {
            bool state = false;
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                {
                    bulkCopy.BatchSize = BatchSize;
                    bulkCopy.DestinationTableName = InsertInTable;
                    try
                    {
                        bulkCopy.WriteToServer(PocoClass);
                        state = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();

                    }
                }

                transaction.Commit();
            }
            return state;
        }

public bool BulkInsert(DataTable PocoClass, string InsertInTable, int BatchSize = 100)

{

bool state = false;

using (SqlTransaction transaction = connection.BeginTransaction())

{

using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))

{

bulkCopy.BatchSize = BatchSize;

bulkCopy.DestinationTableName = InsertInTable;

try

{

bulkCopy.WriteToServer(PocoClass);

state = true;

}

catch (Exception ex)

{

transaction.Rollback();

}

transaction.Commit();

}

return state;

}

und die beiden Extension Methods:

    public static class ExtensionMethods
    {
        public static DataTable AsDataTable<T>(this IEnumerable<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }
        public static Type GetItemType<T>(this IEnumerable<T> enumerable)
        {
            return typeof(T);
        }
    }

public static class ExtensionMethods

{

public static DataTable AsDataTable<T>(this IEnumerable<T> data)

{

PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

var table = new DataTable();

foreach (PropertyDescriptor prop in properties)

table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

foreach (T item in data)

{

DataRow row = table.NewRow();

foreach (PropertyDescriptor prop in properties)

row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;

table.Rows.Add(row);

}

return table;

}

public static Type GetItemType<T>(this IEnumerable<T> enumerable)

{

return typeof(T);

}

Angewendet wird das ganze ganz einfach so:

Parameter beinhaltet die Liste mit allen Inhalten
Parameter gibt an, welche Spalten geprüft werden sollen, ob es zu einem Update kommen soll oder Insert. In meinem Fall, wenn PersNr und CardId bereits in der Datenbank vorhanden sind, so mache ein Update, sonst Insert
Parameter gibt an, was inserted werden soll.
Parameter gibt an, was geupdated werden soll, wenn Parameter 2 zutrifft.

Bitte auf die tatsächlichen Primärschlüssel, Indizes und NOT NULL Schlüssel/Attribute achten. Ansonsten kommt man schnell zu einem Fehler

var list = new List<Personal>();
            for (int i = 1; i < 1000; i++)
            {
               list.Add(new Personal()
               {
                   FirstName = "Andreas",
                   LastName = "Seibel",
                   Birthday = new DateTime(1988, 2, 22),
                   CardId = i + 100,
                   EntryDate = new DateTime(2016, 3, 2),
                   Memo = "Test Memo",
                   ExitDate = null,
                   PersNr = i,
                   Sex = Sex.male
                   
               });
            }

          Time.Data.Db.Time.UpsertData(
              list,
              ind => new {ind.PersNr, ind.CardId},
              ins => new {ins.Birthday,ins.CardId,ins.EntryDate,ins.ExitDate,ins.FirstName,ins.LastName,ins.Memo,ins.PersNr,ins.Sex},
              upd => new { upd.PersNr, upd.Birthday, upd.FirstName, upd.LastName, upd.CardId, upd.EntryDate, upd.Memo },
              "personal"
              );

var list = new List<Personal>();

for (int i = 1; i < 1000; i++)

{

list.Add(new Personal()

{

FirstName = "Andreas",

LastName = "Seibel",

Birthday = new DateTime(1988, 2, 22),

CardId = i + 100,

EntryDate = new DateTime(2016, 3, 2),

Memo = "Test Memo",

ExitDate = null,

PersNr = i,

Sex = Sex.male

});

}

Time.Data.Db.Time.UpsertData(

list,

ind => new {ind.PersNr, ind.CardId},

ins => new {ins.Birthday,ins.CardId,ins.EntryDate,ins.ExitDate,ins.FirstName,ins.LastName,ins.Memo,ins.PersNr,ins.Sex},

upd => new { upd.PersNr, upd.Birthday, upd.FirstName, upd.LastName, upd.CardId, upd.EntryDate, upd.Memo },

"personal"

);

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Bulkupsert oder einfach Update, wenn bereits vorhanden ansonsten Insert in MSSQL

leave your comment cancel

Seiten

Neueste Beiträge

Neueste Kommentare

Archive

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Bulkupsert oder einfach Update, wenn bereits vorhanden ansonsten Insert in MSSQL

leave your comment cancel

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive