Basic Loading

In this chapter you'll find base information concerning static loading of data from:

In order to load data correctly, your 'id' field in the database must be autoincrement.

Loading from a database table

Loading characteristics are defined at the stage of component configuration.

Work with one table

When all necessary data is placed in one database table you should use one of the following approaches:

To initialize the connector by SQL query, from which it will take the table name, filtering and ordering rules, columns list, etc.
To pass the table name and columns separately.

Initialization by SQL query

dhtmlxGridConnector connector = new dhtmlxGridConnector(
   "SELECT ISO, PrintableName FROM Country",
   "UID",
   dhtmlxDatabaseAdapterType.SqlServer2005,
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString
);

For insert/update/delete operations, connectors need the primary key name of the table and the foreign key name for hierarchical controls. Connectors require database adapter to work with database engine or database engine type, and connection string to let connector initialize it automatically. Some connectors also require the names of columns for binding to well-known dhtmlx component properties (e.g. NodeTextColumnName for dhtmlxTreeConnector or StartDateColumnName for dhtmlxSchedulerConnector).

Manual specifying of the table name and columns

dhtmlxGridConnector connector = new dhtmlxGridConnector(
   "BookStore",
   "sales, title, author, price, instore, shipping, bestseller, pub_date",
   "book_id",
   dhtmlxDatabaseAdapterType.SqlServer2005,
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString
);

In common case connector constructors of such type accept:

the database table name
a comma-delimited columns list (some connectors ignore it)
the name of the identity field
a database adapter type or adapter itself
connectionString

Work with several tables

You can use any SQL statements to populate a dhtmlx component through dhtmlxConnector.

dhtmlxGridConnector connector = new dhtmlxGridConnector(
    //SQL with inner join statement 
    "SELECT UID,
    PrintableName,
    TimeZone FROM Country INNER JOIN Timezone ON Country.TZID=Timezone.TZID",
    //Primary key name
    "UID",
    //Database adapter type 
    dhtmlxDatabaseAdapterType.SqlServer2005, 
    //connection string
    ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString 
);

But if your SQL statement contains more than one table, connector won't be able to generate insert/update/delete operations correctly and you will need to make one of the following actions:

Define sql for each operation manually
Use server-side events to define your own processing logic
Define different configs for select and update operations

The 3rd approach is shown in the code snippet below:

dhtmlxGridConnector connector = new dhtmlxGridConnector(
   //SQL with inner join statement 
   "SELECT UID, 
   PrintableName, 
   TimeZone, 
   TZID FROM Country INNER JOIN Timezone ON Country.TZID = Timezone.TZID",
   //Primary key name
   "UID",
   //Database adapter type
   dhtmlxDatabaseAdapterType.SqlServer2005,  
   //connection string
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString 
);
 
connector.Request.CustomSQLs.Add(CustomSQLType.Update, 
   @"UPDATE Country SET PrintableName = '{PrintableName}' WHERE UID{UID}'; 
   UPDATE TimeZone SET TimeZone = '{TimeZone}' WHERE TZID {TZID}"

With such init code grid will display and update data from both tables.

Extra data

dhtmlxGridConnector and dhtmlxTreeGridConnector have an optional constructor parameter - ExtraColumnNames. You can use it to define a list of fields which will be extracted from database table, but won't be sent to the client side.

These fields can be used as attributes or flags, mapped to different properties of records (userdata, row styles, images, etc.).

dhtmlxGridConnector connector = new dhtmlxGridConnector(
    //SQL with inner join statement 
   "SELECT UID, 
   PrintableName, 
   TimeZone, 
   TZID FROM Country INNER JOIN Timezone ON Country.TZID = Timezone.TZID",
   //Primary key name
   "UID",
   //Database adapter type 
   dhtmlxDatabaseAdapterType.SqlServer2005, 
   //connection string
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString, 
   "TZID"//This column will be requested, appeared in all events, but won't be rendered
);

Aliases

To make usage of extracted data handier you can use aliases for DB field names (makes sense only if you use server-side events):

dhtmlxTreeConnector connector = new dhtmlxTreeConnector(
   "Folders", 
   "FolderID AS ID", 
   "ParentID", 
   dhtmlxDatabaseAdapterType.SqlServer2005, 
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString,
   "FolderName AS Text"
);

Tree and TreeGrid specificity

In case of Tree and TreeGrid , the constructor accepts one more parameter - ParentIDColumnName. For default treegrid hierarchy - it's the name of a field , which will be used to link parent and child records.

dhtmlxTreeConnector connector = new dhtmlxTreeConnector(
   "Folders", 
   "FolderID", 
   "ParentID", 
   dhtmlxDatabaseAdapterType.SqlServer2005, 
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString,
   "FolderName"
);

Sometimes during the work with hierarchical data structures the question appears: what value should be used for indicating the top (root) item? dhtmlxTree(Grid)Connector assumes that the root item's ParentID value is null. You can change it by assigning a new value to the RootItemRelationIDValue property:

connector.RootItemRelationIDValue = "0"; //Set ParentID value for root items

Stored procedures

You can use stored procedures to initialize connector. In this case, connector won't be able to generate insert/update/delete operations, so you will need to define each operation manually. The second parameter(primary key) becomes inessential, so you can set it to empty string. You can also use stored procedures in CRUD operations, just like regular SQL statements.

var connector = new dhtmlxJSONDataConnector(
   "EXECUTE storedProc",               
   "",
   dhtmlxDatabaseAdapterType.SqlServer2005,
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString
);

use server-side events to define your own processing logic.

Loading from a data array

Starting from version 1.5, instead of database you can load data from an array which can be filled with any kind of external logic.

In such a case the connector's constructors accept:

the IEnumerable collection
a comma-delimited columns list (some connectors ignore it)
the name of the identity field

public override IdhtmlxConnector CreateConnector(HttpContext context)
{
    var data = new DHTMLXDataContext();
    var connector = new dhtmlxGridConnector(
        data.BookStores, 
        "sales, title, author, price, instore, shipping, bestseller, pub_date", 
        "book_id"
    );
 
    return connector;
}