利用OleDb对象，将数据库中全部表转换成XML文件
作者：孟宪会 出自：【孟宪会之精彩世界】 发布日期：2003年4月16日 12点0分0秒
ASP.NET Dataset让你在页面中使用XML格式的文件提供了便利。如果您的数据全部都在一个数据库中，该如何进行转换呢？如果你的数据库与OleDb兼容的话，下面就看看如何把数据库中的所有表转换成XML文件。文件名字与数据库中的表的名字相同。本代码包含两个版本：C#版本和VB.NET版本。
C#版本：
1@ Page Language="C#"
1@ import Namespace="System"
1@ import Namespace="System.Data"
1@ import Namespace="System.Data.OleDb"
1<script runat="server">
2void Page_Load(object sender, EventArgs e) {
3if (!IsPostBack) {
4TextBox1.Text = "PROVIDER=SQLOLEDB; Data Source=.; Initial Catalog=pubs;User ID=sa;Password=;";
5Label1.Text = "";
6}
7}
8void CreateXml(object sender, EventArgs e) {
9OleDbConnection dataConn = new OleDbConnection(TextBox1.Text);
10Uri Path = Request.Url;
11String ServerUrl = Path.ToString();
12ServerUrl = ServerUrl.Substring(0,ServerUrl.LastIndexOf("/") +1 );
13try {
14Literal1.Text = "已经在你的相同目录下创建了一下文件：
15" ;
16dataConn.Open();
17DataTable schemaTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});
18for(int I = 0; I < schemaTable.Rows.Count; I++){
19OleDbDataAdapter dbAdapter= new OleDbDataAdapter("select * from [" + schemaTable.Rows[I].ItemArray[2].ToString() + "]",dataConn);
20DataSet tableData = new DataSet();
21dbAdapter.Fill(tableData,schemaTable.Rows[I].ItemArray[2].ToString());
22tableData.WriteXml(HttpContext.Current.Server.MapPath(schemaTable.Rows[I].ItemArray[2].ToString() + ".xml"));
23Literal1.Text = Literal1.Text + "<a href='" + ServerUrl + schemaTable.Rows[I].ItemArray[2].ToString() + ".xml'>" + schemaTable.Rows[I].ItemArray[2].ToString() + ".xml</a>
24";
25}
26}
27catch(Exception ex) {
28Label1.Text = ex.Message.ToString();
29}
30finally {
31dataConn.Close();
32}
33}
34</script>
1<html>
2<head>
3<title>leDb2XML：数据库转换成XML文件例子</title>
4</head>
5<body bgcolor="#FFFFFF">
6<center>
7<h2><font face="宋体">OleDb2XML：数据库转换成XML文件例子</font>
8</h2>
9</center>
10<form runat="server">
11<font face="宋体">
12<p align="center">请输入OleDb连接字符串，然后点“生成XML文件”按钮。</p>
13<p align="center">
14<asp:textbox id="TextBox1" runat="server" width="600"></asp:textbox>
15</p>
16<p align="center">
17<asp:button id="Button1" onclick="CreateXml" runat="server" text="生成XML文件"></asp:button>
18</p>
19<p align="center">
20<asp:label font-bold="True" forecolor="Red" id="Label1" runat="server"></asp:label>
21</p>
22<p align="center">
23<asp:literal id="Literal1" runat="server"></asp:literal>
24</p>
25</font>
26</form>
27</body>
28</html>
VB.NET版本：
1@ Page Language="VB"
1@ import Namespace="System"
1@ import Namespace="System.Data"
1@ import Namespace="System.Data.OleDb"
1<script runat="server">
2
3Sub Page_Load(sender As Object, e As EventArgs)
4If Not IsPostBack Then
5TextBox1.Text = "Provider=SqlOLEDB; Data Source=.; Initial Catalog=pubs;User ID=sa;Password=;"
6Label1.Text = ""
7End If
8End Sub
9
10Sub CreateXml(sender As Object, e As EventArgs)
11Dim dataConn As New OleDbConnection(TextBox1.Text)
12Dim Path As Uri = Request.Url
13Dim ServerUrl As String = Path.ToString()
14ServerUrl = ServerUrl.Substring(0,ServerUrl.LastIndexOf("/") + 1 )
15Try
16Literal1.Text = "已经在你的相同目录下创建了一下文件：
17"
18dataConn.Open()
19Dim schemaTable As DataTable = dataConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
20Dim I As Integer
21For I = 0 To schemaTable.Rows.Count - 1
22Dim dbAdapter As New OleDbDataAdapter("select * from [" + schemaTable.Rows(I).ItemArray(2).ToString() + "]", dataConn)
23Dim tableData As New DataSet()
24dbAdapter.Fill(tableData, schemaTable.Rows(I).ItemArray(2).ToString())
25tableData.WriteXml(HttpContext.Current.Server.MapPath((schemaTable.Rows(I).ItemArray(2).ToString() + ".xml")))
26Literal1.Text = Literal1.Text + "<a href='" + ServerUrl + schemaTable.Rows(I).ItemArray(2).ToString() + ".xml'>" + schemaTable.Rows(I).ItemArray(2).ToString() + ".xml</a>
27"
28Next I
29Catch ex As Exception
30Label1.Text = ex.Message.ToString()
31Finally
32dataConn.Close()
33End Try
34End Sub
35
36</script>
1<html>
2<head>
3<title>leDb2XML：数据库转换成XML文件例子</title>
4</head>
5<body bgcolor="#FFFFFF">
6<center>
7<h2><font face="宋体">OleDb2XML：数据库转换成XML文件例子</font>
8</h2>
9</center>
10<form runat="server">
11<font face="宋体">
12<p align="center">请输入OleDb连接字符串，然后点“生成XML文件”按钮。</p>
13<p align="center">
14<asp:textbox id="TextBox1" runat="server" width="600"></asp:textbox>
15</p>
16<p align="center">
17<asp:button id="Button1" onclick="CreateXml" runat="server" text="生成XML文件"></asp:button>
18</p>
19<p align="center">
20<asp:label font-bold="True" forecolor="Red" id="Label1" runat="server"></asp:label>
21</p>
22<p align="center">
23<asp:literal id="Literal1" runat="server"></asp:literal>
24</p>
25</font>
26</form>
27</body>
28</html>
对于数据量大的数据库，处理可能会需要更多的时间，因此有可能需要更改machine.config文件中processModel的responseDeadlockInterval 属性，比如为5分钟，默认为3分钟。
1<processmodel 00:03:00="" clientconnectedcheck="0:00:05" comauthenticationlevel="Connect" comimpersonationlevel="Impersonate" cpumask="0xffffffff" default="" enable="true" idletimeout="Infinite" is="" loglevel="Errors" maxiothreads="25" maxworkerthreads="25" memorylimit="60" password="AutoGenerate" requestlimit="Infinite" requestqueuelimit="5000" responsedeadlockinterval="00:05:00" responserestartdeadlockinterval="00:09:00" restartqueuelimit="10" shutdowntimeout="0:00:05" timeout="Infinite" username="machine" webgarden="false"></processmodel>