利用OleDb对象，将数据库中全部表转换成XML文件
2020-02-03 14:47:34
供稿：网友
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" />
