Accessing an external database

Topics: General, Troubleshooting, Writing modules
Jun 29, 2012 at 9:23 AM
Edited Jun 29, 2012 at 9:26 AM

I have some hierarchical information that I need to store; a Company which has many Logos, many Users and each User has some Addresses.

I thought it best to put this information into an external database but am running in to quite a few problems accessing it which makes me thing I am going about it the wrong way.

Problem 1. I got an "The operation is not valid for the state of the transaction." exception when I tired to access the data. I got around this by wrapping the access code in a 'using (new TransactionScope(TransactionScopeOption.Suppress))' block.

Problem 2. Due to lazy loading, when I tried to access the entity model in my view, I still got the exception. Disabling lazy loading didn't work so I had to do something like this: address.AddressId = address.AddressId; just after my Where() method to work around the issue.

Problem 3. I get the same exception again when I try and update the model and suppressing the transaction scope doesn't seem to help.

 

I'm just wondering if I am going about this the wrong way, maybe I should store my data in content items instead? If anyone can suggest a solution to any of the problems or a better way of storing the data, it'd be greatly appreciated.

Thanks

 

Developer
Jun 29, 2012 at 9:47 AM

Am I currently assuming that you tried to use EF? Orchard has a beautiful content model with powerful data-access services, so in your place I'd definitely look into that first. Since Orchard uses record classes for ORM in the end, you can also use records directly through an IRepository<> implementation. This gives you pretty much everything you'd want to do with any SQL database in terms of querying. Therefore I really don't know why you'd want to use anything external if you have a choice (e.g. there is no existing DB that should be utilized)?

Jun 29, 2012 at 10:17 AM
Edited Jun 29, 2012 at 10:19 AM

Thank you for the reply. Yes, that's correct. I was running Orchard from source and it was taking a while to lunch from VS so my plan was to develop the system in isolation then drop it into a module.

Do you happen to know of any documentation for using IRepository to store custom types? Is it possible to create tables based on an object? I had a quick scan through the Orchard docs but couldn't see anything.

Thanks

Developer
Jun 29, 2012 at 10:37 AM

Then I think you should try to do the whole thing inside Orchard first. I don't know of any documentation, but to use records directly (i.e. not whole content items) you need the following:

  • A DB table. You can create this with migrations. Take a look at the Migrations classes of the built-in modules for examples for how record tables can be created (I think there is also some documentation on migrations). One thing to note is that you'll need to create a stand-alone table (i.e. not a ContentPartRecord one, for example), so you have to specify the primary key yourself. You'll need an int column with the name "Id".
  • Create a corresponding record. This is a simple class with only parameterless ctors and only virtual public properties. Don't forget to include the Id property here too.
  • Inject an IRepository<RecordType> into the class you want to use it. It will give you a whole bunch of functionality. The interface is straightforward and easy to use.

Also, Sipke's blog has some great tutorials.

BTW, see this for speeding up debugging.

Jun 29, 2012 at 10:54 AM

Great, thanks very much, that makes sense, I will give it a go. As its happens, I was reading Spikes blog right before I checking this form, great place!