ORM Magic–Importing from any datasource

This post will be on how to use the approach described in Fast prototyping requires an initial data import mechanism with Excel as input source. The technic is based on System.Data.DataSet class and can be applied to any type of input source!

Thanks to your reports and of course Slava’s help (XPO guru), XVideoRental importing mechanism has been improved a lot and now is faster, has notification events and is decoupled from XAF’s TypesInfo system. This makes it possible to simply copy the ImportData.cs file found in Common.Win project and use it in non XAF solutions!

In previous version this importing mechanism worked only when matching in types of input and output members. This means that if my output Oid property was a System.Guid then input should be of the same type. Now we have workaround this minor issue. If conversion is possible it will be done automatically without the need of any configuration or extra coding!

To better design, support, extend and describe the importing mechanism I used Mspec a functional testing framework and I open source the specifications in our community project eXpandFramework. These follow bellow,

MemberMapper, When Class Has a flag that includes all members
» should collect all members that belong to the class
» should not collect any base class member
» should create new members for each one of them

MemberMapper, When class does not have a flag that includes all ownmembers
» should not create members for those not marked with the attribute
» should create members and name them according to the attribute data
» should create a key property if not included in the conficuration

MemberMapper, When class attribute has data for base members
» should create and name them according to the attribute data
» should map their attributes

MemberMapper, When class is marked to include all member but member is marked seperately
» should use the member marking

MemberMapper, When mapping a referenced member
» should create a member with a dynamic type ownwer

MemberMapper, When reference type is not includedIN the configuration
» should create non reference member

ClassMapper, When creating dynamic classes
» should Create Classes only for the ones that have a marking attribute
» should name the classes taking data from marking attribute

ClassMapper, When class has many to many collection marked for importing
» should create intermediate classes
» should create an auto genareted key for this class
» should create 2 columns with names taken from the marked attribute

DictionaryMapper, When is mapping a dictionary
» should map all marked objects

InitDataImporter, When importing an object
» should create a new output object
» should assign all mapped properties

InitDataImporter, When input membertype is different than output
» should convert the value when simple type
» should convert the value when reference type


21 passed, 0 failed, 0 skipped, took 1.61 seconds (Machine.Specifications 0.5.2-98b543c).

The above specifications are evaluated with every eXpand build and you can see them in eXpand’s build server. Take a quick look at their code located in github to see how easy it is to write them (http://goo.gl/TNv4d).

Now lets see how to use our magic XPO ORM to import into the real world business domain like the one of our XVideoRental demo,

image

 

For the sake of complexity the input source will be a list of excel files and not just a simple Excel file with many sheets.

image

XPO supports a DataStore provider which is based on DataSet. So, first we fill a DataSet with the excel files and for this I will use some DataSet extension methods I contributed to eXpand (see http://goo.gl/TF26g)

static DataSet DataSet() {

    var dataSet = new DataSet();

 

    dataSet.ImportExcelXLS(new[]{

        "Customer", "Receipt", "Company", "CompanyType", "Country", "Employee", "Language", "Movie", "MovieArtist",

        "MovieArtistLine", "MovieCategory", "MovieCategoryPrice", "MovieCompany", "MovieItem",

        "MovieMovies_CountryCountries", "MoviePicture","Person","Artist","ArtistPicture","Rent"

    });

 

    return dataSet;

}

 

Now we are ready to create a UnitOfWork with this DataSet as DataStore like,

 

var inputUnitOfWork = new UnitOfWork(new SimpleDataLayer(new DataSetDataStore(DataSet(), AutoCreateOption.None)));

 

and finally call the Import method of the InitDataImporter class as shown bellow

 

var initDataImporter = new InitDataImporter();

            var unitOfWork = OutputUnitOfWork();

            var inputUnitOfWork = new UnitOfWork(new SimpleDataLayer(new DataSetDataStore(DataSet(), AutoCreateOption.None)));

            initDataImporter.Import(() => new UnitOfWork(unitOfWork.ObjectLayer), () => new UnitOfWork(inputUnitOfWork.ObjectLayer));

This simple, however powerful technic can be applied to any type of input source as long as you are able to fill a DataSet!

You can download a sample project was created with v12.2.5 of our Suite from Importer.Console.

Happy New Year to everybody!

9 comment(s)
Carlitos

Brilliant!!!

29 December, 2012
James Zhong

Thanks Apostolis for your very useful data importing article!

31 December, 2012
Khanh_T

great!

31 December, 2012
Alain Bismark

Hi Tolis

I reviewed the example Excel files.

Basically the data that contains, is the same as we exported previously the tables of database.

In the real life, the things doesnt work like this.

For example, the tables that represent relationships (MovieMovies_CountryCountries) in your example are using GUID Oids to express the relation. This kind of information is nos easy to build by human.

In the ERP world for example, you create this file, using several human readable columns, to specify a key, and late when you perform the import, the system solve the relation using the IDs of the records relateds. Using this techniques, you can create human readable Excels files.

The other thing that I can not check in your example was the master-detail import technique using one single file containing columns with data of the master and child.

Best regards

1 January, 2013
Apostolis Bekiaris (DevExpress)

@Alain The current implementation may not support all existing scenarios, however you can use our support center along with a detail description and if possible excel sample + domain sample and I will be happy to extend this mechanism further

4 January, 2013
Alain Bismark

Thanks Tolis!

Best regards

7 January, 2013
Mohsen Benkhellat

Thanks Tolis for sharing this.

I am in the process of using it as a basis in a current project for an integration framework in addition to initializing data.

As an interface, I would add Match(params string Keys) to apply for a class that will help in knowing which columns in output table guaranty uniqueness (and thus matches any existing record) using a combination of values from a row in source table.

Having designed this module, you know better than anybody if it makes sense to add this functionality (matching) or not. What do you think?

Thanks for any advice.

10 January, 2013
Apostolis Bekiaris (DevExpress)

The current implementation does not query output datasource (assumes it is empty), so

be careful on the performance impact. You need to make something really smart and work with the input datasource where all your objects exist in memory. In any case let us know your results, or you may contribute to eXpand framework for some initial testing from our open source users

10 January, 2013
Mohsen Benkhellat

Indeed, I was (and still am) worried about performance with big data volume. I started making changes for instance to not clear cached imported data between tables as my version works with all OLEDB tables (sheets in Excel) in one database (file in Excel).

If things go well, I may contribute to expand with it.

Thanks and regards

10 January, 2013

Please login or register to post comments.