vb.net update query with vb.net

Question

in an sql updatate query runnging in a vb.net application i am updating the data of some columns to the sql datatable, here is the code

dim cnn = New SqlConnection(connetionString)
Dim q As New SqlCommand("UPDATE products SET rank=@rank,rankchange=@rankchange,PD=@PD,PDP=@PDP,TPD=@TPD where catalogid=@catalogid",cnn)
cnn.Open()
For i = 0 To mainDatatable.Rows.Count - 1
 q.Parameters.Add(New SqlParameter("@rank", mainDatatable.Rows(i)("rank")))
 q.Parameters.Add(New SqlParameter("@rankchange", mainDatatable.Rows(i)("rankchange")))
 q.Parameters.Add(New SqlParameter("@PD", mainDatatable.Rows(i)("PD")))
 q.Parameters.Add(New SqlParameter("@PDP", mainDatatable.Rows(i)("PDP")))
 q.Parameters.Add(New SqlParameter("@TPD", mainDatatable.Rows(i)("TPD")))
 q.ExecuteNonQuery()
 q.Parameters.Clear()
Next

i am having 2 problems

its running slow considering that the table has about 60k records
if the a value in my datatable application was NULL then i want to update it as NULL, what would be the best way? i tried something like the following

If IsDBNull(mainDatatable.Rows(i)("rank")) Then q.Parameters.Add(New SqlParameter("@rank", "NULL")) Else q.Parameters.Add(New SqlParameter("@rank", mainDatatable.Rows(i)("rank"))) End If

A better way to use these params: add the SqlParameters once and update their value. — Hans Keﬆing, Jan 21 '13 at 13:25
Somewhat off-topic, but you should think about doing something like "UPDATE " + tableName + " Set ..." instead of hard-coding your table-name. Really, you should be using stored procs to avoid SQL injection, but that's another story... — , Jan 21 '13 at 13:25
@hans kesting you see its inside a loop, i have to add the parameters from each row and this makes no problem since i clear the parameters after i execute the query everytime — user1570048, Jan 21 '13 at 13:28
@user1570048 - I changed my comment when I saw that "Clear". — Hans Keﬆing, Jan 21 '13 at 13:28

score 2 · Accepted Answer · answered Jan 21 '13 at 13:33

To respond to (1) the speed question...

if you do indeed have 60K+ changes to process you don't need to execute 60K update statements. I would upload the 60K records to a temp table and invoke a stored procedure to do an UPDATE FROM...INNER JOIN statement between the real table and the temp table. Another possible idea if you don't want to rewrite what you've got is to filter out any rows in your datatable that have not been tainted. If there is no change then there is no reason to send those update statements...

Regarding (2) here is syntax...

this should work for you regarding sending NULL values as SqlParameters.

If mainDatatable.Rows(i)("rank") = DBNull.Value Then
  q.Parameters.Add(New SqlParameter("@rankchange", SqlDbType.Int, 4) With { .Value = DBNull.Value })
Else
  q.Parameters.Add(New SqlParameter("@rankchange", SqlDbType.Int, 4) With { .Value = mainDatatable.Rows(i)("rank") })
End If

score 1 · Answer 2 · answered Jan 21 '13 at 13:32

writing following line of code q.Parameters.Add(New SqlParameter("@rank", "NULL")) doesn't make null entry instead it will treat Null as string.
what you can try is this.

q.Parameters.Add(new SqlParameter() { 
        ParameterName="@test",
        IsNullable=true,
        Value=DBNull.Value
    });

vb.net update query with vb.net

2 Answers2