利用OleDb对象，将数据库中全部表转换成XML文件

    ASP.NET Dataset让你在页面中使用XML格式的文件提供了便利。如果您的数据全部都在一个数据库中，该如何进行转换呢？如果你的数据库与OleDb兼容的话，下面就看看如何把数据库中的所有表转换成XML文件。文件名字与数据库中的表的名字相同。本代码包含两个版本：C#版本和VB.NET版本。

C#版本：[测试]


<%@ Page Language="C#" %> <%@ import Namespace="System" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <script runat="server"> void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { TextBox1.Text = "PROVIDER=SQLOLEDB; Data Source=.; Initial Catalog=pubs;User ID=sa;Password=;"; Label1.Text = ""; } } void CreateXml(object sender, EventArgs e) { OleDbConnection dataConn = new OleDbConnection(TextBox1.Text); Uri Path = Request.Url; String ServerUrl = Path.ToString(); ServerUrl = ServerUrl.Substring(0,ServerUrl.LastIndexOf("/") +1 ); try { Literal1.Text = "已经在你的相同目录下创建了一下文件：<br/>" ; dataConn.Open(); DataTable schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"}); for(int i = 0; i < schemaTable.Rows.Count; i++){ OleDbDataAdapter dbAdapter= new OleDbDataAdapter("select * from [" + schemaTable.Rows[i].ItemArray[2].ToString() + "]",dataConn); DataSet tableData = new DataSet(); dbAdapter.Fill(tableData,schemaTable.Rows[i].ItemArray[2].ToString()); tableData.WriteXml(HttpContext.Current.Server.MapPath(schemaTable.Rows[i].ItemArray[2].ToString() + ".xml")); Literal1.Text = Literal1.Text + "<a href='" + ServerUrl + schemaTable.Rows[i].ItemArray[2].ToString() + ".xml'>"; Literal1.Text = Literal1.Text + schemaTable.Rows[i].ItemArray[2].ToString() + ".xml</a><br/>"; } } catch(Exception ex) { Label1.Text = ex.Message.ToString(); } finally { dataConn.Close(); } } </script> <html> <head> <title>leDb2XML：数据库转换成XML文件例子</title> </head> <body bgcolor="#FFFFFF"> <center> <h2><font face="宋体">OleDb2XML：数据库转换成XML文件例子</font> </h2> </center> <form runat="server" ID="Form1"> <font face="宋体"> <p align="center">请输入OleDb连接字符串，然后点“生成XML文件”按钮。</p> <p align="center"> <asp:TextBox id="TextBox1" runat="server" Width="600"></asp:TextBox> </p> <p align="center"> <asp:Button id="Button1" οnclick="CreateXml" runat="server" Text="生成XML文件"></asp:Button> </p> <p align="center"> <asp:Label id="Label1" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label> </p> <p align="center"> <asp:Literal id="Literal1" runat="server"></asp:Literal> </p> </font> </form> </body> </html>


VB.NET版本：[测试] 


<%@ import Namespace="System.Data.OleDb" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System" %> <%@ Page Language="VB" %> <HTML> <HEAD> <title>leDb2XML：数据库转换成XML文件例子</title> <script runat="server"> Sub Page_Load(sender As Object, e As EventArgs) If Not IsPostBack Then 'TextBox1.Text = "Provider=SqlOLEDB; Data Source=.; Initial Catalog=pubs;User ID=sa;Password=;" Label1.Text = "" TextBox1.Text = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb") End If End Sub Sub CreateXml(sender As Object, e As EventArgs) Dim dataConn As New OleDbConnection(TextBox1.Text) Dim Path As Uri = Request.Url Dim ServerUrl As String = Path.ToString() ServerUrl = ServerUrl.Substring(0,ServerUrl.LastIndexOf("/") + 1 ) Try Literal1.Text = "已经在你的相同目录下创建了一下文件：<br/>" dataConn.Open() Dim schemaTable As DataTable schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"}) Dim i As Integer For i = 0 To schemaTable.Rows.Count - 1 Dim dbAdapter As New OleDbDataAdapter("select * from [" + schemaTable.Rows(i).ItemArray(2).ToString() + "]", dataConn) Dim tableData As New DataSet() dbAdapter.Fill(tableData, schemaTable.Rows(i).ItemArray(2).ToString()) tableData.WriteXml(HttpContext.Current.Server.MapPath((schemaTable.Rows(i).ItemArray(2).ToString() + ".xml"))) Literal1.Text = Literal1.Text + "<a href='" + ServerUrl + schemaTable.Rows(i).ItemArray(2).ToString() + ".xml'>" Literal1.Text = Literal1.Text + schemaTable.Rows(i).ItemArray(2).ToString() + ".xml</a><br/>" Next i Catch ex As Exception Label1.Text = ex.Message.ToString() Finally dataConn.Close() End Try End Sub </script> </HEAD> <body bgcolor="#ffffff"> <h2 align=center><font face="宋体">OleDb2XML：数据库转换成XML文件例子</font></h2> <form runat="server" ID="Form1"> <font face="宋体"> <p align="center">请输入OleDb连接字符串，然后点“生成XML文件”按钮。</p> <p align="center"> <asp:TextBox id="TextBox1" runat="server" Width="600"></asp:TextBox> </p> <p align="center"> <asp:Button id="Button1" οnclick="CreateXml" runat="server" Text="生成XML文件"></asp:Button> </p> <p align="center"> <asp:Label id="Label1" runat="server" Font-Bold="True" ForeColor="Red"></asp:Label> </p> <p align="center"> <asp:Literal id="Literal1" runat="server"></asp:Literal> </p> </font> </form> </body> </HTML>


对于数据量大的数据库，处理可能会需要更多的时间，因此有可能需要更改machine.config文件中processModel的responseDeadlockInterval 属性，比如为5分钟，默认为3分钟。 


<processModel
    enable="true"
    timeout="Infinite"
    idleTimeout="Infinite"
    shutdownTimeout="0:00:05"
    requestLimit="Infinite"
    requestQueueLimit="5000"
    restartQueueLimit="10"
    memoryLimit="60"
    webGarden="false"
    cpuMask="0xffffffff"
    userName="machine"
    password="AutoGenerate"
    logLevel="Errors"
    clientConnectedCheck="0:00:05"
    comAuthenticationLevel="Connect"
    comImpersonationLevel="Impersonate"
    responseRestartDeadlockInterval="00:09:00"
    responseDeadlockInterval="00:05:00" default is 00:03:00
    maxWorkerThreads="25"
    maxIoThreads="25" />