CODEWITHSUNDEEP
sql-serversmo
Shai
Shai

Reputation: 569

Script run result using SMO for SQL Server

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"

Upvotes: 1

Views: 1605

Answers (2)

Nerdynosaur
Nerdynosaur

Reputation: 1888

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);
    }
}

Upvotes: 2

Alessandro Alpi
Alessandro Alpi

Reputation: 368

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();

Upvotes: 0

Related Questions