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");
}

CodePudding user response：

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");
}

CodePudding user response：

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");
}