I'm working on a project where I need to import categories and products from another database into uCommerce.
My problem is that I receive from the external source a CategoryID from their Category table and I would like to reuse this ID inside uCommerce because this ID is used to define the category parent and the relation product to category in my external source.
I can see that CategoryID is an identity column auto incremental. However I need to insert programmatically the CategoryID (we will never use the webadmin Umbraco/uCommerce to define Categories).
I have deleted all tests categories (support and software) from uCommerce and reesed the identity to 1 :
DBCC CHECKIDENT ('uCommerce_Category', RESEED,1
Now I can insert the CategoryID that I want from SQL with :
SET IDENTITY_INSERT uCommerce_Category ON
INSERT INTO uCommerce_Category(CategoryId,Name,ProductCatalogId) Values (12,'test',24)
But I don't know how to do this programmatically.
Is uCommerce based on Subsonic ? (it seems so).
If yes, I could maybe use a direct call to SQL instead of using the UCommerce.Entities.Category class to add a new object.
What do you think ?
Do you have another idea or solution to my problem ?
I could also create a new table with the relation uCommerceCatID to external source Cat ID but then I will need to have a database layer to access it etc... if I could avoid it it's easier..
I found out that i can use the CodingHorror class to execute direct SQL as subsonic doesn't allowed to write in the identity.
But it feels a bit dirty:)
so finally i'll use a new table for relation uCommerceCatID/extSourceCatID and create a database layer with Subsonic (same version as uCommerce so I don't need a new dll :))
I'll recommend that you use the solution you decided to go with - a new table to store the relation.
The reason is, that we're moving towards using NHibernate (no worries, SubSonic won't disappear right now), as it has proven to be the best solution for our scenario.
I did study a bit all differents .Net ORM and I would be very interrested to know the reasons of your migration from Subsonic to NHibernate (and also why not Entity Framework 4 for example).
With this question I really don't mean that your decision was not good, and I don't want to criticize your decision, I just want to know it by pure curiosity :)
We're moving away from SubSonic for two reasons - performance and API design. SubSonic auto generates the basic entities, which makes the API (entities etc) very similar to the database schema, and a bit hard to consume for other developers. Apart from that, SubSonic lazy loads all relations, which we have a lot of, making it a performance hit. The parts that wasn't performing properly has already been replaced by NHibernate.
The reason why we decided to go with NHibernate is that it allows us to design the entities as pure POCO classes, instead of replacing something that is auto generated, with some else that is auto generated. I know Entity Framework supports this too.
And just to emphasize - we don't have anything against SubSonic, but it just wasn't the best match for our scenario.
the problem with nHibernate is that not all linq extensions are properly implemented (any, intersect ...) how would you advise someone who needs to build a query that can not be created within the scope of nHibernate.
Self managing CategoryID
Hello,
I'm working on a project where I need to import categories and products from another database into uCommerce.
My problem is that I receive from the external source a CategoryID from their Category table and I would like to reuse this ID inside uCommerce because this ID is used to define the category parent and the relation product to category in my external source.
I can see that CategoryID is an identity column auto incremental. However I need to insert programmatically the CategoryID (we will never use the webadmin Umbraco/uCommerce to define Categories).
I have deleted all tests categories (support and software) from uCommerce and reesed the identity to 1 :
DBCC CHECKIDENT ('uCommerce_Category', RESEED,1
Now I can insert the CategoryID that I want from SQL with :
SET IDENTITY_INSERT uCommerce_Category ON
INSERT INTO uCommerce_Category(CategoryId,Name,ProductCatalogId)
Values (12,'test',24)
But I don't know how to do this programmatically.
Is uCommerce based on Subsonic ? (it seems so).
If yes, I could maybe use a direct call to SQL instead of using the UCommerce.Entities.Category class to add a new object.
What do you think ?
Do you have another idea or solution to my problem ?
I could also create a new table with the relation uCommerceCatID to external source Cat ID but then I will need to have a database layer to access it etc... if I could avoid it it's easier..
Thank you very much,
Fabrice
Hello,
I found out that i can use the CodingHorror class to execute direct SQL as subsonic doesn't allowed to write in the identity.
But it feels a bit dirty:)
so finally i'll use a new table for relation uCommerceCatID/extSourceCatID and create a database layer with Subsonic (same version as uCommerce so I don't need a new dll :))
thanks
fabrice
Hi Fabrice,
I'll recommend that you use the solution you decided to go with - a new table to store the relation.
The reason is, that we're moving towards using NHibernate (no worries, SubSonic won't disappear right now), as it has proven to be the best solution for our scenario.
Regards,
Lasse
Hello Lasse,
OK, thanks for your answer.
I did study a bit all differents .Net ORM and I would be very interrested to know the reasons of your migration from Subsonic to NHibernate (and also why not Entity Framework 4 for example).
With this question I really don't mean that your decision was not good, and I don't want to criticize your decision, I just want to know it by pure curiosity :)
Thank you very much,
Fabrice
Hi Fabrice,
We're moving away from SubSonic for two reasons - performance and API design. SubSonic auto generates the basic entities, which makes the API (entities etc) very similar to the database schema, and a bit hard to consume for other developers. Apart from that, SubSonic lazy loads all relations, which we have a lot of, making it a performance hit. The parts that wasn't performing properly has already been replaced by NHibernate.
The reason why we decided to go with NHibernate is that it allows us to design the entities as pure POCO classes, instead of replacing something that is auto generated, with some else that is auto generated. I know Entity Framework supports this too.
And just to emphasize - we don't have anything against SubSonic, but it just wasn't the best match for our scenario.
- Lasse
Great thank you very much !
hello,
the problem with nHibernate is that not all linq extensions are properly implemented (any, intersect ...) how would you advise someone who needs to build a query that can not be created within the scope of nHibernate.
this is my query
var productsForDisplay = ProductProperty.All()
.Where(pp => keyValuePairs.Any(pair =>
pp.ProductDefinitionFieldId == pair.Key && pp.Value == pair.Value));
I was also wondering if you could explain me how to execute an sql statement using the repository<T> class
thx
is working on a reply...