Script run result using SMO for SQL Server

Question

I am using SMO

I am having hard time to capture result (it could be syntax error message or set of result with data) just like we get in SQL Server Management Studio. I tried several example that I found on internet, but could not make it work as I wanted.

I tried the following two syntaxs

//I am trying catch errors here
int[] results = DBServer.ConnectionContext.ExecuteReader("select * from SomeNonExistingTable");


//I am trying to catch result sets as dataset here
//But I cannot implicitly convert to dataset here using C#
DataSet ds = databaseServer.ConnectionContext.ExecuteWithResults("Select * from mytable");

I tried to get some idea from the following Websites:

SMO ConnectionContext.StatementTimeout setting is ignored

C# SMO Select from Database

Using SMO, still no go... ConnectionContext.ExecuteNonQuery(script) can't understand "GO"

What error(s) are you getting? Specifically, the second one should work; ExecuteWithResults returns a DataSet object. — Ben Thul, Jan 13 '15 at 16:05
If i declare ds as var, and check the value of ds then I can see it is returning dataset, but I cannot cast that result as dataset or convert implicitly. See code below var ds = databaseServer.ConnectionContext.ExecuteWithResults("Select * from mytable"); — Shai, Jan 16 '15 at 03:16

score 2 · Answer 1 · answered Nov 23 '16 at 02:01

You can put in the try and catch.

try {
        DBServer.ConnectionContext.ExecuteReader("select * from SomeNonExistingTable");
}
catch(Exception ex) {
    while(ex.InnerException) {
        err = ex.InnerException;
        Console.WriteLine(err);
    }
}

score 0 · Answer 2 · answered Jan 17 '15 at 13:00

In a Console application, C#, this is a working example:

var server = new Server(@"YourServer");
        server.ConnectionContext.DatabaseName = "YourDatabase";

        try
        {
            var result = server.ConnectionContext.ExecuteReader("SELECT * FROM YourTable");

            while (result.Read())
            {
                Console.WriteLine(result["ValidColumn"]);
            }

        }
        catch (Exception ex)
        {
            // error handling
            Console.WriteLine(ex);
        }

        try
        {
            var result = server.ConnectionContext.ExecuteWithResults("SELECT * FROM YourTable");

            var i = 0;
            while (i < result.Tables[0].Rows.Count)
            {
                var dr = result.Tables[0].Rows[i];
                Console.WriteLine(dr["ValidColumn"]);
                i++;
            }
        }
        catch (Exception ex)
        {
            // error handling
            Console.WriteLine(ex);
        }

        Console.ReadLine();

Script run result using SMO for SQL Server

2 Answers2