Is it possible to output the results of an sql command to a text file, when sent via SMO in C#?

Question

I am using SMO in C# to run an SQL script. I need to output the results of the file into a text file.

When using a command line to run the query I can get the desired result using the "-o [output file]" argument. Is there a way to carry out the same operation using an SMO object?

At the moment my code simply sends an sql script to a server:

// Read the sql file
string script = sqlFile.OpenText().ReadToEnd();

// Create a new sql connection, and parse this into a server connection.
SqlConnection sqlConnection = new SqlConnection(connectionString);
Server server = new Server(new ServerConnection(sqlConnection));

// Run the sql command.
server.ConnectionContext.ExecuteNonQuery(script);

Any help would be much appreciated!

Answer 1

I don't know if you can do the exact same thing, but assuming that the script returns some data you could just execute the script, read the returned data and store it to a file, for example:

using (StreamWriter sw = new StreamWriter("output.txt"))
{
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using(SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = script;
            using(SqlDataReader rdr = cmd.ExecuteReader())
            {
                while(rdr.Read())
                {
                    sw.WriteLine(rdr.Item("colName").ToString();
                }
            }
        }
    }
}

Answer 2

Discovered that the SQL script that I needed to execute basically only outputted errors it encountered into the output file. I was able to catch this using:

catch (Exception ex)
        {
            executedSuccessfully = false;

            SqlException sqlex = ex.InnerException as SqlException;

            if (sqlex != null)
            {
                exceptionText = sqlex.Message;
            }
        }

Thanks for your help though Ho! I may need this in the future...