ADO數據與XML數據間的轉換的類(ASP實現)_自動測試腳本語言

ADO數據與XML數據間的轉換的類(ASP實現)

發表于：2007-06-30來源：作者：點擊數：標簽：

當對現有數據庫的數據進行分析時，經常需要對某一部分的數據進行分析.此時，使用 1.SQL查詢分析器？但其往往不直觀，查找某個關鍵字又需要重新執行新的SQL. 2.SQLXML模板？但又不一定有權限建立新的虛擬目錄，且某些SQL語句SQLXML模板不支持數據拆離時也

當對現有數據庫的數據進行分析時，經常需要對某一部分的數據進行分析.此時，使用
1.SQL查詢分析器？
但其往往不直觀，查找某個關鍵字又需要重新執行新的SQL.
2.SQLXML模板？
但又不一定有權限建立新的虛擬目錄，且某些SQL語句SQLXML模板不支持

數據拆離時也有相似問題。
尤其當不同網絡，不同環境，需要重新導入數據，進行分析或拆離，困難尤為明顯。
能不能有一種方法，可以將數據脫離于數據庫進行分析，需要時再導入到數據庫中？
XML是個很好的選擇！
ADO本身支持數據到XML的轉換，只需要對其格式進行解析，成為自己的XML文件通用格式，就可以進行本地分析
而對通用XML格式進行數據庫映射，就可完成數據重新導入數據庫的工作.

下面是一個ADO數據(表的基本數據)與XML數據間的相互轉換的類(ASP實現)，初步完成表數據的導入、導出。
通用表間關系映射(通過XSD描述),考慮之中，希望各位賜教指點，不勝感激.

一個調用類的例子：
example.asp


<%
Dim aSQL(1,1)
Dim oXMLData

‘’====== 連接數據庫過程 ======
‘’獲得數據庫連接對象 oDbConn
‘’====== 連接數據庫過程 ======

aSQL(0,0) = "PubLable"
aSQL(0,1) = "Select * from PubLabel where cLabelName like ‘’%abc%‘’ Order by nLabelID"
aSQL(1,0) = "PubUser"
aSQL(1,1) = "Select * from PubUser where cUserName like ‘’%abc%‘’ Order by nUserID"

set oXMLData = New TransformData

Call Export()
‘’Call Import()
set oXMLData = nothing

‘’ // 當對象屬性有默認值(default())時,可以不用在賦值

Sub Export() ‘’ // 導出數據

oXMLData.aSQlData = aSQL
‘’ 必須 2維SQL語句數組

oXMLData.bIsSave = 1
‘’ default(1) 是否保存為XML文件

oXMLData.bIsOutput = 1
‘’ default(0) 是否顯示XML數據

oXMLData.sSaveFileName = "Data.xml"
‘’ default(當前時間加隨機數) 如果保存XML數據，XML文件名稱

oXMLData.sSaveFilePath = ""
‘’ default("") 如果保存XML數據，XML文件路徑(相對路徑)

oXMLData.sEncoding = "gb2312"
‘’ default("gb2312") XML文件編碼類型

oXMLData.Export (oDbConn)
‘’ // 導出數據過程

IF (oXMLData.nErrCode<>0) Then ‘’ nErrCode(錯誤代碼)為0，運行成功
Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode)
‘’nErrCode(錯誤代碼)，通過方法GetErrExegesis() 獲得注釋
End IF

End Sub

Sub Import() ‘’ // 導入數據

oXMLData.sXMLFile = "Data.xml" ‘’ 必須數據源XML文件(包含相對路徑)

oXMLData.sVacancyCols = "nLabelID" ‘’ 必須指定某些字段的值可以不導入(屏蔽字段)
‘’ 格式 "nID,dDate" (以‘，’分隔字段)

oXMLData.Import (oDbConn)

IF (oXMLData.nErrCode=0) Then
Response.Write "數據導入成功！"
Else
Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode)
End IF

End Sub
%>

類的代碼：
TransformData.asp

<%
Class TransformData

‘’*****************************************************
‘’ Copyright (c) 2003
‘’ 創建人 : moonpiazza
‘’ 日期 : 2003.5.21
‘’ 描述 : ADO數據與XML數據間的轉換(ASP實現)
‘’ 版本 : 1.0
‘’ 功能 : ADO數據(表的基本數據)與XML數據間的相互轉換
‘’ 待改進 : 表間數據的關聯性(通用)，數據量大時速度問題
‘’
‘’ 版權 : 歡迎改進，翻版不究 :_)
‘’
‘’*****************************************************

‘’*****************************************************
‘’ 公共方法： Export, Import, GetErrExegesis
‘’*****************************************************

‘’============================= 公共變量 End =============================
Private m_oXMLDOM
Private m_oXSLDOM
‘’============================= 公共變量 Begin =============================

‘’============================= 錯誤代碼定義 Begin =============================
Private m_nErrCode_NotArray
Private m_nErrCode_XMLDOM
Private m_nErrCode_ReadData
Private m_nErrCode_WriteData
Private m_nErrCode_Save
Private m_nErrCode_EnsFile
Private m_nErrCode_ErrFile
‘’============================= 錯誤代碼定義 End =============================

‘’============================= 屬性定義 Begin =============================

Private m_aSQlData
Private m_bIsSave
Private m_bIsOutput
Private m_sSaveFileName
Private m_sSaveFilePath
Private m_sXMLFile
Private m_sVacancyCols
Private m_nErrCode
Private m_sEncoding
Private m_sImportSQL

‘’*****************************************************
‘’ 屬性： aSQlData
‘’ 狀態：可寫
‘’ 類型： 2維數組
‘’ 描述： SQL語句數組，1維是表名稱，2維是相應SQL語句
‘’*****************************************************
Public Property Let aSQlData(ByRef p_aSQlData)
m_aSQlData = p_aSQlData
End Property

‘’*****************************************************
‘’ 屬性： bIsSave
‘’ 狀態：可寫
‘’ 類型：數字(0,1) default(1)
‘’ 描述：導出數據時，是否保存為XML文件
‘’*****************************************************
Public Property Let bIsSave(ByRef p_bIsSave)
m_bIsSave = Cint(p_bIsSave)
End Property

‘’*****************************************************
‘’ 屬性： bIsOutput
‘’ 狀態：可寫
‘’ 類型：數字(0,1) default(0)
‘’ 描述：導出數據時，是否顯示XML數據
‘’*****************************************************
Public Property Let bIsOutput(ByRef p_bIsOutput)
m_bIsOutput = Cint(p_bIsOutput)
End Property

‘’*****************************************************
‘’ 屬性： sSaveFileName
‘’ 狀態：可寫，可讀
‘’ 類型：字符串 default(GetRndFileName())
‘’ 描述：導出數據時，如果保存XML數據，XML文件名稱
‘’*****************************************************
Public Property Let sSaveFileName(ByRef p_sSaveFileName)
m_sSaveFileName = p_sSaveFileName
End Property

Public Property Get sSaveFileName()
sSaveFileName = m_sSaveFileName
End Property

‘’*****************************************************
‘’ 屬性： sSaveFilePath
‘’ 狀態：可寫，可讀
‘’ 類型：字符串 default("")
‘’ 描述：導出數據時，如果保存XML數據，XML文件路徑(相對路徑)
‘’*****************************************************
Public Property Let sSaveFilePath(ByRef p_sSaveFilePath)
m_sSaveFilePath = p_sSaveFilePath
End Property

Public Property Get sSaveFilePath()
sSaveFilePath = m_sSaveFilePath
End Property

‘’*****************************************************
‘’ 屬性： sXMLFile
‘’ 狀態：可寫
‘’ 類型：字符串
‘’ 描述：導入數據時，數據源XML文件(包含相對路徑)
‘’*****************************************************
Public Property Let sXMLFile(ByRef p_sXMLFile)
m_sXMLFile = p_sXMLFile
End Property

‘’*****************************************************
‘’ 屬性： sVacancyCols
‘’ 狀態：可寫
‘’ 類型：字符串 default("")
‘’ 格式 "nID,dDate" (以‘，’分隔字段)
‘’ 描述：導入數據時，指定某些字段的值可以不導入(屏蔽字段)
‘’*****************************************************
Public Property Let sVacancyCols(ByRef p_sVacancyCols)
m_sVacancyCols = "," & p_sVacancyCols & ","
End Property

‘’*****************************************************
‘’ 屬性： nErrCode
‘’ 狀態：可讀
‘’ 類型：數字 default(0)
‘’ 描述：錯誤代碼，可通過方法GetErrExegesis(ByRef p_nErrCode) 獲得注釋
‘’*****************************************************
Public Property Get nErrCode()
nErrCode = m_nErrCode
End Property

‘’*****************************************************
‘’ 屬性： sEncoding
‘’ 狀態：可寫
‘’ 類型：字符串 default("gb2312")
‘’ 描述： XML文件編碼類型
‘’*****************************************************
Public Property Let sEncoding(ByRef p_sEncoding)
m_sEncoding = p_sEncoding
End Property

‘’*****************************************************
‘’ 屬性： sImportSQL
‘’ 狀態：可讀
‘’ 類型：字符串 default("gb2312")
‘’ 描述：導入數據時，生成的SQL語句
‘’*****************************************************
Public Property Get sImportSQL()
sImportSQL = m_sImportSQL
End Property
‘’============================= 屬性定義 End =============================

‘’*****************************************************
‘’ 初始化類
‘’*****************************************************
Private Sub Class_Initialize()

Server.ScriptTimeout = 1000

m_nErrCode_NotErr = 0
m_nErrCode_NotArray = 1
m_nErrCode_XMLDOM = 2
m_nErrCode_ReadData = 3
m_nErrCode_WriteData= 4
m_nErrCode_Save = 5
m_nErrCode_EnsFile = 6
m_nErrCode_ErrFile = 7

m_bIsSave = 1
m_bIsOutput = 0
m_sSaveFilePath = ""
m_sSaveFileName = ""
m_sXMLFile = ""
m_sVacancyCols = ""
m_nErrCode = m_nErrCode_NotErr
m_sEncoding = "gb2312"

End Sub

‘’*****************************************************
‘’ 注銷類
‘’*****************************************************
Private Sub Class_Terminate()
Set m_oXMLDOM = Nothing
Set m_oXSLDOM = Nothing
End Sub

‘’============================= 數據導出 Begin =============================

‘’*****************************************************
‘’ 過程： Export(ByRef p_oDbConn)
‘’ 描述：導出數據
‘’ 參數：
‘’ p_oDbConn：數據庫連接對象
‘’
‘’*****************************************************
Public Sub Export(ByRef p_oDbConn)
Dim nI, nMaxI
Dim sTableName, sSQL
Dim sDataXML, sXSLStr
Dim sXMLStr

If (Not IsArray(m_aSQlData)) Then
m_nErrCode = m_nErrCode_NotArray
Exit Sub
End If

ON ERROR RESUME NEXT

Set m_oXSLDOM = Server.CreateObject("Microsoft.XMLDOM")
Set m_oXMLDOM = Server.CreateObject("Microsoft.XMLDOM")

If Err.Number <>0 Then
m_nErrCode = m_nErrCode_XMLDOM
Exit Sub
End If

sXSLStr = GetXSL()

m_oXMLDOM.async = false
m_oXSLDOM.async = false
m_oXSLDOM.loadxml(sXSLStr)

sDataXML = "<?xml version=‘’1.0‘’ encoding=‘’" & m_sEncoding & "‘’?>"
sDataXML = sDataXML & "<DataBase>"

nMaxI = Ubound(m_aSQlData, 1)

For nI=0 To nMaxI

sTableName = m_aSQlData(nI, 0)

If (Len(sTableName) > 0) Then

sSQL = m_aSQlData(nI, 1)
sXMLStr = GetDataXML(sTableName, sSQL, p_oDbConn)

IF (m_nErrCode > m_nErrCode_NotErr) Then
Exit Sub
End IF

sDataXML = sDataXML & sXMLStr
End If

Next

sDataXML = sDataXML & "</DataBase>"

IF (m_bIsOutput) Then
Call ResponseXML(sDataXML)
End IF

IF (m_bIsSave) Then
Call SaveDataXML(sDataXML)
End IF

End Sub

‘’*****************************************************
‘’ 函數： GetRndFileName()
‘’ 描述：獲得隨機名稱，由當前時間和7位隨機數字構成
‘’*****************************************************
Private Function GetRndFileName()
Dim nMax, nMin
Dim sRnd, sDate

Randomize

nMin = 1000000
nMax = 9999999

sRnd = Int( ( (nMax - nMin + 1) * Rnd ) + nMin)
sDate = Replace( Replace( Replace( now(), "-", "") , ":", ""), " ", "")

GetRndFileName = "_" & sDate & sRnd & ".xml"

End Function

‘’*****************************************************
‘’ 函數： GetXSL()
‘’ 描述：獲得XSL文件字符串
‘’*****************************************************
Private Function GetXSL()
Dim sXSLStr

sXSLStr = ""
sXSLStr = sXSLStr & "<?xml version=‘’1.0‘’ encoding=‘’" & m_sEncoding & "‘’?>"
sXSLStr = sXSLStr & "<xsl:stylesheet version=‘’1.0‘’ xmlns:xsl=‘’http://www.w3.org/1999/XSL/Transform‘’ xmlns:s=‘’uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882‘’ xmlns:dt=‘’uuid:C2F41010-65B3-11d1-A29F-00AA00C14882‘’ xmlns:rs=‘’urn:schemas-microsoft-com:rowset‘’ xmlns:z=‘’#RowsetSchema‘’>"
sXSLStr = sXSLStr & "<xsl:output omit-xml-declaration=‘’yes‘’/>"
sXSLStr = sXSLStr & "<xsl:template match=‘’/‘’>"
sXSLStr = sXSLStr & "<xsl:for-each select=‘’/xml/rs:data/z:row‘’>"
sXSLStr = sXSLStr & "<xsl:element name=‘’Row‘’>"
sXSLStr = sXSLStr & "<xsl:for-each select=‘’@*‘’>"
sXSLStr = sXSLStr & "<xsl:attribute name=‘’{name()}‘’>"
sXSLStr = sXSLStr & "<xsl:value-of select=‘’.‘’/>"
sXSLStr = sXSLStr & "</xsl:attribute>"
sXSLStr = sXSLStr & "</xsl:for-each>"
sXSLStr = sXSLStr & "</xsl:element>"
sXSLStr = sXSLStr & "</xsl:for-each>"
sXSLStr = sXSLStr & "</xsl:template>"
sXSLStr = sXSLStr & "</xsl:stylesheet>"

GetXSL = sXSLStr

End Function

‘’*****************************************************
‘’ 函數： GetDataXML(ByRef p_sTableName, ByRef p_sSQL, ByRef p_oDbConn)
‘’ 描述：執行單條SQL，獲得數據轉換后的XML
‘’ 參數：
‘’ 1.p_sTableName ：表的名稱
‘’ 2.p_sSQL ：讀取數據的SQl語句
‘’ 3.p_oDbConn ：數據庫連接對象
‘’
‘’*****************************************************
Private Function GetDataXML(ByRef p_sTableName, ByRef p_sSQL, ByRef p_oDbConn)
Dim oRecordset
Dim sXMLStr, sCleanXML
Dim nEnsData

ON ERROR RESUME NEXT

nEnsData = 0

Set oRecordset = p_oDbConn.Execute(p_sSQL)
If Err.Number <>0 Then
m_nErrCode = m_nErrCode_ReadData
Exit Function
End If

IF (Not oRecordset.eof) Then
nEnsData = 1
End IF

IF (nEnsData = 1) Then
oRecordset.save m_oXMLDOM, 1

oRecordset.close
Set oRecordset = Nothing

sCleanXML = m_oXMLDOM.transformNode(m_oXSLDOM)

sXMLStr = "<" & p_sTableName & ">"
sXMLStr = sXMLStr & sCleanXML
sXMLStr = sXMLStr & "</" & p_sTableName & ">"
Else
sXMLStr = "<" & p_sTableName & "/>"
End IF

GetDataXML = sXMLStr

End Function

‘’*****************************************************
‘’ 過程： SaveDataXML(ByRef p_sXMLStr)
‘’ 描述：保存XML格式的字符串到文件
‘’ 參數：
‘’ p_sXMLStr ： XML格式的字符串
‘’*****************************************************
Private Sub SaveDataXML(ByRef p_sXMLStr)
Dim sFileInfo

If (Len(m_sSaveFileName) = 0) Then
m_sSaveFileName = GetRndFileName()
End If

If (Len(m_sSaveFilePath) = 0) Then
sFileInfo = m_sSaveFileName
Else
IF (Right(m_sSaveFilePath,1) = "/")Then
sFileInfo = m_sSaveFilePath & m_sSaveFileName
Else
sFileInfo = m_sSaveFilePath & "/" & m_sSaveFileName
End IF
End If

m_oXMLDOM.loadxml(p_sXMLStr)

ON ERROR RESUME NEXT

m_oXMLDOM.save ( Server.MapPath(sFileInfo) )
If Err.Number <>0 Then
m_nErrCode = m_nErrCode_Save
Exit Sub
End If

End Sub

‘’*****************************************************
‘’ 過程： ResponseXML(ByRef p_sXMLStr)
‘’ 描述：輸出XML格式的字符串到瀏覽器
‘’ 參數：
‘’ p_sXMLStr ： XML格式的字符串
‘’*****************************************************
Private Sub ResponseXML(ByRef p_sXMLStr)
Response.CharSet = m_sEncoding
Response.ContentType = "text/xml"
Response.write p_sXMLStr
End Sub

‘’============================= 數據導出 End =============================

‘’============================= 數據導入 Begin =============================

‘’*****************************************************
‘’ 過程： Import(ByRef p_oDbConn)
‘’ 描述：導入數據
‘’ 參數：
‘’ p_oDbConn：數據庫連接對象
‘’
‘’*****************************************************
Public Sub Import(ByRef p_oDbConn)
Dim oRootNode

If (Len(m_sXMLFile) < 1) Then
m_nErrCode = m_nErrCode_EnsFile
Exit Sub
End If

ON ERROR RESUME NEXT

Set m_oXMLDOM = Server.CreateObject("Microsoft.XMLDOM")

If Err.Number <>0 Then
m_nErrCode = m_nErrCode_XMLDOM
Exit Sub
End If

m_oXMLDOM.async = false

m_oXMLDOM.load( Server.MapPath(m_sXMLFile) )
If Err.Number <>0 Then
m_nErrCode = m_nErrCode_EnsFile
Exit Sub
End If

If (Len(m_oXMLDOM.xml) < 1) Then
m_nErrCode = m_nErrCode_ErrFile
Exit Sub
End If

Set oRootNode = m_oXMLDOM.documentElement
Set m_oXMLDOM = Nothing

m_sImportSQL = GetImportSQL(oRootNode)

Set oRootNode = Nothing

Call p_oDbConn.Execute(m_sImportSQL)
If Err.Number <>0 Then
m_nErrCode = m_nErrCode_WriteData
Exit Sub
End If

End Sub

‘’*****************************************************
‘’ 函數： GetImportSQL(ByRef p_oDataBase)
‘’ 描述：獲得將XML數據轉換為SQL后的字符串
‘’ 參數：
‘’ p_oDataBase ： XML文件的根節點
‘’
‘’*****************************************************
Private Function GetImportSQL(ByRef p_oDataBase)
Dim oTable, oRow, oDatas, oData
Dim sColNames, sColValues
Dim sColName
Dim sSQL, sTransactionSQL

sSQL = ""

For Each oTable In p_oDataBase.childNodes

For Each oRow In oTable.childNodes

Set oDatas = oRow.selectNodes("@*")

sColNames = ""
sColValues = ""

For Each oData In oDatas

sColName = oData.nodeName

If ( Instr( Lcase(Cstr(m_sVacancyCols)), Lcase(Cstr("," & sColName & ",")) ) < 1) Then
sColNames = sColNames & sColName & ", "
sColValues = sColValues & "‘’" & oData.nodeValue & "‘’, "
End If

Next

sColNames = "(" & Left(sColNames,Len(sColNames)-2) & ") "
sColValues = "(" & Left(sColValues,Len(sColValues)-2) & ") "

sSQL = sSQL & " Insert Into " & oTable.nodeName
sSQL = sSQL & " " & sColNames & " Values " & sColValues & " ; "

Next

Next

Set oData = Nothing
Set oDatas = Nothing
Set oRow = Nothing
Set oTable = Nothing

sTransactionSQL = "Set Xact_Abort On; "
sTransactionSQL = sTransactionSQL & " Begin Transaction; "
sTransactionSQL = sTransactionSQL & sSQL
sTransactionSQL = sTransactionSQL & " Commit Transaction; "
sTransactionSQL = sTransactionSQL & " Set Xact_Abort Off; "

GetImportSQL = sTransactionSQL
End Function

‘’============================= 數據導入 End =============================

‘’*****************************************************
‘’ 函數： GetErrExegesis(ByRef p_nErrCode)
‘’ 描述：獲得錯誤代碼的注釋
‘’ 參數：
‘’ p_oDataBase ： XML文件的根節點
‘’
‘’*****************************************************
Public Function GetErrExegesis(ByRef p_nErrCode)
Dim sExegesis
Dim nErrCode

nErrCode = Cint(p_nErrCode)

Select Case (nErrCode)

Case m_nErrCode_NotErr
sXSLStr = "運行成功！"

Case m_nErrCode_NotArray
sXSLStr = "屬性: SQL語句數組不正確！"

Case m_nErrCode_XMLDOM
sXSLStr = "不能創建XML文檔，服務器必須支持MSXML！"

Case m_nErrCode_ReadData
sXSLStr = "讀取數據庫數據發生錯誤！ " & " "
sXSLStr = sXSLStr & " 請檢查 " & " "
sXSLStr = sXSLStr & "1.數據庫是否已連接 " & " "
sXSLStr = sXSLStr & "2.語句是否正確 "

Case m_nErrCode_WriteData
sXSLStr = "寫入數據庫數據發生錯誤！ " & " "
sXSLStr = sXSLStr & " 請檢查 " & " "
sXSLStr = sXSLStr & "1.數據庫是否已連接 " & " "
sXSLStr = sXSLStr & "2.SQL語句是否正確 " & " "
sXSLStr = sXSLStr & "SQL語句 " & " "
sXSLStr = sXSLStr & "" & m_sImportSQL

Case m_nErrCode_Save
sXSLStr = "不能保存XML文檔，請檢查是否對該目錄或文件有‘’ 寫入權限 ‘’ ！"

Case m_nErrCode_EnsFile
sXSLStr = "不能讀取XM數據，XML文件不存在 ‘’ ！"
sXSLStr = sXSLStr & "文件：" & m_sXMLFile

Case m_nErrCode_ErrFile
sXSLStr = "不能讀取XM數據，XML文件格式錯誤 ‘’ ！"
sXSLStr = sXSLStr & "文件：" & m_sXMLFile

Case Else
sXSLStr = "未知錯誤！"

End Select

GetErrExegesis = " " & sXSLStr & " "

End Function

End Class
%>

原文轉自：http://www.kjueaiud.com

軟件測試 > 測試開發技術 > 軟件測試開發語言 > 自動測試腳本語言 >