1

I have a loop in a vb.net project that makes dynamic SQL which I want to have parameterized. I was planning to call the sub that runs the SQL statement every 10 records because I thought I heard doing it 1 at a time is slower. So how do I commit the SQL query every 10 records if the parameters are changing each time through the loop? I realized I can just append _x.ToString to the variable names and have 20 variables in the list, but I didn't know if there's a "proper" way of doing this that I'm overlooking. Very stripped down code example is below

For _x As Integer = 0 To _dataset.Tables(_x).Rows.Count - 1
    _sql &= "INSERT INTO test (col1, col2) VALUES (@col1, @col2) "
    _parameters.Add(new parameter("@col1", <variable data>) // changes every time through loop
    _parameters.Add(new parameter("@col2", <variable data>) // changes every time through loop

    If _x Mod 10 = 0 Then
        Call executeSQL(_sql, _parameters)
    End If
Next
Tim F
  • 119
  • 1
  • 8
  • 1
    It is almost impossible to create a one size fits all, all purpose query helper. For one thing the data types can be all different for your collection of values. ...and of course the built in functionality available in DataAdapters and DataTables ought not be ignored – Ňɏssa Pøngjǣrdenlarp Nov 20 '17 at 16:21
  • 1
    Rather than cobbling together a big string, consider wrapping the `INSERT` statements in a transaction, prepended with `SET NOCOUNT ON`. If you have many rows and your table supports it, use `SqlBulkCopy`. Both options are more robust than code to manually construct batches of instructions. – Jeroen Mostert Nov 20 '17 at 16:23
  • 2
    "I thought I heard doing it 1 at a time is slower"??? So you write code based on something you think you heard? The problem here is that you are doing each insert one at a time. Where do these values comes from? It would be more efficient to possibly use table parameters so you don't have to execute dozens, hundreds or even thousands of individual inserts. – Sean Lange Nov 20 '17 at 16:23
  • 2
    I would not be so sure that repeated inserts of a single row is slower than a single 10 rows inserts. https://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values – Steve Nov 20 '17 at 16:24
  • You need a ";" in between queries and also you could have to increase the variable number because if col1, col2 are there multiple time, the data will be the same. Also, "I thought I heard doing it 1 at a time is slower" have you tried it? Do you know if it's true in your case? – the_lotus Nov 20 '17 at 16:32
  • I've never done this myself, so this is just a suggestion for something to Google. Try using a DataAdapter: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters – dwilliss Nov 20 '17 at 17:09

0 Answers0