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

try
{
    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
            db.ExecuteNonQuery(sql);
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Categories

%d bloggers like this: