...
As this sample builds on the previous one, in order to be able to run it, please esnure you have the latest build of SmartClient (at least version 8.3p). This can be downloaded from here.
Add the method that gets primary key from the DataSource
In the previous articles, in many places the primary key is used (to get the record from the database). However, to make it generic, this cannot be hard-coded. It is possible to retrieve it from the DataSource definition. To do this, add a method to the DataSource which will search in the loaded fields and return the one which has a primaryKey attribute set to true:
| Code Block | ||||
|---|---|---|---|---|
| ||||
private function getPrimaryKey()
{
$primaryKey = '';
$ds = $this->data_source;
$fields = $ds['fields'];
foreach($fields as $field)
{
if(array_key_exists("primaryKey", $field))
{
$primaryKey = $field['name'];
return $primaryKey;
}
}
} |
Change the Add / Remove / Update methods
| Code Block | ||
|---|---|---|
| ||
private function add($request)
{
$ds = $this->data_source;
// get the table name from data source
$tbl_name = $ds['ID'];
$new_data = $request->data;
$insert_values = array();
// make the query
$columns = '';
$values = '';
foreach($new_data as $key => $value)
{
$columns .= $key.',';
$values .= "?,";
array_push($insert_values, $value);
}
// remove the last comma
$columns = substr($columns, 0, strlen($columns) - 1);
$values = substr($values, 0, strlen($values) - 1);
// result insert query
$insert_query = 'INSERT INTO '.$tbl_name.' ('.$columns.') VALUES ('.$values.')';
// Run the insert query
R::exec($insert_query, $insert_values);
//DSResponse
$response = new DSResponse();
$response->setData($new_data);
$response->setStatus(0);
return $response;
}
private function update($request)
{
$old_data = $request->oldValues;
$update_data = $request->data;
$ds = $this->data_source;
// update all fields which have changed. they are defined in the data property
if ( count($request->getDataKeys()) != 0 )
{
foreach($request->data as $key => $value)
{
$old_data[$key] = $value;
}
}
// get Primary key
$primary_key = $this->getPrimaryKey();
// get the id by primary key
$values = array();
// get the table name from data source
$tbl_name = $ds['ID'];
$set_value = '';
// make the update query
foreach($old_data as $key => $value)
{
if($key != $primary_key)
{
$set_value .= "$key = ?,";
array_push($values, $value);
}
}
array_push($values, $old_data[$primary_key]);
// remove the last comma
$set_value = substr($set_value, 0, strlen($set_value) - 1);
// result insert query
$update_query = 'UPDATE '.$tbl_name.' SET '.$set_value.' WHERE '.$primary_key.'= ?';
R::exec($update_query, $values);
// build the criteria
//DSResponse
$response = new DSResponse();
$response->setData($old_data);
$response->setStatus(0);
return $response;
}
private function remove($request)
{
$ds = $this->data_source;
// get the table name from data source
$tbl_name = $ds['ID'];
$new_data = $request->data;
// get Primary key
$primary_key = $this->getPrimaryKey();
// get the id by primary key
$value = $new_data[$primary_key];
// make the delete query
$delete_query = "DELETE FROM $tbl_name WHERE $primary_key = ?";
// Run the insert query
R::exec($delete_query, array($value));
//DSResponse
$response = new DSResponse();
$response->setData($new_data);
$response->setStatus(0);
return $response;
} |
Adding a new DataSource
To test this new code, define a new DataSource instance for employee and later allow the user to switch between them. Firslyt, the definition of the DataSource itself:
...