Connect Office Apps to .NET with "Lite" Web Services

Listing 1 ConvertDataTableToRecordset

// Converts the specified DataTable into an ADO Recordset.
static public ADODB.Recordset ConvertDataTableToRecordset(DataTable table) 
{
  // Create a new (disconnected) Recordset
  ADODB.Recordset recordset = new ADODB.Recordset();

  // Create an array for the names of the colums to use
  // later with the AddNew method of the Recordset.
  object [] fieldList = new object[table.Columns.Count];

  // Loop through all of the columns in the DataTable
  // and define each as a new field in the Recordset;
  // also add the name to the fieldList array.
  for (int i=0; i < table.Columns.Count; i++) 
  {
    // get the field name, or create one if necessary
    string fieldName = table.Columns[i].ColumnName;
    if ( fieldName == null || fieldName.Length == 0 )
      fieldName = string.Format("Column{0}", i);
    fieldList[i] = fieldName;
			
    // Lookup the field's equivalent ADO type and maximum size
    ADODB.DataTypeEnum adoType;
    int adoSize;
    GetADOType(table.Columns[i].DataType, out adoType, out adoSize);

    // add the field to the ADO Recordset
    recordset.Fields.Append(fieldName, adoType, adoSize,
      ADODB.FieldAttributeEnum.adFldIsNullable, null);

    // These generic values appear to work well with any size
    // decimal/numeric; setting them for other types does not
    // cause harm, but they are necessary for decimals.
    recordset.Fields[i].Precision = 0;
    recordset.Fields[i].NumericScale = 25; //the maximum
  } //for

  // Now that the fields are defined, open the Recordset so we may
  // add the data. Notice that because C# does not work like VB with
  // optional parameters, we use the special Missing object which
  // is defined in System.Reflection.
  recordset.Open(Missing.Value, Missing.Value,
    ADODB.CursorTypeEnum.adOpenUnspecified,
    ADODB.LockTypeEnum.adLockUnspecified, -1);

  for (int i=0; i < table.Rows.Count; i++ )
  {
    // get the current record's values into an array
    object [] values = table.Rows[i].ItemArray;

    // ADO does not recognize GUID, or unique identifiers,
    // unless they are wrapped in curly braces, we check for
    // any occurences of them here and convert them to a
    // string in the format "{00000000-0000-0000-0000-000000000000}"
    for (int j=0; j < values.Length; j++ )
      if ( values[j] is System.Guid )
        values[j] = '{' + values[i].ToString() + '}';

    // add the current record to the ADO Recordset
    recordset.AddNew(fieldList, values);
  }

  // Done - Recordset complete!
  return recordset;
}