Casting from a typed ICE Table to a standard DataTable

Hi folks (and @Bart_Elia)

Is there any way to cast an ICE table, like UD07Table to a DataTable?

I cant do it explicitly, also unfortunately the Copy() method doesnt exist for the ICE tables.

//No
var UD07 = new Ice.Tablesets.UD07Table();
DataTable t = (DataTable)UD07;
//No
var UD07 = new Ice.Tablesets.UD07Table();
DataTable t = UD07.Copy();

Ended up with this…not sure it works yet, but compiling is half the battle

DataTable t = new DataTable();
Epicor.Data.BufferCopy.Copy<Ice.Tablesets.UD07Table,DataTable>(UD07,t);

Curious here, but what is the bigger picture?

Implementation of an SQLBulkInsert. I know 99% of people will smack you for trying to use SQL, but in this case, the volume of data is absurd.

If it’s a UD table I have no issues. :umbrella:

might sound clunky, but couldn’t you just hijack the cli of the DMT? UD tables with the DMT is pretty fast.

Perhaps if i wasnt already 20,000 leagues under the sea with this current implementation (which is a rework of another consultant - that makes it 100x harder)

1 Like

Darn, can be tricking picking up where others left off. But it is interesting to see how other people have solved a problem.

1 Like

That’s a System.Data.DataTable from an Ice.Tableset?

No LINQ data model table to be found in this chat?

I can confirm my buffer copy did NOT seem to work.

Correct.

Take 2:

public static class Ext
{
	public static DataTable ToDataTable<T>(this IList<T> data)
	{
	    PropertyDescriptorCollection properties = 
	        TypeDescriptor.GetProperties(typeof(T));
	    DataTable table = new DataTable();
	    foreach (PropertyDescriptor prop in properties)
	        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
	    foreach (T item in data)
	    {
	        DataRow row = table.NewRow();
	        foreach (PropertyDescriptor prop in properties)
	             row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
	        table.Rows.Add(row);
	    }
	    return table;
	}
}

So that seems to take care of the copy/cast. Now to figure what’s failing with my bulkinsert
image

Dammit - the UD fields are in a diff table :frowning:

You are doing roughly the same thing…

BufferCopy is a high speed copying API to copy between two objects without having to do a custom method to do a:

target.Field1 = source.Field1;

a few billion times for every possible combination of classes. It uses the same engine as two other similar APIs:
DataSetAdapter - Copies to and from a .NET System.Data.DataTable and an Ice.Tableset (Client side in an Impl)
TablesetAdpater - Copies from a SQL Reader into a Ice.Tableset (Server side in the ICE Data Layer from the Get Sprocs)

Example usage in Tip:

DatasetAdapter.CopyTSTableToDataTable<TipRow, TipDataSet.TipDataTable>(tableSet.Tip, dataSet.Tip);

There are a few overloads and we have changed these API sigs a few times so you need to review them on different versions.

3 Likes

FYI - What makes these so bleeding fast is we are actually standing up those billion hard coded classes in memory and compiling them in memory on demand. We cache the resulting compiled copying code.

Some serious perf gains in here.

Hi @Bart_Elia how will be the syntax if I want to copy all the information from a Db.UD02 table into a DataTable? I tried with the example usage in Tip but I’m getting some compiling errors.

Thanks

Yeah yeah it’s a necropost…

@darista to answer your question, here is how you can do it with an anonymous function at the top of your BPM if you are still looking.


Func<List<Ice.Bpm.TempTables.UD02TempRow>,DataTable> ToDataTable = (data) =>
        {
            PropertyDescriptorCollection properties =
                TypeDescriptor.GetProperties(typeof(UD02TempRow));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
            {
                Type myType =
                Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
             //   if(myType != null && !myType.FullName.Contains("Nullable<>")) 
             table.Columns.Add(prop.Name, myType);
            }
            foreach (UD02TempRow item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
                table.Rows.Add(row);
            }
            return table;
        };

USAGE:

var t = ToDataTable(ttUD02);

You’ll need at least these refs:
using System.Reflection;
using System.ComponentModel;

2 Likes