Example / Documentation on Using Editor / readTable() / Views in C# .NET?

sbsu

Are there any examples or documentation regarding the use of Editor's readTable() method to read data from SQL Views using the C# .NET MSSql stack? The documentation I found and am working from is here.

While basic left joins and singular conditions are working fine for me using editor on the C# .NET MSSql stack, I am encountering limitations in scenarios where I need to perform full joins or when trying to model more complicated SQL statements containing subqueries or multiple OR (grouping) conditions.

I did find a basic example of writing a .PHP server script but did not find it's equivalent .NET example. The .PHP example is here. I've extrapolated it and have made it work for a simple test case, as shown below:

            .Where(q =>
               q
                   .Where("Person.PersonEmailAddress", personEmailAddress, "=")
                   .OrWhere(r =>
                   {
                       r.Where("SubscriberEmailAddress", "(SELECT [SubscriberEmailAddress] FROM [Subscriber] WHERE [SubscriberEmailAddress] IS NOT NULL)", "IN", false);
                   })
            )

Working with data from views might be just what I am looking for. What I'd like to do is see a .NET C# example in which the editor reads the data from a view, possibly passing in parameters to the view.

I assume the editable datatable fields would still be determined from POCO models as follows?
.Model<DatatablesNetModelClassName>("DatabaseTableName")

If not, I would need to understand how the editor knows what fields to write back to, especially if the data returned from the view does not match the UI table.

I have found datatables and editor to be incredibly powerful software but it seems much of the examples are intended for LAMP developers first. As far as I can tell, the new .NET5 and .NET6 stacks don't include this type of functionality out of the box (left behind in ASP.NET 4.x) and therefore datatables can server a highly useful purpose in the .NET environment.

Most of our success so far has been through trial and error after studying the .PHP examples. Maybe we haven't yet found the location of the more advanced .NET examples.

Thanks!

allan

Hi,

Our .NET download package includes all of the same examples as the PHP ones on this site.

This is the .NET controller for that same example:

    public class StaffViewController : Controller
    {
        [Route("api/staff-view")]
        [HttpGet]
        [HttpPost]
        public ActionResult Staff()
        {
            var dbType = Environment.GetEnvironmentVariable("DBTYPE");
            var dbConnection = Environment.GetEnvironmentVariable("DBCONNECTION"); 

            using (var db = new Database(dbType, dbConnection))
            {
                var response = new Editor(db, "users")
                    .ReadTable("staff_newyork")
                    .Field(new Field("first_name")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("last_name"))
                    .Field(new Field("phone"))
                    .Field(new Field("city"))
                    .Field(new Field("site")
                        .Get(false)
                        .SetValue(4)
                    )
                    .Process(Request)
                    .Data();

                return Json(response);
            }
        }
    }

We needed to pick one language to use for our server and PHP is the most popular, so we went with that, but .NET and NodeJS get equal treatment and our aim is to keep the libraries basically in-sync (perhaps some small differences between them, like the .NET one has the ability to use models via reflection, while the other two don't).

Let me know how you get on with that if you give it a go.

Regards,
Allan

sbsu

Thank you Allan. I forgot about the download package examples because I was looking for documentation online.

If my understanding of the example code is correct:
* it is reading data from View "staff_newyork" and returning it as Json to the UI
* it is defining the fields from the view which can be edited
* validation and default value decorations get applied

I will experiment with this to see how it may be used but I'm afraid that this isn't the solution I am looking for at the moment.

I have a have a subscription-based data model in which a person may or may not have one or more subscriptions to an item. Also, a person may or may not have a direct relationship to an item outside of a subscription.

I therefore have a database query which performs 5 full table joins and 6 left joins to get the full possibility of data. I then look for a person's email address in more than one table using one or more OR conditions and return the data back to the UI for editing.

If I understand correctly, editor cannot/does not work with stored procedures and if it did, I would need to pass parameters to the stored procedure. Views are intended to be static anyway so parameters are out of the question there as well.

I don't have enough experience yet with your libraries to know all of what's possible but maybe I'm missing something simple regarding full-join or need to figure out a way to refactor the query. Here is a super simple example for reference:

SELECT 
x.something_a, 
y.something_b, 
z.something_c

FROM x

INNER JOIN y
FULL JOIN z

WHERE 
(
(x.something_a= 'something')
OR
(z.something_c = 'something')
)

The value of x.something_a and the value of x.something_b in the WHERE clause are dynamic.

Thanks

allan

Hi,

The way I tend to look at VIEWs is that they are basically just tables which can be queried with a normal SELECT. The SQL engine is clever enough to hide all the complexity of your joins etc behind what looks like a table.

So based on that, we can also add WHERE conditions to a SELECT from the VIEW. Therefore, Editor's .NET libraries ability to apply conditions (i.e. WHERE statements) will still work with a VIEW (just don't include the WHERE statement in the VIEW, do it in the SELECT.

Where the wheels come off here is if you need the ability to update multiple of those tables at a time. I'm afraid that is not something that Editor can do at this time. The only workaround for that is to write your own SQL statements to update the tables based on what is submitted.

Regards,
Allan

sbsu

Allan,

Thanks for your time!

After some more thought and some query refactoring, I think I can come up with a solution if I can obtain some assistance with your library in producing the correct query output.

I've been reviewing and experimenting with ways to utilize where conditions and have been successful with basic where clauses and groupings but can you show me a way in Editor to produce the following query output?:

WHERE 
(   
    ([StatusId] = 0 OR [StatusId] IS NULL) AND [EmailAddress1] = @emailAddress1
    OR 
    ([StatusId] = 0 OR [StatusId] IS NULL) AND [EmailAddress2] IN (Subquery)
)

Assume I've already joined the tables and selected fields. I've had success with multiple conditions and also with the subquery but I'm having trouble coaxing your library properly to produce the output above, as shown above, in which parentheses are important.

P.S., I'm not trying to edit multiple tables at once, so the wheels haven't fallen off just yet!

allan

That's a nice complicated statement! Are you missing parenthesis around the full line for lines 3 and 5 there?

To implement in Editor, it is a nest of lots of nesting

editor.Where( q => {
    q.Where( r => {
      r.Where( s => {
        s.Where("StatusId", 0);
        s.OrWhere("StatusId", null);
      });
      r.Where("EmailAddress1", emailVariable);
    });
    q.OrWhere( r => {
      // etc
    });
} );

Basically each anonymous function creates a new level of SQL parenthesis nesting.

Regards,
Allan

sbsu

Allan, thanks for your help and prompt, professional responses.

I will still need to experiment with this further but this has gotten me going at the moment, thank you.