3

Maybe the answer is existing somewhere but I couldn't find one for this requirement.

The SQL script can contain any kind of commands:

  1. select count(*)....
  2. select Field1, Field2, Field3...
  3. insert into Table1 (...) values (...)
  4. alter table Table1
  5. exec ProcName1...

After executing each command, its results should be captured similarly to SSMS. So results of the above commands look as below:

  1. A result with one column represents number of rows (for the count)
  2. A result set list all rows (for select)
  3. A number of inserted rows (for insert)
  4. Command(s) completed successfully (for alter) ...

This answer sounds good (https://stackoverflow.com/a/1728859/4903729) but results can't be captured.

Any suggestion is much appreciated. Thanks !

Update: found this is helpful https://github.com/DbUp/DbUp, but it seems not giving back the outputs

Community
  • 1
  • 1
hazjack
  • 1,645
  • 13
  • 27
  • You need to create target objects or use dynamics of some sort. – Peter Smith Aug 30 '16 at 07:43
  • @PeterSmith could you please give more detail? – hazjack Aug 30 '16 at 07:45
  • EF, for example, needs to return objects into an entity/object that is predefined. The link you give above is for a 'non-query' i.e. one that does not return results. What are you trying to do? Do you want to use Visual Studio as a replacement for SSMS? You need to give more detail on your requirements. – Peter Smith Aug 30 '16 at 09:26
  • @PeterSmith thanks for yr details. No I don't want to replace SSMS, I just want to migrate my database to a new point thru scripts. But the outputs of scripts are important and users want to see that. – hazjack Aug 30 '16 at 09:53
  • OLEDB and SQLClient has no generalized `Execute()`. You may need to use ADODB https://support.microsoft.com/en-us/kb/308611 in the .Net project which has it. – Serg Aug 30 '16 at 10:00
  • @Serg thanks for mentioning this. Having a question, can it handle the commands which return nothing? – hazjack Sep 01 '16 at 10:45
  • Yes it can . Execute will return nothing (null) instead of recordset. – Serg Sep 01 '16 at 10:59

2 Answers2

1

To get messages you need to subscribe to the event SqlConnection.InfoMessage

For example:

using (var cn = new SqlConnection(cs))
{
  cn.InfoMessage += (sender, eventArgs) => Console.WriteLine("    Message: "+ eventArgs.Message);

I'm using this to get the output of "SET STATISTICS IO ON" (it works with ExecuteNonQuery)

Alain Tésio
  • 489
  • 4
  • 8
  • Yes this is nice Alain! But just for the messages. In many cases it is required to get result sets or number of affected rows.... – hazjack Dec 26 '16 at 06:35
0

After some searching around I come up with few options

First option: with DbUp !

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.

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)

Third option: use sqlcmd and parse the output (in console or in a file with -o param)

Mentioned by @stakx

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

Another option: build from the ground

Some works will be repeated from Dapper or DbUp...

Community
  • 1
  • 1
hazjack
  • 1,645
  • 13
  • 27