Migrating RDS CreateRecordSet in .NET

19. June 2008 06:24 by Mrojas in General  //  Tags: , ,   //   Comments (0)

This is way a discused with a friend for migrating a VB6 RDS CreateRecordset

 

Private Function Foo(rs As ADOR.Recordset) As Boolean
    On Error GoTo Failed
    Dim ColumnInfo(0 To 1), c0(0 To 3), c1(0 To 3)
    Dim auxVar As RDS.DataControl
    Set auxVar = New RDS.DataControl
 
    ColInfo(0) = Array("Value", CInt(201), CInt(1024), True)
    ColInfo(1) = Array("Name", CInt(129), CInt(255), True)
   
    Set rs = auxVar.CreateRecordSet(ColumnInfo)
    Foo = True
    Exit Function
Failed:
    Foo = False
    Exit Function
    
End Function

 

According to MSDN the CreateRecordset function takes a Varriant array with definitions for the columns. This definitions are made up of four parts

 
Attribute Description
Name Name of the column header.
Type Integer of the data type.
Size Integer of the width in characters, regardless of data type.
Nullability Boolean value.
Scale (Optional) This optional attribute defines the scale for numeric fields. If this value is not specified, numeric values will be truncated to a scale of three. Precision is not affected, but the number of digits following the decimal point will be truncated to three.
 

So if we are going to migrate to System.Data.DataColumn we will used a type translation like the following (for now I’m just putting some simple cases)

 
Length Constant Number DataColumn Type
Fixed adTinyInt 16 typeof(byte)
Fixed adSmallInt 2 typeof(short)
Fixed adInteger 3 typeof(int)
Fixed adBigInt 20  
Fixed adUnsignedTinyInt 17  
Fixed adUnsignedSmallInt 18  
Fixed adUnsignedInt 19  
Fixed adUnsignedBigInt 21  
Fixed adSingle 4  
Fixed adDouble 5  
Fixed adCurrency 6  
Fixed adDecimal 14  
Fixed adNumeric 131  
Fixed adBoolean 11  
Fixed adError 10  
Fixed adGuid 72 typeof(System.Guid)
Fixed adDate 7 Typeof(System.DateTime)
Fixed adDBDate 133  
Fixed adDBTime 134  
Fixed adDBTimestamp 135  
Variable adBSTR 8  
Variable adChar 129 typeof(string)
Variable adVarChar 200 typeof(string)
Variable adLongVarChar 201 typeof(string)
Variable adWChar 130  
Variable adVarWChar 202  
Variable adLongVarWChar 203  
Variable adBinary 128  
Variable adVarBinary 204  
Variable adLongVarBinary 205  
So the final code can be something like this: private bool Foo(DataSet rs)

{

try

{

DataColumn dtCol1 = new DataColumn("Value",typeof(string));
dtCol1.AllowDBNull = true;
dtCol1.MaxLength = 1024;

DataColumn dtCol2 = new DataColumn("Name",typeof(string));

dtCol2.AllowDBNull = true;
dtCol2.MaxLength = 255;
DataTable dt = rs.Tables.Add();

dt.Columns.Add(dtCol1);

dt.Columns.Add(dtCol2);

return true;

}

catch

{

return false;

}

}

 

NOTES:

My friend Esteban also told my that I can use C# 3 syntax and write something even cooler like:

DataColumn dtCol1 = new DataColumn()

{

ColumnName = "Value",

DataType = typeof (string),

AllowDBNull = true,

MaxLength = 1024

};