Insert new record, I ended up modifying editor.cs to get it to work

bjlap

Hi,
I use editor to do crud operations on a table. The table has a primary key, some fields and two foreign keys, nothing wild. On the server side I work with .NET MVC.

When building the code for inserting records I ran into the following issue:
The database uses Guids as unique primary keys.
For adding a primary key value to a new record I use a method that is called by the editor'.precreate event. So far so good (I think)

            var db = new DataTables.Database("sqlserver", connectionString);
            var editor = new Editor(db, "HoursEntry", "Id").Model<DataTableHoursEntryCreate>()
                .Field(new Field("HoursEntry.Id", "Id"))
                .Field(new Field("HoursEntry.EntryCreated", "EntryCreated").GetFormatter(Format.DateSqlToFormat(dateTimeFormatInfo.SortableDateTimePattern)))
                .Field(new Field("HoursEntry.Date", "Date").GetFormatter(Format.DateSqlToFormat(dateTimeFormatInfo.ShortDatePattern)))
                .Field(new Field("HoursEntry.Hours", "Hours").GetFormatter(Format.NullEmpty()))
                .Field(new Field("HoursEntry.Minutes", "Minutes").GetFormatter(Format.NullEmpty()))
                .Field(new Field("HoursEntry.Travel", "Travel", typeof(Boolean)))
                .Field(new Field("HoursEntry.Comment", "Comment"))
                .Field(new Field("HoursEntry.ActivityId", "ActivityId", typeof(Guid)))
                .Field(new Field("HoursEntry.ProjectId", "ProjectId", typeof(Guid)))
                .Field(new Field("HoursEntry.ApplicationUserId", "ApplicationUserId"))
                .Field(new Field("Project.Name"))
                .Field(new Field("Activity.Name"))
                .LeftJoin("Project", "Project.Id", "=", "HoursEntry.ProjectId")
                .LeftJoin("Activity", "Activity.Id", "=", "HoursEntry.ActivityId");
editor.PreCreate += prepareDataForCreate;

 private void prepareDataForCreate(object sender, PreCreateEventArgs e)
        {
            // Set primary key
            var id = Guid.NewGuid();
            e.Values.Add("Id", id);
...

The problem is that if I run this code, editor will insert the new record ok, but the json returned contains all records of the table because the primary key is not used when retrieving data to return.

Eventually I ended up adding a line to editor.cs to get this going. I don't like it because now I have changed editor.cs just to get something working for my project.

I have tried specifying "HoursEntry.Id" instead of "Id" as primary key field in the editor definition, but to no avail.

When I add the last 3 lines in the below code in editor.cs in the method _Insert, the problem is solved and the primary key is used to retrieve data.

       private Dictionary<string, object> _Insert(Dictionary<string, object> values)
        {
            if (PreCreate != null)
            {
                PreCreate(this, new PreCreateEventArgs
                {
                    Editor = this,
                    Values = values
                });
            }

            // Insert the new row
            var id = _InsertOrUpdate(null, values);

            // Was the primary key sent? Unusual, but it is possible
            var pkeyField = _FindField(_pkey, "name");
            if (pkeyField != null && pkeyField.Apply("create", values))
            {
                id = values[_pkey];

            }

            // I added the below lines:
            if (id == null)
            {
                id = values["Id"];
            }
            ...

I probably do something wrong and this is why I have to update editor.cs.
Perhaps I shouldn't map from "Hours.Entry.columname" to "columnname"?
(I do that because that's how the variables in the post come in. Perhaps I should change that to include table names).
Thanks,
Bert-Jan

allan

Hi,

Instead of e.Values.Add("Id", id); could you try:

editor.Field('HoursEntry.Id').SetValue(id);

The reason being is that this will let the code with the comment Was the primary key sent? find that field and use it accordingly.

The reason the comment mentioned that this is unusual, is that generally I expect Editor to work with a database that will set the primary key itself on insert (using a trigger or sequence). But the method you use is perfectly valid and should work!

Regards,
Allan