What is the best way to update the MsAccess table in .NET.
When there is a need to update multiple fields in an MSAccess table (e.g. Salary = Salary * Factor, SomeNumber = GetMyBusinessRuleOn (SomeNumber), etc.) and the update needs to affect every record in the table, what technique would you use?
I just started to implement this with DataSets but got stuck ( Updating and keeping the dataset issue )
But maybe this isn't even the ideal way to handle such a batch update?
Note. Updates do not need to be disabled first, so a dataset is not required.
UPDATE:
- One command won't work, I need some sort of recordset or cursor to cycle through the records
a source to share
I would just use ODBCConnection / ODBCCommand and use SQL Update query.
There is a JET Database driver that you can use to establish a database connection to the 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 can use these websites to help you build your connection string:
EDIT - Example of using a data reader to wrap around records to match a business rule
It should be noted that the following example can be improved in certain ways (especially if the database driver supports parameterized queries). I just 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.
}
a source to share
It looks like you just need the update operator:
http://msdn.microsoft.com/en-us/library/bb221186.aspx
You can use the OleDb provider for this .
a source to share