What I am attempting to do, is to be able to use complex objects both for retrieval and for writing back to the database.
For example, I have two classes
[Table("Children")]
public class Child
{
public int Id { get; set; }
public int ParentId { get; set; }
public string Data { get; set; }
}
[Table("Parents")]
public class Parent
{
public int Id { get; set; }
public string Data { get; set; }
public List<Child> Children { get; set; }
public Parent()
{
Children = new List<Child>();
}
}
I populate the Parent object using the method below
public List<Parent> GetAll()
{
var parents = new List<Parent>();
using (SQLiteConnection conn = new SQLiteConnection(sqlConnectionString))
{
string sql = $@"
SELECT * From Parents;
SELECT * FROM Children;
;";
var results = conn.QueryMultiple(sql);
parents = results.Read<Parent>().ToList();
var children = results.Read<Child>();
foreach (var parent in parents)
{
parent.Children = children.Where(a => a.ParentId == parent.Id).ToList();
}
}
return parents;
}
My Question now that I have my populated object, say I want to make changes to Parent including adding/updating/remove values from Parent.Children
This is my first pass at an update method, but I recognize there are issues here, such as making the determination to update or insert based on the child Id being zero or not, as well as this being a bit verbose.
Is there way, and am I missing some functionality of Dapper or Dapper.Contrib that would provide helper methods to make this process easier?
public bool Update(Parent parent)
{
bool result = true;
using (SQLiteConnection conn = new SQLiteConnection(sqlConnectionString))
{
conn.Open();
using (var tran = conn.BeginTransaction())
{
try
{
if (!conn.Update<Parent>(parent))
{
result = false;
}
foreach (var element in parent.Children)
{
if (element.Id == default(int))
{
conn.Insert<Child>(element);
}
else
{
if (!conn.Update<Child>(element))
{
result = false;
}
}
}
tran.Commit();
}
catch(Exception ex)
{
//logger.Error(ex,"error attempting update");
tran.Rollback();
throw;
}
}
}
return result;
}
