Posted with : Studying

How to execute one or many SQL files and handle all outputs in .NET

Have considered about this requirement for a while and after some studies I found few options. Here are some:

First option: with DbUp !

It is very nice for this, it can read sql scripts from various sources (file system, embeded in assembly...), then execute each script in the list (parse each script into a list of commands before executing), then it can log the script's outputs.

But looked into SqlScriptExecutor, it also writes other infos such as script name, error exception into the log source beside script's results.

_config.yml

=> The need is to implement a subclass of SqlScriptExecutor, override method Log to handle the outputs;

=> Then implement a custom of IUpgradeLog to save the result as expected; If number of affected rows is important, it is required to override method Execute

Second option: using Dapper !

With method QueryMultiple, all commands will be executed and outputs are kept in a GridReader object. -> The need is to implement a behavior to serialize that object to expected format (CSV in this case)

_config.yml

3rd option: via sqlcmd.exe utility

This is nice as the input and output can be customize by many parameters.

But seems difficult to manage transaction (per script, per all scripts); not able to run on other DBMS such as MySQL.

And below is my Quick comparison

1. DbUp

It is designed for upgrading databases, can track scripts have been run and skip running them on future upgrades

-> A custom table will be used in each database to store those scripts

-> Seems good in this case

2. Dapper

Dapper is generally used for running scripts, not required much customization.

-> No behavior to keep track of executed scripts -> need to control that by ourself.

References

  1. A question on SO - How to execute SQL files and handle results in .NET
  2. DbUp on Github
  3. Dapper Site
Written on December 25, 2016

Tags