Posted by: Cirilo Meggiolaro | 01/5/2009

Tip of the day #83 – Executing non-query SQL statements with Microsoft.SqlServer.Management.Smo namespace

Let’s check today how to execute non-query statements against a database with the Microsoft.SqlServer.Management.Smo namespace. If you haven’t read anything about it yet, check the Tip#75 for a quick introduction.

How to…

The steps to perform this task are:

  • Create an instance of the Server class;

Server svr = new Server(@”MYPC\SQLEXPRESS”);

  • Retrieve the database object instance passing the server object we created on the previous step and the database name;

Database db = new Database(svr, “SmoDb”);

  • Create a non-query T-SQL statement;
  • Invoke one of the ExecuteNonQuery method overloads;

public void ExecuteNonQuery(string sqlCommand);
public void ExecuteNonQuery(StringCollection sqlCommands);
public void ExecuteNonQuery(string sqlCommand, ExecutionTypes executionType);
public void ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType);

The code must be similar to the following:

/// Creates a server class instance.
Server svr = new Server(@”MYPC\SQLEXPRESS”);

    if (svr != null)
        /// Retrieves the database from the server class instance.
        Database db = svr.Databases[“SmoDb”];

        if (db != null)
            /// Creates a non-query SQL statement.
            string sql = @”INSERT INTO [Customer] ([customer_name], [customer_dob],
                                 [customer_active]) VALUES (‘George Smith’, ’12/12/1976′, 1)”;

            /// Executes the query
catch (Exception ex)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


%d bloggers like this: