[Visual Basic] VB and ADO to Return and update Record Set based XML
출처 : http://www.topxml.com/sql/articles/adoforxml/
Using VB and ADO to return and update Record Set based XML
by: Sean Grimaldi, Serious Consulting LLC.
This article is about making the most of ADO and XML until the complete Visual Basic.net, including ADO.net, becomes available. This article is focused on how you, as a developer, can use ADO and XML today; and get valuable skills that apply to Visual Basic.net. I also want to show how you can use ADO, XML, and XSL today, to improve the quality of your development.
I am taken aback almost daily, at the slow adoption many standards-based technologies face. XSLT is a fine example. XSLT reached candidate recommendation, meaning the w3c considered it stable and encouraged implementation, in November 2000. I am writing this article six months later, in a room packed with developers working for a worldwide software company. Not one of these developers, that I am aware of, has ever used XSLT in a production environment.
Part of the blame must go to how Microsoft spoiled us with Visual Basic. As a development tool, it is unparalleled. Think how simple it is to get an ADO Recordset; Visual Basic will even assist you in debugging the SQL query.
Unfortunately, at this time the situation is not at all the same with XSLT. Microsoft does not currently make a powerful IDE that includes a powerful XML editor. Visual Interdev, for example, mangles XSL. The best XML IDE available from Microsoft is XML Notepad (http://msdn.microsoft.com/xml/NOTEPAD/download.asp). As Visual Basic developers expecting features like statement completion, this is a tremendous loss.
We all suspect Visual Basic.net will provide us with the tools to make the use of these technologies simple, but until then, I would like to offer a few pointers to flatten the learning curve.
As an example of coming XML tools, Microsoft includes a XML Designer with color codes and statement and tag completion in Visual Studio.net.
Figure 1. Color-Coded XML Designer with Statement and Tag Completion
Since most developers prefer code samples to help illustrate technologies with which they are unfamiliar, this article loosely follows a sample. The sample scenario is simple and something you will likely run into frequently. I am interested in improvements you may make or ways in which you extend it to be more useful. Please email me your comments at my website, http://www.SeriousConsulting.com.
ADO
Every Visual Basic developer sooner or later becomes quite strong with ADO, because it is comparatively simple and allows access to every data source the developer is likely to encounter.
The disconnected Recordset is the staple of Microsoft web development. It is most likely the single programming practice that can increase the scalability of a Visual Basic and/or ASP driven web site more than any other. ADO makes it almost effortless. A disconnected Recordset is simply a Recordset that no longer has a live connection to the server. To disconnect a recordset, set it's ActiveConnection property equal to nothing. At this point it is safe to close the ADO connection object as well. In ADO.net by default the data set is disconnected.
|
'declare variables Dim objConn as new ADODB.Connection Dim objRS as new ADODB.Recorset Dim strConnectionString as String Dim strSQL as String 'open Connection objConn.open strConnectionString 'optimize record set properties objRS.CursorLocation = AdUseClient objRS.CursorType = adOpenStatic objRS.LockType = adLockBatchOptimistic 'open the record set objRS.open strSQL , objConn 'disconnect the record set and close Connection Set objRS.ActiveConnection = Nothing ObjConn.Close 'use the recordset While NOT objRS.eof 'do something Wend |
In a couple of lines data can be retrieved from almost any data source a developer is likely to encounter, including text files and many spreadsheets.
XML
XML is the future for almost all data transmission and data manipulation. Notice that I did not say anything about the Internet; XML is that huge!
Microsoft, IBM, and Sun support XML-based standards, which include SOAP and UDDI. These two XML based technologies promise interoperability at the method level across languages, domains, and platforms. This interoperability allows the RAD abilities of Visual Basic to be felt everywhere, ensuring Visual Basics' place as the most popular development language for years to come.
SOAP is a platform independent protocol, in this sense like HTTP, for exchanging messages in a decentralized, distributed-environment using XML. SOAP is more standards-based and platform-neutral than previous technologies like CIS, RDS, and Remote Scripting.
The Universal Discovery Description and Integration (UDDI) specifications define a way to publish and discover information about Web Services using XML. Since a major portion of new development will be Web Services based, UDDI is extremely important because it allows the appropriate functionality to be found on the Internet and utilized.
Best of all, this major shift to platform-independent Web Services as a new industry-wide software model, is fairly simple, all thanks to XML.
Although you can hand-code XML, as you can with HTML, it is easier to have the application do the work. Although this is really BizTalk's forte, here is an example of SQL Server 2000 doing the work, using the very handy FOR XML clause.
|
SELECT TOP 10 FirstName FROM Employees FOR XML AUTO Results in: <Employees FirstName="Nancy" EmployeeID="1"/> <Employees FirstName="Andrew" EmployeeID="2"/> <Employees FirstName="Janet" EmployeeID="3"/> <Employees FirstName="Margaret" EmployeeID="4"/> <Employees FirstName="Steven" EmployeeID="5"/> <Employees FirstName="Michael" EmployeeID="6"/> <Employees FirstName="Robert" EmployeeID="7"/> <Employees FirstName="Laura" EmployeeID="8"/> <Employees FirstName="Anne" EmployeeID="9"/> |
Unfortunately, the FOR XML AUTO clause does not support all SQL statements. It currently does not even support GROUP BY, for example, which is fairly common.
An alternative to the FOR XML clause is executing SQL statements using the URL. Although this sounds like the dream of every ASP developer, after all it takes the form of a URL with a SQL statement in the query string, URL access does not allow you to easily write generic Visual Basic functions that return XML from any data source.
|
|
How can you write a generic Visual Basic function that accepts standard SQL statements? You can not just tack FOR XML AUTO on the end of a regular SQL statement or stick it in the URL.
Even worse, you rely on other database vendors supporting FOR XML or SQL access using HTTP in exactly the same manner.
ADO equals effortless XML
Fortunately plain ADO does allow you to easily retrieve XML, today.
You can write a generic Visual Basic function, or even one in VB Script, based on the model of a SQL Select query as a parameter and a return value of a XML string. Furthermore it allows you to easily retrieve the XML, use it, and update it back to the database. This is functionality you are likely to use again and again, so it is valuable to put the functionality in a data access class.
Since there are so many interesting options available while writing this class, each worthy of its own article, I broke it down into three parts. Perhaps in future articles, I will explore each part in more depth.
Get the XML from ADO
Call this class ClsDataAccess so it is clear what we intend to use it for. The most obvious way to implement the class using a simple method GetRS, is still fairly awkward to use.
|
|
The awkwardness comes from having the ConnectionStringproperty exposed to the developer, especially if the password is reasonably secure.
|
"provider=SQLOLEDB; data source=db3x.seriousconsulting.com; initial catalog=Pubs; UID=mtsusr01; PWD=38710z2c7993F82" |
How secure can a password be if every developer, designer, content manager, and QA team member has access to a username and password with full read and write permissions on the production server? Furthermore, changing values within the connection string does not allow the connection to be pooled as efficiently, reducing scalability.
A solution that simplifies reuse and increases security is to set the connection strings as enumerated constants. Here I provide constants for only two database connections, but in practice, there may often be several.
|
'the db connectionstrings constants Public Enum eCONNECTIONSTRING CS_Pubs = 0 CS_NorthWind= 1 End Enum |
A simple private function in ClsDataAccess that applies values to the enumerated constants may look like this:
|
Private Function EvalEnumCS(ConnectionString) 'this is a helper function used by the methods to evaluate the enum value If ConnectionString = CS_ Pubs Then EvalEnumCS = "provider=SQLOLEDB; data source = db3x.seriouscons ulting.com; initial catalog = Pubs; UID = mtscsusr01; PWD =1eer5130q82F" ElseIf ConnectionString = CS_ NorthWind Then EvalEnumCS = "provide r=SQLOLEDB; data source=db2x.ohgolly.com; initial catalog = NorthWind; UID = mtscsusr02; PWD = c4883H55433" End If Exit Function |
By using enumerated constants the method is simpler to use; and by using a function to process the values of the constants, access can be changed in one place for the whole site. Additionally developers do not need to know any passwords, they create an object from the ClsDataAccess class, pass in a SQL query as a parameter and pick a constant.
Figure 2. Using Enumerated Constants for Database Access
A subtle point that you may have missed is that the connection strings are using a DNS as the datasource property. This allows the compiled component to be deployed to a development server, QA server, or a production server and hit the correct database without being recompiled. This is perhaps the best way to get around the serious shortcoming of having to recompile the class to access a different database in development versus production without having to pass the server name or DSN as an argument of the method. Remember, passing the Connection String or a DSN as a parameter does not make the GetRS method especially useful.
Here is the GetRS method of the ClsDataAccess, with the enumerated constants and a function to evaluate the constants incorporated into the class.
|
Public Function GetRS(ByVal strSQL As String,ByVal ConnectionString As eCONNECTIONSTRING = CS_Pubs) As ADODB.Recordset 'declare variables Dim objConn as new ADODB.Connection Dim objRS as new ADODB.Recorset Dim strConnectionString as String 'set the connection string = to the value of the constant strConnectionString = CStr(EvalEnumCS(ConnectionString)) 'open Connection objConn.open strConnectionString 'open the record 'continue as before |
This GetRS method is pretty good as a demonstrator, but it returns an ADO Recordset rather than XML. You may want to add error handling and keep it as a method in your data access class, adding a separate method to return XML.
Using Microsoft XML Parser (MSXML) 3.0 there are a couple of ways to get a XML document from the Recordset. Since the goal is to get XML, and XML is a string, I save the Recordset object to an ADO stream object as XML.
|
'once the record set is open objRS.save objADOStream, 1 'adPersistXML Set objRS.ActiveConnection = Nothing Conn.Close ' Put the Recordset Stream into a string variable. strXML = objADOStream.ReadText(-1) '-1=adReadAll 'return the XML string rather than the record set object |
With ADO 2.5 and later, Recordset objects can be persisted into any object that implements the IStream interface. The obvious choice is the ADO Stream object, although the ASP response object also implements IStream.
I use the ADO stream object, which is regrettably infrequently used, so the XML does not have to be saved as a document before we can work with it. The Recordset can also be saved directly into the XML DOM object. This is more scalable, as it skips having to save the Recordset into an ADO Stream and then loading the ADO Stream into the DOM object. For demonstration purposes, it is clearer to do the extra step. Now, you have a usable function that returns XML as a string in a standard ADO Recordset definition. ADO even allows persistence of hierarchical Recordsets into XML, so you are not especially limited.
Get HTML from the XML
Just to give you an idea of the many uses of XML, lets display the XML as HTML on a web page. Since XML is just data in a text format, you have to transform it with a presentation style before it can be displayed as HTML. If you write out the raw XML string to a web page the user's browser will not display anything. However, the XML is shown in Source View.
It may not be clear to you what you are seeing in the page source. The source is broken into two sections, a schema section followed by a data section.
The XML document starts with a definition of the record set schema and some additional Meta information. The simplest way to think of a schema is to think of the document as an instance of the schema in the same way an object is an instance of a class.
The actual Recordset data is contained in elements that look like this:
|
<z:row FirstName='Sean' LastName='Grimaldi' /> |
If you try working with the XML and experience unexpected problems remember that ADO, and almost all of Windows, is UTF-8 format, where as Java and XML are Unicode.
The Presentation = XML + XSL
In this instance we are using only using simple XSL, although you could also display the XML using Cascading Style Sheets (CSS).
XSL becomes complex quickly, so it may be useful to keep it basic at first. There are many excellent code samples and articles on XSL on the Internet, even if there are currently no tools as simple as Visual Basic for working with XSL.
It may be simpler to use a generic XSL style sheet since it will work on any ADO DB Recordset; and modify the XSL as the situation calls for. An excellent example of a generic XSL style sheet can be in Michele Vivoda topxml.com article http://www.topxml.com/xsl/articles/xsl_ado/.
An ASP page that combines the XML and the XSL could be very short.
|
'get the XSL style sheet styleFile = Server.MapPath("Genericxsl.xsl") 'create an XML DOM object, an XSL Dom object, and your Data Access object Set XMLDoc = Server.CreateObject("Microsoft.XMLDOM") Set XSLDoc = Server.CreateObject("Microsoft.XMLDOM") Set DataAccess = server.CreateObject("ProDataAccess.ClsDataAccess") XMLDoc.async = false 'load the XML returned from your GetRS method into the XML object XMLDoc.loadXML(DataAccess.GetRs(strSQL,1)) XSLDoc.async = false 'load the XSL stylesheet into the XSL object XSLDoc.load(styleFile) 'write our the HTML result of combining the two Response.Write XMLDoc.transformNode(XSLDoc) |
Combining the XML returning GetRS method with the XSL style sheet results in a plain little HTML table. Since XSL is much more powerful than what the example has demonstrated, please remember that you could do much, much, more with the XML. Microsoft Biztalk Server excels at generating XSL so one XML schema can be mapped to a different XML schema. This means that if you can map fields from one XML document to fields in a generic XML document, you can write more abstract code that deals with one generic case. This results in more code reuse, faster development, centralized error handling, and more maintainable code.
Get ADORS from the XML
Since you now know that XSL can transform one XML document into an XML document with a different schema, you may have realized that you can transform a very dissimilar XML document into an XML document that is valid to the ADO Recordset schema. This means you could convert the ADO schema based XML document back into a Recordset, which could be updated to the server/Database. This uses the source parameter of the ADO Recordset object.
|
|
Almost Done
As I stated, this article is about making the most of ADO and XML until the full-blown Visual Basic.net becomes available. It covers the Stream object, which is unfortunately commonly ignored. The sample began showing how ADO could be used to get an XML document from the database. Secondly the sample demonstrated how the XML document could be used to display records in an HTML table using XSLT. Lastly, the sample showed how to get the XML into a Recordset object so that the database could be updated.
Obviously this example could be extended quite a bit. As mentioned, to increase scalability the Stream object could be eliminated from the class. Other extensions, such as more sophisticated XSLT combined with CSS would be valuable in real-world situations.
I hope you reuse the concepts in this article often and to good measure.
Here are some resources you may find useful:
MSDN ADO Stream
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/mdao1ajx.htm
UDDI.org
http://www.uddi.org/
W3C SOAP
http://www.w3.org/TR/SOAP/
W3C XSL
http://www.w3.org/Style/XSL/
