Importing XML data into an Access database using ADO.NET

Given an existing Microsoft Access database with the schema already defined and an XML file with data only with no schema, import the XML data into the Access database. Let’s say that the XML file has data for just a single table to make it easy.

The first attempt can look as simple as this

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + TableName, connection);
DataTable table = new DataTable();
table.ReadXml(XMLPath);
adapter.Update(table);

This failed for a couple of reasons there was no table name and there was no schema information for the reader to put the xml data into. Now here is a second attempt.

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + TableName, connection);
DataTable table = new DataTable(TableName);
adapter.Fill(table); // Get the table schema only from db
XmlReadMode mode = table.ReadXml(XMLPath);
adapter.Update(table);

Here the table name is specified and the Fill command sets the schema information in the DataTable even if there are no rows in the actual database yet. The update still fails because the adapter does not have an INSERT command. The final attempt looks like this.

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DatabasePath);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + TableName, connection);
OleDbCommandBuilder cmdbuilder = new OleDbCommandBuilder(adapter);
adapter.InsertCommand = cmdbuilder.GetInsertCommand();
DataTable table = new DataTable(TableName);
adapter.Fill(table); // Get the table schema only from the actual database since there should not be any records yet.
XmlReadMode mode = table.ReadXml(XMLPath);
int rows = adapter.Update(table);
connection.Close();

This reads all of the data from the XML file into the existing table in the Access database. Three cheers!

Advertisements
Tagged with:
Posted in Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: