Converting DataSet into Recordset

         

           I was working on one of my most challenging application, before few months. In that we have to use third party DLL for generating PDF file in ASP.NET 2.0. The requirement is that we have to use the DLL which client was using for his ASP application. The main hurdle  for me is that, the DLL accepts only Recordset as input.

           I search on the net however I did not found any solution. After few days of searching I tried to implement by my own. I took the help of my senior "Nikhil" and we foung the solution. Below is the code for converting DataSet into Recordset.

 

static public ADODB.Recordset ConvertToRecordset(DataTable inTable)
{
     ADODB.Recordset result = new ADODB.Recordset();
     result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

     ADODB.Fields resultFields = result.Fields;
     System.Data.DataColumnCollection inColumns = inTable.Columns;

    foreach (DataColumn inColumn in inColumns)
    {
          resultFields.Append(inColumn.ColumnName
         , TranslateType(inColumn.DataType)
         , inColumn.MaxLength
         , inColumn.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable :
         ADODB.FieldAttributeEnum.adFldUnspecified
        , null);
    }

    result.Open(System.Reflection.Missing.Value
   , System.Reflection.Missing.Value
   , ADODB.CursorTypeEnum.adOpenStatic
   , ADODB.LockTypeEnum.adLockOptimistic, 0);

   foreach (DataRow dr in inTable.Rows)
   {
              result.AddNew(System.Reflection.Missing.Value,
              System.Reflection.Missing.Value);

              for (int columnIndex = 0; columnIndex < inColumns.Count; columnIndex++)
             {
                     resultFields[columnIndex].Value = dr[columnIndex];
              }
     }

     return result;
}

static ADODB.DataTypeEnum TranslateType(Type columnType)
{
         switch (columnType.UnderlyingSystemType.ToString())
        {
                    case "System.Boolean":
                               return ADODB.DataTypeEnum.adBoolean;

                    case "System.Byte":
                              return ADODB.DataTypeEnum.adUnsignedTinyInt;

                   case "System.Char":
                              return ADODB.DataTypeEnum.adChar;

                   case "System.DateTime":
                              return ADODB.DataTypeEnum.adDate;

                   case "System.Decimal":
                              return ADODB.DataTypeEnum.adCurrency;

                   case "System.Double":
                             return ADODB.DataTypeEnum.adDouble;

                   case "System.Int16":
                              return ADODB.DataTypeEnum.adSmallInt;

                   case "System.Int32":
                             return ADODB.DataTypeEnum.adInteger;

                    case "System.Int64":
                              return ADODB.DataTypeEnum.adBigInt;

                   case "System.SByte":
                             return ADODB.DataTypeEnum.adTinyInt;

                   case "System.Single":
                            return ADODB.DataTypeEnum.adSingle;

                   case "System.UInt16":
                             return ADODB.DataTypeEnum.adUnsignedSmallInt;

                    case "System.UInt32":
                              return ADODB.DataTypeEnum.adUnsignedInt;

                   case "System.UInt64":
                             return ADODB.DataTypeEnum.adUnsignedBigInt;

                    case "System.String":
                   default:
                             return ADODB.DataTypeEnum.adVarChar;
         }
}

 

You can email me, if you have any questions.

Happy Programming.