[Download Code]
Now that we know the database has a table that can hold the XML file we start inserting records. Again we create and open an OleDbConnection to the database and iterate through the rows in the DataTable from the XML file, creating a SQL insert command for each row of data. Do this by iterating through the DataTable columns to get the column names and values for the current row. Finally, use the OleDbCommand ExecuteNonQuery method to insert each row into the database.
// Iterate rows in the DataTable
foreach(DataRow dr in dataTableXml.Rows)
{
// Create the sql insert command for each row
string sqlCmd = "insert into [" + tableName + "] (";
// Iterate the datatable columns
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
// Add the column name
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";
// Iterate the DataTable columns
for(int x = 0;x < dataTableXml.Columns.Count;x++)
{
// Add the column value for this row
sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";
// Create and execute the insert command
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
oledbCmd.ExecuteNonQuery();
}