Making Queries

Simple queries

Applicable: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Form

By default, the connector generates all INSERT/UPDATE/DELETE queries automatically, based on configuration.

For more details on this topic, see the 'Basic Loading' chapter.

In case of dnd, the connector will process an action as a sequence of 'insert' and 'delete' operations.

Complex queries

When you need to define your own logic you should use one of two ways:

defining custom SQL code for operation
using server-side events to customize operations

Custom queries for an action

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Form

You can define your own SQL code for specific action (INSERT, UPDATE or DELETE) as follows:

dhtmlxGridConnector connector = new dhtmlxGridConnector(
   "SELECT ISO, PrintableName FROM Country",
   "UID",
   dhtmlxDatabaseAdapterType.SqlServer2005,
   ConfigurationManager.ConnectionStrings["SamplesDatabase"].ConnectionString
);
 
connector.Request.CustomSQLs.Add(
    CustomSQLType.Delete, 
    "UPDATE Country SET PrintableName = '-------------' WHERE UID={UID}"
);

Parameters:

CustomSQLType. Possible values are: 'Update', 'Insert', 'Delete'
SQL statement. It can use fields(or their aliases) which were mentioned in the connector's constructor while loading data.

Using server-side events

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Form

To customize operations you can use the following server-side events:

The example below adds one more field ("modify_date") into the final 'update' statement.

connector.BeforeUpdate += new EventHandler<DataActionProcessingEventArgs>(
connector_BeforeUpdate
);   
void connector_BeforeUpdate(object sender, DataActionProcessingEventArgs e)
{
   e.DataAction.Data.Add("modify_date", Tools.ConvertToString(DateTime.Now));
}

The next example adds the "created_date" column into the 'insert' statement:

connector.BeforeInsert += new EventHandler<DataActionProcessingEventArgs>(
connector_BeforeInsert
);
void connector_BeforeInsert(object sender, DataActionProcessingEventArgs e)
{
   e.DataAction.Data.Add("create_date", Tools.ConvertToString(DateTime.Now));
}

And the completely custom SQL:

connector.BeforeUpdate += new EventHandler<DataActionProcessingEventArgs>(
connector_BeforeUpdate
);  
void connector_BeforeUpdate(object sender, DataActionProcessingEventArgs e)
{
 this.Connector.Request.Adapter.ExecuteNonQuery(
 "UPDATE ........ SET ........ WHERE ......"
 );
 e.DataAction.SetCompleted();
}