Posted by: Cirilo Meggiolaro | 01/1/2009

Tip of the day #79 – Create and alter table with Microsoft.SqlServer.Management.Smo namespace

Today’s tip is all about how to create and alter tables in a database using the Microsoft.SqlServer.Management.Smo namespace I’ve been written about this week.

If you haven’t checked the Tip #75 you should take some time to read the introduction about this namespace and know a little bit about the main classes: Server and Database. They are the entry point for most part of the tasks you may want to perform on the database.

Table class

The Table class contains several properties and methods that allow you to create, alter, drop and set the table characteristics.

How to…

For this example we are going to create a table named Customer with two columns: customer_id and customer_name. The following list describes the steps to perform this task:

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

  • Once you have an instance of the Database we need to create a table object instance using one of the following overloads;

public Table();
public Table(Database database, string name);
public Table(Database database, string name, string schema);

  • The next step is to create columns, set their properties and add them to the table. The column class have the following overloads:

public Column();
public Column(SqlSmoObject parent, string name);
public Column(SqlSmoObject parent, string name, DataType dataType);
public Column(SqlSmoObject parent, string name, DataType dataType, bool isFileStream);

  • The last step is to invoke the Create method;

tbl.Create();

Your 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 new table instance.
            Table tbl = new Table(db, “Customer”);

            /// Create a column to store the customer id.
            Column col1 = new Column(tbl, “customer_id”, DataType.Int);

            /// Defines the column settings.
            col1.Identity = true;
            col1.IdentityIncrement = 1;
            col1.IdentitySeed = 1;
            col1.Nullable = false;

            /// Adds the column to the table.
            tbl.Columns.Add(col1);

            /// Create a column to store the customer name.
            Column col2 = new Column(tbl, “customer_name”, DataType.VarChar(200));

            /// Defines the column settings.
            col2.Nullable = false;

            /// Adds the column to the table.
            tbl.Columns.Add(col2);

            /// Adds the table to the database.
            tbl.Create();
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Alter Table

The steps you need to perform to alter a table are pretty similar:

  • Retrieve a server instance;
  • Retrieve a database instance;
  • Retrieve a table instance;
  • Perform the properties you need like create or drop columns, add constraints, foreign keys and so on;
  • Invoke the table Alter method.

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)
        {
            /// Retrieves the table instance.
            Table tbl = db.Tables[“Customer”];

            if (tbl != null)
            {
                /// Create a column to store the customer date of birth.
                Column col3 = new Column(tbl, “customer_dob”, DataType.DateTime);

                /// Defines the column settings.
                col3.Nullable = false;

                /// Adds the column to the table.
                tbl.Columns.Add(col3);

                /// Alter table.
                tbl.Alter();
            }
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Tomorrow’s tip is going to explain how to work with indexes and define a primary key for our Customer table created during today’s tip.

Stay tuned!

Advertisements

Responses

  1. Awesome Cirilo!


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: