1

This questions had been posted several times (1,2,3,4), but I did not find one that would apply to my case.

I have a Dictionary with the structure:

public Dictionary<Int32, PhaseMag> Data;

Where PhaseMag:

public struct PhaseMag
{
    public Single Magnitude;
    public Single Phase;
}

Each Key value will contain two 2 values (Mag. and Phase). If you prefer to see an image.

  • I need to store all the content of this Dictionary in a unique Table in the DB (Microsoft SQL). Each line of the Dictionary should become one line of my DB Table. My final table will contain 3 fields, 1) Key 2) Mag and 3) Phase. For example, if I have 30 Keys, my table will contain 30 lines, one for each key.

My ideas:

  • Create a foreach loop based on each Key and create an insert into to the DB (one for Key->Mag and other for Key->Phase). But I don't think that this will be the best approach, especially, because my dictionary contains several thousand of lines.

So, what should be my approach to do this? I simply need to save my Dictionary into the DB, which each line of the Dic. will be one line from the DB.

FFLS
  • 565
  • 1
  • 4
  • 19
  • Create a `DataTable` from your `Dictionary` and pass it to a stored procedure in your sql server as a table valued parameter. – Zohar Peled Aug 03 '17 at 13:51
  • 1
    Loop the dictionary populating a DataTable then send it to the server as a bulk operation: https://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row – Alex K. Aug 03 '17 at 13:51
  • Yes... I was trying to do this without populating a DataTable but I think that this is the best approach! Thank you. – FFLS Aug 03 '17 at 13:53
  • Do you want to use pure System.Data.SqlClient or something like Entity Framework? If you want to use pure System.Data.SqlClient API you can increase efficiency with BULK INSERT https://msdn.microsoft.com/pl-pl/library/ms188365(v=sql.110).aspx – fdafadf Aug 03 '17 at 13:54

1 Answers1

2

Assume you're using SQL Server, here're two of many options you have:

  1. Bulk insert, it's recommended.

    public void BulkWrite(Dictionary<Int32, PhaseMag> data)
{
    var dataTable = new DataTable();
    dataTable.Columns.Add(new DataColumn { DataType = typeof(int), ColumnName = "Key" });
    dataTable.Columns.Add(new DataColumn { DataType = typeof(Single), ColumnName = "Magnitude" });
    dataTable.Columns.Add(new DataColumn { DataType = typeof(Single), ColumnName = "Phase" });

    foreach (var x in data)
    {
        var r = dataTable.NewRow();
        dataTable.Rows.Add(r);
        r[0] = x.Key;
        r[1] = x.Value.Magnitude;
        r[2] = x.Value.Phase;
    }


    using (var conn = new SqlConnection("conneciton string"))
    {
        conn.Open();
        using (var bulkCopy = new SqlBulkCopy(conn))
        {
            bulkCopy.BatchSize = 4000;
            bulkCopy.DestinationTableName = "YorTableName";
            bulkCopy.WriteToServer(dataTable);
        }
    }
}

  2. Multiple inline query, executed as batch. As all your data are number, so low risk of SQL injection even using inline query.

    public void InlineQueryWrite(Dictionary<Int32, PhaseMag> data)
{
    using (var conn = new SqlConnection("conneciton string"))
    {
        conn.Open();

        foreach (var bulk in data.Select((d, i) => new {d, i}).GroupBy(x => x.i % 10))
        {
            var sb = new StringBuilder();
            foreach (var x in bulk)
            {
                sb.AppendFormat("Insert Into Your_Table (Key, Magnitude, Phase) Values ({0},{1},{2});", x.d.Key, x.d.Value.Magnitude, x.d.Value.Phase);
            }

            using (var command = conn.CreateCommand())
            {
                command.CommandText = sb.ToString();
                command.ExecuteNonQuery();
            }
        }

    }
}

I haven't ran/tested through the code, but they should work.

Steven.Xi
  • 1,670
  • 1
  • 13
  • 15