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

sogotobj

浏览: 656603 次
性别:
来自: 北京

最近访客更多访客>>

meteor_shower

u012363178

xy106520

keep_alived

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1200)

社区版块

存档分类

XML .net ASP.net VB.NET ASP

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

作者：孟宪会出自：【孟宪会之精彩世界】

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" />

分享到：