利用OleDb對象，將數據庫中全部表轉換成XML文件
2024-09-05 20:55:52
供稿：網友
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'>" + 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">
<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" onclick="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版本：
<%@ page language="vb" %>
<%@ import namespace="system" %>
<%@ import namespace="system.data" %>
<%@ import namespace="system.data.oledb" %>
<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 = ""
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 = 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'>" + 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>
<html>
<head>
<title>ledb2xml：數據庫轉換成xml文件例子</title>
</head>
<body bgcolor="#ffffff">
<center>
<h2><font face="宋體">oledb2xml：數據庫轉換成xml文件例子</font>
</h2>
</center>
<form runat="server">
<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" onclick="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" />
