More efficient way of running multiple update queries on an Access database?

Question

I have multiple queries like this right now which involve updating different fields of the same row in an Access database:

//Update database
string updatequery = "UPDATE [table] SET [Last10Attempts] = ? WHERE id = ?";
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + "database.accdb");
con.Open();
OleDbDataAdapter da = new OleDbDataAdapter(updatequery, con);

var accessUpdateCommand = new OleDbCommand(updatequery, con);
accessUpdateCommand.Parameters.AddWithValue("Last10Attempts", last10attempts);
accessUpdateCommand.Parameters.AddWithValue("ID", currentid + 1);
da.UpdateCommand = accessUpdateCommand;
da.UpdateCommand.ExecuteNonQuery();

//update last10attemptssum
updatequery = "UPDATE [table] SET [Last10AttemptsSum] = ? WHERE id = ?";
accessUpdateCommand = new OleDbCommand(updatequery, con);
accessUpdateCommand.Parameters.AddWithValue("Last10AttemptsSum", counter);
accessUpdateCommand.Parameters.AddWithValue("ID", currentid + 1);
da.UpdateCommand = accessUpdateCommand;
da.UpdateCommand.ExecuteNonQuery();

//increment totalquestionattempt
updatequery = "UPDATE [table] SET [total-question-attempts] = ? WHERE id = ?";
accessUpdateCommand = new OleDbCommand(updatequery, con);                            
accessUpdateCommand.Parameters.AddWithValue("total-question-attempts", questionattempts + 1);
accessUpdateCommand.Parameters.AddWithValue("ID", currentid + 1);
da.UpdateCommand = accessUpdateCommand;
da.UpdateCommand.ExecuteNonQuery();

con.Close();

I was wondering if there is a more efficient way of running these update queries - ie. combining them into one query.

Steve · Accepted Answer · 2014-07-16 07:42:37Z

4

There is no need to use an OleDbDataAdapter in your context above. You could use a simple command and execute it

Said that, an Update sql statement can update more than one field. Just write

 string updatequery = @"UPDATE [table] SET [Last10Attempts] = ?, 
                                           [Last10AttemptsSum] = ?,
                                           [total-question-attempts] = ?
                              WHERE id = ?";
 using(OleDbConnection con = new OleDbConnection(.........))
 using(OleDbCommand cmd = new OleDbCommand(updatequery, con))
 {
     con.Open();
     cmd.Parameters.AddWithValue("Last10Attempts", last10attempts);
     cmd.Parameters.AddWithValue("Last10AttemptsSum", counter);
     cmd.Parameters.AddWithValue("total-question-attempts", questionattempts + 1);
     cmd.Parameters.AddWithValue("ID", currentid + 1);
     cmd.ExecuteNonQuery();
 }

The only thing to keep present when working with OleDb is the fact that the parameters are used in the exact order in which the parameter placeholder appears in the command text. So they should be added to the parameter collection in the order expected by the command text

edited Jul 16, 2014 at 7:42

answered Jul 15, 2014 at 23:38

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

Fixed a couple of commas missing and removed an extraneous brace

Collectives™ on Stack Overflow

More efficient way of running multiple update queries on an Access database?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related