PetaPoco (DotNetNuke) and SQL - adding an object with a List property or other compound object
I am using PetaPoco based data access layer (DotNetNuke 7.0). I've used it successfully when working with relatively simple objects, but now I need to insert an object containing at least one property that is a list of other objects.
For example:
class Person
{
public Person(){}
public string name { get; set; }
public List<Address> addresses { get; set; }
}
class Address
{
...
}
The actual object I'm working with is much more complex than the example above - there are at least four composite List objects in the object that needs to be inserted into the repository.
What I would like to do is define a table in SQL and make a simple call to PetaPoco like this:
public static void AddOrder(Person person)
{
using (IDataContext context = DataContext.Instance())
{
var repository = context.GetRepository<Person>();
repository.Insert(person);
}
}
This is based on the fact that the object is passed to the web service from the Knockout / jQuery interface, so the JSON string is converted to a data object, which must then be stored in the database.
I think there are three questions:
-
How do I write a SQL table that represents Person and contains a list of addresses?
-
How do I write the necessary PetaPoco code to insert a Person object along with any objects it contains?
-
Should I forget about trying to store the object in the database and just store the JSON string in the database?
Thanks for watching :)
source to share
I haven't installed DotNetNuke 7 yet, however I have looked into the source code at codeplex and I think you can do it like this:
public static void AddOrder(Person person)
{
using (IDataContext context = DataContext.Instance())
{
var repositoryPerson = context.GetRepository<Person>();
var repositoryAddrress = context.GetRepository<Address>();
context.BeginTransaction();
try
{
repositoryPerson.Insert(person);
foreach(var address in person.addresses)
{
repositoryAddress.Insert(address);
}
context.Commit();
}
catch (Exception)
{
context.RollbackTransaction();
throw;
}
}
}
I have not tested it, so I cannot guarantee that it works, however it seems correct to me.
source to share