Recently I have been doing some work on an older system using ADODB recordsets. Every now and then we still have to support these old systems. I wanted to create an open recordset in code on the .NET side to pass to the older system, but ADODB really wants an open database connection in order to open a recordset. My first obvious attempt was to create the recordset object, then create the field objects and add them to the recordset. I could create the recordset this way, but I couldn't open it, and a recordset must be open in order to add data rows. What ADODB really wanted was a table that matched my schema so it could connect to it before adding rows. This was just not practicable.
I discovered that you can open an ADODB recordset from an XML data source, and with the ADODB Stream object, that source can be in memory. Since this was something that I needed to use a lot for this project, I wrote a helper class, named RecordSetHelper, that will create the xml with an easy-to-use interface. I used Linq XML objects to create the XML string, then some rather simple code to put the pieces together into the correct schema that ADODB was expecting. If anyone wants the complete code, I can email it to them. The high points are below.
Here is the xml for a simple ADODB recordset with a couple of data rows:
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset"
xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly" rs:updatable="true">
<s:AttributeType name="FirstName" rs:number="1" rs:write="true">
<s:datatype
dt:type="string"
rs:dbtype="str"
dt:maxLength="200"
rs:precision="0"
rs:fixedlength="true"
rs:maybenull="false" />
</s:AttributeType>
<s:AttributeType name="LastName" rs:number="2" rs:write="true">
<s:datatype
dt:type="string"
rs:dbtype="str"
dt:maxLength="200"
rs:precision="0"
rs:fixedlength="true"
rs:maybenull="false" />
</s:AttributeType>
<s:AttributeType name="DOB" rs:number="3" rs:write="true">
<s:datatype
dt:type="dateTime"
rs:dbtype="variantdate"
dt:maxLength="16"
rs:precision="0"
rs:fixedlength="true"
rs:maybenull="false" />
</s:AttributeType>
<s:extends type="rs:rowbase" />
</s:ElementType>
</s:Schema>
<rs:data>
<rs:insert>
<z:row
FirstName="Hugh"
LastName="Morris"
DOB="1975-02-05T00:00:00" />
<z:row
FirstName="Anne"
LastName="Teak"
DOB="1932-06-07T00:00:00" />
<z:row
FirstName="Jean"
LastName="Poole"
DOB="1989-05-05T00:00:00" />
</rs:insert>
</rs:data>
</xml>
Creating the XML in code each time seemed rather tedious, so I made an interface to create a recordset using the RecordSetHelper. Here is an NUnit test that uses the RecordSetHelper to create an open recordset that is identical to the xml listed above.
[Test]
public void RecordsetHelperTest()
{
RecordSetHelper helper = new RecordSetHelper();
helper.AddField(new FieldItem
{
Name = "FirstName",
FieldType = VariantType.String
});
helper.AddField(new FieldItem
{
Name = "LastName",
FieldType = VariantType.String
});
helper.AddField(new FieldItem
{
Name = "DOB",
FieldType = VariantType.Date
});
helper.AddDataRow(new object[]
{
"Hugh", "Morris", "2/5/1975"
});
helper.AddDataRow(new object[]
{
"Anne", "Teak", "6/7/1932"
});
helper.AddDataRow(new object[]
{
"Jean", "Poole", "5/5/1989"
});
XDocument xRS = helper.MakeXML();
ADODB.Recordset rs = helper.GetRecordSet();
Assert.IsInstanceOf(typeof(ADODB.Recordset), rs);
Assert.AreEqual((int)ObjectStateEnum.adStateOpen, rs.State);
}
Here is the code to take the XML (returned by the MakeXml function) and turn it into an open ADODB recordset that you can manipulate. The stream is the ADODB.Stream object, not a .NET Stream object.
public Recordset GetRecordSet()
{
var doc = MakeXML();
var rs = new Recordset();
var stream = new Stream();
stream.Open(
Type.Missing,
ConnectModeEnum.adModeUnknown,
StreamOpenOptionsEnum.adOpenStreamUnspecified,
null,
null);
stream.WriteText(doc.ToString(), StreamWriteEnum.adWriteChar);
stream.Position = 0;
rs.Open(
stream,
Type.Missing,
CursorTypeEnum.adOpenUnspecified,
LockTypeEnum.adLockUnspecified, 0);
return rs;
}
The MakeXML is the method that takes all the pieces needed to create the xml string and puts them all together.
public XDocument MakeXML()
{
var root = MakeRoot();
var fields = GetFieldObjects();
var rowSetSchema = MakeFieldsXml(fields);
root.Add(rowSetSchema);
if (_rows.Count > 0)
{
var data = MakeDataXml();
root.Add(data);
}
var xdoc = new XDocument(root);
return xdoc;
}
The first thing to do was to create the XML namespace objects to be used when attaching the field and data elements. I created these as fields at the class level.
internal XNamespace xns
{
get { return "http://www.w3.org/2000/xmlns/"; }
}
internal XNamespace xnss
{
get { return "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; }
}
internal XNamespace xnsdt
{
get { return "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; }
}
internal XNamespace xnsrs
{
get { return "urn:schemas-microsoft-com:rowset"; }
}
internal XNamespace xnsz
{
get { return "#RowsetSchema"; }
}
The "MakeRoot" function creates the root element, named "xml". The rest of the code just creates each piece and adds it to the root element. There is some funny kind of syntax needed to create the namespace attributes. You have to use the "+" operator with the XNamespace object and the prefix, then add the urn as the value. I struggled for a while with this and finally looked up the answer. It still seems like some strange idiomatic syntax, but it works.
private XElement MakeRoot()
{
var root = new XElement("xml",
new XAttribute(xns + "s", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"),
new XAttribute(xns + "dt", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"),
new XAttribute(xns + "rs", "urn:schemas-microsoft-com:rowset"),
new XAttribute(xns + "z", "#RowsetSchema"));
return root;
}
Since each field is the same schema, but differs in its name, datatype, and a few other attributes, I created an abstract base class named RSField. The public GetFieldElement method calls the abstract method GetDataTypeElement, which is what creates the distinct xml for each data type correctly.
public abstract class RSField
{
internal XNamespace xns
{
get { return "http://www.w3.org/2000/xmlns/"; }
}
internal XNamespace xnss
{
get { return "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; }
}
internal XNamespace xnsdt
{
get { return "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; }
}
internal XNamespace xnsrs
{
get { return "urn:schemas-microsoft-com:rowset"; }
}
protected XElement FieldElement;
protected string FieldName { get; set; }
protected int Ordinal { get; set; }
protected RSField(string fieldName, int ordinal)
{
FieldName = fieldName;
Ordinal = ordinal;
}
protected abstract XElement GetDataTypeElement();
public XElement GetFieldElement()
{
var element = new XElement(xnss + "AttributeType",
new XAttribute("name", FieldName),
new XAttribute(xnsrs + "number", Ordinal.ToString()),
new XAttribute(xnsrs + "write", "true"));
element.Add(GetDataTypeElement());
return element;
}
}
Here is an example of an overriding class:
public class RSIntegerField : RSField
{
public RSIntegerField(string fieldName, int ordinal)
: base(fieldName, ordinal)
{
}
protected override XElement GetDataTypeElement()
{
var element = new XElement(xnss + "datatype",
new XAttribute(xnsdt + "type", "i8"),
new XAttribute(xnsdt + "maxLength", "8"),
new XAttribute(xnsrs + "precision", "0"),
new XAttribute(xnsrs + "fixedlength", "true"),
new XAttribute(xnsrs + "maybenull", "false"));
return element;
}
}
In the MakeXML method, after creating the root element, all the field objects are created. Each field object is created. The field objects are of type RSField, which contains the xml for each field.
private RSField[] GetFieldObjects()
{
var fields = new RSField[_fields.Count];
for (int i = 0; i < _fields.Count; i++)
{
FieldItem field = _fields.ElementAt(i);
switch (field.FieldType)
{
case VariantType.Integer:
case VariantType.Long:
{
fields[i] = new RSIntegerField(field.Name, (i + 1));
break;
}
case VariantType.Single:
{
fields[i] = new RSSingleField(field.Name, (i + 1));
break;
}
case VariantType.Double:
{
fields[i] = new RSDoubleField(field.Name, (i + 1));
break;
}
case VariantType.Currency:
{
fields[i] = new RSCurrencyField(field.Name, (i + 1));
break;
}
case VariantType.Date:
{
fields[i] = new RSDateField(field.Name, (i + 1));
break;
}
case VariantType.String:
{
fields[i] = new RSTextField(field.Name, (i + 1));
break;
}
case VariantType.Boolean:
{
fields[i] = new RSBooleanField(field.Name, (i + 1));
break;
}
case VariantType.Byte:
{
fields[i] = new RSByteField(field.Name, (i + 1));
break;
}
default:
{
throw new ArgumentException("Field type is not valid");
}
}
}
return fields;
}
The objects collection that this is looping through are the type of a FieldItem class used to hold the name and data type of the field. In the NUnit test above, I added several fields using FieldItem. I decided to use the Microsoft.VisualBasic namespace for the data type enum, but I could also have used the ADODB.DataTypeEnum, but the names seemed clunkier; "adBigInt" instead of just "Long".
public class FieldItem
{
public string Name { get; set; }
public VariantType FieldType { get; set; }
}
The MakeFieldsXML, which is called from the MakeXML method, takes all the fields of RSField type and creates the Linq XElement tree for all the fields. This is then attached to the root element.
private XElement MakeFieldsXml(IEnumerable<RSField> fields)
{
var schema = new XElement(xnss + "Schema",
new XAttribute("id", "RowsetSchema"));
var elementType = new XElement(xnss + "ElementType",
new XAttribute("name", "row"),
new XAttribute("content", "eltOnly"),
new XAttribute(xnsrs + "updatable", "true"));
foreach (var field in fields)
elementType.Add(field.GetFieldElement());
elementType.Add(new XElement(xnss + "extends",
new XAttribute("type", "rs:rowbase")));
schema.Add(elementType);
return schema;
}
The data is passed to the RecordSetHelper in the form of an object array. The array must have the same number of elements as the number of fields. The MakeDataXML method creates the xml that contains all the data rows.
private XElement MakeDataXml()
{
var data = new XElement(xnsrs + "data");
var insert = new XElement(xnsrs + "insert");
foreach (var row in _rows)
{
var rowElement = new XElement(xnsz + "row");
for (int i = 0; i < _fields.Count; i++ )
{
var field = _fields.ElementAt(i);
object value = row[i];
string s = value.ToString(field.FieldType);
var val = new XAttribute(field.Name, s);
rowElement.Add(val);
}
insert.Add(rowElement);
}
data.Add(insert);
return data;
}
The only thing special about this piece of code is the ToString method on the value object-type variable. Just using the base ToString didn't work since it was of type "object", and the value may have been entered as a string, it may not have internally converted it to the correct type. Also, the date fields had to be in a certain format or it would crash when you retrieved the value from the recordset. So I created an extension method overriding the base ToString on object. It just converts the object to the correct underlying data type before converting to a string.
public static string ToString(this object me, VariantType type)
{
string s;
switch (type)
{
case VariantType.Integer:
{
s = Convert.ToInt32(me).ToString();
break;
}
case VariantType.Long:
{
s = Convert.ToInt64(me).ToString();
break;
}
case VariantType.Single:
{
s = Convert.ToSingle(me).ToString();
break;
}
case VariantType.Double:
{
s = Convert.ToDouble(me).ToString();
break;
}
case VariantType.Currency:
{
s = Convert.ToDecimal(me).ToString();
break;
}
case VariantType.Byte:
{
s = Convert.ToByte(me).ToString();
break;
}
case VariantType.Date:
{
DateTime dtValue = DateTime.Parse(me.ToString());
s = dtValue.ToString("s");
break;
}
case VariantType.Boolean:
{
s = Convert.ToBoolean(me).ToString();
break;
}
default:
{
s = Convert.ToString(me);
break;
}
}
return s;
}
If you find this useful, and you would like the complete source code, let me know and I'll email you the project.
Technorati Tags:
ADODB
Linq
.NET
XML