​

Frends provides four tasks to integrate with the SQL server. You can check Tasks' documentation by clicking their name:

You should use the ExecuteQuery method when you need to execute a SQL query or script that returns data from a SQL database, for example, a SELECT statement. ExecuteQuery is the most versatile option, but for bid queries, it might require the most work. It will basically execute any SQL query.

In essence, the ExecuteProcedure task will provide an easy way to execute a stored procedure in a database. Stored procedures are pre-compiled and optimized SQL scripts that are stored in the database, and can be executed multiple times. Stored procedures can also be executed with ExecuteQuery.

BatchOperation is probably the most used Task for SQL integrations. You should use the BatchOperation method when you want to perform multiple operations, such as insert, update, or delete operations, as a single, atomic unit of work. This can be useful for improving performance and reducing the number of round trips to the database, as well as for maintaining data consistency in the face of potential failures or errors. They can also help ensure atomicity and consistency of data, as the entire batch will either succeed or fail as a single unit, rather than individual requests potentially succeeding or failing independently.

To make using of BatchOperation easier you can copy the insert statement from SSMS and use that as a template for any query.

BulkInsert should be used when you need to insert a large number of records into a database table at once. The normal insert process can become slow and inefficient when dealing with a large volume of data. By using bulk insert, you can significantly improve the performance and efficiency of your data insertion process. Under the hood BulkInsert uses the Bulk Copy Program (BCP).

When using BulkInsert data validation must be performed prior to bulk insert operations since bulk insert operations often bypass normal data validation rules. In particular all data types have to be right, otherwise query will just fail. This is why you should think carefully is it necesseary to use BulkInsert or would BatchInsert be more easier and robust. It should be also noted that BulkInsert guesses the data types based on the first row and null values are assumed to be strings.

The next article is Introduction to General SQL tips

​