Original text: http://aspalliance.com/articleViewer.aspx?aId=477&pId=-1
Author: Andrew Mooney Translation: Mencius Chapter E [Download source code
// Check if the table exists, if it exists there will be a record in the DataTable
if(schemaTable.Rows.Count < 1)
sqlCmd = "create table " + tableName + " (";
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
//Add column text/string type length 100
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + " char(100),";
}
sqlCmd = sqlCmd .Substring(0,sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
oledbCmd.ExecuteNonQuery();
Add XML data to the database
// Traverse the Rows in the DataTable
foreach(DataRow dr in dataTableXml.Rows)
{
string sqlCmd = "insert into [" + tableName + "] (";
// Traverse the columns of Datatable
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
//Add column name
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";
// Traverse DataTable columns
for(int x = 0;x < dataTableXml.Columns.Count;x++)
{
//Add column value to row
sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";
}
sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
oledbCmd.ExecuteNonQuery();
}
Issues to note:
When testing, first download the source code, create an empty database, set the modifiable permissions Database.mdb, Database.xls, and create an empty directory
connection string example for FoxPro/dBase:
Access: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:datadatabase.mdb;
Excel: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:datadatabase.xls;Extended Properties=Excel 8.0;
FoxPro/dBase: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:data;Extended Properties=dBASE IV;
SQL Server: Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=database;User ID=sa;Password=;