DHTMLX Docs & Samples Explorer

Data configuration

Initialization

All parameters necessary for connector's correct work are usually passed into constructor.
There're 2 approaches of setting parameters therefore there 2 contructor types:

  • Initialize connector by SQL query, from where it takes table name, filtering and ordering rules, columns list etc.
  • Separately pass table name and columns
Initialize from SQL:
            dhtmlxGridConnector connector = new dhtmlxGridConnector(
                "SELECT ISO, PrintableName FROM Country",
                "UID",
                dhtmlxDatabaseAdapterType.SqlServer2005,
                ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString
            );

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

Manually specify tables 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:

  • database table name
  • comma-delimited columns list (some connectors ignore it)
  • name of identity field 1)
  • Database adapter type or adapter itself
  • ConnectionString 2)

This is all you need to make connector implement select, insert, update and delete operations.

Joint Tables and Complex Queries Operations

You are allowed to use any SQL statements to populate any dhtmlx component through dhtmlxConnector.

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

In case your SQL query was against single table, it is quite probable that insert/update/delete operations do not require any additional code. dhtmlxConnector will parse your SQL and generate insert/update/delete statements based on used table and fields names.

If your SQL statement contains more than one table, connector will not be able to generate insert/update/delete operations correctly, so you will need do one from next

  • define sql for each operation manually
  • use server side events to define your own processing logic

The first approach is shown in the sample below

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

Grid will display and update data from and in both tables.

Extra data

dhtmlxGridConnector as well as dhtmlxTreeGridConnector has ExtraColumnNames optional constructor parameter. You can use it for columns selection that shouldn't be rendered. E.g. your might be interedted to select user's access rights bits, but in common scenario it's bad idea to show in in grid.

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

Tree and TreeGrid

When using dhtmlxTreeConnector and dhtmlxTreeGridConnector, one more parameter appears in constructor: ParentIDColumnName. This one used to build relationship between child and root items.

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

In hierarchical data structures sometimes appear question: what value should be used for indicating top (root) item. dhtmlxTree(Grid)Connector assumes that root item's ParentID value is null. You can change it by assigning new value to RootItemRelationIDValue property:

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

Aliases

You can use aliases for DB field names to made later usage of extracted data more usable ( will have sense only if your are using server side events )

            dhtmlxTreeConnector connector = new dhtmlxTreeConnector(
                "Folders", 
                "FolderID AS ID", 
                "ParentID", 
                dhtmlxDatabaseAdapterType.SqlServer2005, 
                ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString,
                "FolderName AS Text"
            );
1) optional
2) if data adapter type was specified