CODEWITHSUNDEEP
c#ms-access
user3600400
user3600400

Reputation: 181

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

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.

Upvotes: 1

Views: 1001

Answers (1)

Steve
Steve

Reputation: 216273

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

Upvotes: 4

Related Questions