Creating a Data Gateway Connector

In order to run data gateway, you must have a connector. Archer has a connector available for use out of the box, called the Flexible SQL Connector. For more information about this connector, see Using the Flexible SQL Connector. As an alternative, develop your own connector. Use the following tasks to create a data gateway connector that reads, writes, and searches data stored in an external database table. This guide assumes that you have experience using Microsoft Visual Studio to create and implement C# .NET projects.

The connector reads the values of the fields in each record in your data gateway application in Archer from an external database. In the example within this topic, the external database includes a Records table with one column for each field in your data gateway application. One column in your table must be an identifier, which maps each record in the external table to its respective record in the data gateway application. In this example, the identifier is ID.

Task 1: Create the Project

You implement a data gateway connector as a .NET DLL. Do the following to create the project.

1. Using Microsoft Visual Studio, create a new Visual C# Class Library with the following settings:

   • Assembly name: ArcherTech.Datasource.SqlDemo

   Note: All data gateway connector names must start with the ArcherTech.Datasource prefix.

   • Target framework: .NET Framework 4.7.2
2. Add the following references to the project:

   • ArcherTech.Datasource.Common (Archer assembly location "C:\inetpub\wwwroot\RSAarcher\bin\ArcherTech.Datasource.Common.dll")

   • ArcherTech.IOC (Archer assembly location "C:\inetpub\wwwroot\RSAarcher\bin\ArcherTech.IOC.dll")

   • System.Runtime.Serialization (part of the .NET Framework)

Task 2: Create the Datasource Class

The data gateway Datasource class represents the programmatic logic for a connector. The Datasource class includes methods for initializing the connector, generating and parsing external record identities, and returning objects dedicated to reading, writing, and searching data. Do the following to create the Datasource class:

1. Add a new class file to the project named SqlDatasource.

2. Update the class to implement the ArcherTech.Datasource.Common.Interfaces.IContentDatasource interface, leaving the default implementation.

Task 3: Specify the Configuration Template

Datasource Initialization and Configuration

The data gateway system does the following to configure and initialize a connector:

• Reads stored connector configuration data

• Obtains configuration template instance from the connector

• Updates the configuration template with the configuration data

• Passes the configuration template to the Initialize() method of the connector

The configuration template is an implementation of the IDatasourceConfiguration interface that provides the data gateway system with a structured set of empty, named property instances which the system updates with matching configuration values. The data gateway framework includes the DatasourceConfiguration class, which specifies basic configuration parameters. If your deployment requires advanced configuration logic, skip to Task 4. Otherwise, do the following to specify the default configuration template.

1. Open the SqlDatasource class created in Task 2.
2. Add the following code to the GetConfigurationTemplate() method:
   var config = new DatasourceConfiguration();
   config.AddConfigurationData<string>("ConnectionString");
   return config;

This code returns the DatasourceConfiguration class with the ConnectionString property when the system requests a configuration template. If your deployment requires advanced configuration logic, see Task 4 to create a custom template. Otherwise, proceed to Task 5.

Task 4 (Optional): Create a Custom Configuration Template

Datasource Initialization and Configuration

The data gateway system does the following to configure and initialize a connector:

• Reads stored connector configuration data

• Obtains configuration template instance from the connector

• Updates the configuration template with the configuration data

• Passes the configuration template to the Initialize() method of the connector

You can create a custom implementation of IDatasourceConfiguration if your deployment requires advanced configuration logic. Do the following to create a custom configuration template.

1. Add a new class file named TextFileConfiguration to the project.

2. Implement the ArcherTech.Datasource.Common.Interfaces.IDatasourceConfiguration interface with the following code:
   
   publicclassTextFileConfiguration: IDatasourceConfiguration
   {
   publicTextFileConfiguration()
   {
   Properties = newList<DatasourceConfigurationData>
   {
   newDatasourceConfigurationData
   {
   Name = "FilePath",
   Type = typeof(string)
   }
   };
   }
   publicIEnumerable<DatasourceConfigurationData> Properties { get; }
   }
   This code implements the Properties configuration property and adds a property for the text file path.
3. Open the TextFileConfiguration class created in Step 1 and locate the GetConfigurationTemplate() method.
4. Delete the following line from GetConfigurationTemplate():
   throw new System.NotImplementedException();
   
5. Add the following line to GetConfigurationTemplate():
   return new TextFileConfiguration();

This code returns the custom TextFileConfiguration class with FilePath property when the system requests a configuration template.

Task 5: Implement Datasource Initialization

The IContentDatasource interface includes an Initialize(IDatasourceConfiguration) method for initializing the Datasource. The initialization API is extensible enough to manage any custom initialization scenario. You can use this method to pass login information for another system, timeout configurations for web requests, and other parameters. The initialize method passes the database connection string to the SQL connector. Do the following to implement Datasource initialization.

1. Delete the default line:
   throw new System.NotImplementedException();
   

2. Add a namespace import in the class:
   using ArcherTech.Datasource.Common.Extensions;
   

3. Add a private field named connectionString to the class:
   private string connectionString;

4. Add the following code inside the method:
   public void Initialize(IDatasourceConfiguration configuration)
   {
   connectionString = configuration.GetPropertyValue("ConnectionString");
   }
   

This code attempts to find the DatasourceConfiguration property with the name ConnectionString and set its value to a local class member. The connection string is required when reading, writing, and searching data.

Task 6 (Optional): Create a Custom Content Part Identity Class

If your deployment requires custom comparison logic for identifying records, do the following to create a custom implementation of the ContentPartIdentityBase class.

1. Add a new class named TextFileContentPartIdentity to the project.

2. Implement the ArcherTech.Datasource.Common.ContentPartIdentityBase class with the following code:
   [Serializable]
   [DataContract]
   public class TextFileContentPartIdentity : ContentPartIdentityBase, ISerializable
   {
   private readonly string _id;
   private const string ID_INFO = "id";
   #region Public Constructor
   public TextFileContentPartIdentity(string id)
   {
   _id = id;
   }
   public TextFileContentPartIdentity(SerializationInfo serializationInfo, StreamingContext streamingContext
   {
   _id = serializationInfo.GetString(ID_INFO);
   }
   #endregion
   #region Public Properties
   public string Value => _id;
   #endregion
   #region Public Methods
   public override int CompareTo(object obj)
   {
   if (obj is TextFileContentPartIdentity)
   {
   var obj1 = obj as TextFileContentPartIdentity;
   return _id.CompareTo(obj1._id);
   }
   if (obj is string)
   {
   var obj1 = (string)obj;
   return _id.CompareTo(obj1);
   }
   throw new ArgumentException();
   }
   public override bool Equals(object obj)
   {
   if (obj == null)
   return false;
   if (obj is string)
   {
   return (string)obj == _id;
   }
   if (obj is TextFileContentPartIdentity)
   {
   var obj1 = obj as TextFileContentPartIdentity;
   return _id == obj1._id;
   }
   //comparing two ContentPartIdentityBase objs will end up here with obj typed as ContentPartIdentityBase.
   //it will fall through to here and ToString() is the only valid way to compare them
   if (obj is ContentPartIdentityBase)
   {
   bool test = obj.ToString() == ToString();
   return test;
   }
   throw new ArgumentException();
   }
   public override int GetHashCode()
   {
   return _id.GetHashCode();
   }
   public void GetObjectData(SerializationInfo info, StreamingContext context)
   {
   info.AddValue(ID_INFO, _id);
   }
   public override string ToString()
   {
   return_id;
   }
   #endregion
   #region public operators...
   public static explicit operator TextFileContentPartIdentity(string a)
   {
   return new TextFileContentPartIdentity(a);
   }
   public static explicit operator string(TextFileContentPartIdentity a)
   {
   return a._id;
   }
   public static bool operator ==(TextFileContentPartIdentity a, TextFileContentPartIdentity b)
   {
   if (ReferenceEquals(null, a)
   return ReferenceEquals(null, b);
   return a._id == b._id;
   }
   public static bool operator !=(TextFileContentPartIdentity a, TextFileContentPartIdentity b)
   {
   return a._id != b._id;
   }
   public static bool operator ==(TextFileContentPartIdentity a, string b)
   {
   return a. Equals(b);
   }
   public static bool operator !=(TextFileContentPartIdentity a, string b)
   {
   return !a.Equals(b);
   }
   #endregion
   }

Task 7: Enable Generation of Content Part Identities

The data gateway connector must generate a new external record identifier when creating content in the system. The GenerateContentPartIdentity() method performs this function. Do the following to enable the connector to generate content part identities.

1. Open the SqlDatasource class and locate the GenerateContentPartIdentity() method.

2. Add the following code:
   public ContentPartIdentityBase GenerateContentPartIdentity()
   {
   var identifier = Guid.NewGuid();
   using (var conn = new SqlConnection(connectionString))
   using (var cmd = conn.CreateCommand())
   {
   conn.Open();
   //INSERT new record that will be updated with all the fields in the Set method.
   cmd.CommandText = $"INSERT INTO Records (ID) VALUES ('{identifier}');";
   cmd.ExecuteNonQuery();
   }
   return new ContentPartIdentity(identifier.ToString());
   }
   This code enables the method to generate a new Guid and use its value as the identifier for a new SqlContentPartIdentity instance. Typical external content stores execute logic to generate new records in the external system instead of inside the Datasource itself. When you add a new record into your data gateway application in Archer, the method inserts a new identifier in the records table that represents the new record.

Task 8: Enable Parsing of Content Part Identities

The data gateway connector must parse a string representation of an external record identifier into a ContentPartIdentityBase instance so the data gateway system can identify the record. The ParseContentIdentity() method performs this function. Do the following to enable the connector to parse content part identities.

1. Locate the ParseContentPartIdentity(string) method.

2. Update the method with the following code:
   public ContentPartIdentityBase ParseContentPartIdentity(string contentPartIdentity)
   {
   return new ContentPartIdentity(contentPartIdentity);
   }
   This code enables the method to create a new instance of the ContentPartIdentity class and pass it to the string identifier.

Task 9: Enable Datasource Cloning

To ensure thread safety when processing external content, the data gateway system clones Datasource connectors when the system requests new instances. Datasource cloning decreases the amount of time it takes to create a the new connector instance. The Datasource Clone() method performs this function. Do the following to enable the connector to prepare a clone of the current instance and its properties.

1. Locate the Clone() method method.

2. Update the method with the following code:
   public IContentDatasource Clone()
   {
   SqlDatasource newObject = MemberwiseClone() as SqlDatasource; returnnewObject;
   }
   

Task 10: Create the Record Data Transfer Object (DTO)

A DTO class containing record properties represents each record when reading and writing data. Do the following to create the record DTO class:

1. Add a new class named SqlRecord to the project.

2. Implement the class with the following code:
   public class SqlRecord
   {
   public SqlRecord(ContentPartIdentity identity)
   {
   Identity = identity;
   }
   public ContentPartIdentity Identity { get; }
   public string Name { get; set; }
   public string Description { get; set; }
   public int Count { get; set; }
   }

Task 11: Create the Data Reader

The data reader returns the appropriate SqlContentPart when read. The data gateway framework includes the ContentDataReader class by default, which satisfies basic data reader requirements, and has hooks for creating content part identities and reading fields. If you need to create a custom data reader, skip this task and perform the procedure in Task 12. Otherwise, do the following to create the data reader using the ContentDataReader class.

1. Open the SqlDatasource class.

2. Add the following code to provide logic for reading the fields when creating an instance of the ContentDataReader class, where SQL is a string that represents the connection name or is an alias for the connector:
   var reader = new ContentDataReader <SqlRecord>("SQL", records, fieldIds, x => new ContentPartIdentity(x.key));
3. Add code similar to the following example to set up reading for each field, where name is the name of the column in the external database that represents the Name field in your data gateway application:
   reader.HandleField("name", x => newTextFieldValue { Data = x.Name });

Task 12 (Optional): Create Custom Content Data Reader

A custom content data reader iterates records, builds out fields, and assembles a response. Do the following to implement a custom data reader:

1. Implement the ArcherTech.Datasource.Common.IContentDataReader interface.

2. Add the following code:
   private readonly IEnumerator<TextFileContentPart> _parts;
   public TextFileDataReader(IEnumerable<TextFileRecord> records, IEnumerable<string> fieldIds)
   {
   var parts = new List<TextFileContentPart>();
   foreach (TextFileRecordrecord in records)
   {
   ContentPartIdentityBase identity = new TextFileContentPartIdentity(record.Identity);
   var part = new TextFileContentPart(identity);
   parts.Add(part);
   foreach(string fieldid in fieldIds.Distinct())
   {
   ContentPartDataField dataField;
   switch (fieldid.ToLower())
   {
   case "name":
   dataField = new ContentPartDataField<TextFieldValue>
   {
   FieldValue = new TextFieldValue { Data = record.Name }
   };
   break;
   case "description":
   dataField = new ContentPartDataField<TextFieldValue>
   {
   FieldValue = new TextFieldValue { Data = record.Description }
   };
   break;
   case "count":
   dataField = new ContentPartDataField<NumericFieldValue>
   {
   FieldValue = new NumericFieldValue { Data = record.Count }
   };
   break;
   default:
   throw new ArgumentException($"No field handler found for
   {fieldid.ToLower()}");
   }
   dataField.Alias = "TF";
   dataField.Identity = identity;
   dataField.SourceFieldId = fieldid;
   part.ContentParts.Add(dataField);
   }
   }
   _parts = parts.GetEnumerator();
   }
   The Datasource class generates a text file records list when creating the data reader instance. This code adds a constructor to the class, which takes an enumerable of TextFileRecords and an enumerable of field IDs, then parses the TextFileRecords into a TextFileContentPart DTO which aids in getting the specific field data when records are read. The code also sets a local enumerator to aid in iterating the results.
3. Add the following code to check if additional parts must be read:
   public bool Read()
   {
   return _parts.MoveNext();
   }
4. Add the following code:
   public IEnumerable<ContentPartDataField<T>> ReadData<T>(IEnumerable<string> fieldIds) where T : FieldValueBase
   {
   var dataFields = new List<ContentPartDataField<T>>();
   if (_parts.Current != null)
   dataFields.AddRange(_parts.Current.ContentParts.OfType<ContentPartDataField<T>>());
   return dataFields;
   }
   When reading data, the ReadData() method supplies a list of field IDs to be returned with the record. The generic <T> value of the method identifies the type of data field to be returned. This code retrieves the content parts of the same type for the current record.
   
5. Add the following code to notify the data reader to close any connections:
   public void Close() {}
6. Add the following code to implement the CurrentIdentity property and return the current identity in the content parts enumerator:
   public ContentPartIdentityBase CurrentIdentity => _parts.Current?.Identity;

Task 13: Prepare and Return the Data Reader

For a list of field types supported by Flexible SQL connectors and custom connectors, see Configure Data Gateway Connections (RESTful API).

When the data gateway system requests a data reader, the connector must read the records from the database for the list of identifiers passed to the Get() method. Do the following to enable the connector to prepare and return the data reader.

1. Locate the Get() method in the SqlDatasource class, and add the following code, where SQL is a string that represents the connection name or is an alias for the connector:
   public IContentDataReader Get(IEnumerable<ContentPartIdentityBase> contentIds, IList<string> fieldIds)
   {
   List<SqlRecord> records = new List<SqlRecord>();
   using (SqlConnection conn = new SqlConnection(connectionString))
   using (SqlCommand cmd = conn.CreateCommand())
   {
   conn.Open();
   cmd.CommandText = $"SELECT id, {string.Join(",", fieldIds)} from Records WHERE id IN ({string.Join(",",contentIds.Select(x => $"'{x}'"))});";
   using (var dr = cmd.ExecuteReader())
   {
   while (dr.Read())
   {
   SqlRecord r = new SqlRecord(new ContentPartIdentity(dr.GetString(0)));
   if (fieldIds.Contains("name"))
   r.Name = dr.GetString(dr.GetOrdinal("name"));
   if(fieldIds.Contains("description"))
   r.Description = dr.GetString(dr.GetOrdinal("description"));
   if(fieldIds.Contains("count"))
   r.Count = dr.GetInt32(dr.GetOrdinal("count"));
   records.Add(r);}
   }
   }
   var dataReader =
   new ContentDataReader<SqlRecord>("SQL", records, fieldIds, x => x.Identity);
   dataReader.HandleField("name", x => new TextFieldValue { Data= x.Name });
   dataReader.HandleField("description", x => new TextFieldValue { Data = x.Description });
   dataReader.HandleField("count", x => new NumericFieldValue { Data = x.Count });
   return dataReader;
   }
   This code takes an enumerable of ContentPartIdentityBase instances and a list of strings representing the field ids to return for each record. The method then reads the SQL records using the connection string read from the configuration template described in Task 3, and passes the records and field ids to a new instance of a ContentDataReader, which it returns to the caller.
   

2. Locate the GetAsync() method and add the following code:
   public Task<IContentDataReader> GetAsync(IEnumerable<ContentPartIdentityBase> contentIds, IList<string> fieldIds)
   {
   return Task.Run(() => Get(contentIds, fieldIds));
   }
   This code performs the same function as the Get() method in Step 1, but is handled as an asynchronous task.

Task 14: Create the Data Writer

The data gateway framework includes the ContentDataWriter class by default, which satisfies basic data writer requirements, and has hooks for writing fields from Archer into the DTO and for publishing the DTO to the external data source. If you need to create a custom data writer, skip this task and perform the procedure in Task 15. Otherwise, do the following to create the data writer using the ContentDataWriter class.

1. Open the SqlDatasource class.

2. Add the following code to provide logic for reading fields from Archer when creating an instance of the ContentDataReader class:
   var writer = new ContentDataWriter<SqlRecord>(x =>
   new SqlRecord((ContentPartIdentity)x),
   x => SaveRecord(x));

3. Add code similar to the following example to set up writing of each field into the DTO:
   writer.HandleField<TextFieldValue>("name", (field, record) => record.Name = field.Data);

Note: The class also provides a hook for committing the write after completing all records, if supported by the external data source.

Task 15 (Optional): Create a Custom Content Data Writer

A custom data writer follows a specific sequence of events to allow the connector to write individual records and commit all changes at the end of processing. Do the following to implement a custom content data writer.

1. Implement the IContentDataWriter interface.

2. Add the following code:
   

   private readonly ContentWriteResultCollection _results;
   private readonly IList<TextFileRecord> _records;
   private TextFileRecord _currentRecord;
   private ContentWriteResult _currentResult;
   private readonly string _filePath;
   public TextFileDataWriter(string filePath)
   {
   _filePath = filePath;
   _results = new ContentWriteResultCollection();
   _records = newList<TextFileRecord>();
   }
   This code creates a constructor that takes the file path that the main Datasource read from configuration and sets up two collections. The first collects all write results from saving records, and the other builds a collection of TextFileRecord instances to persist to the text file. The code also adds private member variables to hold the current record being written, the current write results being created, and the file path to the text file.

3. Add the following code:
   public void StartRecord(ContentSaveContext context)
   {
   _currentRecord = new TextFileRecord(context.Identity as TextFileContentPartIdentity);
   _currentResult = new ContentWriteResult
   {
   Identity = context.Identity,
   Exceptions = new List<Exception>(),
   IsSuccessful = true
   };
   }
   This code adds the StartRecord() method, which is called first in the sequence and passes a context object with enough information for the data writer to prepare writing field data. This is an opportunity to create a new TextFileRecord instance to collect the current record field data, and create a write result object to track the current record write success and exceptions.
   

4. Add the following code:
   public ContentWriteResult ProcessFields<T>(IEnumerable<ContentPartDataField<T>> dataFields)
   where T : FieldValueBase
   {
   var exceptions = new List<Exception>();
   // Each ContentPartDataField represents a type of field and its value.
   // The data field's source field id is the field identifier from the external system.
   // Parse the value from each data field based on its source field id and update.
   // the current TextFileRecord.
   foreach (ContentPartDataField<T> dataField in dataFields)
   {
   switch (dataField.SourceFieldId.ToLower())
   {
   case "name":
   var nameDataField = dataField as ContentPartDataField<TextFieldValue>;
   if (nameDataField == null)
   {
   exceptions.Add(new ArgumentException($"Name field is not of type
   {typeof(TextFieldValue).Name}."));
   }
   _currentRecord.Name = nameDataField.FieldValue.Data;
   break;
   case "description":
   var descriptionDataField = dataField asContentPartDataField<TextFieldValue>;
   if (descriptionDataField == null)
   {
   exceptions.Add(new ArgumentException($"Description field is not of type
   {typeof(TextFieldValue).Name}."));
   }
   _currentRecord.Description = descriptionDataField.FieldValue.Data;
   break;
   case "count":
   var orderDataField = dataField as ContentPartDataField<NumericFieldValue>;
   if (orderDataField == null)
   {
   exceptions.Add(new ArgumentException($"Count field is not of type
   {typeof(TextFieldValue).Name}."));
   }
   _currentRecord.Count = (int) orderDataField.FieldValue.Data.Value;
   break;
   }
   }
   // Add the exceptions for the data field type to the result for the entire record.
   _currentResult.Exceptions.AddRange(exceptions);
   _currentResult.IsSuccessful = !_currentResult.Exceptions.Any();
   // Return the write result for just this collection of fields
   return new ContentWriteResult
   {
   Exceptions = exceptions,
   Identity = _currentResult.Identity,
   IsSuccessful = !exceptions.Any()
   };
   }
   This code adds the ProcessFields() method, which receives a collection of ContentPartDataField<T> objects from the data gateway framework, and is called multiple times for each field type (text, numeric, date, and so on) until all field types for the current record have been written.

5. Add the following code:
   public void EndRecord()
   {
   _records.Add(_currentRecord);
   _results.Add(_currentResult);
   }
   This code adds the EndRecord() method, which permits the data writer to write a single record to the system if necessary, or perform cleanup between records. For the TextFileDataWriter, the method adds the current TextFileRecord to the local record collection and the current write result to its local collection.

6. Add the following code:
   public ContentWriteResultCollection Commit()
   {
   // Serialize the text file records to JSON and save to file.
   MemoryStream ms = newMemoryStream();
   DataContractJsonSerializer ser = new
   DataContractJsonSerializer(typeof(IEnumerable<TextFileRecord>));
   ser.WriteObject(ms, _records);
   byte[] json = ms.ToArray();
   ms.Close();
   File.WriteAllBytes(_filePath, json);
   // Return the write results.
   return_results;
   }
   This code adds the Commit() method, which serializes the collection of TextFileRecords created during the previous steps to JSON, then writes them to the file and returns the write results back to the system.

7. Add the following code:
   public Task<ContentWriteResultCollection> CommitAsync()
   {
   return Task.Run(() => Commit());
   }
   This code adds the CommitAsync() method, which performs the same function as Commit(), but is handled as an asynchronous task.

Task 16: Prepare and Return the Data Writer

The data gateway system requests the data writer from the Datasource connector. Do the following to enable the connector to create and return the data writer.

1. Open the SqlDatasource class.

2. Locate the GetWriter() method and add the following code:
   public IContentDataWriter GetWriter()
   {
   var writer = new ContentDataWriter<SqlRecord>(x =>
   new SqlRecord((ContentPartIdentity)x),
   WriteSqlRecord);
   writer.HandleField<TextFieldValue>("name", (field, record) => record.Name = field.Data);
   writer.HandleField<TextFieldValue>("description", (field, record) => record.Description = field.Data);
   writer.HandleField<NumericFieldValue>("count", (field, record) => record.Count = Convert.ToInt32(field.Data.GetValueOrDefault()));
   returnwriter;
   }
   This code returns a new ContentDataWriter with the appropriate connection string.

3. Add the following code:
   private void WriteSqlRecord(SqlRecord record)
   {
   using (var conn = new SqlConnection(connectionString))
   using (var cmd = conn.CreateCommand())
   {
   conn.Open();
   cmd.CommandText = //ALWAYS UPDATE BECAUSE NEW RECORDS ARE INSERTED IN GenerateContentPartIdentity()
   $"UPDATE Records SET name='{record.Name}', description='{record.Description}',
   count={record.Count} where id='{record.Identity.PartId}';";
   cmd.ExecuteNonQuery();
   }
   }
   This code writes the record to the database.

Task 17: Implement Content Deletion

The data gateway connector can delete data from records in the database by batch. Do the following to implement content deletion.

1. Open the SqlDatasource class.

2. Locate the Delete() method and add the following code:
   public void Delete(IEnumerable<ContentPartIdentityBase> identities)
   {
   using (var conn = new SqlConnection(connectionString))
   using (var cmd = conn.CreateCommand())
   {
   conn.Open();
   cmd.CommandText = $"DELETE FROM Records WHERE id IN ({string.Join(",",
   identities.Select(x => $"'{x}'"))})";
   cmd.ExecuteNonQuery();
   }
   }
   

Task 18: Construct SearchQueryBuilder

SearchQueryBuilder is a series of IConditionBuilder objects that define each specific search parameter. SearchQueryBuilder includes IConditionBuilder implementations for common scenarios, such as text contains and numeric equals. You can find the IConditionBuilder implementations under the namespace 'ArcherTech.Datasource.Common.Domain.Search.Conditions'.

You can extend SearchQueryBuilder and the provided IConditionBuilder classes through method overrides or behavior injection to create a custom IConditionBuilder implementation.

Do the following to construct SearchQueryBuilder for your deployment.

1. Open the SearchQueryBuilder class.
2. Pass in the condition builders that make up your desired query. Replace or supplement the condition builders in the following example as needed:
   var queryBuilder = new SearchQueryBuilder(
   new NumericEqualConditionBuilder(),
   new NumericNotEqualConditionBuilder(),
   new NumericGreaterThanConditionBuilder(),
   new NumericLessThanConditionBuilder(),
   new TextContainsConditionBuilder(), // new SqlLikeConditionBuilder(),
   new TextNotContainsConditionBuilder(), // new SqlNotLikeConditionBuilder(),
   new TextEqualConditionBuilder(),
   new TextExactConditionBuilder(),
   new TextNotEqualConditionBuilder(),
   new TextNotExactConditionBuilder())
3. (Optional) Specify a condition builder for a single field, if necessary:
   queryBuilder.SetFieldConditionBuilder("name", new NameConditionBuilder());

Task 19: Implement IContentSearchFilter

The IContentSearchFilter class parses an expression tree, using either SearchQueryBuilder or custom logic, then executes that query in the external data source. The class returns search results inside a SearchFilterResponse object, which contains the content part identities for the matching records. Do the following to implement the IContentSearchFilter.

1. Open the SqlDatasource class.
2. Locate the GetFilter() method and add the following code:
   var queryBuilder = new SearchQueryBuilder(
   new NumericEqualConditionBuilder(),
   new NumericNotEqualConditionBuilder(),
   new NumericGreaterThanConditionBuilder(),
   new NumericLessThanConditionBuilder(),
   new SqlLikeConditionBuilder(),
   new SqlNotLikeConditionBuilder(),
   new TextEqualConditionBuilder(),
   new TextExactConditionBuilder(),
   new TextNotEqualConditionBuilder(),
   new TextNotExactConditionBuilder());
   return new SqlContentSearchFilter(connectionString, queryBuilder);
   This code and returns the appropriate IContentSearchFilter object.
3. Add the following code:
   public class SqlContentSearchFilter : IContentSearchFilter
   {
   private readonly string connectionString;
   private readonly SearchQueryBuilder queryBuilder;
   public SqlContentSearchFilter(string connectionString, SearchQueryBuilder queryBuilder)
   {
   this.connectionString = connectionString;
   this.queryBuilder = queryBuilder;
   }
   public SearchFilterResponse EvaluateNode(SearchExpressionTree searchExpression)
   {
   List<ContentPartIdentityBase> identities = new List<ContentPartIdentityBase>();
   StringBuilder sql = new StringBuilder();
   sql.Append("SELECT id FROM Records WHERE
   ").Append(queryBuilder.BuildQuery(searchExpression));
   using (var conn = new SqlConnection(connectionString))
   using (var cmd = conn.CreateCommand())
   {
   conn.Open();
   cmd.CommandText = sql.ToString();
   using (var dr = cmd.ExecuteReader())
   {
   while (dr.Read())
   {
   identities.Add(new ContentPartIdentity(dr.GetString(0)));
   }
   }
   }
   return new SearchFilterResponse { ContentPartIdentityBases = identities };
   }
   }
   This code implements IContentSearchFilter.

Task 20: (Optional) Implement Test Connection

Users can test their data gateway connections when the connector configuration includes the Test Connection functionality. To implement the Test Connection functionality of data gateway, include the following in your custom connector:

1. Open the SqlDatasource class.
2. Update the class to implement the ArcherTech.Datasource.Common.Interfaces.ITestableDatasource interface.
3. Implement the TestConnection() method as per your external source and return TestConnectionResult with the result.

Task 21: Add a Connection for the Data Gateway Connector

Use the data gateway API to add a connection for the data gateway connector.

The connection must include:

• The full name of the Datasourceclass type: (ArcherTech.Datasource.SqlDemo.SqlDatasource).

• Unique name (alias) for the Datasource. (This ties datasources to fields in Archer.)

• Custom properties (key-value pairs) to be passed to the connector.

For more information, see Add Connection.

Task 22: Map Fields

Use the data gateway API to map external record fields to Archer field definitions.

Create an application with three fields with the following attributes in Archer, then change the field map for each field to point to an external field:

Name	Type
Name	Text
Description	Text
Count	Numeric

For more information, see Add or Edit Field Maps.

Task 23: Deploy the Connector

Before deploying the connector, you must build the .NET project using an x64 active solution platform. Building the project creates an ArcherTech.Datasource.SqlDemo.dll file in the project output folder. Do the following to deploy the connector:

1. Copy this file to three places in your Archer web application installation. If you need the connector to work with the Job Engine, you must copy the file to the same directory locations on your Job Engine servers.

   • [web root directory]\bin\DatasourcePlugins\

   • [web root directory]\Api\bin\DatasourcePlugins\
   • [web root directory]\ContentApi\bin\DatasourcePlugins\
   • [web root directory]\MobileAPI\bin\DatasourcePlugins\
2. If Archer is running, restart IIS.
3. To test the connector, navigate to the application with the fields you mapped in Task 21, and do the following:
   • Add a new record. You should see the record now in the database.
   • Update the record and notice how the values in the database change.
   • Perform a search to find any record that contains text in the name field.
   • Delete the record to confirm it was removed from the database.