Retrieve the database data based on the user's corresponding access permissions
Level: advanced
note
The examples in this article are also implemented in the web service. The package that contains the web service implementation is attached in the Resources block.
Example 1
Example
Create an EntitySchemaQuery instance.
CreateESQ() method
public string CreateESQ() {
var result = "";
/* Retrieve the instance of the object schema manager. */
EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
/* Retrieve the instance of the schema to set as root for the EntitySchemaQuery instance. */
var rootEntitySchema = esqManager.GetInstanceByName("City") as EntitySchema;
/* Create the EntitySchemaQuery instance that has the rootEntitySchema set as root. */
var esqResult = new EntitySchemaQuery(rootEntitySchema);
/* Add columns to select in the resulting query. */
esqResult.AddColumn("Id");
esqResult.AddColumn("Name");
/* Retrieve the Select instance associated with the EntitySchemaQuery query. */
Select selectEsq = esqResult.GetSelectQuery(SystemUserConnection);
/* Retrieve the text of the EntitySchemaQuery instance's resulting query. */
result = selectEsq.GetSqlText();
return result;
}
Example 2
Example
Clone the EntitySchemaQuery instance.
CreateESQClone() method
public string CreateESQClone() {
var result = "";
EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
var esqSource = new EntitySchemaQuery(esqManager, "Contact");
esqSource.AddColumn("Id");
esqSource.AddColumn("Name");
/* Clone the EntitySchemaQuery instance from the esqSource instance. */
var esqClone = new EntitySchemaQuery(esqSource);
result = esqClone.GetSelectQuery(SystemUserConnection).GetSqlText();
return result;
}
Example 3
Example
Retrieve the query results.
GetEntitiesExample() method
public string GetEntitiesExample() {
var result = "";
/* Create a query to the City schema, add the Name column to the query. */
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
var colName = esqResult.AddColumn("Name");
/* Run the database query and retrieve the entire resulting objects collection. */
var entities = esqResult.GetEntityCollection(UserConnection);
for (int i = 0; i < entities.Count; i++) {
result += entities[i].GetColumnValue(colName.Name).ToString();
result += "\n";
}
/* Run the database query and retrieve the object that has the set identifier. */
var entity = esqResult.GetEntity(UserConnection, new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"));
result += "\n";
result += entity.GetColumnValue(colName.Name).ToString();
return result;
}
Example 4
Example
Use the EntitySchemaQuery query cache.
UsingCacheExample() method
public Collection <string> UsingCacheExample() {
/* Create a query to the City schema, add the Name column to the query. */
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
esqResult.AddColumn("Name");
/* Define the key the cache will use to store the query results. Creatio will use the session level Creatio cache with local storage since the Cache object property is not redefined. */
esqResult.CacheItemName = "EsqResultItem";
/* The collection to place the query results. */
var esqCityNames = new Collection <string> ();
/* The collection to place the cached query results. */
var cachedEsqCityNames = new Collection <string> ();
/* Run the database query and retrieve the resulting objects collection. Creatio will cache the query results after this operation. */
var entities = esqResult.GetEntityCollection(UserConnection);
/* Process the query results and populate the esqCityNames collection. */
foreach(var entity in entities) {
esqCityNames.Add(entity.GetTypedColumnValue <string> ("Name"));
}
/* Retrieve the link to the esqResult query cache as a data table in memory using the CacheItemName key. */
var esqCacheStore = esqResult.Cache[esqResult.CacheItemName] as DataTable;
/* Populate the cachedEsqCityNames collection using the query cache values. */
if (esqCacheStore != null) {
foreach(DataRow row in esqCacheStore.Rows) {
cachedEsqCityNames.Add(row[0].ToString());
}
}
return cachedEsqCityNames;
}
Example 5
Example
Use the additional settings of the EntitySchemaQuery query.
ESQOptionsExample() method
public Collection <string > ESQOptionsExample() {
/* Create a query instance that has the City root schema. */
var esqCities = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
esqCities.AddColumn("Name");
/* Create a query that has the Country root schema. */
var esqCountries = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Country");
esqCountries.AddColumn("Name");
/* Create an instance of settings to return first 5 strings in the query. */
var esqOptions = new EntitySchemaQueryOptions() {
PageableDirection = PageableSelectDirection.First,
PageableRowCount = 5,
PageableConditionValues = new Dictionary <string, object> ()
};
/* Retrieve the city collection that contains the first 5 cities of the resulting dataset. */
var cities = esqCities.GetEntityCollection(UserConnection, esqOptions);
/* Retrieve the country collection that contains the first 5 countries of the resulting dataset. */
var countries = esqCountries.GetEntityCollection(UserConnection, esqOptions);
var esqStringCollection = new Collection <string> ();
foreach(var entity in cities) {
esqStringCollection.Add(entity.GetTypedColumnValue <string> ("Name"));
}
foreach(var entity in countries) {
esqStringCollection.Add(entity.GetTypedColumnValue <string> ("Name"));
}
return esqStringCollection;
}