CODEWITHSUNDEEP
bltoolkit
BigBlackDog
BigBlackDog

Reputation: 544

How to add parent-child relations with BLToolkit

i'd like to add some new records to a set of tables in a parent/child relationship by using BLToolkit. Unfortunately, i can't figure out what i'm missing, since after the commit, i only have the parent rows in my database.

When I inspect the Parent-Object before i add it to the parents-List, it contains the Child-Object like expected. I'm quite sure I'm missing something in the SQLQuery-Bit, but i don't know what.

Here are the things i've set up.

    CREATE TABLE [dbo].[PARENTS](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [SOME_VALUE] [int] NOT NULL,
        CONSTRAINT [PK_PARENT] PRIMARY KEY CLUSTERED 
        (
            [ID] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[CHILDS](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [PARENT_ID] [int] NOT NULL,
        [SOME_TEXT] [nvarchar](50) NOT NULL,
        CONSTRAINT [PK_CHILD] PRIMARY KEY CLUSTERED 
        (
            [ID] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[CHILDS]  WITH CHECK ADD  CONSTRAINT [FK_CHILD_PARENT] FOREIGN KEY([PARENT_ID])
    REFERENCES [dbo].[PARENTS] ([ID])
    GO

    ALTER TABLE [dbo].[CHILDS] CHECK CONSTRAINT [FK_CHILD_PARENT]

[TableName("PARENTS")]
public class Parent
{
    public Parent()
    {
        Children = new List<Child>();
    }

    [MapField("ID"), PrimaryKey, NonUpdatable]
    public int Id;

    [MapField("SOME_VALUE")]
    public int SomeValue;

    [Association(ThisKey = "ID", OtherKey = "PARENT_ID", CanBeNull = false)]
    public List<Child> Children;
}

[TableName("CHILDS")]
public class Child
{
    [MapField("ID"), PrimaryKey, NonUpdatable]
    public int Id;

    [MapField("SOME_TEXT")]
    public string SomeText;

    [Association(ThisKey = "PARENT_ID", OtherKey = "ID", CanBeNull = false)]
    public Parent Parent;
}

using (DbManager db = new DbManager())
{
    db.BeginTransaction();
    SqlQuery<Parent> query = new SqlQuery<Parent>();

    IList<Parent> parents = new List<Parent>();
    for (int i = 0; i < 10; i++)
    {
        Parent parent = new Parent();
        parent.SomeValue = i;

        Child child = new Child();
        child.SomeText = i.ToString();

        parent.Children.Add(child);
        parents.Add(parent);
    }
    query.Insert(db, 10, parents);
    db.CommitTransaction();
}

Upvotes: 0

Views: 1256

Answers (1)

Mladen Macanović
Mladen Macanović

Reputation: 1194

There is nothing wrong with your code. BLToolkit is a lightweight ORM, which means it does not keep track of the state of your objects. This is different from other ORMs like EF, L2SQL, NHibernate. In BLToolkit you work directly with the database. What you should do is:

  1. Insert Parent and get it's identity.
  2. Then Insert Child and set it the parent id.

Also, when you insert/update or delete do it inside the try/catch like this

using (DbManager db = new DbManager())
{
    try
    {
        db.BeginTransaction();

        // do something here...

        db.CommitTransaction();
    }
    catch
    {
        db.RollbackTransaction();
    }
}

Upvotes: 2

Related Questions