I need to replicate a MS Access database in .NET via an XML file that I receive over the internet. The target database has to be exactly the same as the original (same content and same PKs).
Since I have many tables to copy, I use an OleDbDataAdapter that generates the insert queries for me. This works well, even for tables that have an auto-generated Guid as their primary key. The INSERT command generated by the OleDbCommandBuilder (cmdBuilder.GetInsertCommand()) has the Guid field in its parameters, so the guid inserted is the same as the source DB.
The only problem I have is with tables that have an autonumber integer as their primary key. The INSERT command that is generated does not include the PK field, so the number inserted does not match the source database when there are holes in the sequence.
Is there a property hidden somewhere that will include the autonumber column when the insert command is generated?
Here's my code:
public void InsertContentFromXml(string tableName, string xml)
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]", _connection))
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
OleDbCommand insertCommand = builder.GetInsertCommand();
// insertCommand does not have the autonumber column in its insert query
}
}
The table has 3 fields:
IdReport [integer/autonumber]
ReportName [Text]
ReportType [Text]
The insert command generates 2 parameters: ReportName and ReportType
Thank you
