Returning robust RecordSet from WebService

  • Thread starter Thread starter alexttp
  • Start date Start date
A

alexttp

My WebService API needs to return a RecordSet.
I know it's impossible (since the ADODB RecordSet is not
serializable), and thus I return an XmlDocument representing it.

Actually, the flow is as follows:
1. Given an SQL statement, it is executed on the DB, resulting in a
DataSet.
2. This DataSet is then used to populate an ADODB RecordSet in the
following manner:

private static Recordset CreateADODBRecordSet(DataTable i_oDataTable)
{
Recordset oRS = new Recordset();
try
{
//Loop through each column in the Dataset
foreach(DataColumn oColumn in i_oDataTable.Columns)
{
//Create the Field Types for the recordset
oRS.Fields.Append(oColumn.ColumnName,
GetADOType(oColumn.DataType.ToString()),
GetADOTypeSize(oColumn.DataType.ToString)),
FieldAttributeEnum.adFldIsNullable,
System.Reflection.Missing.Value);
}
//Open the recordset
oRS.Open(System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
CursorTypeEnum.adOpenKeyset,
LockTypeEnum.adLockOptimistic, 1);
//Loop through the table and fill the ADO Recordset
for(int i = 0; i < i_oDataTable.Rows.Count; i ++)
{
oRS.AddNew(System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
//Loop through each column
for(int j = 0; j < i_oDataTable.Columns.Count; j ++)
{
oRS.Fields[j].Value = i_oDataTable.Rows[j];
}
oRS.Update(System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
}
if(oRS.RecordCount > 0)
{
//Move to the first record
oRS.MoveFirst();
}
return oRS;
}
catch(System.Exception ex)
{
return null;
}
}

3. This RS is then converted into an XmlDocument:

private static XmlDocument ConvertADODBRecordset2XmlDocument(
ADODB.Recordset i_oRS)
{
XmlDocument oRes = null;
MSXML2.DOMDocument40 oDomDoc = new MSXML2.DOMDocument40Class();
i_oRS.Save(oDomDoc, ADODB.PersistFormatEnum.adPersistXML);
oRes = new XmlDocument();
oRes.LoadXml(oDomDoc.xml);
return oRes;
}

4. So far, all is well until... we try to put some unprintable
characters in one of the string fields! I mean, say, ASCII 31 and
smaller...
This time the i_oRS.Save(oDomDoc,
ADODB.PersistFormatEnum.adPersistXML) produces oDomDoc with an empty
XML in it... Naturally, converting it into XmlDocuments makes no
good... :-(

SO, THE QUESTION IS: what would you recommend to overcome this
problem?

Eagerly waiting for any suggestions... Other ways of converting
DataSet to RS are also accepted ;-)

Alex
 
Hi Alexttp,

Sorry however this intrigues me.
My WebService API needs to return a RecordSet.
I know it's impossible (since the ADODB RecordSet is not
serializable), and thus I return an XmlDocument representing it.
Click to expand...

What you want to do, if you have serialize you probably wants to deserialize
it again. How do you want to do it.

Serializing a recordset on the serverside is as far as I know very easy,
that is using the dataadapter.fill(dataset, recordset) and than
dsWriteXml(path).

Cor
 
Cor Ligthert said:
Hi Alexttp,

Sorry however this intrigues me.

What you want to do, if you have serialize you probably wants to deserialize
it again. How do you want to do it.

Serializing a recordset on the serverside is as far as I know very easy,
that is using the dataadapter.fill(dataset, recordset) and than
dsWriteXml(path).

Cor
Click to expand...


What I really meant is that ADODB.RecordSet cannot be a type returned
by .NET WebService method. Compiler doesn't allow it, so I need to
find an alternative return type. That's it.

But, back to my question. Any ideas on that?
 
Cor Ligthert said:
Hi Alex,

Using the dataset it is so simple,

You can try this sample, it is even more simple possible than described here
when you do not use a typed dataset (made by the desinger). However for the
sample it makes it clear that they did it this way.

http://msdn.microsoft.com/library/d...atingDistributedWebApplicationWalkthrough.asp

I hope this helps?

Cor
Click to expand...

Dear Cor,
can you please read my original message thoroughly?
I didn't ask how to use DataSets - I know it.
I didn't ask how to use typed DataSets - I know it either.
I even didn't ask how to transcribe DataSet into an ADODB RecordSet -
I've already found a way to do that...

All I wanted to know is how to deal with the unprintable characters in
the strings of these DataSets/RecordSets, when saving them into
DomDocument.
Or, given there's no simple way to do that, I would welcome any
alternative way of saving the original DataSet into an XmlDocument
representing a RecordSet structure...

Can anyone help with this please?

Thanks in advance,

Alex
 
Hi Alex,

this was what you also was writting
I know it's impossible (since the ADODB RecordSet is not
serializable), and thus I return an XmlDocument representing it.
Click to expand...

How do you want to deserialize when even serialize is not possible?
Serializing means representing an object in a serialized string (and that
can be a XML string in which the tags can helps to deserialize it back).

I thought I answered that in my first answer to you and did only give you an
alternative

Cor
 
Back
Top