Getting started

The .NET Editor libraries make it super quick for you to provide complex CRUD based web applications to end users. In order to achieve this we need to introduce the basic concepts of how the libraries operate and how you can use the interfaces that they provide to access your own databases. This page summarises how the Editor libraries operate and the concepts involved in using them.

Concept

It is important to understand the big picture of the approach the Editor .NET libraries take for interfacing between the database and the client-side DataTable / Editor before delving into the detail of how to actually read and write data.

The libraries provide an Editor class, an instance of which is created and will relate, primarily, to a single SQL table which it will read and write data from and to.

The Editor instance is then instructed about the database table columns that it should operate on and how. These columns are used to populate both the DataTable and Editor, and Editor has the ability to write to them. Fields from additional tables can also be included through the use of join statements.

Finally the data being sent by the client side is processed. There are four basic request types that DataTables and Editor can make:

• Read - Get the data to display
• Create - Create a new row of data
• Edit - Update an existing row with changed data
• Remove - Delete one or more rows from the database

The Editor class will automatically detect which of these four requests are being made and handle it correctly for you without adjustment. The data resulting from the request is then sent back to the client to it to complete the processing.

Basic initialisation

The first step to using the Editor libraries in any project is to include a reference to the DataTables-Editor-Server.dll, which provides all the classes and methods required. You can do this in Visual Studio by right clicking on the References option in your project's Solution Explorer. From the context menu select the Add Reference option to open a dialogue box. Using the Browse option navigate to where you unzipped the Editor download package and locate the DataTables-Editor-Server.dll file. Select and then click the Add button.

To complete the installation, add a using statement to the files where you will be using the Editor components (typically the model and controller):

using DataTables;

MVC

Editor can use the MVC model that is common in many other parts of the .NET framework. Using MVC you define a model that describes the structure of the data for each row in the table, while the controller acts upon the data given to it, processing the data as described by the model.

Model

The Editor model describes the data that is to be read from the database, the JSON structure for sending the data to the client-side and also the parameters that Editor submits when editing data. This is done with a simple class that contains the names of the database properties and their types. For example:

public class StaffModel
{
    public string   first_name { get; set; }
    public string   last_name { get; set; }
    public string   position { get; set; }
    public string   email { get; set; }
    public string   office { get; set; }
    public int      extn { get; set; }
    public int      age { get; set; }
    public int      salary { get; set; }
    public string   start_date { get; set; }
}

Please note that the model's data structure is used both for representation of the data from the database and the JSON data sent to the client-side for each row. This means that the data types used must be directly applicable to JSON as well as the database. In practice this means that string, int and Decimal are the data types that should be used. Other data forms such as DateTime fields should be given as a string, which Editor's libraries will convert automatically.

Joins

Data obtained from multiple tables (joined data) can also be described using models. You can opt to use a flat class as shown above, or a nested class.

When using a model to describe the structure of joined tables the LeftJoin() method must still be used in the controller to define the actual join logic. Please refer to the join documentation for further details.

Flat classes

When describing a joined table with flat classes, you can use the Editor.Model() method with its optional string parameter to give the table name that the fields in the class relate to. You can also call the Editor.Model() method multiple times in order to attach multiple models (as of Editor 1.7).

namespace WebApiExamples.Models
{
    public class JoinModelUsers
    {
        public string first_name { get; set; }
        public string last_name { get; set; }
        public string phone { get; set; }
        public int site { get; set; }
    }

    public class JoinModelSites
    {
        public string name { get; set; }
    }
}

The controller would then look like the following - note the string passed into the Editor.Model() method, in addition to the typing generic:

var response = new Editor(db, "users")
    .Model<JoinModelUsers>("users")
    .Model<JoinModelSites>("sites")

Nested class

It is also possible to use C#'s nested class ability to describe joined data. A nested class is used for each table that data is to be read from with the properties of the nested class describing the fields to be read from that table. The nested class name must match the name of the table (which is why the example below uses lower case class names - it matches the table name!).

This example implements exactly the same as above, but with nested classes:

namespace WebApiExamples.Models
{
    public class JoinModel
    {
        public class users
        {
            public string first_name { get; set; }
            public string last_name { get; set; }
            public string phone { get; set; }
            public int site { get; set; }
        }

        public class sites
        {
            public string name { get; set; }
        }
    }
}

In this case the controller used will be:

var response = new Editor(db, "users")
    .Model<JoinModel>()

Attributes

The properties in the model match the database column names, but there are a number of custom attributes that can also be used to modify the behaviour of each field, listed below. To use these attributes in your model, be sure to have using DataTables; in your model file.

• EditorGet( bool ) Since 2.0.5 - Provide the get flag for the field - i.e. if it should be read from the database or not.
• EditorHttpName( string ) - Define the HTTP name for this property. This is used in the JSON data and the submitted form data. Although it doesn't provide much in the way of security, it does mean that your database column names don't need to be exposed on the client-side if you would prefer not to have that information external to your application.
• EditorIgnore() Since 2.0.4 - Instruct Editor to ignore the property. It will not be used to read from the database, or for data sent from the client-side.
• EditorSet( Field.SetType ) Since 2.0.5 - Provide the set flag for the field - i.e. if it should be written on create, edit, both or neither.
• EditorTypeError( string ) - If a type error occurs and the data read from the database or the submitted form data cannot be assigned to the data type given for the property in question Editor will generate an error. The text of that error can be given using this attribute.

As an example, the following property makes use of both attributes:

[EditorTypeError("Age must be an integer")]
[EditorHttpName("user_age")]
public int Age { get; set; }

To ignore a property in a class:

[EditorIgnore()]
public string Location;

Controller

Where the model defines the data structure, the controller processes it. An Editor instance is constructed using:

new Editor( db, table, pkey="id" );

Where:

• db is an instance of the Database class, which connects to the database
• table is the table name to operate on.
• pkey is an optional parameter that specifies the primary key of the table - the default is id. This option can also be given as a string array to an overloaded constructor (see below).

The Database class can be initialised in a number of different ways (refer to the Editor .NET API reference documentation for full details):

• new Database(string, string)
• new Database(string, DbConnectionStringBuilder)
• new Database(string, DbConnection)

In the example package a database instance is created in each individual controller using a connection string.

Compound keys

As of Editor 1.6 the .NET libraries support compound keys: a key which is made up from the data in two or more table columns, rather than just a single column as used in many tables. To use a compound key, simply pass the primary key information into the third parameter for the Editor constructor as an array:

new Editor( db, "visitors", new []{"visitor_id", "visit_date"} );

No special configuration is required on the client-side to support compound keys, however, when creating new rows you must submit the data for the columns that make up the compound key (an error will be shown otherwise). Editor cannot currently read information that is generated by the database. If you need to set a server-side computed value (e.g. current time), use the Field.SetValue() method to set the value. This limitation is due to the database engines that Editor currently supports.

Chaining

Like the Editor Javascript API the .NET Editor API is fully chainable, allowing potentially complex code to be expressed succinctly. The majority of the methods that Editor and its child classes provide are chainable - i.e. they return the instance that you are working with. Consider for example:

var editor = new Editor( Db, 'staff' );
editor.Fields( ... );
editor.Process( ... );
DtResponse data = editor.Data();

Using chained style, the above code block can be rewritten as:

DtResponse data = new Editor( Db, 'staff' )
    .Fields( ... )
    .Process( ... )
    .Data();

Using a chained style of coding is not required, although you will see it used in this documentation and the examples Editor comes with.

Fields

Once the Editor instance has been created and configured for what database table it should read from, it needs to be instructed what fields it should access on that table. This is can be done through the Model<T>() and Field() methods.

• The Model<T>() method will accept an Editor model class, as described above, as the data type and Editor will automatically process the model, adding fields as appropriate.
• The Field() method can be used, both to define fields, and to add additional instructions to individual fields (validators and formatters for example) that were added by a model

Field instances are added to Editor, by creating a new Field() instance, and attaching it to Editor using Editor.Field(). The parameter to the Field constructor, is the name of the column to read / write data, and also the name that will be used to return the data to DataTables and Editor. The Fields() method can take as many field instances as you wish to define and can also be called multiple times to add additional fields.

In the example below the Editor instance is configured with the model defined above and the first_name field has simple validation attached to it:

new Editor( db, 'staff' )
    .Model<StaffModel>()
    .Field( new Field( 'first_name' ).Validator( Validation.NotEmpty() ) );

Field instances provide validation and formatting options as well as simple get / set options. Please refer to the documentation for sections for detailed information.

Parameter names

By default the column name given as the first parameter to the Field instance constructor will also be the name that the field is given in the JSON sent to the client, and the name of the field that the libraries will look for in the data submitted to the server from the Editor form. This name (JSON and submitted data) can be modified using an optional second parameter for the Field constructor - e.g.:

new Field( "first_name", "fname" )

This can be useful if you wish to obfuscate the SQL columns names so the client will never see the actual database naming, or simply want to reduce the size of the JSON object transmitted to the client.

SQL functions

While you will usually wish to just obtain data from SQL columns, as shown above, it can also be useful to use SQL functions for certain operations. The Field instance can be specified with an SQL function - this is found by Editor simply by the use of parenthesis. In this case you will likely wish to use the optional second parameter to name the data in the JSON sent to the client.

Important Fields which make use of an SQL function can only be read. They cannot be written to. If an attempt is made to write to such a field, an error will be thrown. Use Field.Set( false ) to ensure that the field is not written to.

In the following example, the SQL Server function datediff is used (note that in other SQL dialects, functions can also be used). The method is used to calculate the difference between a field (start_date) and the current date, in days, then naming the field as ago in the JSON sent to the server. Note also that the Field.Set() method is used to ensure that the field is not written to:

new Field("datediff(day, start_date, getdate())", "ago")
    .Set(false)

Data processing

When DataTables and Editor make a request to the server to get or update data, they send the data using HTTP parameters (POST is the default for Editor). This information can be obtained in your application added passed through to the Editor class for processing (use [FromBody] in Web API projects and Request.Form in MVC projects).

The data is acted upon by using the Process() method of the Editor class, with the data sent from the client being passed into it. At this point the Editor instance will perform whatever action the client-side has asked of it - be it to create a new row, update and existing row, etc. These actions will be automatically detected.

To continue the example above (without the validation for brevity), if we add a Process() method call we now have (this is MVC style):

new Editor( db, 'staff' )
    .Model<StaffModel>()
    .Process( Request.Form );

Return data

The final step is to send the data back to the client for it to process (e.g. display an error message if validation failed or redraw the table with the updated data if everything was successful). The data sent back to the client is in the JSON data format and again the Editor class will set this up automatically for you. The Data() method of the Editor class will return a DtResponse data type which can be passed through the JSON() method of both the Web API and MVC controllers to create the JSON.

A completed controller might be as simple as:

DtResponse response = new Editor( db, 'staff' )
    .Model<StaffModel>()
    .Process( Request.Form )
    .Data();

return Json(response);

And that's it! That is all that is required on the server-side, when using the Editor .NET libraries to add read / write ability to your database table. Additional fields can be trivially added using the model and other tables also setup for editing.

The next step is to add validation to your fields to ensure that the data sent is the data you expect.