當(dāng)對現(xiàn)有數(shù)據(jù)庫的數(shù)據(jù)進行分析時,經(jīng)常需要對某一部分的數(shù)據(jù)進行分析.此時,使用 1.SQL查詢分析器? 但其往往不直觀,查找某個關(guān)鍵字又需要重新執(zhí)行新的SQL. 2.SQLXML模板? 但又不一定有權(quán)限建立新的虛擬目錄,且某些SQL語句SQLXML模板不支持 數(shù)據(jù)拆離時也有相似問題。 尤其當(dāng)不同網(wǎng)絡(luò),不同環(huán)境,需要重新導(dǎo)入數(shù)據(jù),進行分析或拆離,困難尤為明顯。 能不能有一種方法,可以將數(shù)據(jù)脫離于數(shù)據(jù)庫進行分析,需要時再導(dǎo)入到數(shù)據(jù)庫中? XML是個很好的選擇! ADO本身支持數(shù)據(jù)到XML的轉(zhuǎn)換,只需要對其格式進行解析,成為自己的XML文件通用格式,就可以進行本地分析 而對通用XML格式進行數(shù)據(jù)庫映射,就可完成數(shù)據(jù)重新導(dǎo)入數(shù)據(jù)庫的工作. 下面是一個ADO數(shù)據(jù)(表的基本數(shù)據(jù))與XML數(shù)據(jù)間的相互轉(zhuǎn)換的類(ASP實現(xiàn)),初步完成表數(shù)據(jù)的導(dǎo)入、導(dǎo)出。 通用表間關(guān)系映射(通過XSD描述),考慮之中,希望各位賜教指點,不勝感激. 一個調(diào)用類的例子: example.asp <!--#include file="transformData.asp"--> <% Dim aSQL(1,1) Dim oXMLData '====== 連接數(shù)據(jù)庫過程 ====== '獲得數(shù)據(jù)庫連接對象 oDbConn '====== 連接數(shù)據(jù)庫過程 ====== 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
' // 當(dāng)對象屬性有默認值(default())時,可以不用在賦值 Sub Export() ' // 導(dǎo)出數(shù)據(jù) oXMLData.aSQlData = aSQL ' 必須 2維SQL語句數(shù)組 oXMLData.bIsSave = 1 ' default(1) 是否保存為XML文件 oXMLData.bIsOutput = 1 ' default(0) 是否顯示XML數(shù)據(jù) oXMLData.sSaveFileName = "Data.xml" ' default(當(dāng)前時間加隨機數(shù)) 如果保存XML數(shù)據(jù),XML文件名稱 oXMLData.sSaveFilePath = "" ' default("") 如果保存XML數(shù)據(jù),XML文件路徑(相對路徑) oXMLData.sEncoding = "gb2312" ' default("gb2312") XML文件編碼類型 oXMLData.Export (oDbConn) ' // 導(dǎo)出數(shù)據(jù)過程 IF (oXMLData.nErrCode<>0) Then ' nErrCode(錯誤代碼)為0,運行成功 Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode) 'nErrCode(錯誤代碼),通過方法GetErrExegesis() 獲得注釋 End IF End Sub Sub Import() ' // 導(dǎo)入數(shù)據(jù) oXMLData.sXMLFile = "Data.xml" ' 必須 數(shù)據(jù)源XML文件(包含相對路徑) oXMLData.sVacancyCols = "nLabelID" ' 必須 指定某些字段的值可以不導(dǎo)入(屏蔽字段) ' 格式 "nID,dDate" (以‘,’分隔字段) oXMLData.Import (oDbConn) IF (oXMLData.nErrCode=0) Then Response.Write "數(shù)據(jù)導(dǎo)入成功!" Else Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode) End IF End Sub %> 類的代碼: TransformData.asp <% Class TransformData '***************************************************** ' Copyright (c) 2003 ' 創(chuàng) 建 人 : moonpiazza ' 日 期 : 2003.5.21 ' 描 述 : ADO數(shù)據(jù)與XML數(shù)據(jù)間的轉(zhuǎn)換(ASP實現(xiàn)) ' 版 本 : 1.0 ' 功 能 : ADO數(shù)據(jù)(表的基本數(shù)據(jù))與XML數(shù)據(jù)間的相互轉(zhuǎn)換 ' 待 改 進 : 表間數(shù)據(jù)的關(guān)聯(lián)性(通用),數(shù)據(jù)量大時速度問題 ' ' 版 權(quán) : 歡迎改進,翻版不究 :_) ' '*****************************************************
'***************************************************** ' 公共方法: 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 ' 狀態(tài): 可寫 ' 類型: 2維數(shù)組 ' 描述: SQL語句數(shù)組,1維是表名稱,2維是相應(yīng)SQL語句 '***************************************************** Public Property Let aSQlData(ByRef p_aSQlData) m_aSQlData = p_aSQlData End Property
'***************************************************** ' 屬性: bIsSave ' 狀態(tài): 可寫 ' 類型: 數(shù)字(0,1) default(1) ' 描述: 導(dǎo)出數(shù)據(jù)時,是否保存為XML文件 '***************************************************** Public Property Let bIsSave(ByRef p_bIsSave) m_bIsSave = Cint(p_bIsSave) End Property
'***************************************************** ' 屬性: bIsOutput ' 狀態(tài): 可寫 ' 類型: 數(shù)字(0,1) default(0) ' 描述: 導(dǎo)出數(shù)據(jù)時,是否顯示XML數(shù)據(jù) '***************************************************** Public Property Let bIsOutput(ByRef p_bIsOutput) m_bIsOutput = Cint(p_bIsOutput) End Property
'***************************************************** ' 屬性: sSaveFileName ' 狀態(tài): 可寫,可讀 ' 類型: 字符串 default(GetRndFileName()) ' 描述: 導(dǎo)出數(shù)據(jù)時,如果保存XML數(shù)據(jù),XML文件名稱 '***************************************************** Public Property Let sSaveFileName(ByRef p_sSaveFileName) m_sSaveFileName = p_sSaveFileName End Property Public Property Get sSaveFileName() sSaveFileName = m_sSaveFileName End Property
'***************************************************** ' 屬性: sSaveFilePath ' 狀態(tài): 可寫,可讀 ' 類型: 字符串 default("") ' 描述: 導(dǎo)出數(shù)據(jù)時,如果保存XML數(shù)據(jù),XML文件路徑(相對路徑) '***************************************************** Public Property Let sSaveFilePath(ByRef p_sSaveFilePath) m_sSaveFilePath = p_sSaveFilePath End Property Public Property Get sSaveFilePath() sSaveFilePath = m_sSaveFilePath End Property
'***************************************************** ' 屬性: sXMLFile ' 狀態(tài): 可寫 ' 類型: 字符串 ' 描述: 導(dǎo)入數(shù)據(jù)時,數(shù)據(jù)源XML文件(包含相對路徑) '***************************************************** Public Property Let sXMLFile(ByRef p_sXMLFile) m_sXMLFile = p_sXMLFile End Property
'***************************************************** ' 屬性: sVacancyCols ' 狀態(tài): 可寫 ' 類型: 字符串 default("") ' 格式 "nID,dDate" (以‘,’分隔字段) ' 描述: 導(dǎo)入數(shù)據(jù)時,指定某些字段的值可以不導(dǎo)入(屏蔽字段) '***************************************************** Public Property Let sVacancyCols(ByRef p_sVacancyCols) m_sVacancyCols = "," & p_sVacancyCols & "," End Property
'***************************************************** ' 屬性: nErrCode ' 狀態(tài): 可讀 ' 類型: 數(shù)字 default(0) ' 描述: 錯誤代碼,可通過方法GetErrExegesis(ByRef p_nErrCode) 獲得注釋 '***************************************************** Public Property Get nErrCode() nErrCode = m_nErrCode End Property
'***************************************************** ' 屬性: sEncoding ' 狀態(tài): 可寫 ' 類型: 字符串 default("gb2312") ' 描述: XML文件編碼類型 '***************************************************** Public Property Let sEncoding(ByRef p_sEncoding) m_sEncoding = p_sEncoding End Property
'***************************************************** ' 屬性: sImportSQL ' 狀態(tài): 可讀 ' 類型: 字符串 default("gb2312") ' 描述: 導(dǎo)入數(shù)據(jù)時,生成的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
'============================= 數(shù)據(jù)導(dǎo)出 Begin ============================= '***************************************************** ' 過程: Export(ByRef p_oDbConn) ' 描述: 導(dǎo)出數(shù)據(jù) ' 參數(shù): ' p_oDbConn: 數(shù)據(jù)庫連接對象 ' '***************************************************** 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
'***************************************************** ' 函數(shù): GetRndFileName() ' 描述: 獲得隨機名稱,由當(dāng)前時間和7位隨機數(shù)字構(gòu)成 '***************************************************** 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
'***************************************************** ' 函數(shù): 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
'***************************************************** ' 函數(shù): GetDataXML(ByRef p_sTableName, ByRef p_sSQL, ByRef p_oDbConn) ' 描述: 執(zhí)行單條SQL,獲得數(shù)據(jù)轉(zhuǎn)換后的XML ' 參數(shù): ' 1.p_sTableName : 表的名稱 ' 2.p_sSQL : 讀取數(shù)據(jù)的SQl語句 ' 3.p_oDbConn : 數(shù)據(jù)庫連接對象 ' '***************************************************** 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格式的字符串到文件 ' 參數(shù): ' 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格式的字符串到瀏覽器 ' 參數(shù): ' p_sXMLStr : XML格式的字符串 '***************************************************** Private Sub ResponseXML(ByRef p_sXMLStr) Response.CharSet = m_sEncoding Response.ContentType = "text/xml" Response.write p_sXMLStr End Sub
'============================= 數(shù)據(jù)導(dǎo)出 End ============================= '============================= 數(shù)據(jù)導(dǎo)入 Begin ============================= '***************************************************** ' 過程: Import(ByRef p_oDbConn) ' 描述: 導(dǎo)入數(shù)據(jù) ' 參數(shù): ' p_oDbConn: 數(shù)據(jù)庫連接對象 ' '***************************************************** 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
'***************************************************** ' 函數(shù): GetImportSQL(ByRef p_oDataBase) ' 描述: 獲得將XML數(shù)據(jù)轉(zhuǎn)換為SQL后的字符串 ' 參數(shù): ' p_oDataBase : XML文件的根節(jié)點 ' '***************************************************** 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 '============================= 數(shù)據(jù)導(dǎo)入 End =============================
'***************************************************** ' 函數(shù): GetErrExegesis(ByRef p_nErrCode) ' 描述: 獲得錯誤代碼的注釋 ' 參數(shù): ' p_oDataBase : XML文件的根節(jié)點 ' '***************************************************** 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語句數(shù)組 不正確!" Case m_nErrCode_XMLDOM sXSLStr = "不能創(chuàng)建XML文檔,服務(wù)器必須支持MSXML!" Case m_nErrCode_ReadData sXSLStr = "讀取數(shù)據(jù)庫數(shù)據(jù)發(fā)生錯誤! " & "<BR>" sXSLStr = sXSLStr & " 請檢查 " & " " sXSLStr = sXSLStr & "1.數(shù)據(jù)庫是否已連接 " & " " sXSLStr = sXSLStr & "2.語句是否正確 " Case m_nErrCode_WriteData sXSLStr = "寫入數(shù)據(jù)庫數(shù)據(jù)發(fā)生錯誤! " & "<BR>" sXSLStr = sXSLStr & " 請檢查 " & " " sXSLStr = sXSLStr & "1.數(shù)據(jù)庫是否已連接 " & " " sXSLStr = sXSLStr & "2.SQL語句是否正確 " & "<BR>" sXSLStr = sXSLStr & "SQL語句 " & "<BR><BR>" sXSLStr = sXSLStr & "" & m_sImportSQL Case m_nErrCode_Save sXSLStr = "不能保存XML文檔,請檢查是否對該目錄或文件有' 寫入權(quán)限 ' !" Case m_nErrCode_EnsFile sXSLStr = "不能讀取XM數(shù)據(jù),XML文件不存在 ' !" sXSLStr = sXSLStr & "文件:" & m_sXMLFile Case m_nErrCode_ErrFile sXSLStr = "不能讀取XM數(shù)據(jù),XML文件格式錯誤 ' !" sXSLStr = sXSLStr & "文件:" & m_sXMLFile Case Else sXSLStr = "未知錯誤 !" End Select GetErrExegesis = "<BR>" & sXSLStr & "<BR>" End Function End Class %>
|