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:

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