Serenity: My UpdateJoinedRow method

[Estrusco]

I have written a generic method for update two or more table related in a view row, as extension table.
I write it here hoping that it will be useful to someone.

Related to issues 778 and 1114.

I start writing a new attribute, OriginalColumnAttribute and a new interface.

The attribute:

using System;

namespace MyProj.Data
{
    /// <summary>
    /// Set the name of the field in the related table
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)]
    public class OriginalColumnAttribute : Attribute
    {
        public string OriginalColumnName { get; }

        /// <summary>
        /// The name of the field in the related table
        /// </summary>
        public OriginalColumnAttribute(string OriginalColumnName)
        {
            this.OriginalColumnName = OriginalColumnName;
        }
    }
}

The interface:

using Serenity.Data;

namespace MyProj.Data
{
    public interface IIdParentRow
    {
        Int32Field IIdParent { get; set; }
    }
}

In the view that work as view, I use the new attribute in the expression fields.

    [LeftJoin("jExtend1", "[dbo].[xyzExtendTable1]", "T0.[ID] = jExtend1.[IDParent]")]
    [LeftJoin("jExtend2", "[dbo].[xyzExtendTable2]", "T0.[ID] = jExtend2.[IDParent]")]
    public sealed class xyzViewRow : Row, IIdRow, INameRow
    {
        [DisplayName("Id"), Column("ID"), Identity]
        public Int32? Id
        {
            get { return Fields.Id[this]; }
            set { Fields.Id[this] = value; }
        }

        [DisplayName("xyzField"), Column("xyzField"), NotNull]
        public String xyzField
        {
            get { return Fields.xyzField[this]; }
            set { Fields.xyzField[this] = value; }
        }

        [...]

        [DisplayName("from table 1")]
        [Updatable(true), Expression("jExtend1.[extField1]"), MinSelectLevel(SelectLevel.Details)]
        [OriginalColumn("extField1")]
        public Boolean? jExtend1extField1
        {
            get { return Fields.jExtend1extField1[this]; }
            set { Fields.jExtend1extField1[this] = value; }
        }

        [...]

        [DisplayName("from table 2")]
        [Updatable(true), Expression("jExtend2.[extField2]"), MinSelectLevel(SelectLevel.Details)]
        [OriginalColumn("extField2")]
        public Boolean? jExtend1extField2
        {
            get { return Fields.jExtend1extField2[this]; }
            set { Fields.jExtend1extField2[this] = value; }
        }

        [...]

The joined rows must be implement the interface IIdParentRow

    public sealed class xyzExtendTable1Row : Row, IIdRow, IIdParentRow 
    {
        [DisplayName("Id"), Column("ID"), Identity]
        public Int32? Id
        {
            get { return Fields.Id[this]; }
            set { Fields.Id[this] = value; }
        }

        [DisplayName("IdParent"), Column("IDParent"), NotNull]
        [ForeignKey("[dbo].[xyzViewTable]", "ID")]
        public Int32? IdParent
        {
            get { return Fields.IdParent[this]; }
            set { Fields.IdParent[this] = value; }
        }

        [...]

        public Int32Field IIdParent
        {
            get { return Fields.IdParent; }
            set { Fields.IdParent = value; }
        }

        [...]

Then here are the methods. The second overload with the parameter joinName is for the views with two or more joins in which are fields with same name.

        public void UpdateJoinedRow<TViewRow, TJoinedRow>(TViewRow viewRow, TJoinedRow joinedRow)
            where TViewRow : Row, IIdRow
            where TJoinedRow : Row, IIdRow, IIdParentRow, new()
        {
            UpdateJoinedRow<TViewRow, TJoinedRow>(viewRow, joinedRow, string.Empty);
        }

        public void UpdateJoinedRow<TViewRow, TJoinedRow>(TViewRow viewRow, TJoinedRow joinedRow, string joinName)
            where TViewRow : Row, IIdRow
            where TJoinedRow : Row, IIdRow, IIdParentRow, new()
        {
            bool Create = IsCreate;
            Int32 viewRowId = (Int32)viewRow.IdField[viewRow].Value;
            if (viewRowId == 0)
                throw new Exception("ViewRowId = 0 in " + nameof(UpdateJoinedRow));

            TJoinedRow oldRow = null;
            if (!IsCreate)
                oldRow = Connection.TryFirst<TJoinedRow>(joinedRow.IIdParent == viewRowId);

            TJoinedRow updL = null;
            if (oldRow != null)
                updL = oldRow;
            else
            {
                Create = true;
                updL = new TJoinedRow();
                updL.IIdParent[updL] = viewRowId;
            }

            var ppi = typeof(TViewRow).GetProperties();

            foreach (var pi in ppi)
            {
                OriginalColumnAttribute ca = (OriginalColumnAttribute)Attribute.GetCustomAttribute(pi, typeof(OriginalColumnAttribute));
                if (ca != null && !string.IsNullOrWhiteSpace(ca.OriginalColumnName))
                {
                    Field searchField = updL.FindField(ca.OriginalColumnName);
                    if (Field.Equals(searchField, null))
                        continue;

                    if (string.IsNullOrEmpty(joinName) ||
                        (!string.IsNullOrEmpty(joinName) && viewRow.FindField(pi.Name).Join.Name == joinName))
                        updL[ca.OriginalColumnName] = viewRow[pi.Name];
                }
            }

            if (Create)
                Connection.Insert(updL);
            else
                Connection.UpdateById(updL, ExpectedRows.One);
        }

Then in MySaveHandler

            protected override void AfterSave()
            {
                base.AfterSave();

                if (Row != null)
                {
                    UpdateJoinedRow(Row, new xyzExtendTable1Row(), "jExtend1");
                    UpdateJoinedRow(Row, new xyzExtendTable2Row(), "jExtend2");
                }
            }

Enjoy

[edson]

Thanks @Estrusco !

[volkanceylan]

Nice to see you guys moving to advanced level...

[Estrusco]

You are welcome :-)

[dfaruque]

Thank you for sharing.

[marcobisio]

Thank you very much @Estrusco for sharing your code with us!

[volkanceylan]

Thanks for the basic idea @Estrusco. I was thinking about something similar for sometime, and finally got it into Serenity itself.

2.4.12.1 has details part under Customer dialog, which actually saves into an extension table (CustomerDetails), It's an integrated and hopefully clean enough approach.

[Estrusco]

You are welcome @volkanceylan :)