How to run sql from a .net application against sqlserver and get output like with SQL Management Studio?

Question

I'd like my app to run sql files and receive the results output as if the sql was run from SQL Management Studio with results to text. i.e. nicely formatted recordsets, printed messages, rows affected, errors, running multiple batches separated by GO statements, etc. Any suggestions how to do this without building it from scratch on top of a normal SQL connection?

I'm using C# .NET

Answer 1

You don't say what language your application is written in, but if you're using .Net, you might get some help from the source code for QueryExPlus, a lightweight OSS alternative to SSMS.

Answer 2

Probably the easiest is to use GetXml() method of dataset and then apply XSLT transformation to produce the desired result.