本文主要是介绍利用OleDb将xml文件的数据导入到数据库中，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Html代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="xmlole.aspx.cs" Inherits="xmlole" %>
<!DOCTYPE html PUBLIC "-//W 3C //DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
<asp:Label ID="Message" runat="server" Text="Label"></asp:Label>
<asp:TextBox ID="textBoxOleDb" Text="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/ttt/Database.mdb" runat="server"></asp:TextBox>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/tt/database.xls;Extended Properties=Excel 8.0">Excel</asp:ListItem>
<asp:ListItem Value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/tt/database.mdb">Access</asp:ListItem>
<asp:ListItem Value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:/tt;Extended Properties=dBASE IV">FoxPro/dBase</asp:ListItem>
<asp:ListItem Value="Provider=SQLOLEDB;Data Source=.; Initial Catalog=htmldemo;User ID=sa;Password=123">SQL Server</asp:ListItem>
</asp:DropDownList></div>
</form>
</body>
</html>
CS代码：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
public partial class xmlole : System.Web.UI.Page
{
#region 设置变量
protected System.Data.DataSet dataSetXml;
protected System.Data.DataTable dataTableXml;
protected string tableName;
protected OleDbConnection oledbConn = new OleDbConnection(DropDownList1.SelectedValue);
#endregion
/// <summary>
/// 检查与xml文件名对应的表是否存在,不存在则自动按照xml文件中的节点创建字段
/// </summary>
private void TableCheck()
{
try
{
oledbConn.Open();
DataTable schemaTable = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] { null, null, tableName, "TABLE" });
String sqlCmd = "";
if (schemaTable.Rows.Count < 1)
{
sqlCmd = "create table " + tableName + " (";
for (int i = 0; i < dataTableXml.Columns.Count; i++)
{
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + " char(100),";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd, oledbConn);
oledbCmd.ExecuteNonQuery();
}
}
catch
{
Message.Text = "The table could not be created or database does not exist.";
}
finally
{
oledbConn.Close();
}
}
/// <summary>
/// 向创建的表中插入数据
/// </summary>
private void TableInsert()
{
try
{
oledbConn.Open();
foreach (DataRow dr in dataTableXml.Rows)
{
string sqlCmd = "insert into [" + tableName + "] (";
for (int i = 0; i < dataTableXml.Columns.Count; i++)
{
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") values (";
for (int x = 0; x < dataTableXml.Columns.Count; x++)
{
sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'", "''") + "',";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd, oledbConn);
oledbCmd.ExecuteNonQuery();
}
}
catch
{
Message.Text = "There was an error adding the XML data to the table.";
}
finally
{
oledbConn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Message.Text = "";
dataSetXml = new DataSet();
dataTableXml = new DataTable();
try
{
dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXml.Text));
dataTableXml = dataSetXml.Tables[0];
tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);
}
catch
{
Message.Text = "Cannot find find XML file or XML is wrong format.";
}
if (dataTableXml.Rows.Count > 0)
{
TableCheck();
TableInsert();
if (Message.Text == String.Empty)
{
Message.Text = "Inserting XML into OleDb data source is complete.";
}
}
}
}
几个常用的数据库的Provider：
Access: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/database.mdb"
Excel:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/data/database.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="
这篇关于利用OleDb将xml文件的数据导入到数据库中的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
