.NET Server-Side Validation, Conditional Set Values, Triggers and Compound Keys

ryetgr

I've spent a lot of time with datatables over the last few months, and was very entry-level to web development when I started. I've built a working ASP.NET MVC 5 app, and consider myself fairly decent using datatables editor. Most of the time when faced with challenges along the way I've been able to find the right community page, or many life-saving support pages with "Allan" walking people through problems and so helping me with my project.

Having said that, I have some lasting "topics" that despite much effort cannot find a good solution for. I was hoping someone may have the solution, as it would really help make my project more reliable. When I outline the following, I apologize in advance if I'm missing something glaringly straight forward, but thank you for the information:

1 Server-Side Validation

In .NET Winforms, WPF, Consle app, or ASP.NET validation is very easy to implement. When I use editor, I really don't know how to get anywhere near the same level of validation, and the documentation for .NET validation is very limited imo.

Scenario 1 - > I have 5 editor fields I want to be required fields only if a certain field in the editor is set to "fail". The only way I managed to add this validation server-side is on the client side, capture the "fail" value in the editor's ajax property when posting. Then in the controller setting the field's Validation Empty (bool) to true/false depending on the variable passed in from client-side.

First of all I find this is very hacky. Is there not a better straight forward way I can just access the formData on the server-side, rather than relying on passing in an ajax client-side variable?
Second, why can't I just in a more straight forward way add a validation rule to the field that says "if formData.field_abc =="fail", min char length = 3. I'm not sure how to achieve this.

There are many other types of validation I would love to be able to do on server-side, like if you have for example 3 number fields, and quantity A must equal quantity B + C. How can I have this validation with editor? This is very simple in C#, but I feel like I don't know where to start with the editor.

2 Conditional .SetValue Server-Side

I have some datatables where I want to set the value of the field only if the action is "create". For example:
if (form_count > 0 && formData["action"] != null && formData["action"] == "create")
.Field(new Field("datetime_stamp").SetValue(DateTime.Now))
.Field(new Field("receipt_no").SetValue(create_receipt))

So if the action is NOT create, I don't want to set the value for these fields. So what I'm doing currently is wrapping the entire using (var db = new db... var response =- new Editor....) inside an IF statement, and saying IF the action is "create", run this editor code, ELSE and then I paste the exact same "(var db = new db... var response =- new Editor....)" and all the fields which is 30 lines, with the only difference being the .SetValue is not in the editor version of the else statement. Now I know I am new, but even I know this is very bad code for many reasons.

3 Inability to use Triggers to create primary keys (Now I know I can live with this, but if there is a work around I'd love to know about it)

When I have a datatable that is for example an "orders table" or "order details table". The expectation is not to rely on identity columns that increment (1,1) because I'd like assurance that order #'s will always increment by 1 and the identity column (1,1) in MSSQL cannot guarantee this. Not to mention in an order details table you will have Order # 3000 for example, and with 4 line items for this order the line items would need to be 1,2,3,4 as line number. These line numbers are also part of a compound primary key for the order detail table Primary Key (ORDER_NO,LINE_NO).

Now the easiest way to get this to work, would be to simply make an INSTEAD OF trigger on the row insert, and let the SQL trigger create the correct order # and line # with simple calculation in the trigger. However, when I do this, the web page never refreshes, the animation never occurs, and of course it's because as you will see when debugging and using debug(true) the correct row is never returned after the insert. So as far as I can tell, and I think I've read this from Allan on at least once occasion, is that datatables is not compatible with this type of trigger implementation.

So instead of trying to make this work with triggers, I am just using the above .SetValue strategy with the IF statements wrapped around the action "create" conditional

4 Compound Primary Keys do not work if you are using .SetValue to create the primary key.

For example when a user creates a new line item for an order, and the order detail primary key is (ORDER_NO,LINE_NO)
If you are using .SetValue on line_no, I was never able to create the editor instance on the server side with a
compound primary key. No matter how I tried, the debug(true) would never bring back the LINE_NO with the order number,
so I always got line 0. That said, the insert was correct, but the response back from the editor would always bring back 0 for the line# ... the SQL never brought back the newly inserted line #.

Based on how exhaustive I was, I am pretty confident this will just not work, so what I've done is
hijacked the editor response, so right before "return Json" I have to
var x = response.data[0]["tbl_name"];
var xx = x as System.Collections.Idictionary;
and then
xx["line_no"] = 3
(in the example where I had inserted 3 as .SetValue , overwriting the responses line = 0 (0 or 1 I can't recall) as the SQL in the debug(true) would never bring back the correct line_no.

Thank you in advance for any insight, I hope shedding light on this would in some way help others with the same problems! (I hope I'm not the only one!!)

The important, I feel of these issues out of 10 in terms of the benefit to a project (10 being highest benefit) in resolving them are probably

10/10 #1 Server-Side Validation (pretty core topic for any good project)
8/10 #2 Conditional .SetValue Server-Side (because code becomes much simpler and cleaner)
7/10 #3 Inability to use Triggers to create primary keys (letting mssql server handle inserts could alleviate a lot of strain from the editor
3/10 #4 Compound Primary Keys do not work if you are using .SetValue to create the primary key. (probably can live with this, but did spend hours trying to understand it...)

Thanks!
Ryan

allan

Hi Ryan,

Scenario 1 - > I have 5 editor fields I want to be required fields only if a certain field in the editor is set to "fail".

A server-side event is the way to do this. Use the event to check for your value, then conditionally add the validators to the field. The preEdit / preCreate events happen before the validation, so you can add validation there.

Conditional .SetValue Server-Side (because code becomes much simpler and cleaner)

Server-side events are the answer here as well, since you can target different events.

Inability to use Triggers to create primary keys (letting mssql server handle inserts could alleviate a lot of strain from the editor

Agreed! This is a limitation that I'm aware of, and find frustrating myself. The main issue when I was looking into this was the ability to have the database return all of the computed primary key values. I think it was MySQL where I just could not find a way to make that happen, it would only ever return a single column. The libraries need to play to the lowest common denominator since they are effectively an abstraction layer.

Inability to use Triggers to create primary keys (letting mssql server handle inserts could alleviate a lot of strain from the editor

That sounds like a bug! Thanks for letting me know about that.

Allan