This C# / sql query code takes a lot of time to update the table

Question

Can anyone help improve performance? Updating the table takes a lot of time.

I am updating the serial number from datagridview to a table called dbo.json

// UPDATE dbo.json with numbers 
private void BtnUpdateSql_Click(object sender, EventArgs e)
{
    string VAL1;
    string VAL2;

    foreach (DataGridViewRow row in DgvWhistlSorted.Rows)
        if (string.IsNullOrEmpty(row.Cells[5].Value as string))
        {
        }
        else
        {
            for (int i = 0; i <= DgvWhistlSorted.Rows.Count - 2; i++)
            {
                VAL1 = DgvWhistlSorted.Rows[i].Cells[6].Value.ToString();
                VAL2 = DgvWhistlSorted.Rows[i].Cells[0].Value.ToString();

                var cnn = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

                using (var con = new SqlConnection(cnn))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "UPDATE dbo.json SET RowN = @VAL1  WHERE [A-order] = @VAL2";

                    cmd.Parameters.AddWithValue("@VAL1", VAL1);
                    cmd.Parameters.AddWithValue("@VAL2", VAL2);
                            
                    cmd.Connection = con;

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
        
        MessageBox.Show("dbo.json is ready");
}

Answer 1

I found that the fastest way would be to save the DATAGRIDVIEW to an SQL table and continue the process with - stored procedure + update query - between two tables - now it flies ... Thank you all

Answer 2

Create the connection ONCE...you're creating a new database connection each time through the loop! And in fact you do not need to create new command objects each time. You can reuse the command object because the parameters are the same. Just clear the params each time through the loop.

Also don't do the grid view count in the loop, set a variable for it.

string query = "UPDATE dbo.json SET RowN = @VAL1  WHERE [A-order] = @VAL2";
int counter = DgvWhistlSorted.Rows.Count - 2;
using (SqlConnection con = new SqlConnection(cnn))
{
      con.Open();
      using(SqlCommand cmd = new SqlCommand(cnn,query))
      {
         cmd.Parameters.Clear();
         //Do your loop in here
         for (int i = 0; i <= counter; i++)
         {
            VAL1 = DgvWhistlSorted.Rows[i].Cells[6].Value.ToString();
            VAL2 = DgvWhistlSorted.Rows[i].Cells[0].Value.ToString();
            cmd.Parameters.AddWithValue("@VAL1", VAL1);
            cmd.Parameters.AddWithValue("@VAL2", VAL2);                           
                          
            cmd.ExecuteNonQuery();
        }

     }
                
}

Answer 3

A better idea is to do this in one command, by passing all the data in a Table-Value Parameter (TVP):

First create the table type. I don't know your data types, so I'm guessing here. Make sure to match the types to the existing table.

CREATE TYPE dbo.OrderJson (
  Order int PRIMARY KEY,
  RowN nvarchar(max) NOT NULL
);

Then you can pass the whole thing in one batch. You need to create a DataTable to pass as the parameter, or you can use an existing datatable.

// UPDATE dbo.json with numbers 
private void BtnUpdateSql_Click(object sender, EventArgs e)
{
    var table = new DataTable {
        Columns = {
            { "Order", typeof(int) },
            { "RowN", typeof(string) },
        },
    };
    foreach (DataGridViewRow row in DgvWhistlSorted.Rows)
        if (!string.IsNullOrEmpty(row.Cells[5].Value as string))
            table.Rows.Add(DgvWhistlSorted.Rows[i].Cells[0].Value, DgvWhistlSorted.Rows[i].Cells[6].Value)

    const string query = @"
UPDATE dbo.json
SET RowN = t.RowN
FROM dbo.json j
JOIN @tmp t ON t.order = j.[A-order];
";
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["sql"].ConnectionString))
    using (var cmd = new SqlCommand(query, con))
    {
        cmd.Parameters.Add(new SqlParameter("@tmp", SqlDbType.Structured) { Value = table, TypeName = "dbo.OrderJson" });
        con.Open();
        cmd.ExecuteNonQuery();
    }
        
    MessageBox.Show("dbo.json is ready");
}

Answer 4

You shouldn't create the connection and command inside such a tight loop - create and open the connection and command ONCE before the loop, and in the loop, only set the parameter values and execute the query for each entry.

Something like this:

// UPDATE dbo.json with numbers 
private void BtnUpdateSql_Click(object sender, EventArgs e)
{
    string VAL1;
    string VAL2;

    // define connection string, query text *ONCE* before the loop  
    string cnn = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
    string updateQuery = "UPDATE dbo.json SET RowN = @VAL1  WHERE [A-order] = @VAL2;";
    
    // create connection and command *ONCE* 
    using (SqlConnection con = new SqlConnection(cnn))
    using (SqlCommand cmd = new SqlCommand(updateQuery, cnn))
    {
        // Define parameters - adapt as needed (don't know the actual datatype they have)
        cmd.Parameters.Add("@VAL1", SqlDbType.VarChar, 100);
        cmd.Parameters.Add("@VAL2", SqlDbType.VarChar, 100);

        // open connection ONCE, for all updates
        con.Open();
        
        foreach (DataGridViewRow row in DgvWhistlSorted.Rows)
        {
            if (!string.IsNullOrEmpty(row.Cells[5].Value as string))
            {
                for (int i = 0; i <= DgvWhistlSorted.Rows.Count - 2; i++)
                {
                    VAL1 = DgvWhistlSorted.Rows[i].Cells[6].Value.ToString();
                    VAL2 = DgvWhistlSorted.Rows[i].Cells[0].Value.ToString();

                    // set the values
                    cmd.Parameters["@VAL1"].Value = VAL1;
                    cmd.Parameters["@VAL2"].Value = VAL2;

                    // execute query
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // close connection after all updates are done
        con.Close();
    }

    MessageBox.Show("dbo.json is ready");
}