Symphony Framework
Symphony Harmony
Overview > Symphony Harmony
 Symphony Harmony API
 DBConnection

To use the Symphony Harmony capabilities to manage data and execute Synergy .Net method you need to establish a connection to the data source.  The Symphony.Harmony.DBConnector class enables the creation of the required local (in-process) or remote database connection.

 

A local (in-process) connection mean that all data access and stored procedure method execution is in-process.  This is an example of connecting to a local Symphony Harmony database.

Simple select
copyCode 
.define C_CONNTYPE    ,"SymLocal:"
.define C_USERNAME    ,"richard"
.define C_PASSWORD    ,"sillypassword"
.define C_TABLEMAP    ,"!ExampleData.Data.TableMapper.MapTableToFile"

            mConnectionString = C_CONNTYPE + C_USERNAME + "/" + C_PASSWORD + C_TABLEMAP
            mConnector = new DBConnector(mConnectionString)

This will result in a connection string like.

Example connection string
copyCode 
"SymLocal:richard/sillypassword!ExampleData.Data.TableMapper.MapTableToFile"

The table mapping class/method provide the Symphony Harmony framework with a method of converting the table name to the Physical Synergy DBMS file name.  See Symphony_TableMapping template for more details.

To connect to a remote Symphony Bridge server use the following example connection settings.

Simple select
copyCode 
.define C_CONNTYPE    ,"SymRemote:"
.define C_USERNAME    ,"richard"
.define C_PASSWORD    ,"sillypassword"
.define C_TABLEMAP    ,"!ExampleData.Data.TableMapper.MapTableToFile"
.define C_HOSTNAME    ,"@localhost"
.define C_HOSTPORT    ,":8082"
.define C_HOSTTYPE    ,HostServiceType.ConsoleServerTcp

            mConnectionString = C_CONNTYPE + C_USERNAME + "/" + C_PASSWORD + C_HOSTNAME + C_HOSTPORT + C_TABLEMAP
            mConnector = new DBConnector(mConnectionString, C_HOSTTYPE)

This will result in a connection string similar to.

Example connection string
copyCode 
"SymRemote:richard/sillypassword@localhost:8082!ExampleData.Data.TableMapper.MapTableToFile"

This will result in a connection string like.

 

To connect to a Symphony Bridge server using a Microsoft Azure Service Bus Relay connection use the following example connection settings.

Simple select
copyCode 
.define C_CONNTYPE    ,"SymRemote:"
.define C_USERNAME    ,"richard"
.define C_PASSWORD    ,"sillypassword"
.define C_TABLEMAP    ,"!ExampleData.Data.TableMapper.MapTableToFile"
.define C_HOSTNAME    ,"@SymphonyBridgeDemo"
.define C_RELAYKEY    ,"YiOMWlT7JQtT8cPO7dQLH+psuWF6cMAy/nB9EaqyZQs="
.define C_HOSTTYPE    ,HostServiceType.RelayServer

            mConnectionString = C_CONNTYPE + C_USERNAME + "/" + C_PASSWORD + C_HOSTNAME + C_TABLEMAP
            mConnector = new DBConnector(mConnectionString, C_HOSTTYPE, C_RELAYKEY)
 

This will result in a connection string similar to.

Example connection string
copyCode 
"SymRemote:richard/sillypassword@SymphonyBridgeDemo!ExampleData.Data.TableMapper.MapTableToFile"
 DataSelect.RunDataSelect method

The DataSelect.RunDataSelect method allows for the query of the Synergy DBMS file using SQL like SELECT syntax.  For details and examples see the Symphony.Harmony.DataSelect class.

 

An example of making a select call through Symphony Harmony.

Simple select
copyCode 
private mSimpleSelectResults    ,@List<Part_Data>

public property SimpleSelectResults, @List<Part_Data>
    method get
    proc
        mreturn mSimpleSelectResults
    endmethod
endproperty
        
private method doSimpleSelect, void
    endparams
proc
    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select * from part ", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod
               

 The result (mSimpleSeletResults) will be a List<T> of type Part_Data.  The Part_Data is a code generated Symphony Data Object instance.

You can await the RunDataSelect method.

Asynchronous select
copyCode 
private async method doSimpleSelectAsync, void
    endparams
proc
    ;;query the remote database
    data response = await DataSelect.RunDataSelect(mConnector, "select * from part ", 
    &    new Part_Data())

    mSimpleSelectResults = response.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod
               

Awaiting the RunDataSelect method executes the select on a background thread and allows program execution to continue.  This can help to improve the responsiveness of your application from a user perspective.

You can select specific fields from the database by listing the fields to be returned.

Select filtering
copyCode 
private method doSimpleSelectFiltered, void
    endparams
proc
    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select description, id, cost_price from part ", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod
               

Will return only the "id" and "description" fields.

You can limit the number of records returned by supplying a where clause.  The where clause allows you to select only records that match your selection criteria.

Select case sensitive
copyCode 
private method doSimpleSelectWhere, void
    endparams
proc
    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select description, id, from part where description like 'pads'", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

This will limit the records returned from the file to only those records that contains the characters 'pads' in the description field.  selection is by default case sensitive.  You can change this by defining the case sensitivity setting on the Symphony.Harmony.DBConnector instance.

Select case insensitive 
copyCode 
private method doSimpleSelectWhereCase, void
    endparams
proc
    mConnector.SetCaseSensitivity(false)

    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select description, id, from part where description like 'pads'", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

Will return all records where the description field contains the letters "pads" regardless of case.

you can specify the where clause values as bound parameters by using positional parameters. 

Select binding
copyCode 
private mCostPriceValue    ,decimal

public property CostPriceValue, decimal
    method get
    proc
        mreturn CostPriceValue
    endmethod
    method set
    proc
        CostPriceValue = value
    endmethod
endproperty
        

private method doSimpleSelectWhereBound, void
    endparams
proc
    mConnector.SetCaseSensitivity(false)

    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select description, id, from part where cost_price > :1", 
    &    new Part_Data(), CostPriceValue).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

Will limit the returned records to only those where the cost_price is greater than the value in the CostPriceValue property. 

You can further limit the number of records returned by using the TOP keyword.

Select TOP #
copyCode 
private method doSimpleSelectTop, void
    endparams
proc
    mConnector.SetCaseSensitivity(false)

    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select top 10 * from part", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

Will limit the number of records returned to 10.

The default order of the returned records is in primary key order.  The order in which the results are returned can be changed by specifying the fields to sort on and the order. 

Select ascending
copyCode 
private method doSimpleSelectASC, void
    endparams
proc
    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select * from part order by cost_price ASC", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

This will return the records ordered by the cost_price field in ascending order.

using the DESC keyword will return the data in descending order.

Select descending
copyCode 
private method doSimpleSelectDESC, void
    endparams
proc
    ;;query the remote database
    mSimpleSelectResults = DataSelect.RunDataSelect(mConnector, "select * from part order by cost_price DESC", 
    &    new Part_Data()).Result.OfType<Part_Data>().ToList()

    RaisePropertyChanged("SimpleSelectResults")

endmethod

This will return the records ordered by the cost_price field in descending order.

 DataUpdate.RunDataUpdate method

The DataUpdate.RunDataUpdate method allows for the updating of one or more records in a Synergy DBMS file using SQL like UPDATE syntax. For details and examples see the Symphony.Harmony.DataUpdate class.

 

An example of updating a record in the Synergy DBMS file using the RunDataUpdate method.

Simple select
copyCode 
private mIdToUpdate    ,string

public property IdToUpdate, string
    method get
    proc
        mreturn mIdToUpdate
    endmethod
    method set
    proc
        mIdToUpdate = value
        RaisePropertyChanged("IdToUpdate")
    endmethod
endproperty

private mDescriptionToUpdate    ,string

public property DescriptionToUpdate, string
    method get
    proc
        mreturn mDescriptionToUpdate
    endmethod
    method set
    proc
        mDescriptionToUpdate = value
        RaisePropertyChanged("DescriptionToUpdate")
    endmethod
endproperty

private method doPartUpdate, void
    endparams
proc
    data response    ,int

    response = DataUpdate.RunDataUpdate(mConnector, "update part set Description = :1 where Id = :2", new Part_Data(),
    &    DescriptionToUpdate, IdToUpdate).Result

    IdToUpdateMessage = "Total items updated = " + %string(response)

endmethod        

private mIdToUpdateMessage    ,string    

public property IdToUpdateMessage, string
    method get
    proc
        mreturn mIdToUpdateMessage
    endmethod
    method set
    proc
        mIdToUpdateMessage = value
        RaisePropertyChanged("IdToUpdateMessage")
    endmethod
endproperty

This will update a single matching record in the file.

You can update multiple records with a single call, for example, this code will update the cost_price for all records in the file that match a particular current cost price.

Simple select
copyCode 
private mCostPriceToUpdate    ,Decimal

public property CostPriceToUpdate, Decimal
    method get
    proc
        mreturn mCostPriceToUpdate
    endmethod
    method set
    proc
        mCostPriceToUpdate = value
    endmethod
endproperty
        

private method doBulkUpdate, void
    endparams
proc
    data response    ,int

    response = DataUpdate.RunDataUpdate(mConnector, "update part set cost_price = :1 where cost_price = :2", new Part_Data(),
    &    CostPriceToUpdate, CostPriceValue).Result

    IdToUpdateMessage = "Total items updated = " + %string(response)

endmethod

You can update the complete record using the simplified syntax and only defining the table name and the key value.

Simple select
copyCode 
private method doFullUpdate, void
    endparams
proc
    data response    ,int

    response = DataUpdate.RunDataUpdate(mConnector, "update part where Id = :1", new Part_Data(),
    &    IdToUpdate).Result

    IdToUpdateMessage = "Total items updated = " + %string(response)

endmethod

The contents of the passed Symphony Data Object are used to update the data in the record in the file.  The routine will return with the number of records updated.

 DataInsert.RunDataInsert method

The DataInsert.RunDataInsert method allows the inserting or creating of records within a Synergy DBMS file using SQL like INSERT syntax. For details and examples see the Symphony.Harmony.DataInsert class.

 

An example of inserting a record into a file where the data to be inserted is define in the passed in Symphony Data Object.

Simple select
copyCode 
.region "Demonstrate how to create records using Symphony Harmony"

        private mIdToCreate    ,string

        public property IdToCreate, string
            method get
            proc
                mreturn mIdToCreate
            endmethod
            method set
            proc
                mIdToCreate = value
                RaisePropertyChanged("IdToCreate")
            endmethod
        endproperty

        private mDescriptionToCreate    ,string

        public property DescriptionToCreate, string
            method get
            proc
                mreturn mDescriptionToCreate
            endmethod
            method set
            proc
                mDescriptionToCreate = value
                RaisePropertyChanged("DescriptionToCreate")
            endmethod
        endproperty

        private method doGroupCreate, void
            endparams
        proc
            data response    ,int

            response = DataInsert.RunDataInsert(mConnector, "insert into group ", 
            &    new Group_Data() {Group_id = IdToCreate, Description = DescriptionToCreate}).Result

            IdToCreateMessage = "Total items created = " + %string(response)

        endmethod        

        private mIdToCreateMessage    ,string    

        public property IdToCreateMessage, string
            method get
            proc
                mreturn mIdToCreateMessage
            endmethod
            method set
            proc
                mIdToCreateMessage = value
                RaisePropertyChanged("IdToCreateMessage")
            endmethod
        endproperty

.endregion

The response is an integer value that indicates the number of records inserted into the file.  This will either be 1 for a successful insert operation or zero if not records were created.

 DataDelete.RunDataDelete method

The DataDelete.RunDataDelete method allows for the deletion of records from a Synergy DBMS file using SQL like DELETE syntax. For details and examples see the Symphony.Harmony.DataDelete class.

 

An example of deleting a record in the file identified by the primary key value.

Simple select
copyCode 
.region "Show how to delete records using Symphony Harmony"

        private mIdToDelete    ,string

        public property IdToDelete, string
            method get
            proc
                mreturn mIdToDelete
            endmethod
            method set
            proc
                mIdToDelete = value
                RaisePropertyChanged("IdToDelete")
            endmethod
        endproperty

        private method doPartDelete, void
            endparams
        proc
            data response    ,int

            response = DataDelete.RunDataDelete(mConnector, "delete from part where ID = :1", new Part_Data(), IdToDelete).Result

            IdToDeleteMessage = "Total items deleted = " + %string(response)

        endmethod        


        private mIdToDeleteMessage    ,string    

        public property IdToDeleteMessage, string
            method get
            proc
                mreturn mIdToDeleteMessage
            endmethod
            method set
            proc
                mIdToDeleteMessage = value
                RaisePropertyChanged("IdToDeleteMessage")
            endmethod
        endproperty
        
.endregion
Not providing a where clause will delete all records within the file!

 

 DataExecute.RunDataExecute method

The DataExecute.RunDataExecute method allows for the execution of stored procedure methods defined in a Synergy .Net class library using SQL like EXEC syntax. For details and examples see the Symphony.Harmony.DataExecute class.

 

This is an example of a stored procedure method.  The method

Simple select
copyCode 
{MethodSecurity(True)}
public method PriceUpdate, @StringResponse_Data
    in req percentUpdate    ,decimal
    endparams
proc
    
    data counter    ,int,0

    ;;work through all produts and update prices.
    data partItem    ,@Part_Data    ,new Part_Data()
    data partIO        ,@Part_FileIO

    partIO = new Part_FileIO(FileOpenMode.UpdateIndexed)
    while (partIO.ReadNextRecord(partItem) == FileAccessResults.Success)
    begin
        partItem.Cost_price = partItem.Cost_price * percentUpdate
        partIO.UpdateRecordUsingGRFA(partItem)
        incr counter
    end
    partIO.CloseChannel()

    mreturn new StringResponse_Data("Part items updated: " + %string(counter))

endmethod

To execute this method through the Symphony Harmony framework use the DataExecute.RunDataExecute method.  when calling the method you must fully qualify it with the namespace and class names as well as the desired method name.  Arguments can be passed and the API supports all standard .Net types.  Synergy types like a, i and d are not supported.

Simple select
copyCode 
.region "Demonstrate how to execute a remote procedure"

        private mPercentageValue    ,decimal

        public property PercentageValue, decimal
            method get
            proc
                mreturn mPercentageValue
            endmethod
            method set
            proc
                mPercentageValue = value
                RaisePropertyChanged("PercentageValue")
            endmethod
        endproperty
        
        private method doPercentageUpdate, void
            endparams
        proc

            data response    ,@List<StringResponse_Data>

            response = DataExecute.RunDataExecute(mConnector, "exec @ExampleData.Procedures.StoredProcedures.PriceUpdate",
            &    new StringResponse_Data(), PercentageValue).Result.OfType<StringResponse_Data>().ToList()

            PercentageUpdateMessage = response[0].StringValue
            
        endmethod

        private mPercentageUpdateMessage    ,string

        public property PercentageUpdateMessage, string
            method get
            proc
                mreturn mPercentageUpdateMessage
            endmethod
            method set
            proc
                mPercentageUpdateMessage = value
                RaisePropertyChanged("PercentageUpdateMessage")
            endmethod
        endproperty

The result will be determined by the defined Symphony Data Object which must match the return type of the declared method.  The method can declare a single instance or a collection of instances.

 

 Built in Types

As well as exposing code-generated Symphony Data Object types the Symphony Framework provide a number of built-in types.  These types can be used to return simple data from stored Procedure methods.  The following table lists the available built-in types.

Built-in Type Use Rest API type?
BooleanResponse_Data Used to return a simple True/False response from a stored procedure call. Yes. (Boolean_DataREST)
ByteResponse_Data Return a streamed byte array. No.
Column_Data* List the columns available within a table. Yes. (Column_DataREST)
DateTimeResponse_Data Return a full DateTime type from a method. Yes. (DateTimeResponse_DataREST)
IntResponse_Data Used to return an integer value. Yes. (IntResponse_DataREST)
Parameter_Data* List the parameters defined for a given routine. Yes. (Parameter_DataREST)
Routine_Data* List the available stored procedure methods available through Symphony Bridge. Yes. (Routine_DataREST)
ServerResponse_Data Return a structure response class that includes a dynamic collection of response Symphony Data Object instances and cal status information. No.
StringResponse_Data Used to return a string item from a stored procedure method. Yes. (StringResponse_DataREST)
Table_Data* List the available tables within the current Symphony Bridge instance. Yes. (Table_DataREST)
SysInfo* Provide system level information Yes. (Sysinfo_DataREST)
If a built-in type has a Rest API type then it can be returned from a stored procedure method and used through the REST API (details below).  The name to be used through the REST API is identified. The stored procedure method should return the non REST version and the Symphony Bridge will map this to the correct REST instance.
*Built-in types are not designed to be exposed through custom stored procedure methods and are restricted to use within the Symphony Framework.

 

       
 REST API
Although the Symphony Harmony does not provide a REST API client the Symphony Bridge provides a fully functional WEB API Restful service.  Connecting to the service is documented here for consistency

The WEB API Restful implementation provided by the Symphony Bridge server provides access to structure based resources in a standard and uniform manner.  THe structure based resources are code-generated using the Symphony_DataREST and the Symphony_RESTController templates.  The Symphony_DataREST template create the required Symphony Data Object classes which become the available "resources".  The Symphony_RESTController template creates the required controller code and logic to provide access to the structure based resources.

Access to resources and operations upon those resources can be controlled through Symphony Authorization.  See the Symphony Bridge documentation for more details.
 GET operations

Each repository structure can be accessed using a GET operation.  the GET operation will return resources from the server but not make any changes to the database.  For example, for a structure called "part" the Symphony Bridge WEB API Restful protocol provides access to the resource via the example URI.  Notice the plural value of the structure.

GET
copyCode 
http://localhost:8083/api/v1/Parts

This will return, for example, all the available part records in the file:

GET results
copyCode 
[
    {
        "Id": "220A01610",
        "Groupid": "BRAKE",
        "Supplierid": "BREMBO",
        "Description": "Brembo - HPK Machined Radial Caliper Kit",
        "Technical_info": "69.13,\"Radial 2 Piece 2 Pad P3034 Caliper - 108mm Mounting Centres. Radial mount two piece, 2 pad racing and high performance caliper. 4 pot aluminium piston (\u009d 30 / 34 mm) caliper design. Caliper body is machined from solid aluminium, to the same specification as Brembo racing calipers. High temperature main seals plus dust seals allowing for use on road, track and race. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackday discs. (HPK calipe",
        "Quantity": 1,
        "Cost_price": 1
    },
    {
        "Id": "220A39710",
        "Groupid": "BRAKE",
        "Supplierid": "BREMBO",
        "Description": "Brembo - M4 Forged Radial Monobloc Caliper Kit",
        "Technical_info": "Radial mount one piece forged monobloc, 2 pad high performance caliper (108mm centres). 4 pot aluminium piston (\u009d 34 mm) caliper design. High temperature main seals plus dust seals. Anti - vibration spring for the brake pads. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackday discs. (M4 forged monobloc calipers will only run with wide track discs - discs with a braking surface height of 35mm. They cannot be used with narrow track race discs",
        "Quantity": 1,
        "Cost_price": 804.88
    }
]

To locate a specific resource, pass the key, or id, of the resource.

GET/id
copyCode 
http://localhost:8083/api/v1/Parts/220A39710

This will return the matching resource.

If the resource has a segmented primary key, pass all the segments as a comma separated value.

GET
copyCode 
http://localhost:8083/api/v1/Orderlines/0010234,1,001
GET/id results
copyCode 
[
    {
        "Id": "220A39710",
        "Groupid": "BRAKE",
        "Supplierid": "BREMBO",
        "Description": "Brembo - M4 Forged Radial Monobloc Caliper Kit",
        "Technical_info": "Radial mount one piece forged monobloc, 2 pad high performance caliper (108mm centres). 4 pot aluminium piston (\u009d 34 mm) caliper design. High temperature main seals plus dust seals. Anti - vibration spring for the brake pads. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackday discs. (M4 forged monobloc calipers will only run with wide track discs - discs with a braking surface height of 35mm. They cannot be used with narrow track race discs",
        "Quantity": 1,
        "Cost_price": 804.88
    }
]

You can limit the data coming back from the query by specifying the fields you wish returned.  Use the "field=" parameter and provide a comma separated list of fields.

GET?fields=
copyCode 
http://localhost:8083/api/v1/Parts?fields=id,description

This will limit the returned data to the fields specified, in this example the "id" and the "Description" fields.

GET?fields=
copyCode 
[
    {
        "Id": "220A01610",
        "Description": "Brembo - HPK Machined Radial Caliper Kit"
    },
    {
        "Id": "220A39710",
        "Description": "Brembo - M4 Forged Radial Monobloc Caliper Kit"
    },
    {
        "Id": "220A80210",
        "Description": "Brembo - HPK Nickel Finish Radial Caliper Kit"
    },
    {
        "Id": "AFAM01",
        "Description": "AFAM - XHR / XHRC Coloured X-Ring Racing Chains"
    }
]

You can limit the records returned by providing a filter criteria.  Use the "filter=" parameter and provide the filter conditions to apply.

GET?filter=
copyCode 
http://localhost:8083/api/v1/Parts?fields=id,description&filter=description like 'Pads'

Will limit the returned results to only those records where the description field contains the word 'Pads'.  The selection will be case sensitive.

GET?filter=
copyCode 
[
    {
        "Id": "BEN01",
        "Description": "Bendix - MF Sintered Brake Pads (front)"
    },
    {
        "Id": "BEN02",
        "Description": "Bendix - MRR Racing Sintered Pads (front)ÿ"
    },
    {
        "Id": "BEN03",
        "Description": "Bendix - MCR C-Matrix Race Pads (front)"
    },
    {
        "Id": "BEN04",
        "Description": "Bendix - MSR S-Matrix Race Pads (front)"
    },
    {
        "Id": "BEN05",
        "Description": "Bendix - MR Sintered Brake Pads (rear)"
    },
    {
        "Id": "BEN06",
        "Description": "Brembo - SA Sintered Pads (front)"
    }
]

By default the filtering is case sensitive.  To override this you can define  a case sensitive setting.  Use the "casing=" parameter and set the value to false.

GET?casing=false
copyCode 
http://localhost:8083/api/v1/Parts?fields=id,description&filter=description like 'pads'&casing=false

Will return all records that contain the value 'pads' regardless of case.

GET?casing=
copyCode 
[
    {
        "Id": "BEN01",
        "Description": "Bendix - MF Sintered Brake Pads (front)"
    },
    {
        "Id": "BEN02",
        "Description": "Bendix - MRR Racing Sintered Pads (front)ÿ"
    },
    {
        "Id": "BEN03",
        "Description": "Bendix - MCR C-Matrix Race Pads (front)"
    }
]

The default order of the returned records is in primary key order.  The order in which the results are returned can be changed by defining the required order.  Use the "sort=" parameter specifying the fields to sort on and the order.  Append the "-" sign to the field to specify a descending sort order.  Append the "+" sign to the field to specify a ascending sort order.

GET?sort=
copyCode 
http://localhost:8083/api/v1/Parts?fields=description,cost_price&sort=-cost_price

will return the records sorted in descending cost_price order.

GET?sort=
copyCode 
[
    {
        "Description": "WP Suspension - Competition Shock",
        "Cost_price": 873.29
    },
    {
        "Description": "Brembo - M4 Forged Radial Monobloc Caliper Kit",
        "Cost_price": 804.88
    },
    {
        "Description": "Brembo - Radial 2 Piece 2 Pad P3236 Caliper",
        "Cost_price": 763.75
    },
    {
        "Description": "WP Suspension - Road/Track Shockÿ",
        "Cost_price": 688.39
    },
    {
        "Description": "ISR - Radial Master Cylinder",
        "Cost_price": 549.15
    },
    {
        "Description": "Brembo - HPK Disc Kit (front)",
        "Cost_price": 517
    },
    {
        "Description": "Brembo - Radial Brake Master Cylinder (Machined)",
        "Cost_price": 499.38
    },
    {
        "Description": "Chicken Hawk - Pro Line Digital Controlled Tyre Wa",
        "Cost_price": 450
    }
]

Using "sort=+cost_price" would return the results in ascending order of cost_price.

GET?sort=
copyCode 
[
    {
        "Description": "Brembo - Reservoir Pipe (06.5375.03) cm",
        "Cost_price": 0.08
    },
    {
        "Description": "Standard Copper Washer",
        "Cost_price": 0.18
    },
    {
        "Description": "Brembo - Reservoir Pipe Clips",
        "Cost_price": 0.75
    },
    {
        "Description": "Brembo - HPK Machined Radial Caliper Kit",
        "Cost_price": 1
    },
    {
        "Description": "Brembo - HPK Nickel Finish Radial Caliper Kit",
        "Cost_price": 1
    }
]

By default the number of returned records will be the number of matching records from the file.  You can limit the number of returned records by using the "limit=" parameters.

GET?limit=3
copyCode 
http://localhost:8083/api/v1/Parts?limit=3

Will limit the number of returned records to 3.

GET?limit=3
copyCode 
[
    {
        "Id": "220A01610",
        "Groupid": "BRAKE",
        "Supplierid": "BREMBO",
        "Description": "Brembo - HPK Machined Radial Caliper Kit",
        "Technical_info": "69.13,\"Radial 2 Piece 2 Pad P3034 Caliper - 108mm Mounting Centres. Radial mount two piece, 2 pad racing and high performance caliper. 4 pot aluminium piston (\u009d 30 / 34 mm) caliper design. Caliper body is machined from solid aluminium, to the same specification as Brembo racing calipers. High temperature main seals plus dust seals allowing for use on road, track and race. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackday discs. (HPK calipe",
        "Quantity": 1,
        "Cost_price": 1
    },
    {
        "Id": "220A39710",
        "Groupid": "BRAKE",
        "Supplierid": "BREMBO",
        "Description": "Brembo - M4 Forged Radial Monobloc Caliper Kit",
        "Technical_info": "Radial mount one piece forged monobloc, 2 pad high performance caliper (108mm centres). 4 pot aluminium piston (\u009d 34 mm) caliper design. High temperature main seals plus dust seals. Anti - vibration spring for the brake pads. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackday discs. (M4 forged monobloc calipers will only run with wide track discs - discs with a braking surface height of 35mm. They cannot be used with narrow track race discs",
        "Quantity": 1,
        "Cost_price": 804.88
    },
    {
        "Id": "220A80210",
        "Groupid": "FILTERS",
        "Supplierid": "CHICKEN HAWK",
        "Description": "Brembo - HPK Nickel Finish Radial Caliper Kit",
        "Technical_info": "51.25,\"NICKEL FINISH - CNC Radial 2 Piece 2 Pad P3034 Caliper - 108mm Mounting Centres. Radial mount two piece, 2 pad racing and high performance caliper. 4 pot aluminium piston (\u009d 30 / 34 mm) caliper design. Caliper body is machined from solid aluminium, to the same specification as Brembo racing calipers. High temperature main seals plus dust seals allowing for use on road, track and race. Wide pad path design to allow these calipers to be used with standard fitment discs, HPK discs or trackda",
        "Quantity": 1,
        "Cost_price": 1
    }
]

This capability enables the easy ability to implement a paging capability where you can limit the number of items on a page.  For example you can limit the number of items returned per page to 3.  To access the next "page" of records simply combine the limit= parameter with the filter= parameter using the primary key value from the last record returned.

GET?limit=3&filter=Id>'220A80210'
copyCode 
http://localhost:8083/api/v1/Parts?limit=3&filter=Id>'220A80210'

Resulting in the next 3 items returned from the server.

GET?limit=3&filter=Id>'220A80210'
copyCode 
[
    {
        "Id": "AFAM01",
        "Groupid": "CHAIN AND SPROCKET",
        "Supplierid": "AFAM",
        "Description": "AFAM - XHR / XHRC Coloured X-Ring Racing Chains",
        "Technical_info": "FAM chains are made according to stringent technical specifications by the largest Japanese chain producers. They are the result of over 20 years of experience and know how and a collaboration between the AFAM R&D department and the top Japanese chain technicians. Thus ensuring the most appropriate chains for all of today?s high performance racing motorcycles. The very latest are the XHR / XHRC Hyper heavy duty?X? ring chains. These are manufactured using the latest revolutionary XP-2 ?X? ring j",
        "Quantity": 10,
        "Cost_price": 125.17
    },
    {
        "Id": "AFAM02",
        "Groupid": "PIT AND PADDOCK",
        "Supplierid": "AFAM",
        "Description": "Afam - Professional Chain Tool",
        "Technical_info": "Workshop only, professional chain link assembly and removal tool. This special tool alone allows to quickly and precisely install or remove any make or type of motorcycle chain link accurately and correctly. Contains a full set of blocks to avoid over tightening.\"",
        "Quantity": 3,
        "Cost_price": 261.23
    },
    {
        "Id": "AR01",
        "Groupid": "HANDLING",
        "Supplierid": "ARROW",
        "Description": "Arrow - Titanium Steering Dampers",
        "Technical_info": "Arrow R&D in collaboration with a technical organization working side by side with World Superbike and GP teams, have realized a new range of steering dampers as the result of an intense development and testing period on the race tracks all over the world. The new steering dampers by Arrow Special Parts provide a damping action by using hydraulic braking managed by oil flowing through calibrated holes. The steering dampers are manufactured from titanium and aluminium alloy with a chromed steel d",
        "Quantity": 2,
        "Cost_price": 288.95
    }
]

 

 POST Operations

The code generated controller code provides the ability to perform a POST operation. The POST operation will insert the passed resource into the Synergy DBMS data file.   It is the responsibility of the client to ensure the required data, such as the primary key segments, are populated.

POST
copyCode 
http://localhost:8083/api/v1/Groups

The data for the POST operation should be passed in the body of the request.

POST- full payload
copyCode 
POST /api/v1/Groups HTTP/1.1
Host: localhost:8083
Content-Type: application/json
Cache-Control: no-cache
{"Group_id":"NEWGROUP", "Description":"New group description"}

The response will contain the header "Location" that contains the primary key value of the records created.

POST response
copyCode 
Content-Length -> 0
Date -> Fri, XX Sep XXXX XX:XX:XX GMT
Location -> NEWGROUP
Server -> Microsoft-HTTPAPI/2.0
Set-Cookie -> clientID=e22bec29-4104-4ffc-97ef-8e99e6220a53; expires=Sat, XX Sep XXXX XX:XX:XX GMT; domain=localhost; path=/

 

 PUT Operations

The code generated controller code also provides the ability to perform a PUT operation. The PUT operation will update the resource in the file or insert the resource if it does not exist.  It is the responsibility of the client to ensure the required data such as the primary key segments, are populated.

PUT
copyCode 
http://localhost:8083/api/v1/Groups

The data for the PUT operation should be passed in the body of the request.

PUT payload
copyCode 
PUT /api/v1/Groups HTTP/1.1
Host: localhost:8083
Content-Type: application/json
Cache-Control: no-cache
{"Group_id":"EXTRAGROUP", "Description":"Extra group to add to the file"}

The response will contain the status HTTP Status code (200 for success) and, if the resource was updated, a custom header "X-Symphony-Result" that contains the number of records updated.  If the resource was inserted (created) in the Synergy DBMS file then the response will contains the header "Location" that contains the primary key value of the records created.

 The name of this custom header item can be configured by setting the "SYMPHONY_BRIDGE_CUSTOM_HEADER_MNEMONIC" environment variable.  See the Symphony Bridge section for details.

 

Only the fields within the record in the file that have been passed in to the POST operation will be updated.  Therefore do not pass null values unless you require those fields to be updated accordingly.

PUT- full payload
copyCode 
POST /api/v1/Groups HTTP/1.1
Host: localhost:8083
Content-Type: application/json
Cache-Control: no-cache
{
 "Group_id":"NEWGROUP",
 "Description":""
}

This payload will clear the Description field of the corresponding record in the file.

PUT response (resource updated)
copyCode 
Content-Length -> 0
Date -> Fri, XX Sep XXXX XX:XX:XX GMT
Server -> Microsoft-HTTPAPI/2.0
Set-Cookie -> clientID=b1bf2984-cb73-49c0-9e20-b8395c63f3a3; expires=Sat, XX Sep XXXX XX:XX:XX GMT; domain=localhost; path=/
X-Symphony-Result -> 1
PUT response (resource inserted)
copyCode 
Content-Length -> 0
Date -> Fri, XX Sep XXXX XX:XX:XX GMT
Location -> NEWGROUP
Server -> Microsoft-HTTPAPI/2.0
Set-Cookie -> clientID=e22bec29-4104-4ffc-97ef-8e99e6220a53; expires=Sat, XX Sep XXXX XX:XX:XX GMT; domain=localhost; path=/
                       
 DELETE Operations

The code generated controller code also provides the ability to perform a DELETE operation. The DELETE operation will delete the resource from the file. 

DELETE
copyCode 
http://localhost:8083/api/v1/Groups/NEWGROUP

The response will contain the status HTTP Status code (200 for success) and a custom header "X-Symphony-Result" that contains the number of records deleted.

DELETE
copyCode 
Content-Length -> 0
Date -> Fri, XX Sep XXXX XX:XX:XX GMT
Server -> Microsoft-HTTPAPI/2.0
Set-Cookie -> clientID=b1bf2984-cb73-49c0-9e20-b8395c63f3a3; expires=Sat, XX Sep XXXX XX:XX:XX GMT; domain=localhost; path=/
X-Symphony-Result -> 1

If the resource has a segmented primary key, pass all the segments as a comma separated value.

DELETE
copyCode 
http://localhost:8083/api/v1/Orderlines/0010234,1,001
Access to resources and operations upon those resources can be controlled through Symphony Authorization.  See the Symphony Bridge documentation for more details.
 Stored Procedures

The Symphony Bridge provides the ability to execute remote logic written in Synergy .Net.  A stored procedure is a method within a class that you write and attribute to indicate to the Symphony Bridge that the routine is available as a stored procedure.  Once written and built into a class library the assembly should be placed into the Symphony Bridge root folder. 

Below is an example of a store procedure method.

Store Procedure
copyCode 
namespace ExampleData.Procedures
    public class StoredProcedures

        {MethodSecurity(True)}
        public method SupplierPartList  ,@List<Supplier_Data>
            endparams
        proc
            mreturn DataSelect.RunDataSelect(mLocalConnection, "select * from supplier", new Supplier_Data()).Result.OfType<Supplier_Data>().ToList()
        endmethod

    endclass
endnamespace

In the above example the class method is configured with the {MethodSecurity(True)} attribute indicating that this method is available through Symphony Bridge.  All stored procedure methods return either an individual instance of or a collection of Symphony Data Object based objects.  The code generated controller classes provide the ability to perform the various operations and execute your stored procedures.  It is your responsibility to ensure that the actions defined within the stored procedures confirm to the actions defined by the HTTP verb being used.

The above example is of a stored procedure that returns a collection of Supplier items therefore it can be executed using a GET operation.

To execute the stored procedure you specify the method name using the procedure= parameter.

GET stored procedure
copyCode 
http://localhost:8083/api/v1/Suppliers?procedure=SupplierPartList

This will execute the SupplierPartList method and return the results.

GET stored procedure
copyCode 
[
    {
        "Supplier_id": "WP",
        "Name": "WP Suspension",
        "Address_1": "Unit 19B",
        "Address_2": "Hillside Business Park",
        "Address_3": "Kempson Way",
        "City": "Bury St Edmonds, Suffolk",
        "Postal_code": "IP32 7EA",
        "Web_address": "www.wpsuspension.co.uk",
        "MatchingPartList": [
            {
                "Id": "WP01",
                "Groupid": "HANDLING",
                "Supplierid": "WP",
                "Description": "WP - Progressive Fork Springs",
                "Technical_info": "The springs, progressively wound from high-grade silicon chrome spring steel, ensure that your front fork will respond better, and react more controllable when compressed under braking. Solid, stable construction (secured stroke). 15% uprated from standard. Progressively wound WP springs reduce initial dive under braking. Each model has been extensively tested, and to achieve the best possible results each set of Pro-Line progressive springs are supplied in combination with the correct oil type",
                "Quantity": 16,
                "Cost_price": 89.36,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/WP01.jpg"
            },
            {
                "Id": "WP02",
                "Groupid": "HANDLING",
                "Supplierid": "WP",
                "Description": "WP Suspension - 4014 Hydraulic Preload Adjuster",
                "Technical_info": "The WP hydraulic spring pre-load adjuster offers you an ideal alternative for adjusting your shock absorbers to different loads even more easily and comfortably. As a result you will always be able to enjoy optimum comfort and a feeling of safety. Whether you are travelling with or without a passenger a simple turn of the adjusting knob is sufficient, making hook spanners and tools unnecessary. The adjusting knob is simple to fit in any easily accessible location, which not only prevents your ha",
                "Quantity": 8,
                "Cost_price": 164.94,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/WP02.jpg"
            },
            {
                "Id": "WP03",
                "Groupid": "HANDLING",
                "Supplierid": "WP",
                "Description": "WP Suspension - Road/Track Shockÿ",
                "Technical_info": "Anodised aluminium CNC machined from aircraft material. Exceptional cooling properties due to the use of heat diverting aluminium alloys. 20% lighter through the use of quality materials. Rebound damping adjustment. Damping High / Low speed seperately adjustable, each 16 times (256 possibilities in total). Spring preload continuously adjustable (Hydraulic preload adjuster optional). Adjustable length as standard. 14mm piston rod. Recognised safety certificate.\"",
                "Quantity": 1,
                "Cost_price": 688.39,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/WP03.jpg"
            },
            {
                "Id": "WP04",
                "Groupid": "HANDLING",
                "Supplierid": "WP",
                "Description": "WP Suspension - Competition Shock",
                "Technical_info": "This specially for the race track designed shock absorber offers the rider the handling and feedback that they desire. The shock is suitable for sport and racing events where it is used by some of the most successful motorsport teams (i.e. Honda Tenkate Racing or Yamaha Supersport). The ?Super Competition shock absorber? excels through its lightweight and the many adjustment possibilities, such as rebound and balance regulation (adjustable length). It is completely rebuildable so you can change",
                "Quantity": 1,
                "Cost_price": 873.29,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/WP04.jpg"
            },
            {
                "Id": "WP05",
                "Groupid": "HANDLING",
                "Supplierid": "WP",
                "Description": "WP Suspension - Steering Damperÿ",
                "Technical_info": "WP?s 1508 steering damper has an oil circulation system that utilizes the ?twin loop? technology and has only a small reservoir that is filled with 8bar nitrogen. The low pressure in the oil chamber ensures a perfect performance. Overall, the suspension is slightly progressive. The nitrogen ensures that no thermal expansion of the oil occurs, thus the shock absorbtion remains constant. The steering damper has a special adjustment which can be easily adjusted to one of it?s 32 settings using a ne",
                "Quantity": 3,
                "Cost_price": 352.38,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/WP05.jpg"
            }
        ]
    }
]

If the stored procedure accepts arguments these can be passed to the method using the param= parameter.  The format of the param parameter is a JSON string that represents a dictionary containing a key and value pairing.  Here the stored procedure accepts a string argument.

Stored procedure
copyCode 
        {MethodSecurity(True)}
        public method SupplierPartListByCity    ,@List<Supplier_Data>
            in req cityName                     ,string
            endparams
        proc
            mLocalConnection.SetCaseSensitivity(false)
            mreturn DataSelect.RunDataSelect(mLocalConnection, "select * from supplier where City like :1",
            &        new Supplier_Data(), cityName).Result.OfType<Supplier_Data>().ToList()
        endmethod
GET stored procedure
copyCode 
http://localhost:8083/api/v1/Suppliers?procedure=SupplierPartListByCity&param={"city":"Buxton"}

This limits the results to only suppliers that are based in the city that contains the string "buxton".

GET stored procedure
copyCode 
[
    {
        "Supplier_id": "ARROW",
        "Name": "Arrow Performance PArts",
        "Address_1": "67 hollow Way",
        "Address_2": "",
        "Address_3": "",
        "City": "Buxton",
        "Postal_code": "BX5 4RT",
        "Web_address": "",
        "MatchingPartList": [
            {
                "Id": "AR01",
                "Groupid": "HANDLING",
                "Supplierid": "ARROW",
                "Description": "Arrow - Titanium Steering Dampers",
                "Technical_info": "Arrow R&D in collaboration with a technical organization working side by side with World Superbike and GP teams, have realized a new range of steering dampers as the result of an intense development and testing period on the race tracks all over the world. The new steering dampers by Arrow Special Parts provide a damping action by using hydraulic braking managed by oil flowing through calibrated holes. The steering dampers are manufactured from titanium and aluminium alloy with a chromed steel d",
                "Quantity": 2,
                "Cost_price": 288.95,
                "ImageURL": "http://symphonybridge.cloudapp.net/SymphonyImages/AR01.jpg"
            }
        ]
    }
]

You can pass a Symphony Data Object as a stored procedure argument.  To do so you must define the argument as a "DataObjectItem" which is a reserved parameter format.  When declaring the DataObjectItem you can provide all the required information such as the Symphony Data Object class name, the assembly holding the class and the field names and values by specifying the DataObjectData.

DataObjectItem
copyCode 
{
  "DataObjectItem":{
    "DataObjectClass":"ExampleData.Data.Group_DataREST, ExampleData",
    "DataObjectData":{
      "Group_id":"group_id_value",
      "Description":"group description value"
     }
  }
}

Once passed to the Symphony Bridge server the actual class will be located in the defined assembly, an instance of the class created and populated with the data within the DataObjectData value before being passed to the stored procedure.

 

 Built in Resources

The Symphony Bridge WEB API Restful service protocol provides a number of built-in resources that can be used to identify elements exposed by the server.  All of these capabilities are controlled by the configuration setting "SYMPHONY_EXPOSE_CATALOGUE_INFO".  This setting must be set to "True" to allow access to these built-in resources.  See the Symphony Bridge section for more details.

You can return basic information relating to he instance of the Symphony Bridge server by using the SysInfo resource.  This resource is avaialbe without specifying an API verison on teh URI and the information will be returned regardless of the setting of "SYMPHONY_EXPOSE_CATALOGUE_INFO".

GET SysInfo
copyCode 
http://localhost:8083/api/SysInfo

Will return basic system information.

GET SysInfo
copyCode 
[
    {
        "Api_verion": 1,
        "Framework_verison": "3.2.22.0",
        "Synergy_version": "10.3.3",
        "Catalogue_exposed": true
    }
]

 

You can query the Symphony Bridge server to return a collection tables, or resources, available within the server.  Use the SysTables resource to return a collection of tables.

GET SysTables
copyCode 
http://localhost:8083/api/v1/SysTables

Will return a collection of all the tables available.

GET SysTables
copyCode 
[
    {
        "Name": "Group_Data",
        "Assemblyname": "ExampleData.Data",
        "Fullyqualifiedname": "ExampleData.Data.Group_Data,ExampleData",
        "Restable": true,
        "Restablename": "ExampleData.Data.Group_DataREST,ExampleData.Data",
        "Tablespec": "ExampleData.Data.Group"
    },
    {
        "Name": "Part_Data",
        "Assemblyname": "ExampleData.Data",
        "Fullyqualifiedname": "ExampleData.Data.Part_Data,ExampleData",
        "Restable": true,
        "Restablename": "ExampleData.Data.Part_DataREST,ExampleData.Data",
        "Tablespec": "ExampleData.Data.Part"
    },
    {
        "Name": "Supplier_Data",
        "Assemblyname": "ExampleData.Data",
        "Fullyqualifiedname": "ExampleData.Data.Supplier_Data,ExampleData",
        "Restable": true,
        "Restablename": "ExampleData.Data.Supplier_DataREST,ExampleData.Data",
        "Tablespec": "ExampleData.Data.Supplier"
    }
]

Full details of each table is available. 

The Symphony Bridge can be queried to identify the details of each table.  From the above returned information the "Tablespec" value can be used to query the Symphony Bridge server to return information relating to an individual table.  Use the built-in SysColumns resource passing the required table reference.  The table reference must be fully qualified.

GET SysColumns
copyCode 
http://localhost:8083/api/v1/SysColumns/ExampleData.Data.Part

Returns the individual field information for the requested table.

GET SysColumns
copyCode 
[
    {
        "Name": "Id",
        "Type": "System.String"
    },
    {
        "Name": "Groupid",
        "Type": "System.String"
    },
    {
        "Name": "Supplierid",
        "Type": "System.String"
    },
    {
        "Name": "Description",
        "Type": "System.String"
    },
    {
        "Name": "Technical_info",
        "Type": "System.String"
    },
    {
        "Name": "Quantity",
        "Type": "System.Int32"
    },
    {
        "Name": "Cost_price",
        "Type": "System.Decimal"
    },
    {
        "Name": "ImageURL",
        "Type": "System.String"
    }
]

To query the available stored procedure methods you can use the SysRoutines resource.  This will return a collection which details all known stored procedure methods available within the Symphony Bridge.

GET SysRoutines
copyCode 
http://localhost:8083/api/v1/SysRoutines

Will return a collection listing all known stored procedures.

GET SysRoutines
copyCode 
[
    {
        "Name": "PriceUpdate",
        "Classname": "ExampleData.Procedures.StoredProcedures",
        "Assembly": "ExampleData",
        "Fullyqualifiedname": "ExampleData.Procedures.StoredProcedures.PriceUpdate",
        "Returntype": "Symphony.Harmony.Model.StringResponse_Data"
    },
    {
        "Name": "GetGroups",
        "Classname": "ExampleData.Procedures.StoredProcedures",
        "Assembly": "ExampleData",
        "Fullyqualifiedname": "ExampleData.Procedures.StoredProcedures.GetGroups",
        "Returntype": "Symphony.Harmony.Model.ServerResponse_Data`1[[System.Collections.Generic.List`1[[ExampleData.Data.Group_Data, ExampleData, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], mscorlib, Version=4."
    },
    {
        "Name": "SupplierPartList",
        "Classname": "ExampleData.Procedures.StoredProcedures",
        "Assembly": "ExampleData",
        "Fullyqualifiedname": "ExampleData.Procedures.StoredProcedures.SupplierPartList",
        "Returntype": "System.Collections.Generic.List`1[[ExampleData.Data.Supplier_Data, ExampleData, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]"
    }
]

From the list of available routines you can retrieve the routine signature information for an individual routine using the SysParameters resource.  This will return a collection of parameter information for the specified routine name.  You must use the fully qualified name when requesting information about a routine.

GET SysParameters
copyCode 
http://localhost:8083/api/v1/SysParameters/ExampleData.Procedures.StoredProcedures.PriceUpdate

Will return a collection detailing the parameters for the selected routine.

GET SysParameters
copyCode 
[
    {
        "Name": "percentUpdate",
        "Type": "System.Decimal",
        "Isreturned": false
    }
]

 

 

 

 

 

 

 

 

 

 

p;

 

 

Copyright: RCP Consultants.

Send Feedback