1) 方法一:利用XmlReader,以流的方式生成
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
Public Function GetArchives()Function GetArchives() As String
![](/Images/OutliningIndicators/InBlock.gif)
Dim cSql As String
![](/Images/OutliningIndicators/InBlock.gif)
Dim Constr_ZT As String = Application("ConStr") & "zt" & Session("LOGZTID") & "sysinfo"
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cycn As New SqlClient.SqlConnection(Constr_ZT)
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cycmd As SqlClient.SqlCommand = New SqlClient.SqlCommand
![](/Images/OutliningIndicators/InBlock.gif)
Dim CydSet As DataSet = New DataSet
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cyadp As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(cSql, Cycn)
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cysb As System.Text.StringBuilder = New System.Text.StringBuilder
![](/Images/OutliningIndicators/InBlock.gif)
Try
![](/Images/OutliningIndicators/InBlock.gif)
Cycn.Open()
![](/Images/OutliningIndicators/InBlock.gif)
Cycmd.Connection = Cycn
![](/Images/OutliningIndicators/InBlock.gif)
Cysb = New System.Text.StringBuilder
![](/Images/OutliningIndicators/InBlock.gif)
Cyadp.SelectCommand.Connection = Cycn
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
'客户档案
![](/Images/OutliningIndicators/InBlock.gif)
cSql = "SELECT * FROM customer_list ORDER BY frem"
![](/Images/OutliningIndicators/InBlock.gif)
Cyadp.SelectCommand.CommandText = cSql
![](/Images/OutliningIndicators/InBlock.gif)
Cyadp.Fill(CydSet, "cus")
![](/Images/OutliningIndicators/InBlock.gif)
'供应商
![](/Images/OutliningIndicators/InBlock.gif)
cSql = "SELECT * FROM vendor_list ORDER BY frem"
![](/Images/OutliningIndicators/InBlock.gif)
Cyadp.SelectCommand.CommandText = cSql
![](/Images/OutliningIndicators/InBlock.gif)
Cyadp.Fill(CydSet, "pro")
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
'为各表字段设置属性类型,如不设置将以节点的形式返回
![](/Images/OutliningIndicators/InBlock.gif)
Dim i, Ci As Integer
![](/Images/OutliningIndicators/InBlock.gif)
For i = 0 To CydSet.Tables.Count - 1 Step 1
![](/Images/OutliningIndicators/InBlock.gif)
For Ci = 0 To CydSet.Tables(i).Columns.Count - 1 Step 1
![](/Images/OutliningIndicators/InBlock.gif)
CydSet.Tables(i).Columns(CydSet.Tables(i).Columns(Ci).ColumnName).ColumnMapping = MappingType.Attribute
![](/Images/OutliningIndicators/InBlock.gif)
Next
![](/Images/OutliningIndicators/InBlock.gif)
Next
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
'生成xml数据
![](/Images/OutliningIndicators/InBlock.gif)
Cysb.Append(Replace(CydSet.GetXml, "NewDataSet", "root"))
![](/Images/OutliningIndicators/InBlock.gif)
CydSet.Reset()
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
Return Cysb.ToString()
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
Catch ex As Exception
![](/Images/OutliningIndicators/InBlock.gif)
If Cycn.State = ConnectionState.Open Then Cycn.Close()
![](/Images/OutliningIndicators/InBlock.gif)
Return ""
![](/Images/OutliningIndicators/InBlock.gif)
End Try
![](/Images/OutliningIndicators/InBlock.gif)
End Function
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
例:
For XML 的
SQL 语句表达式:
SELECT…
FROM…
WHERE…
ORDER BY…
FOR XML (raw | auto [, ELEMENTS] |
explicit)
[, XMLData]
[, BINARY base64])
“for xml raw”与“for xml auto”的区别:
n for xml raw:
¨ 在结果集中每一行有一个
元素
¨ 没有嵌套的子元素
¨ 在结果集中的列/值表示成
元素的特性/值
¨ 和数据库的结果集很类似,但表示成XML 格式,很容易转换
n for xml auto
¨ 数据库中的表/视图名表示成元素名
¨ 列名用来表示特性,如果加ELEMENTS 选项,列表示成子元素
¨ 支持嵌套的 XML 输出结果,结果集中的层次结构(元素的嵌套)基于 SELECT 子句中指定的列所标识的表顺序
¨ 可以用表和列别名来改变元素和特性名
2) 方法二:利用DataSet,以块的方式生成
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
Public Function A()Function A() As String
![](/Images/OutliningIndicators/InBlock.gif)
Dim cSql As String
![](/Images/OutliningIndicators/InBlock.gif)
Dim Constr_ZT As String = Application("CONSTR") & "zt" & Session("LOGZTID") & "sysinfo"
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cycn As New SqlClient.SqlConnection(Constr_ZT)
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cycmd As New SqlClient.SqlCommand("", Cycn)
![](/Images/OutliningIndicators/InBlock.gif)
Try
![](/Images/OutliningIndicators/InBlock.gif)
Cycn.Open()
![](/Images/OutliningIndicators/InBlock.gif)
Cycmd.Connection = Cycn
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cyreader As System.Xml.XmlReader = Cycmd.ExecuteXmlReader()
![](/Images/OutliningIndicators/InBlock.gif)
Dim Cysb As System.Text.StringBuilder = New System.Text.StringBuilder
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
Cysb.Append("")
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
cSql = "select * from customer_list order by frem for xml raw"
![](/Images/OutliningIndicators/InBlock.gif)
Cycmd.CommandText = cSql
![](/Images/OutliningIndicators/InBlock.gif)
Cyreader.Read()
![](/Images/OutliningIndicators/InBlock.gif)
While Not Cyreader1.EOF
![](/Images/OutliningIndicators/InBlock.gif)
Cysb.Append(Cyreader.ReadOuterXml())
![](/Images/OutliningIndicators/InBlock.gif)
End While
![](/Images/OutliningIndicators/InBlock.gif)
Cyreader.Close()
![](/Images/OutliningIndicators/InBlock.gif)
Cycn.Close()
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
cSql = "select * from vendor_list order by frem for xml raw"
![](/Images/OutliningIndicators/InBlock.gif)
Cycmd.CommandText = cSql
![](/Images/OutliningIndicators/InBlock.gif)
Cyreader.Read()
![](/Images/OutliningIndicators/InBlock.gif)
While Not Cyreader1.EOF
![](/Images/OutliningIndicators/InBlock.gif)
Cysb.Append(Cyreader.ReadOuterXml())
![](/Images/OutliningIndicators/InBlock.gif)
End While
![](/Images/OutliningIndicators/InBlock.gif)
Cyreader.Close()
![](/Images/OutliningIndicators/InBlock.gif)
Cycn.Close()
![](/Images/OutliningIndicators/InBlock.gif)
Cysb.Append("")
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
Return Cysb.ToString() ''形成XML数据岛
![](/Images/OutliningIndicators/InBlock.gif)
Catch ex As Exception
![](/Images/OutliningIndicators/InBlock.gif)
If Cycn.State = ConnectionState.Open Then Cycn.Close()
![](/Images/OutliningIndicators/InBlock.gif)
Return ""
![](/Images/OutliningIndicators/InBlock.gif)
End Try
![](/Images/OutliningIndicators/InBlock.gif)
End Function
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
3) 方法一与方法二的比较
n 方法一适合小批量的数据查询,当数据量较大的时候,方法二的速度会快些。
n 对 web 服务器资源要求:方法一低,方法二高。