Returning robust RecordSet from WebService

[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

 

[Cor Ligthert]

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.

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

 

[alexttp]

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

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]

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

 

[alexttp]

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

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

 

[Cor Ligthert]

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.

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