[!WARNING] Legacy tasks — do not use in new projects. The tasks in this repository are part of Frends legacy task library. For current, supported tasks visit tasks.frends.com.

• Frends.Sql
  • Installing
  • Building
  • Contributing
  • Documentation
    • Sql.ExecuteQuery
    • Sql.ExecuteProcedure
    • Sql.BulkInsert
    • Sql.BatchOperation
  • License

Frends.Sql

FRENDS SQL Tasks.

Installing

You can install the task via FRENDS UI Task view, by searching for packages. You can also download the latest NuGet package from https://www.myget.org/feed/frends/package/nuget/Frends.Sql and import it manually via the Task view.

Building

Clone a copy of the repo

git clone https://github.com/FrendsPlatform/Frends.Sql.git

Restore dependencies

dotnet restore

Rebuild the project

dotnet build

Run Tests To run the tests you will need an SQL server (you can use prepared docker-compose file to run it out of box). You can set the database connection string in test project appsettings.json file

dotnet test Frends.Sql.Tests

Create a nuget package

dotnet pack Frends.Sql

Contributing

When contributing to this repository, please first discuss the change you wish to make via issue, email, or any other method with the owners of this repository before making a change.

1. Fork the repo on GitHub
2. Clone the project to your own machine
3. Commit changes to your own branch
4. Push your work back up to your fork
5. Submit a Pull request so that we can review your changes

NOTE: Be sure to merge the latest from "upstream" before making a pull request!

Documentation

Sql.ExecuteQuery

Input

| Property | Type | Description | Example | | ----------------- | ---------------------------------- | -------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | Query | string | The query that will be executed to the database. | select Name,Age from MyTable where AGE = @Age | | Parameters | Array{Name: string, Value: string} | A array of parameters to be appended to the query. | Name = Age, Value = 42 | | Connection String | string | Connection String to be used to connect to the database. | TrustServerCertificate=True;Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword; |

Options

| Property | Type | Description | | ------------------------------- | --------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Command Timeout | int | Timeout in seconds to be used for the query. 60 seconds by default. | | Sql Transaction Isolation Level | SqlTransationIsolationLevel | Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Possible values are: Default, None, Serializable, ReadUncommitted, ReadCommitted, RepeatableRead, Snapshot. Additional documentation https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx |

Result

JToken. JObject[]

Example result

[
 {
  "Name": "Foo",
  "Age": 42
 },
 {
  "Name": "Adam",
  "Age": 42
 }
]

The second name 'Adam' can be now be accessed by #result[1].Name in the process parameter editor.

Sql.ExecuteProcedure

Input

| Property | Type | Description | Example | | ----------------- | ---------------------------------- | -------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | Execute | string | The stored procedure that will be executed. | SpGetResultsByAge | | Parameters | Array{Name: string, Value: string} | A array of parameters to be appended to the query. | Name = Age, Value = 42 | | Connection String | string | Connection String to be used to connect to the database. | TrustServerCertificate=True;Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword; |

Options

| Property | Type | Description | | ------------------------------- | --------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Command Timeout | int | Timeout in seconds to be used for the query. 60 seconds by default. | | Sql Transaction Isolation Level | SqlTransationIsolationLevel | Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Possible values are: Default, None, Serializable, ReadUncommitted, ReadCommitted, RepeatableRead, Snapshot. Additional documentation https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx |

Result

JToken. JObject[]

Example result

[
 {
  "Name": "Foo",
  "Age": 42
 },
 {
  "Name": "Adam",
  "Age": 42
 }
]

The second name 'Adam' can be now be accessed by #result[1].Name in the process parameter editor.

Sql.BulkInsert

Input

| Property | Type | Description | Example | | ----------------- | ------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | | Input Data | string | The data that will be inserted into the database. The data is a json string formated as Json Array of objects. The data has to have the same number of columns in the same order as the destination table. | [{"Column1": "One", "Column2": 10},{"Column1": "Two", "Column2": 20}] | | Table Name | string | Destination table name. | MyTable | | Connection String | string | Connection String to be used to connect to the database. | TrustServerCertificate=True;Server=myServerAddress;Database=myDataBa