Thursday, February 01, 2007

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.

6 comments:

Anonymous said...

Looks like the listed code is from here http://www.codeproject.com/cs/database/DataTableToRecordset.asp

You will also need to add a project reference to the adodb.dll or adodb COM object (with autogenerated .NET wrapper) in order to access the ADODB namespace.

Anonymous said...

The title should be "Converting DATATABLE into Recordset". Saying DataSet gives people false impression.

Anonymous said...

Please sir send me the codez so I can steal it from Code Project and claim it as my own...

Anonymous said...

Sir, please send the codez to Steve. He has new hobby called "Converting DataSet into Recordset".

Anonymous said...

Salut! Vicki Moore . payday loans

Anonymous said...

cash advance toronto This website is a good I liked it quite a bit
AAA Toronto Payday Loans 1172 Bay St #101, Toronto, ON M5S 2B4 (416) 477-2817