用Xml2OleDb将XML文件插入到数据库

作者：Eve Cole 更新时间：2009-06-24 16:44:45

原文：http://aspalliance.com/articleViewer.aspx?aId=477&pId=-1

作者：Andrew Mooney 翻译：孟子E章 [下载源代码

// 检查表是否存在，如果存在在DataTable中将有一条记录
if(schemaTable.Rows.Count < 1)


sqlCmd = "create table " + tableName + " (";
for(int i = 0;i < dataTableXml.Columns.Count;i++)
{
    // 添加列text/string type 长度 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();

添加XML数据到数据库

// 遍历DataTable中的Rows
foreach(DataRow dr in dataTableXml.Rows)
{
    string sqlCmd = "insert into [" + tableName + "] (";
    // 遍历Datatable的列
    for(int i = 0;i < dataTableXml.Columns.Count;i++)
    {
        // 添加column name
        sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
    }
    sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ") values (";
    // 遍历 DataTable columns
    for(int x = 0;x < dataTableXml.Columns.Count;x++)
    {
        // 添加column value到row
        sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'","''") + "',";
    }
    sqlCmd = sqlCmd.Substring(0,sqlCmd.Length - 1) + ");";
    OleDbCommand oledbCmd = new OleDbCommand(sqlCmd,oledbConn);
    oledbCmd.ExecuteNonQuery();
}

注意的问题：
测试时先下载源代码，创建一个空的数据库，设置可修改权限Database.mdb,Database.xls，对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=;