.NET Editor Validation with DB Query

wadeparallon

Description of problem: I've search the forums and I haven't really found a good example that was definitive and the documentation leads me to a direction that I just need a little more hand holding on. I feel like the pieces are there but I haven't gotten it exactly right or maybe I'm just asking for the best practice here.

I have a table of Rates. These are based on Organizations. As such, it must be unique by Year per Organization.

Based on the documentation I've accomplished this:

.Field(new Field("Year")
                .Validator(Validation.NotEmpty())
                .Validator(Validation.Numeric())
                .Validator((val, data, host) =>
                {
                    //Return NULL is passing validation
                    if (val == null) return null;
                    var exists = host.Db
                        .Sql($"SELECT CASE WHEN EXISTS (SELECT * FROM Rates WHERE OrganizationId={id} AND [Year]='{val}') THEN 'TRUE' ELSE 'FALSE' END as doesExist")
                        .Fetch();
                    return exists["doesExist"].ToString() == "TRUE" ? $"Rate already exists for year {val}" : null;

                })

However, this also fires on CREATE and EDIT, which prevents editing an existing year.. because it exists.

Do I need to duplicate this code in a .NET event like preCreate and preEdit? I can't find an example of this in the .NET package I've downloaded and this would be very helpful if was included in those demos.

allan

Hi,

There are two different ways to handle this:

1) As you say, using PreCreate and PreEdit to add different validators based on the action being performed:

editor.PreCreate += (sender, e) => {
  editor.Field("Year").Validator(...);
};


editor.PreEdit += (sender, e) => {
  // Use e.Id
  editor.Field("Year").Validator(...);
};

2) You can check inside your validator if you are doing an edit by looking at host.Action. If it is "edit" then use host.Id to get the row currently being edited to be able to discount it in the query.

The second method is what the .NET libraries for Editor uses. You can see the code for that here.

Allan

wadeparallon

It didn't occur to me that .Validator could be added in the events as well. I've just been using PreCreate and PreEdit to set values (modifiedby/createdby).

I was looking at Unique but didn't see a way I could customize the query. Like it has to be unique per Org, so I could have a 2019 twice (year column), as long as its in 2 different Orgs.

allan

Yes, the Unique validator can't be customised in that way (it is a good idea to allow uniqueness over two columns though), but its code might be useful as a reference when writing custom validators.

Allan

wadeparallon

This is what I came up with, just to close the loop.

.Validator((val, data, host) =>
                {
                    //Return NULL is passing validation
                    if (val == null) return null;
                    
                    var query = $"SELECT * FROM Rates WHERE OrganizationId={id} AND [Year]='{val}'";
                    
                    if (host.Action == "edit")
                    {
                        query += $" AND [Id] != {host.Id}";
                    }
                    var queryResult = host.Db
                        .Sql($"SELECT CASE WHEN EXISTS ({query}) " +
                             $"THEN 'true' ELSE 'false' END as doesExist")
                        .Fetch();

                    return  Convert.ToBoolean(queryResult["doesExist"]) ? $"Rate already exists for year {val}" : null;
                    
                })

Having an overload for Unique would be helpful... Like a "compound key" or a List of Columns, if you will.

allan

I think you've got an SQL injection issue there. You need to bind the value that is submitted from the client-side (assuming the year does come from the client-side?).

There actually isn't an easy way of doing a bind with the .Sql() method at the moment (I'll look into adding an extra parameter for that) - what you could do is:

host.Db
  .Query("raw")
  .Bind(":year", val)
  .Exec($"SELECT CASE WHEN ...")
  .Fatch();

The .Sql() method is just a wrapper for return Query("raw").Exec(sql);.

Allan

wadeparallon

Thanks for the heads up! Will make the adjustments.

The year value does come from the Editor, the OrganizationId is from the URL /Organizations/1/Rates

wadeparallon

var query = $"SELECT * FROM Rates WHERE OrganizationId={id} AND [Year]='{val}'";
                    
                    if (host.Action == "edit")
                    {
                        query += $" AND [Id] != {host.Id}";
                    }
                    var queryResult = host.Db
                        .Query("raw")
                        .Bind("Year", val)
                        .Bind("Id", host.Id)
                        .Bind("OrganizationId", id)
                        .Exec($"SELECT CASE WHEN EXISTS ({query}) " +
                              $"THEN 'true' ELSE 'false' END as doesExist")
                        .Fetch();

Fixed.

allan

Nice one