Geeks With Blogs
Annie Bougie
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:




Posted on Thursday, July 2, 2009 12:54 PM C# Code | Back to top


Comments on this post: Create an ADODB Recordset in code from XML

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
You mentioned sn offer to e-mail the code. I've been struggling to convert COM ADO Recordsets to .NET ADO Recordsets and believe this will get me much closer. May I please take you up on your offer of the sources?
Left by Scott Mills on Sep 27, 2009 12:44 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hai there.. i have the same problems for convert xml schema row to dataset.. can you send me to email what you've been coding for create an adodb in code from xml
Left by prumz on Mar 14, 2010 7:49 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hi,

Like what you've done.

Would you please send me the code also.

thanks.
Left by bevan on Mar 21, 2012 10:06 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hi Anne!!!
I need your code for create an ADODB Recordset code from XML.
(http://geekswithblogs.net/AnneBougie/archive/2009/07/02/create-an-adodb-recordset-from-xml.aspx)

regards
José


Left by José Donoso on Aug 27, 2012 6:22 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hi Anne,

Theat would be great if you can send me the code.

Thanks and regards,
Wesley
Left by wesley on Dec 06, 2012 2:31 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
thank you for your help, can you send me your code
Left by Mohamed EL Zahar on Dec 24, 2012 10:03 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
This is great post. Could you please share the code for this class.
Left by Nirmal on Jun 03, 2013 5:19 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
could you pls email me the code.
Left by avinash on Jun 19, 2013 3:12 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Please email code. Thank you!
Left by David Carr on Jul 12, 2013 8:22 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
I have been struggling with this for quite some time and would LOVE to have the complete code. You Rock!! Thanks!!
Left by Norma on Aug 08, 2013 11:53 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Please, email me the source code.

thank you so much.
Left by Thomson on Jan 21, 2016 7:31 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Please e-mail me the source.

Thanks.
Left by Richard on Jan 14, 2017 10:57 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Could you email me the source code?
Thank you so much.
Left by Jason O on May 24, 2017 8:18 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Could you please email me the source code?
Thank you so much Anne.
Left by Andrea Ceroni on Jun 12, 2017 7:35 PM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hi Anne,

Could you please email me the source code?

Thanks and regards,
Igor
Left by Igor on Jul 18, 2017 2:43 AM

# re: Create an ADODB Recordset in code from XML
Requesting Gravatar...
Hi Anne,
Could you send me the code, thanks!
Left by frank nong on Sep 14, 2017 6:39 PM

Your comment:
 (will show your gravatar)


Copyright © Annie Bougie | Powered by: GeeksWithBlogs.net