Data Layer Basic API

Get Fragment (Not updatable data set)

Returns a list of data from the Data Definition containing only the requested columns and basic columns of the class. 

Retrieves data according to the current user permissions. 

Retrieved data cannot be updated:

public FragmentRequestBase(string className, ColumnSelectOption columnSelectOption, string additionalColumns)

Parameters:

• className: the Schema name of the class constructed by this request (internal name of the Data Definition);

• ColumnSelectOption: defines which columns of a fragment to get. Possible options:

  • All - gets all columns of a class;

  • Basic - gets only the basic columns like ID, TimeStamp, UsedInType-columns of a class;

  • List - additional columns can be added to the fragment request;

  • WithoutBlobColumns - all columns that are text or binary are excluded, if not explicitly added.

• additionalColumns: columns that are added additionally to the columns that are already included by the columnSelectOption.

Example:

FragmentRequestBase class2 = new FragmentRequestBase("TestClass2", ColumnSelectOption.All, "");
class2.Load();

Assert.IsTrue(class2.DataSet.Tables.Contains("TestClass2"));

Example with Where expression (additional ASQL parameter):

FragmentRequestBase class2 = new FragmentRequestBase("TestClass2", ColumnSelectOption.All, "");
class2.Where = "IntAttrib=100";
class2.Load();

Assert.IsTrue(class2.DataSet.Tables.Contains("TestClass2"));

Additional parameters

The retrieved result can be filtered, sorted, or limited to the specific number of rows.

public FragmentRequestBase(Guid classId, ColumnSelectOption columnSelectOption,
            string additionalColumns, int count, int start, string[] orderBy)

Parameters: 

• count: the maximum number of the data rows returned in the result, zero count returns all rows;
• start: the starting row in the result. If specified, requires the orderBy argument.
• orderBy: first string in the array determines the name of the column, the second defines the order in which the data of the column is sorted, e.g. ACS or DESC;

Additionally, you can restrict the types where the fragments are used by specifying the type ID(s). If the type does not exist, the corresponding ID is ignored.

For instance, the result will include only those Data Definition fragments that are a part of the specified Configuration Item(s):

FragmentRequestBase request = new FragmentRequestBase("TestClass", ColumnSelectOption.List,
    "ID, Name, CommonState", 20, 0,
    new string[] { "Name", "ASC" });
 
request.RestrictToTypeIds = new[] { ciSchemaId };
request.Load();

Get Fragment (Updatable dataset)

A request that allows defining which fragment (data row) to get. 

Retrieves data according to the current user permissions. 

Retrieved data can be updated:

public FragmentRequest(string className, Guid fragmentID)

Parameters: 

• className the name of the class (technical name of the Data Definition)

• fragmentID the id of the fragment to retrieve (Guid of the single row in the Data Definition)

Example:

var request = new FragmentRequest("Class2", newID);
request.Load();
var testFragment = request.SpsFragment;

The result of the fragment request is a SPSFragment that can be updated with the methods described below.

Create Fragment

Create a new fragment (row) in the Data Definition. Allows accessing Data Definitions according to the current user permissions. 

public static SPSFragment CreateFragment(Guid classId, Guid newFragmentId)

Parameters: 

• classID: ID of the Data Definition;
• fragmentID: new fragment ID;

The new fragment is not stored in the database unless you explicitly store it. The underlying Data Definition contains one new row with the ID column filled with the newFragmentID. If a fragment with the newFragmentID already exists an SPSDataEngineException is thrown.

Example:

var schemaIdOfDataDefinition = SPSDataEngineSchemaReader.ClassGetIDFromName("Class2");

var fragment = FragmentRequest.CreateFragment(schemaIdOfDataDefinition, Guid.NewGuid());
fragment["UsedInTypeMyCi"] = = myObjectId; //contains the id of the object
fragment["Attribute"] = "someValue";
fragment.Update();

Update Fragment​​​

Allows updating attributes of the fragment (Data Definition) by the class name and row ID.

Updates data according to the current user permissions.

Example:

var request = new FragmentRequest("Class2", newID);
request.Load();
var testFragment = request.SpsFragment;

testFragment["Attribute"] = "changed";
testFragment.Update();

Delete Fragment

Allows deleting attributes of the fragment (Data Definition data) by the class name and row ID.

Deletes data according to the current user permissions.

var request = new FragmentRequest("Class2", newID);
request.Load();
var testFragment = request.SpsFragment;
 
testFragment.Delete();

Fragments Batch Update

The batch update is implemented in a different way than a single fragment update and uses the query command approach.

Unlike other examples that fetch the data from the database, work locally, and then apply changes to the database, the query command works directly with the database. The code is executed as is, the user permissions are ignored. Query command requires the Schema ID of the fragment, not the name.

public UpdateQueryCommand(Guid targetClassID,
            string targetAttributes,
            string sourceColumns,
            string sourceWhereString)

Parameters: 

• targetClassID: ID of the Data Definition; 

• targetAttributes: attribute name of the Data Definition;

• sourceColumns: the value that will be set by the query command, which can be a static value or an ASQL expression and additionally requires an alias;

• sourceWhereString: ASQL where expression or the condition that specifies which records of the Data Definition should be updated. If you omit this argument, all records in the Data Definition will be updated!

Example: 

• Static content:

var command = new UpdateQueryCommand(
    SPSDataEngineSchemaReader.ClassGetIDFromName("Class2"), "Attribute", "'staticValue' as static",
    "Attribute like 'test%'");
command.Execute();

• Updating data by using content from the Data Definition that is added as a relation:

var command = new UpdateQueryCommand(
    SPSDataEngineSchemaReader.ClassGetIDFromName("Class2"), "Attribute", "Relation.AttributeInOtherDD + ', ' + Attribute as relation",
    "Attribute like 'test%'");
command.Execute();

Get Object

Use this code to get the complete object or define which fragments to include in the result. Retrieves data according to the current user permissions. Retrieved data can be updated.

Gets the complete data of the Configuration Item:

public ObjectRequest(string typeName, Guid objectId, FragmentSelectOption fragmentSelectOption)

Parameters:

• typeName: the name of the type (Configuration Item internal name);
• objectId: the Guid of the object;
• FragmentSelectOption: the fragments you want to be included in the result:
  • All - gets all Data Definitions of the Configuration Item, loads all classes into the requested SPSObject;
  • Mandatory - loads only the mandatory classes into the requested SPSObject;
  • List - the requested classes loaded into the SPSObject can be explicitly set;
  • WithoutBlobColumns - this is an exception from the other values, as it does not affect the loaded classes but the columns of the loaded classes. In all classes, the text and image columns are not loaded.

Example:

ObjectRequest request = new ObjectRequest("CIName", ciId, FragmentSelectOption.All);
request.Load();
SPSObject myCi = request.SpsObject;

Returns SPSObject that contains the content of the object. Retrieved data can be updated.

Create Object

By creating a new object you create a new Configuration Item instance in the database. 

static public SPSObject CreateObject(Guid typeID, Guid newObjectID)

Parameters: 

• typeID: the schema ID of the object to create;
• newObjectID: the ID of the object to create;

This new object is not stored in the database unless you explicitly store it.

All classes of the SPSObject are added, and the mandatory classes of the SPSObject are filled with new rows, where the columns needed to define the SPSObject are filled with values. If a SPSObject with the newObjectID already exists a SPSDataEngineException is thrown.

Example:

var spsObject = ObjectRequest.CreateObject(ciSchemaId, Guid.NewGuid());
spsObject["MemberDd"]["Attribute"] = "someValue";
spsObject.Update();

Update Object

Allows updating the object. First, retrieve the object by the Configuration Item name and object ID, then specify the fragment name (Data Definition) and the attribute that should be updated. Updates data according to the current user permissions.

Example:

ObjectRequest request = new ObjectRequest("CIName", ciId, FragmentSelectOption.All);
request.Load();
SPSObject myCi = request.SpsObject;

myCi["MemberDd"]["Attribute"] = "changed";
myCi.Update();

Delete Object

Allows deleting the object. First, retrieve the object by the Configuration Item name and object ID, then you can delete the object. 

Deletes data according to the current user permissions.

ObjectRequest request = new ObjectRequest("CIName", ciId, FragmentSelectOption.All);
request.Load();
SPSObject myCi = request.SpsObject;
 
myCi.Delete();

Objects Batch Update

Multiple objects update is not implemented.