DataTables logo
≡

Reading Data

The DataTable class provides a read only interface to a database table, reading the data from the columns configured and returning it to the client-side for display in response to a DataTables ajax request. It also supports server-side processing for large data sets and advanced filtering options for ColumnControl and SearchBuilder.

Basic initialisation

In this documentation we'll assume that you have successfully installed the DLL for the DataTables .NET libraries and established a database connection which is available in the variable db. Also assumed is that you have imported the DataTables namespace with using DataTables, as noted in the installing documentation.

In your controller, construct a DataTable instance using:

new DataTable( 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).

Optionally, the primary key can also be given as an array of column names if your table has a compound key. See the editing documentation for more information on this form.

Columns

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

  • The Column() method can be used, both to define columns, and to add additional instructions to individual columns (formatters for example) that were added by a model class
  • The Model<T>() method will accept a model class, as described below, as the data type and it will be automatically processed, adding columns from the model class to be read from the database.

Procedural style

Individual columns can be added using the DataTable.Columns() method, which can take as many Column instances as you wish to define, and can also be called multiple times to add additional fields.

In the example below the DataTable instance is configured with five simple fields:

new DataTable( db, "staff" )
    .Columns(
        new Column( "first_name" ),
        new Column( "last_name" ),
        new Column( "position" ),
        new Column( "email" ),
        new Column( "office" )
    );

Column instances also provide the ability to operate on columns for filtering via ColumnControl and SearchBuilder as needed. Please refer to the documentation for those 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 Column( "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.

Models (MVC style)

If you prefer to define a class that contains the database column names that you wish to use, you can do so, by creating a basic class and then passing it to the DataTable.Model<T>() method as a type parameter.

For example, consider the following model:

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; }
}

Then it can be given to the DataTable class using:

new DataTable( db, "staff" )
    .Model<StaffModel>();

This results in exactly the same configuration and the procedural style shown above.

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 the DataTables 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 DataTable.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 DataTable.Model() method multiple times in order to attach multiple models.

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 DataTable.Model() method, in addition to the typing generic:

var response = new DataTable(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 DataTable(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 DataTable / 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;

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 in the libraries 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.

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.

new Column("datediff(day, start_date, getdate())", "ago");

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 DataTable class, with the data sent from the client being passed into it. At this point the DataTable 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:

new DataTable( 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 DataTable class will set this up automatically for you. The Data() method of the DataTable class will return a DtResponse data type which can be passed through the Json() method of your controller to create the JSON.

A completed controller might be as simple as:

DtResponse response = new DataTable( 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 DataTables .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.

Next steps

Now that you've got an DataTable instance you'll need to put it a controller so Ajax requests can be correctly routed to it. See the WebAPI, MVC and WebForm documentation for more details. You can of course configure DataTable for more complex data:

For editable tables, there is the Editor class.