Loading ...
Sorry, an error occurred while loading the content.

Returning a .NET DataSet to a .NET client

Expand Messages
  • Maurice McCabe
    In response to requests regarding returning a dataset to a .NET client per following message: http://groups.yahoo.com/group/soaplite/message/3395 The following
    Message 1 of 1 , Oct 25, 2005
    • 0 Attachment
      In response to requests regarding returning a dataset to a .NET client
      per following message:
      http://groups.yahoo.com/group/soaplite/message/3395

      The following code is cut and paste from an existing application and has
      not been tested as a standalone application. Set you own values for
      undefined globals (for example $dbh the database handle) and edit code
      for your own purposes.

      # GetDataSet method is called from .NET client via a WSDL
      sub GetDataSet {
      my $retVal = doGetDataSet(@_);
      return SOAP::Data->name('GetDataSetResult' =>
      SOAP::Data->type('xml' => $retVal));
      }

      # return a .NET dataset containing multiple arbitary query results
      sub doGetDataSet {
      my $envelope = pop;
      my %queries = SQLqueriesPayload($envelope->{_message});
      # run queries
      my $schemaStr;
      my $resultSetStr;
      for my $queryName (keys %queries) {
      next if !$queryName;
      my $sth = $dbh->prepare($queries{$queryName});
      $sth->execute;
      my $colNames = $sth->{'NAME'};
      my $colTypes = $sth->{TYPE};
      my $resultSetRef = $sth->fetchall_arrayref;
      $schemaStr .= ZIProx::DataSet::getResultSetSchema($queryName,
      $colNames, $colTypes);
      $resultSetStr .= ZIProx::DataSet::getResultSet($queryName,
      $resultSetRef, $colNames);
      }
      # assemble the final xml
      my $xmlContent = ZIProx::DataSet::assembleDataSet($schemaStr,
      $resultSetStr);
      return $xmlContent;
      }

      sub SQLqueriesPayload {
      my ($xml) = @_;
      return undef if (!$xml);
      my $d = SOAP::Custom::XML::Deserializer
      -> deserialize($xml)
      -> valueof('//SQLqueries') || return undef;
      my %queries;
      foreach my $SQLquery ($d->SQLquery) {
      $queries{$SQLquery->QueryName} = $SQLquery->QuerySQL;
      }
      return %queries;
      }

      package ZIProx::DataSet;
      # Copyright Orbsoft, Inc. (www.orbsoft.com)
      # construct a Microsoft .NET DataSet
      # (no external libraries required -- implemented using string
      manipulation)

      my $schemaHdr =<<"EOF";
      <xs:schema id="NewDataSet" xmlns=""
      xmlns:xs="http://www.w3.org/2001/XMLSchema"
      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
      <xs:element name="NewDataSet" msdata:IsDataSet="true">
      <xs:complexType>
      <xs:choice maxOccurs="unbounded">
      EOF

      my $RSschemaHdr = << "EOF";
      <xs:element name="QUERYNAME">
      <xs:complexType>
      <xs:sequence>
      EOF

      my $RSschemaRow = << "EOF";
      <xs:element name="COLNAME" type="xs:COLTYPE"
      minOccurs="0" /> EOF

      my $RSschemaFtr = << "EOF";
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      EOF

      my $schemaFtr = << "EOF";
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>
      EOF

      my $diffgramHdr = << "EOF";
      <diffgr:diffgram
      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
      xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
      <NewDataSet xmlns="">
      EOF

      my $RSRowHdr = << "EOF";
      <QUERYNAME diffgr:id="QUERYNAMEROWCNT"
      msdata:rowOrder="ROWORDER"> EOF

      my $RSRow = << "EOF";
      <COLNAME>ROWCOLVALUE</COLNAME>
      EOF

      my $RSRowFtr = << "EOF";
      </QUERYNAME>
      EOF

      my $diffgramFtr = << "EOF";
      </NewDataSet>
      </diffgr:diffgram>
      EOF

      my %dataTypes = (
      '1' => 'string',
      '3' => 'int',
      '4' => 'int',
      #'8' => 'number',
      '8' => 'double',
      #'11' => 'dateTime',
      '11' => 'string',
      '12' => 'string',
      );

      sub assembleDataSet {
      my ($resultSetSchemaStr, $resultSetStr) = @_;
      return $schemaHdr . $resultSetSchemaStr . $schemaFtr . $diffgramHdr
      . $resultSetStr . $diffgramFtr; }

      sub addColumn {
      my ($resultSet, $colNames, $colTypes, $col, $colName, $colType) =
      @_;
      unshift @$colNames, $colName;
      unshift @$colTypes, $colType;
      my $i;
      foreach my $row (@$resultSet) {
      unshift @$row, $col->[$i++];
      }
      }

      sub getResultSetSchema {
      my ($queryName,$colNames, $colTypes) = @_;
      (my $RSschemaStr = $RSschemaHdr) =~ s/QUERYNAME/$queryName/;
      foreach my $i (0 .. @$colNames-1) {
      my $colName = fixXMLTag($colNames->[$i]);
      (my $myRSSchemaRow = $RSschemaRow) =~ s/COLNAME/$colName/;
      $myRSSchemaRow =~ s/COLTYPE/$dataTypes{$colTypes->[$i]}/;
      $RSschemaStr .= $myRSSchemaRow;
      }
      return $RSschemaStr . $RSschemaFtr;
      }

      sub getResultSet {
      my ($queryName, $resultSetRef, $colNames) = @_;
      my $i=0;
      my $RSStr;
      foreach my $row (@$resultSetRef) {
      (my $RSRowStr = $RSRowHdr) =~ s/QUERYNAME/$queryName/g;
      $RSRowStr =~ s/ROWORDER/$i/;
      $i++;
      $RSRowStr =~ s/ROWCNT/$i/;
      foreach my $col (0 .. @$colNames-1) {
      my $colName = fixXMLTag($colNames->[$col]);
      (my $myResultSetRow = $RSRow) =~ s/COLNAME/$colName/g;
      my $escapeVal = escape($row->[$col]);
      $myResultSetRow =~ s/ROWCOLVALUE/$escapeVal/;
      $RSRowStr .= $myResultSetRow;
      }
      (my $myRSRowFtr = $RSRowFtr) =~ s/QUERYNAME/$queryName/;
      $RSStr .= $RSRowStr . $myRSRowFtr;
      }
      return $RSStr;
      }

      sub escape {
      # must escape certain characters or the XML document is invalid
      my ($str) = @_;
      $str =~ s/</</g;
      $str =~ s/>/>/g;
      $str =~ s/&/&/g;
      #$str =~ s/"/"e;/g;
      $str =~ s/'/'/g;
      return $str;
      }

      sub fixXMLTag {
      # must fix illegal tag names or the XML document is invalid
      my ($tag) = @_;
      $tag =~ s/\///g;
      $tag =~ s/ //g;
      return $tag;
      }

      1;

      #Must (or recommend) use a 'Literal' serializer/deserializer to get .NET
      SOAP packages off the wire when setting-up the SOAP server:
      use SOAP::Lite;
      use SOAP::Transport::HTTP ;
      my $uri = 'ZIProxSOAP';
      my $serializer = LiteralSerializer->new;
      my $deSerializer = LiteralDeserializer->new;


      $daemon = SOAP::Transport::HTTP::CGI
      -> serializer($serializer)
      -> deserializer($deSerializer)
      -> dispatch_to('./modules');

      $daemon->serializer->xmlschema('2001');

      $daemon -> handle ;

      BEGIN {

      package LiteralSerializer;
      @LiteralSerializer::ISA = 'SOAP::Serializer';
      sub xmlize {
      my $self = shift;
      my($name, $attrs, $values, $id) = @{+shift};
      $attrs ||= {};

      # keep only namespace attributes for all elements
      my $a = $attrs->{xmlns} ? {xmlns => $attrs->{xmlns}} : {};

      return $self->SUPER::xmlize([$name, $a, $values, $id]);
      }
      sub envelope {
      $_[2] = (UNIVERSAL::isa($_[2] => 'SOAP::Data') ? $_[2] :
      SOAP::Data->name($_[2])->attr({xmlns => $uri}))
      if $_[1] =~ /^(?:method|response)$/;
      shift->SUPER::envelope(@_);
      }

      package LiteralDeserializer;
      @LiteralDeserializer::ISA = 'SOAP::Deserializer';
      sub deserialize {
      my $self = shift;
      my $result = $self->SUPER::deserialize(@_);
      $result->{_message} = @_[0];
      return $result;
      }

      }

      This WSDL is cut and paste from an existing application specific WSDL
      and is untested:
      <?xml version="1.0" encoding="utf-8"?>
      <definitions
      xmlns:http="http://schemas.xmlsoap.org/wsdl/http/"
      xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
      xmlns:s="http://www.w3.org/2001/XMLSchema"
      xmlns:s0="ZIProxSOAP"
      xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
      xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/"
      xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/"
      targetNamespace="ZIProxSOAP"
      xmlns="http://schemas.xmlsoap.org/wsdl/">
      <types>
      <s:schema elementFormDefault="qualified"
      targetNamespace="ZIProxSOAP">
      <s:element name="GetDataSet">
      <s:complexType />
      </s:element>
      <s:element name="GetDataSetResponse">
      <s:complexType>
      <s:sequence>
      <s:element minOccurs="0" maxOccurs="1"
      name="GetDataSetResult">
      <s:complexType>
      <s:sequence>
      <s:element ref="s:schema" />
      <s:any />
      </s:sequence>
      </s:complexType>
      </s:element>
      </s:sequence>
      </s:complexType>
      </s:element>
      </s:schema>
      </types>
      <message name="GetDataSetSoapIn">
      <part name="parameters" element="s0:GetDataSet" />
      </message>
      <message name="GetDataSetSoapOut">
      <part name="parameters" element="s0:GetDataSetResponse" />
      </message>
      <portType>
      <operation name="GetDataSet">
      <input message="s0:GetDataSetSoapIn" />
      <output message="s0:GetDataSetSoapOut" />
      </operation>
      </portType>
      <binding name="ZIProxSoap" type="s0:ZIProxSoap">
      <soap:binding transport="http://schemas.xmlsoap.org/soap/http"
      style="document" />
      <operation name="GetDataSet">
      <soap:operation soapAction="ZIProxSOAP#GetDataSet"
      style="document" />
      <input>
      <soap:body use="literal" />
      </input>
      <output>
      <soap:body use="literal" />
      </output>
      </operation>
      </binding>
      <service name="ZIProx">
      <port name="ZIProxSoap" binding="s0:ZIProxSoap">
      <soap:address
      location='http://www.yourserver.com/yoursoapserverapp/soap.cgi'/>
      </port>
      </service>
      </definitions>

      # Sample VB.Net code snippet to request, retrieve and display a
      Microsoft .NET DataSet from a SOAPLite server:
      Dim mySOAPLiteServer as SOAPLiteServer = New SOAPLiteServer()
      Dim myQueries as New HashTable()
      myQueries("Authors") = "select * from Authors"
      myQueries("Publishers") = "select * from Publishers"
      Dim myDataSet as Dataset = mySOAPLiteServer.GetDataSet(myQueries)
      myWebPageAuthorsDataGrid.DataSource =
      myDataSet.Tables("Authors").DefaultView
      myWebPageAuthorsDataGrid.DataBind()

      Happy Programming!

      Maurice
    Your message has been successfully submitted and would be delivered to recipients shortly.