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;
