sqlbulkcopy with concurrent queue

Question

I have a concurrent queue defined with a class object which holds 65000 records defined like below

ConcurrentQueue<Data> DataQueue = new ConcurrentQueue<Data>();

public class Data
{
    public string Id { get; set; }
    public string T { get; set; }
    public string D { get; set; }
    public string L { get; set; }
    public string I { get; set; }
    public string V { get; set; }
}

I am using following code to insert into db

public void InsertIntoDB()
    {

        using (cn = new SqlConnection(connectionString))
        {
            cn.Open();

            Data item;
            while (SpotDataQueue.Count > 0)
            {
                if (DataQueue.TryDequeue(out item))
                {
                    using (SqlCommand cm = cn.CreateCommand())
                    {
                        cm.CommandText = @"INSERT INTO [TableName] ([WId], [L], [I], [V],[JId],[I],[DateTime]) VALUES (@WId, @l, @i, @v, @jid,@i,@dt)";
                        cm.Parameters.AddWithValue("WId", item.Id);
                        cm.Parameters.AddWithValue("@l", item.L);
                        cm.Parameters.AddWithValue("@i", item.I);
                        cm.Parameters.AddWithValue("@v", item.V);
                        cm.Parameters.AddWithValue("@jid", 1);
                        cm.Parameters.AddWithValue("@i", false);
                        cm.Parameters.AddWithValue("@dt", DateTime.Now);
                        cm.ExecuteNonQuery();
                    }
                }
            }
        }
    }

Table structure :

WId         nvarchar(50)    AllowNulls
L           nvarchar(MAX)   AllowNulls
I           nvarchar(MAX)   AllowNulls
V           nvarchar(MAX)   AllowNulls
JId         int             AllowNulls
I           bit             AllowNulls
DateTime    datetime        AllowNulls

How to convert my concurrent queue of type Data to a DATATABLE or DATAREADER to make SQLBULKCOPY possible ?

Thanks.

I would be careful with your `InsertIntoDB` function, if you insert faster than you write to the concurrentQueue your `while` loop will exit and new data will be added afterward. Consider switching to a `new BlockingCollection(new ConcurrentQueue())` and using `foreach(Data item in SpotDataQueue.GetConsumingEnumerable()` instead) of the while loop and the `TryDequeue` — Scott Chamberlain, Jan 13 '17 at 15:04
@ScottChamberlain : Hi, queue is already loaded with 65000 records, so that issue is not here in this scenario. I will consider your suggestion too.Thanks :) — Naveen kumar, Jan 13 '17 at 16:41

score 3 · Accepted Answer · edited May 23 '17 at 12:01

Here is a method that does the job with the help of reflection(changed parameter type):

public static DataTable ToDataTable<T>(this IEnumerable<T> items)
{
    var tb = new DataTable(typeof(T).Name);

    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

    foreach(var prop in props)
    {
        tb.Columns.Add(prop.Name, prop.PropertyType);
    }

    foreach (var item in items)
    {
       var values = new object[props.Length];
        for (var i=0; i<props.Length; i++)
        {
            values[i] = props[i].GetValue(item, null);
        }

        tb.Rows.Add(values);
    }

    return tb;
}

Now you can use it with your Queue:

DataTable dataSourceForSqlBulkCopy = DataQueue.ToDataTable();

Hi, thanks for the response, I will try this and let you know :) — Naveen kumar, Jan 13 '17 at 14:58

sqlbulkcopy with concurrent queue

1 Answers1