Amit Kumar on .Net 2.0./3.0./3.5.

Hi, Friends
I am going to express a new topic which is more intresting , the topic is how we convert a ADO.Net DataSet to ADO RecordSet.
I need this conversion, when we are working BI-Reporting Application. In this project we have a third party control, its name is TeeChart6.0. control. That control takes a record set not the Dataset. When we try to pass the DataSet to TeeChart control it fires a COM Exception so, I need this conversion.

Before doing the conversion from DataSet to RecordSet , you must take the refrence of the ADOB library version 7.0.3300.0.

Now we post the complete code of it.
For doing this we create three function
1. bool CheckIntDataTpye(Type colType) : It check for the DataType
2. static ADODB.Recordset ConvertDsToRs(DataTable inTable):- It convert the DS to RS.
3. static ADODB.DataTypeEnum TranslateColumnType(Type columnType):- It is for DataTypeEnum.
Function Body
1.
private bool CheckIntDataTpye(Type colType)
{
switch (colType.UnderlyingSystemType.ToString())
{
case "System.Int16":
return true;
case "System.Int32":
return true;
case "System.Int64":
return true;
case "System.SByte":
return true;
case "System.Byte":
return true;
case "System.Decimal":
return true;
case "System.Double":
return true;
case "System.Single":
return true;
case "System.UInt16":
return true;
case "System.UInt32":
return true;
case "System.UInt64":
return true;
default:
return false;
}
}

Function Body
2.
public static ADODB.Recordset ConvertDsToRs(DataTable inTable)
{
ADODB.Recordset result = new ADODB.Recordset();
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ADODB.Fields resultField = result.Fields;
System.Data.DataColumnCollection inColumns = inTable.Columns;
foreach (DataColumn inColumn in inColumns)
{
resultField.Append(inColumn.ColumnName, TranslateColumnType(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 colIndex = 0; colIndex < inColumns.Count; colIndex++)
{
resultField[colIndex].Value = dr[colIndex];
}
}



return result;
}
Function Body
3.
private static ADODB.DataTypeEnum TranslateColumnType(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;
}

}

Note:- Used Namespaces
using System.Data.SqlClient;
using System.Data;