What is the best way to update a MsAccess table in .NET

Question

When several fields in a MSAccess table need to be updated (For instance Salary=Salary*Factor, SomeNumber=GetMyBusinessRuleOn(SomeNumber) etc...),and the update should affect every record in a table, which technique would you use?

I have just started to implement this with DataSets, but got stuck (http://stackoverflow.com/questions/858798/updating-and-persisting-dataset-problem)

But maybe this isn't even the ideal way to handle this kind of batch update?

Note : the updates don't have to be on disconnected data first, so a dataset is not necessary.

UPDATE :

• One command won't do, I need some kind of recordset or cursor to cycle through the records

Answer 1

I would just use a ODBCConnection/ODBCCommand and use a SQL Update query.

There is a JET Database driver that you should be able to use to establish a database connection to a MSAccess database using the ODBCConeection object.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\PathTo\\Your_Database_Name.mdb; User Id=admin; Password=";

using (OdbcConnection connection = 
           new OdbcConnection(connectionString))
{
    // Suppose you wanted to update the Salary column in a table
    // called Employees
    string sqlQuery = "UPDATE Employees SET Salary = Salary * Factor";

    OdbcCommand command = new OdbcCommand(sqlQuery, connection);

    try
    {
        connection.Open();
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    // The connection is automatically closed when the
    // code exits the using block.
}

You could use these websites to help you generate a connection string:

• http://www.connectionstrings.com/
• http://www.sqlstrings.com/

EDIT - Example for using a data reader to cycle through records in order to aply the business rule

I should note that the following example could be improved in certain ways (especially if the database driver supports parameterized queries). I only wanted to give a relatively simple example to illustrate the concept.

using (OdbcConnection connection = 
           new OdbcConnection(connectionString))
{
    int someNumber;
    int employeeID;
    OdbcDataReader dr = null;
    OdbcCommand selCmd = new OdbcCommand("SELECT EmployeeID, SomeNumber FROM Employees", connection);

    OdbcCommand updateCmd = new OdbcCommand("", connection);

    try
    {
        connection.Open();
        dr = selCmd.ExecuteReader();
        while(dr.Read())
        {
            employeeID = (int)dr[0];
            someNumber = (int)dr[1];
            updateCmd.CommandText = "UPDATE Employees SET SomeNumber= " + GetBusinessRule(someNumber) + " WHERE employeeID = " + employeeID;

            updateCmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
       // Don't forget to close the reader when we're done
       if(dr != null)
          dr.Close();
    }
    // The connection is automatically closed when the
    // code exits the using block.
}

Answer 2

Sounds like you just need an update statement:

http://msdn.microsoft.com/en-us/library/bb221186.aspx

You can use the OleDb Provider for this.