I was tasked with implementing a .NET Web Service to provide access to our application data by outside entities. The Web Service must support clients written in .NET languages, Visual Basic 6.0, and non-Microsoft languages. All access to our database must be through stored procedures, so we did not need to support running direct SQL queries. The design was to use a single method that accepts an XML string that describes the stored procedure to run, its parameters, and how to return the data if any data is returned (
DataSet XML or
Recordset XML). The implementation used the .NET data access objects to return a
DataSet and ADODB objects to return a
Recordset. The returned data was then serialized to an XML string and returned to the caller. At first, this worked like a charm, however, as soon as the load testing started, it was obvious, I had a problem. At the web server, a single user returning a large set of data utilized 70% of the CPU; two simultaneous users pegged it at 100%, five or more users - some would get timeouts! This was very bad!
I knew going in that there would be some performance penalty for using ADODB in the .NET code, but I never dreamed it would be so pronounced. It seems that the penalty for using COM interop from .NET can be quite severe. Back to the drawing board.
Now for the problem, I knew from the beginning that if I had to provide the XML node list that a
DataSet persists in a form, along with the XML schema, any .NET or non-Microsoft client should easily be able to use to utilize the data, then the plan for returning persisted
Recordset formatted XML to VB 6.0 clients was in serious jeopardy. So far as I knew, there was no native way to convert a
DataSet to an ADODB
Recordset in VB.NET. Knowing that someone, somewhere was bound to have had this problem before me, I began to search the internet to see if anyone had come up with a solution that I could use. I found a couple of articles that had VB 6.0 code that use the .NET node list and schema to construct an ADODB
Recordset, but they were client side solutions, I needed something that would work on the server. I found a Microsoft Support article: How To Convert an ADO.NET DataSet to ADO Recordset in Visual Basic .NET, that appeared to be exactly what I had been searching for.
I followed the instructions in the article, set up the code, and it worked! The only problem I had now was that the example code from Microsoft was writing the XML to a file and reading an XSL file. Since I need this to be a server solution, I needed to eliminate the file IO. No big deal, I altered the code to generate the XSL on the fly in a string (it was quite small), and used a
MemoryStream to contain the XML instead of a file. I tried this out and it worked. Now I had to test it against some real world data. I set up some test code to pull data from our test database, and sent it through the conversion function to see what I get on the other side.
The first problem I saw was that the only fields getting a data type were integers and strings, for all other fields the data type was blank. This was causing an error when attempting to load the
Recordset from the ADODB Stream. I tracked down where the code was determining the data type, and sure enough, integers and strings were all they were trapping for. I added several data types and a
Case Else that set anything I was not specifically trapping, to type string. Thinking this should work, I tried another run and still got an error loading the XML into the
Recordset. I commented out all of the fields returned in the stored procedure except one, ran again, and it worked. I repeated this process, un-commenting another field each time, trying to figure out what type of field I was having a problem with. It turned out to be date-time fields. After a bit of research, I found that in the
DataSet, the date-time format included time zone information. For the
Recordset to accept a date-time with this additional time zone information, the data type in the XML must be set to
dateTime.iso8601tz. I made this change, attempted another run, and it worksed.
Now I wrapped the code in a class and integrated it into the Web Service and began unit testing. I ran through a couple of calls, then hit another problem - binary fields. This one took a while to research, but I found that binary fields in a
DataSet are persisted as base64 encoded strings; the ADODB
Recordset expects binary fields to be persisted as binary hex encoded strings. Since the Microsoft code for transforming the data portion of the
DataSet is the part that uses the XSL transformation, there was no opportunity to re-encode the data in a binary field. First, I tried setting the data type in the XML to
bin.base64. This allowed the
Recordset to load the data without an error, but, the binary data ended up stored in the
Recordset as a string field containing the base64 encoded string. In order to have the
Recordset convert the field to binary upon loading, as it should, it must be encoded in binary hex, and the data type in the XML set to
bin.hex. To solve this, I rewrote the transformation code to loop through the
DataSet, and added the data to the XML using the
XmlTextWriter just like the rest of the code does to add the header and schema information. This gave me the chance to detect binary fields and binary hex encode them.
Now I needed to find or write a function to perform the binary hex encoding. I couldn't find any ready-made code on the internet, but, I did find information on binary hex encoding.
BinaryHex encoding simply takes each octet (byte) of the binary stream, divides it into two 4 bit nibbles, and places the hexadecimal character representing that nibbles value in the output string. For example, if you have 32 bits of binary data:
divide into octets (bytes):
10010010 11110001 10101100 11010100
divide the octets into 4 bit nibbles:
1001 0010 1111 0001 1010 1100 1101 0100
decimal values of nibbles:
9 2 15 1 10 12 13 4
9 2 F 1 A C D 4
encoded string representing the original 32 bit binary value:
Armed with the above information, it was simple to write a small function to binary hex encode a byte array and return the resultant string. I added this to the class, modified the data transformation code to detect binary fields, and applied this new function to the data. Now I have a conversion class that I can use. The only thing that you may need to update is the function that determines the data type to put in the XML. If you need a data type that I'm not trapping, returning it as a string is not sufficient, just add a
Case statement for your data type to the function.
Now, on to the code!
- Microsoft Windows 2003, Windows XP, Windows 2000, or Windows NT 4.0 Service Pack 6a
- Microsoft Data Access Components (MDAC) 2.6 or later
- Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
- Microsoft Visual Basic .NET syntax
- ADO.NET and earlier versions of ADO
DataSet and ADO
Recordset XML formats
It should be noted here that I started with the code in the above mentioned Microsoft Support article. The code presented, however, is a substantial update of that code.
Note: You must call the
FillSchema method of the
DataAdapter to obtain the schema information with your
DataSet. If you do not, all fields will be created as a string data type.
GetADORS function provides the entry point and logic flow for the class. It also creates the
XmlTextReader used by the rest of the functions to build the output XML string.
Public Function GetADORS(ByVal DS As DataSet, _
ByVal dbName As String) As String
Dim mStream As New MemoryStream
Dim xWriter As New XmlTextWriter(mStream, Nothing)
xWriter.Indentation = 8
xWriter.Formatting = Formatting.Indented
WriteSchemaElement(DS, dbName, xWriter)
mStream.Position = 0
Dim Buffer As Array
Buffer = Array.CreateInstance(GetType(Byte), mStream.Length)
mStream.Read(Buffer, 0, mStream.Length)
Dim TextConverter As New UTF8Encoding
Catch ex As Exception
Err.Raise(100, ex.Source, ex.ToString)
First, I've added two lines that indicate to the
XmlTextWriter that I want the XML to be indented. The entire purpose of this is to make the output XML human readable. These lines can be omitted if you like. Having the output XML easily readable made debugging the class much easier. Next,
WriteADONamespaces is called to add the
Recordset schema to the output XML.
WriteSchemaElement is then called to add the schema elements.
TransformData is called to properly format the data and add it to the output XML. Finally, the contents of the
MemoryStream are prepared for return as a string.
Private Sub WriteADONamespaces(ByRef xWriter As XmlTextWriter)
xWriter.WriteStartElement("", "xml", "")
xWriter.WriteAttributeString("xmlns", "s", Nothing, _
xWriter.WriteAttributeString("xmlns", "dt", Nothing, _
xWriter.WriteAttributeString("xmlns", "rs", Nothing, _
xWriter.WriteAttributeString("xmlns", "z", _
The code in
WriteADONamespaces is essentially unchanged from the code in the original Microsoft article. I have removed the comment describing the format of this section of the XML.
Private Sub WriteSchemaElement(ByVal DS As DataSet, _
ByVal dbName As String, ByRef xWriter As _
xWriter.WriteStartElement("s", "Schema", _
xWriter.WriteStartElement("s", "ElementType", _
xWriter.WriteAttributeString("name", "", "row")
xWriter.WriteAttributeString("content", "", "eltOnly")
xWriter.WriteAttributeString("rs", "updatable", _
WriteSchema(DS, dbName, xWriter)
The code in
WriteSchemaElement, also, is essentially unchanged from the code in the original Microsoft article. I have removed the comment describing the format of this section of the XML.
Private Sub WriteSchema(ByVal DS As DataSet, ByVal dbName _
As String, ByRef xWriter As XmlTextWriter)
Dim i As Int32 = 1
Dim DC As DataColumn
For Each DC In DS.Tables(0).Columns
DC.ColumnMapping = MappingType.Attribute
xWriter.WriteStartElement("s", "AttributeType", _
xWriter.WriteAttributeString("name", "", DC.ToString)
xWriter.WriteAttributeString("rs", "number", _
xWriter.WriteAttributeString("rs", "baseCatalog", _
xWriter.WriteAttributeString("rs", "baseTable", _
xWriter.WriteAttributeString("rs", "keycolumn", _
xWriter.WriteAttributeString("rs", "autoincrement", _
xWriter.WriteStartElement("s", "datatype", _
xWriter.WriteAttributeString("dt", "type", _
xWriter.WriteAttributeString("dt", "maxlength", _
xWriter.WriteAttributeString("rs", "maybenull", _
i = i + 1
DC = Nothing
The code in
WriteSchema, also, is essentially unchanged from the code in the original Microsoft article. I have removed the comment describing the format of this section of the XML.
Private Function GetDatatype(ByVal DType As String) As String
Select Case (DType)
Case "System.Int32", "System.Int16", "System.Integer"
GetDatatype function has been expanded to handle more data types than the original function. The original function only recognized
Case Else has also been added to return string type for all data types not in the
Private Sub TransformData(ByVal DS As DataSet, _
ByRef xWriter As XmlTextWriter)
xWriter.WriteStartElement("", "rs:data", "")
Dim i As Long
Dim j As Integer
For i = 0 To DS.Tables(0).Rows.Count - 1
xWriter.WriteStartElement("", "z:row", "")
For j = 0 To DS.Tables(0).Columns.Count - 1
= "System.Byte" Then
If Not IsDBNull(DS.Tables(0).Rows(i).Item(
If Not IsDBNull(DS.Tables(0).Rows(i).Item(
TransformData adds the "rs:data" section of the XML. The function loops through the
DataSet adding "z:row" elements for each data row. This function also adds the end tag for the root (XML) element.
Private Function DataToBinHex(ByVal thisData As Byte()) As String
Dim sb As New StringBuilder
Dim i As Integer = 0
For i = 0 To thisData.Length - 1
sb.Append(Hex((thisData(i) And &HF0) / 2 ^ 4))
sb.Append(Hex(thisData(i) And &HF))
DataToBinHex function performs the encoding of binary data.
As long as developers are faced with integrating .NET and VB 6.0, there will be a need to have the ability to pass data from one to the other. In the case where VB 6.0 is the client, the code presented here should help to alleviate the problem. While the code in the Microsoft article was a good starting point, it has a few shortcomings that prevent it from operating correctly in many real world situations. I believe that I have addressed the major concerns and shortfalls, and am presenting code that can be dropped into a project and used "as is" in most situations.
- Original submission - 03/08/2006.