> ## Documentation Index
> Fetch the complete documentation index at: https://docs.superoffice.com/llms.txt
> Use this file to discover all available pages before exploring further.

# How to perform an archive provider query

> How to perform an archive provider query

It is necessary to query data from a database when writing applications. Archive providers are designed for this. They handle the complex business logic of filtering and flattening the complex relationships in the database into a simple flat table.

While archive providers make complex queries possible through web services, they of course work in and with their lower-level data types executed in the belly NetServer.

## NetServer Core vs web services

This section shows how to query an archive provider both using the core NetServer API, as well as the NetServer Web Services API.

There are subtle differences between core and web services APIs, but for the most part, they are the same. One of the biggest differences is how value types are returned (integers, Double, and DateTime).

Both examples represent a query that selects all sales where the sale project ID is set to 47.

<Tabs>
  <Tab title="NetServer Core">
    ```csharp NetServer Core theme={null}
    // specify the name and instantiate the archive provider
    IArchiveProvider provider = ArchiveProviderFactory.Create("FindSale");

    // set the desired fields
    provider.SetDesiredColumns("saleId", "heading");

    // set the desired entities
    provider.SetDesiredEntities("sale");

    // set the paging information
    provider.SetPagingInfo(50, 0);

    // specify and set the criteria restriction
    provider.SetRestriction(new ArchiveRestrictionInfo("projectId", "=", "47"));

    // execute the query and iterate over the results
    foreach (ArchiveRow row in provider.GetRows(""))
    {
      int saleId = (int)row.ColumnData["saleId"].RawValue;
      string heading =  row.ColumnData["heading"].RawValue.ToString();
      Console.WriteLine("SaleId {0}, Heading {1}", saleId, heading);
    }

    // remember to close the provider to release resources
    provider.Close();
    ```
  </Tab>

  <Tab title="NetServer Web Services">
    ```csharp NetServer Web Services theme={null}
    // Instantiate the FindAgent web service proxy class
    using (FindAgent fa = new FindAgent())
    {
      // specify the archive provider name
      string providerName = "FindSale";

      // set the desired fields
      string[] columns = { "saleId", "heading", "projectId" };

      // specify the criteria restriction
      ArchiveRestrictionInfo[] restrictions =
      {
        new ArchiveRestrictionInfo()
        {
          Name = "projectId",
          Operator = "=",
          Values = new [] {"47"},
          IsActive = true
        }
      };

      // execute the query and get the results
      var results = fa.FindFromRestrictionsColumns(restrictions, providerName, columns, 50, 0);

      // ensure there are results and iterate over the them
      if ( results != null && results.ArchiveRows != null && results.ArchiveRows.Length > 0 )
      {
        foreach (var row in results.ArchiveRows)
        {
          var saleId = row.ColumnData["saleId"].DisplayValue;
          var heading = row.ColumnData["heading"].DisplayValue;
          Console.WriteLine("SaleId {0}, Heading {1}",
              SuperOffice.CRM.Globalization.CultureDataFormatter.
              ParseEncodedInt(saleId).ToString(),
              heading.ToString());
        }
      }
    }
    ```
  </Tab>
</Tabs>

## Example

The following example demonstrates how a `PersonProvider` is used to query data from the database.

```csharp theme={null}
using SuperOffice;
using SuperOffice.CRM.ArchiveLists;
using(SoSession newSession = SoSession.Authenticate("SAL0", ""))
{
  SuperOffice.CRM.ArchiveLists.IArchiveProvider personArchive = new PersonProvider();

  //Get the list of columns handled by this provider
  List<ArchiveColumnInfo> availableColumns = personArchive.GetAvailableColumns();

  //Get the list of Entities supported by this provider
  List<ArchiveEntityInfo> availableEntities = personArchiveGetAvailableEntities();

  //Display the list of column names and entity names in two list boxes
  foreach (ArchiveColumnInfo columninfo in availableColumns)
  {
    FieldsListBox.Items.Add(columninfo.Name);
  }
  foreach (ArchiveEntityInfo entityinfo in availableEntities)
  {
    entitiesListBox.Items.Add(entityinfo.Name);
  }

  //Set the columns that needs to be returned
  personArchive.SetDesiredColumns("personId", "fullName","personUdef:SuperOffice:6", "personUdef:SuperOffice:1");

  //set the paging properties of the provider.
  personArchive.SetPagingInfo(10, 0);
  personArchive.SetOrderBy(new ArchiveOrderByInfo("contactId", SuperOfficeUtil.OrderBySortType.DESC),
  new ArchiveOrderByInfo("personUdef:SuperOffice:6", SuperOffice.UtilOrderBySortType.ASC));

  //An array of restrictions with an implicit and in between them.
  personArchive.SetRestriction(new ArchiveRestrictionInfo("personId", ">","50"),
  new ArchiveRestrictionInfo("personUdef:SuperOffice:6", "=", "1"));

  //Display the retrieved data in another list box
  int rowNo = 1;
  foreach (ArchiveRow row in personArchive.GetRows())
  {
    if (rowNo == 1)
    {
      foreach (KeyValuePair<string, ArchiveColumnData>column inrowColumnData)
      {
        resultsListbox.Items.Add(column.Key);
      }
    }
    foreach (KeyValuePair<string, ArchiveColumnData> column inrowColumnData)
    {
      resultsListbox.Items.Add(column.Value.ToString());
    }
    ++rowNo;
  }
}
```

Here we have initially created an archive provider of type Person. PersonProvider is a specially designed archive provider:

1. Retrieve the list of columns that can be handled by this archive provider through the `GetAvailableColumns` method.
2. Retrieve the Entities that are supported by this archive provider via the `GetAvailableEntities` method.
3. `GetAvailableColumns()` and `GetAvailableEntities` return data lists of type `ArchiveColumnInfo` and `ArchiveEntityInfo` respectively.

In the above example, we have retrieved the `ColumnInfo` and `EntityInfo` from the `PersonProvider` and displayed them in 2 list-boxes.

![PersonProvider -screenshot][img1]

Finally, we have retrieved the set of rows satisfying the above query and displayed them in another Listbox.

![Result -screenshot][img2]

## NetServer REST WebApi

Using the `Sale` endpoint and issue an `OData` query (see [documentation][1]):

```http theme={null}
GET /Cust12345/api/v1/Sale?$select=saleId,heading,projectId&$filter=projectId eq 47 HTTP/1.1
Host: sod.superoffice.com:443
Authorization: Bearer 8A:Cust12345.AS5...sy9
Accept: application/json
```

## NetServer REST Agent Service

Using the RESTful Agent Find endpoint (see [documentation][2]):

```http theme={null}
POST /Cust12345/api/v1/Agents/Find/FindFromRestrictionsColumns HTTP/1.1
Host: sod.superoffice.com:443
Authorization: Bearer 8A:Cust12345.AS5...sy9
Accept: application/json
Content-Type: application/json

{
  "ProviderName": "FindSale",
  "DesiredColumns": [
    "saleId",
    "heading",
    "projectId"
  ],
  "Restrictions": [
    {
      "Name": "projectId",
      "Operator": "=",
      "Values": [
        "47"
      ],
      "IsActive": true
    }
  ],
  "Page": 0,
  "PageSize": 100
}
```

[1]: ../reference/restful/rest/Sale/v1SaleEntity_GetAll

[2]: /en/api/reference/webapi/SuperOffice.WebApi.Agents.FindAgent

[img1]: /media/loc/en/api/archive-providers/query-1.jpg

[img2]: /media/loc/en/api/archive-providers/query-2.jpg


## Related topics

- [NetServer archive providers](/en/api/archive-providers/index.md)
- [How to retrieve members of a specific selection using archive provider](/en/api/search/selection/archive/get-selection-members-provider.md)
- [REST WebAPI search](/en/api/search/odata/index.md)
- [How to Search using Find Selections](/en/api/search/find-selection/how-to-search.md)
- [Check application API performance](/en/developer-portal/analytics/metrics-performance.md)
- [How to get a list of invitations (services)](/en/api/web-services/howto/diary/get-invitations-services.md)