1

I need to improve dramatically the execution time of a process wich currently is just like this:

private Dictionary<int, SingleElement> elements_buffer;

// ... Create and load values into "elements_buffer"
// (string, Datetime, double)

string query = "INSERT INTO Tests.dbo.test_table "
    + "(element_name,element_init,element_width) VALUES";

SingleElement element_aux;

for (int i = 0; i < MAX_ELEMS_IN_DICT; i++)
{
    element_aux = elements_buffer[i];

    query = query
        + "('"
        + element_aux.name
        + "','"
        + element_aux.init
        + "',"
        + element_aux.width
        + ")";

    if (i < MAX_ELEMS_IN_DICT+1) {
        query = query + ",";
    }
}

// ... Execute the query

I was going to use Datatable for the new version, but I've been reading about using SqlBulkCopy together with IDatareader, as mentioned in:

SqlBulkCopy performance

C# loops and mass insert

It looks like to me a better choice for my code, but can't get to figure it out how to code it, though I would like to use it.

Can I have some help with the translated code, please?

Thanks in advance

Community
  • 1
  • 1
Alberto Martín
  • 439
  • 13
  • 26
  • 2
    Please use [parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html) instead of string concatenations. This kind of concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. – Soner Gönül Sep 29 '13 at 11:08
  • Actually it's not accessible from the Internet and security it's not a problem; I appreciate your comment anyway, but if you could help me with my question it would be great. – Alberto Martín Sep 29 '13 at 12:19

1 Answers1

1

As you already have figured out, you need to implement custom IDataReader. As your source is Dictionary - only few relatively simple method/properties such as int FieldCount, bool Read(), object Get(int i) should be implemented.

You may find useful examples (a simple one) of custom IDataReader implementations by googling sqlbulkcopy custom idatareader.

Also keep in mind that SqlBulkCopy ignores triggers, foreign keys and other constraints and is not able to handle exceptions.

kasitan
  • 509
  • 4
  • 15