Posted by: Cirilo Meggiolaro | 12/31/2008

Tip of the day #78 – Create and alter a database with Microsoft.SqlServer.Management.Smo namespace

Let’s check today how to create and alter a Sql Server database using the Microsoft.SqlServer.Management.Smo namespace.

If you haven’t checked the Tip #75, is a good time to read. The tip explains the basic concepts and the two main classes: Server and Database.

How to…

Create a database

  • Create an instance of the Server class;

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

  • A new instance of the Database class is necessary, passing as parameter the server object that we created on the previous step and a name to the new database. For this example let’s call it SmoDb;

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

  • Once you have an instance of the Sql Server, you may set some options like AutoShrink, the log files and so on. If you want to use the server defaults you don’t need to set any property. The following settings are for demonstration purposes only;

db.DatabaseOptions.AutoShrink = false;
db.DatabaseOptions.Trustworthy = false;
db.DatabaseOptions.AutoClose = false;

  • The last step is to invoke the Create method. If the user has permission to create a database the operation must succeed.

db.Create();

The code must be similar to the following:

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

try
{
    if (svr != null)
    {
        /// Creates a new database instance passing the server object and the new database name.
        Database db = new Database(svr, “SmoDb”);

        /// Set database properties
        db.DatabaseOptions.AutoShrink = false;
        db.DatabaseOptions.Trustworthy = false;
        db.DatabaseOptions.AutoClose = false;

        /// Creates the database on the server.
        db.Create();
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Alter a database

  • Create an instance of the Server class passing the database server (or server instance) name as parameter;

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

  • If you have a valid Sql Server instance created, you must retrieve the database object using the databases collection property available under the Server class;

Database db = svr.Databases[“SmoDb”];

  • Once you have a valid database instance, you must change the properties you need. Again, just an example;

db.DatabaseOptions.BrokerEnabled = true;

  • The last step is to invoke the Alter method under the database object.

db.Alter();

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[“SmoTest”];

        if (db != null)
        {
            /// Change some options
            db.DatabaseOptions.BrokerEnabled = true;

            /// Update the database settings on the server.
            db.Alter();
        }
    }
}
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: