Classic ASP to .NET WebService interfacing (Dataset to RecordSet)

[Adam Short]

I am trying to write a routine that will connect a .NET server with a
classic ASP server.

I know the following code doesn't work! The data is being returned as a
dataset, however ASP does not recognise datasets and requires a recordset.
Can the datatypes be converted? At the Classic ASP end or .NET end? Can
SOAP toolkit provide the conversion, can any toolkit provide a conversion?

==================================================================================

Web Service Code :
---------------------

dim strSelect as string
dim srcData as ODBCconnection
dim fltData as ODBCdataAdapter
dim myPath as String

myPath = me.Context.Request.ServerVariables("APPL_PHYSICAL_PATH")


dim rtnData as DataSet

strSelect = "SELECT * FROM myDataSource"

' srcData = new ODBCConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=" & myPath & "..\data\myDataSource.mdb" )

srcData = new ODBCConnection( "DSN=MyDataSource;uid=;pwd=" )

fltData = new ODBCdataAdapter( strSelect, srcData )

rtnData = new dataset

fltData.fill( rtnData )

return rtnData

==================================================================================

ASP Web Server Code:
-------------------------

SET objSoapClient = Server.CreateObject("MSSOAP.SoapClient")


' needs to be updated with the url of your Web Service WSDL and is
' followed by the Web Service name

objSoapClient.ClientProperty("ServerHTTPRequest") = True

Call
objSoapClient.mssoapinit("http://system.evolucion.co.uk/evolucion-services.asmx?WSDL")

set RecordSet = Server.CreateObject("ADODB.Recordset")

' use the SOAP object to call the Web Method Required
RecordSet = objSoapClient.getEvolucionVersionList()

strOutput = strOutput & "<P>On-Line Result : " & RecordSet.RecordCount

==================================================================================

 

[Manohar Kamath]

The DataSet and RecordSet objects are not compatible -- Dataset represents a
whole database (including tables, relations, etc.), while the recordset
represents two-dimensional view of data (Table, view, result from stored
proc, etc)

In my opinion, you need to architect your solution better. You could return
a recordset object from the webservice, or a two-dimensional array.

 

[Bob Barrows [MVP]]

I am trying to write a routine that will connect a .NET server with a
classic ASP server.

I know the following code doesn't work! The data is being returned
as a dataset, however ASP does not recognise datasets and requires a
recordset. Can the datatypes be converted?

No.
The dataset is returned as XML. You have to parse the returned XML Document
to extract your data. You can use the MSXML parser to extract the nodes you
need.

Bob Barrows

 

[Adam Short]

how do you return a recordset from a webservice?

I have looked and not found anything yet?

 

[Bob Barrows [MVP]]

You can't.
Well .. maybe ... using Interop, you might be able to create an ADO
recordset and stream it to XML which can be returned but ... I doubt it.
You'll need to ask in a dotnet newsgroup to be sure, but I really doubt it.
If it IS possible, then ADO will have no problem converting the returned XML
recordset into an ADO recordset using the Open method.

A web service returns results in the form of XML. A dotnet page can convert
the XML into the appropriate dotnet object. This cannot be done by vbscript.
You need to parse the returned XML using the methods found in the MSXML
parser: selectNodes, selectSingleNode, etc.

Bob Barrows

 

[Adam Short]

Right! I think I understand it now.

I will use an array instead, then I will also be able to use the same
webservice when connecting to linux based servers.

 

[Manohar Kamath]

Or, you can send the XML of the ADO recordset back, and re-construct it on
the client. Use Interop on the server to work with the ADO recordset.

// ON THE SERVER -- Web Service
// Add references to ADODB library

// Create a stream object
myStream = new ADODB.Stream();

// Replace the constants with their actual values
recordSet.Save(myStream, adPersistXML);
output = myStream.ReadText(adReadAll);

// Return the XML string, complete with schema
return output;

on the client, just re-create the disconnected recordset
http://support.microsoft.com/kb/263247

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com

 

[Bob Barrows [MVP]]

Or, you can send the XML of the ADO recordset back, and re-construct
it on the client. Use Interop on the server to work with the ADO
recordset.

// ON THE SERVER -- Web Service
// Add references to ADODB library

// Create a stream object
myStream = new ADODB.Stream();

// Replace the constants with their actual values
recordSet.Save(myStream, adPersistXML);
output = myStream.ReadText(adReadAll);

// Return the XML string, complete with schema
return output;

on the client, just re-create the disconnected recordset
http://support.microsoft.com/kb/263247

All right, I wasn't sure this was possible, so thanks for confirming that it
is possible.

I do appreciate that you are answering the question that was asked. But I do
feel the need to express this reservation:

My only issue with this solution is that it will force ALL consumers of this
service to use ADO, essentially forcing any .Net apps that consume it to
also use Interop to process the results. I realize that you could pass a
flag indicating how the results should be returned (dataset vs recordset)
but that forces the developer to maintain two sets of code that do
essentially the same thing. My preference would be to simply create a sparse
xml document containing the data and return that.

However, if this service is intended to be consumed only by non-.Net
applications, then go for it.

Bob Barrows

 

[Manohar Kamath]

Bob,

Not really, since we still return XML that you can parse it to get back
results -- no different from say if you returned "plain xml." The solution
will not apply to all situations. I am only suggesting based on the case in
hand.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com

 

[Bob Barrows [MVP]]

Bob,

Not really, since we still return XML that you can parse it to get
back results --

But now we are back to my initial suggestion that the poster parse the
dataset xml in his classic ASP application to extract his data.
Except that you are suggesting the reverse: that the .Net consumer parse the
recordset XML to extract his data.

no different from say if you returned "plain xml."

I'm just saying that my preference would be to avoid the Interop.

The solution will not apply to all situations. I am only suggesting
based on the case in hand.

That's why I said:
Bob Barrows

 

[Adam Short]

So many choices, I really appreciate everything everyone has said regarding
this matter, what to do is the ultimate question.

The scenario follows;

Windows 2003 Server has web services.

Classic ASP, ASP.NET & PHP Websites will eventually need the ability to get
the data.

I am now looking at the possibility of passing back an array of objects.
i.e.

MyClass
{
obj1 as Integer
obj2 as String
}

The Web Service generates a structure very well indeed and all looks well.
However when I try to read the object at the Classic ASP side it fails. I
understand passing through to .NET should be easy, so what do you guys
think?

My tests so far show that SOAPClient can easily interpret strings and
integers, although I am having difficulty with arrays of these types.

My ASP code looks like this

SET objSoapClient = Server.CreateObject("MSSOAP.SoapClient")

' needs to be updated with the url of your Web Service WSDL and is
' followed by the Web Service name

objSoapClient.ClientProperty("ServerHTTPRequest") = True

Call objSoapClient.mssoapinit("http://domain/script.asmx?WSDL")

' use the SOAP object to call the Web Method Required
thisData = objSoapClient.testInteger()

strOutput = strOutput & "<P>On-Line Result : " & thisData

thisData = objSoapClient.testString()

strOutput = strOutput & "<P>On-Line Result : " & thisData

thisData = objSoapClient.testObject()

strOutput = strOutput & "<P>On-Line Result : " & thisData.Filename

thisData = objSoapClient.testIntegerArray()

strOutput = strOutput & "<P>On-Line Result : " & thisData(0)

set objSoapClient = nothing

How can I get SOAP to read the integer array? as the above code doesn't
work, also, why is the object not being created as I understand the SOAP
toolkit shoul be able to cope with this.

Here is a snippet from the Web Services script

<webmethod()> public function testIntegerArray() as integer()

Dim MyList(2) As integer

MyList(0)=1000
MyList(1)=2000

Return MyList

end function

<webmethod()> public function testObject() as
DataTypesVB.Enumerations.EvolucionVersionList

Dim MyList As DataTypesVB.Enumerations.EvolucionVersionList

MyList = New DataTypesVB.Enumerations.EvolucionVersionList()
MyList.FileName="test.asp"
MyList.Ver = "1.0"

Return MyList

end function


<webmethod()> public function testString() as String

Dim MyList As String

MyList="test.asp"

Return MyList

end function

<webmethod()> public function testInteger() as integer

Dim MyList As integer

MyList=1000

Return MyList

end function

This is driving me crazy, I'm sure its very simple.

Regards

Adam

 

[Bob Barrows [MVP]]

So many choices, I really appreciate everything everyone has said
regarding this matter, what to do is the ultimate question.

The scenario follows;

Windows 2003 Server has web services.

Classic ASP, ASP.NET & PHP Websites will eventually need the ability
to get the data.

I am now looking at the possibility of passing back an array of
objects. i.e.

MyClass
{
obj1 as Integer
obj2 as String
}

The Web Service generates a structure very well indeed and all looks
well. However when I try to read the object at the Classic ASP side
it fails. I understand passing through to .NET should be easy, so
what do you guys think?

A classic ASP consumer can not interpret a system object. All it can do is
parse the XML that is returned. It cannot directly read any objects. You can
see the xml that is returned by browsing to your web service. Use the
interface provided to enter parameters and run the procedure. The resulting
page will show the XML that is being passed to the client. You will need to
use the methods in the msxml parser to extract your data from that XML. Here
are a few articles to help you:

http://www.google.com/search?source...LD:en&q=consuming+web+services+in+classic+ASP

Bob Barrows

 

[Manohar Kamath]

Would be interesting to see if a VBScript class can be used instead -- Since
SOAP, when de-serializing, just maps properties by their name.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com

 

[Bob Barrows [MVP]]

Yes, it would be interesting to see. Unfortunately, I don't have time to
find out
Maybe this weekend ...

Bob

 

[Chris Hohmann]

I am trying to write a routine that will connect a .NET server with a
classic ASP server.

I know the following code doesn't work! The data is being returned as a
dataset, however ASP does not recognise datasets and requires a recordset.
Can the datatypes be converted? At the Classic ASP end or .NET end? Can
SOAP toolkit provide the conversion, can any toolkit provide a conversion?

[snip]

Here's a proof of concept I came up with. Basically, it makes an HTTP GET
call the getEvolucionVersionList operation of your webservice, then loads
the resultant xml into a MSXML2.DomDocument and transforms the xml to html
using a XSLT stylesheet. HTTP, XML and XSLT are all pretty standard, so this
concept should port easily to PHP, ASP.NET, JSP, etc...

[EvolucionVersionList2HTML.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="html" version="4.0" indent="yes"/>
<xsl:template match="NewDataSet">
<html>
<body>
<table>
<tr>
<th>RecordID</th>
<th>ModuleName</th>
<th>Description</th>
<th>FileName</th>
<th>Version</th>
<th>Location</th>
<th>SystemVariable</th>
<th>DateEdited</th>
<th>Status</th>
</tr>
<xsl:apply-templates select="Table"/>
</table>
<xsl:value-of select="count(Table)"/> Record(s)
</body>
</html>
</xsl:template>
<xsl:template match="Table">
<tr>
<td><xsl:value-of select="RecordID"/></td>
<td><xsl:value-of select="ModuleName"/></td>
<td><xsl:value-of select="Description"/></td>
<td><xsl:value-of select="FileName"/></td>
<td><xsl:value-of select="Version"/></td>
<td><xsl:value-of select="Location"/></td>
<td><xsl:value-of select="SystemVariable"/></td>
<td><xsl:value-of select="DateEdited"/></td>
<td><xsl:value-of select="Status"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>

[getEvolucionVersionList.asp]
<%
Dim url, http, xml, xsl
url =
"http://system.evolucion.co.uk/evolucion-services.asmx/getEvolucionVersionList"
Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0")
http.Open "GET",url,False
http.Send
Set xml = CreateObject("MSXML2.DOMDocument.4.0")
xml.loadXML http.responseText
Set xsl = CreateObject("MSXML2.DOMDocument.4.0")
xsl.load Server.MapPath("EvolucionVersionList2HTML.xsl")
xml.transformNodeToObject xsl, Response
Set http = Nothing
Set xsl = Nothing
Set xml = Nothing
%>

NOTES:
1. I'm currently using MSXML 4.0. If you're using version 3.0 (which is
likely), you'll need to modify the asp file accordingly. For example,
"MSXML2.ServerXMLHTTP.4.0" would become "MSXML2.ServerXMLHTTP.3.0"

2. I opted not to include the SourceCode data in the html table
presentation.

3. There's nothing wrong with using SOAP to get the data instead of HTTP to
get the data as you did in your origianl code. However, you indicated that
eventually end users would need to be able to consume the service from a
number of different environments. Some of those environments may not have
SOAP, so I wanted to show that it could be done without it.

4. On a completely unrelated note, I noticed that you're using the ".inc"
extension for your include files. Also, it appears you're loading objects
into the Application scope. Here are two articles that explain why these are
not good ideas:

http://aspfaq.com/show.asp?id=2269
http://aspfaq.com/show.asp?id=2053


HTH
-Chris Hohmann

 

[Adam Short]

Thankyou for your input, and comments.

I am fully aware that I could use XMLRPC as you have suggested, but am
really just looking to utilize SOAP as an alternative. More research than
necessity, in fact to be honest I am very disappointed with SOAP on the
whole, and wonder why anyone would implement a system which is so
restrictive.

In the past I have used a hacked XMLRPC procedure developed originally by
David Carter-Tod. Although bugged, I have fixed most of them.

I am trying to write a routine that will connect a .NET server with a
classic ASP server.

I know the following code doesn't work! The data is being returned as a
dataset, however ASP does not recognise datasets and requires a
recordset. Can the datatypes be converted? At the Classic ASP end or
.NET end? Can SOAP toolkit provide the conversion, can any toolkit
provide a conversion?

[snip]

Here's a proof of concept I came up with. Basically, it makes an HTTP GET
call the getEvolucionVersionList operation of your webservice, then loads
the resultant xml into a MSXML2.DomDocument and transforms the xml to html
using a XSLT stylesheet. HTTP, XML and XSLT are all pretty standard, so
this concept should port easily to PHP, ASP.NET, JSP, etc...

[EvolucionVersionList2HTML.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="html" version="4.0" indent="yes"/>
<xsl:template match="NewDataSet">
<html>
<body>
<table>
<tr>
<th>RecordID</th>
<th>ModuleName</th>
<th>Description</th>
<th>FileName</th>
<th>Version</th>
<th>Location</th>
<th>SystemVariable</th>
<th>DateEdited</th>
<th>Status</th>
</tr>
<xsl:apply-templates select="Table"/>
</table>
<xsl:value-of select="count(Table)"/> Record(s)
</body>
</html>
</xsl:template>
<xsl:template match="Table">
<tr>
<td><xsl:value-of select="RecordID"/></td>
<td><xsl:value-of select="ModuleName"/></td>
<td><xsl:value-of select="Description"/></td>
<td><xsl:value-of select="FileName"/></td>
<td><xsl:value-of select="Version"/></td>
<td><xsl:value-of select="Location"/></td>
<td><xsl:value-of select="SystemVariable"/></td>
<td><xsl:value-of select="DateEdited"/></td>
<td><xsl:value-of select="Status"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>

[getEvolucionVersionList.asp]
<%
Dim url, http, xml, xsl
url =
"http://system.evolucion.co.uk/evolucion-services.asmx/getEvolucionVersionList"
Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0")
http.Open "GET",url,False
http.Send
Set xml = CreateObject("MSXML2.DOMDocument.4.0")
xml.loadXML http.responseText
Set xsl = CreateObject("MSXML2.DOMDocument.4.0")
xsl.load Server.MapPath("EvolucionVersionList2HTML.xsl")
xml.transformNodeToObject xsl, Response
Set http = Nothing
Set xsl = Nothing
Set xml = Nothing
%>

NOTES:
1. I'm currently using MSXML 4.0. If you're using version 3.0 (which is
likely), you'll need to modify the asp file accordingly. For example,
"MSXML2.ServerXMLHTTP.4.0" would become "MSXML2.ServerXMLHTTP.3.0"

2. I opted not to include the SourceCode data in the html table
presentation.

3. There's nothing wrong with using SOAP to get the data instead of HTTP
to get the data as you did in your origianl code. However, you indicated
that eventually end users would need to be able to consume the service
from a number of different environments. Some of those environments may
not have SOAP, so I wanted to show that it could be done without it.

4. On a completely unrelated note, I noticed that you're using the ".inc"
extension for your include files. Also, it appears you're loading objects
into the Application scope. Here are two articles that explain why these
are not good ideas:

http://aspfaq.com/show.asp?id=2269
http://aspfaq.com/show.asp?id=2053


HTH
-Chris Hohmann

 

[Bob Barrows [MVP]]

I think MS agrees with you, given that they have deprecated that toolkit.

Thankyou for your input, and comments.

I am fully aware that I could use XMLRPC as you have suggested, but am
really just looking to utilize SOAP as an alternative. More research
than necessity, in fact to be honest I am very disappointed with
SOAP on the whole, and wonder why anyone would implement a system
which is so restrictive.

In the past I have used a hacked XMLRPC procedure developed
originally by David Carter-Tod. Although bugged, I have fixed most
of them.

I am trying to write a routine that will connect a .NET server with
a classic ASP server.

I know the following code doesn't work! The data is being
returned as a dataset, however ASP does not recognise datasets and
requires a recordset. Can the datatypes be converted? At the
Classic ASP end or .NET end? Can SOAP toolkit provide the
conversion, can any toolkit provide a conversion?

[snip]

Here's a proof of concept I came up with. Basically, it makes an
HTTP GET call the getEvolucionVersionList operation of your
webservice, then loads the resultant xml into a MSXML2.DomDocument
and transforms the xml to html using a XSLT stylesheet. HTTP, XML
and XSLT are all pretty standard, so this concept should port easily
to PHP, ASP.NET, JSP, etc...

[EvolucionVersionList2HTML.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xslutput method="html" version="4.0" indent="yes"/>
<xsl:template match="NewDataSet">
<html>
<body>
<table>
<tr>
<th>RecordID</th>
<th>ModuleName</th>
<th>Description</th>
<th>FileName</th>
<th>Version</th>
<th>Location</th>
<th>SystemVariable</th>
<th>DateEdited</th>
<th>Status</th>
</tr>
<xsl:apply-templates select="Table"/>
</table>
<xsl:value-of select="count(Table)"/> Record(s)
</body>
</html>
</xsl:template>
<xsl:template match="Table">
<tr>
<td><xsl:value-of select="RecordID"/></td>
<td><xsl:value-of select="ModuleName"/></td>
<td><xsl:value-of select="Description"/></td>
<td><xsl:value-of select="FileName"/></td>
<td><xsl:value-of select="Version"/></td>
<td><xsl:value-of select="Location"/></td>
<td><xsl:value-of select="SystemVariable"/></td>
<td><xsl:value-of select="DateEdited"/></td>
<td><xsl:value-of select="Status"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>

[getEvolucionVersionList.asp]
<%
Dim url, http, xml, xsl
url =

"http://system.evolucion.co.uk/evolucion-services.asmx/getEvolucionVersionLi
st"

Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0")
http.Open "GET",url,False
http.Send
Set xml = CreateObject("MSXML2.DOMDocument.4.0")
xml.loadXML http.responseText
Set xsl = CreateObject("MSXML2.DOMDocument.4.0")
xsl.load Server.MapPath("EvolucionVersionList2HTML.xsl")
xml.transformNodeToObject xsl, Response
Set http = Nothing
Set xsl = Nothing
Set xml = Nothing
%>

NOTES:
1. I'm currently using MSXML 4.0. If you're using version 3.0 (which
is likely), you'll need to modify the asp file accordingly. For
example, "MSXML2.ServerXMLHTTP.4.0" would become
"MSXML2.ServerXMLHTTP.3.0"

2. I opted not to include the SourceCode data in the html table
presentation.

3. There's nothing wrong with using SOAP to get the data instead of
HTTP to get the data as you did in your origianl code. However, you
indicated that eventually end users would need to be able to consume
the service from a number of different environments. Some of those
environments may not have SOAP, so I wanted to show that it could be
done without it.

4. On a completely unrelated note, I noticed that you're using the
".inc" extension for your include files. Also, it appears you're
loading objects into the Application scope. Here are two articles
that explain why these are not good ideas:

http://aspfaq.com/show.asp?id=2269
http://aspfaq.com/show.asp?id=2053


HTH
-Chris Hohmann

 

[Adam Short]

I have decided to implement a sort of XML recordset system using Web
Services to generate an XML recordset which will be pulled into an ASP ADO
Recordset, however in all the excitement I've gone and killed my ADODB
component on the server. There's another posting covering that!

Once I've sorted this issue out I will begin testing the final code. If it
works I will post it here.

Thanks Again to everyone

Regards

Adam

 

[Adam Short]

Well here it is!! The answer I think I have been looking for!!!


First you need to get adodb.dll and insert this line of code at the top of your webservice.

<%@ Assembly name="adodb" %>

Then add the line;

imports adodb

Then the rest is history

<webmethod()> public function testRecordSet() as ADODB.Recordset


' srcData = new ODBCConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & myPath & "..\data\evolucion.mdb" )
' srcData = new ODBCConnection( "DSN=PhutureUKSystem;uid=;pwd=" )



' Declare and instantiate an ADODB Connection object.
Dim objCon As New ADODB.Connection
Dim objData As New ADODB.Recordset
Dim objStream As New ADODB.Stream

objCon.Open ("DSN=PhutureUKSystem; uid=; pwd=")

objData.Open ("EvolucionCode", objCon, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic)

objData.Save(objStream, ADODB.PersistFormatEnum.adPersistXML)

return objStream.ReadText

'Clean up. Finally always occurs so cleanup here.
objCon.Close()
objData.Close()

end function

A recordset is returned, now all I need to do is open it up the other end!