Posted by: Cirilo Meggiolaro | 12/30/2008

Tip of the day #77 – Retrieving database locks with Microsoft.SqlServer.Management.Smo namespace

In today’s tip we are going to check how to retrieve a list of all the existing locks in a database using the Microsoft.SqlServer.Management.Smo namespace.

How to…

If you haven’t checked the Tip #75, take a look before you proceed to check the main classes: Server and Database.

For our example we need to perform the following tasks:

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

  • Once you have a valid database instance, you must use the EnumLocks method to retrieve a DataTable object containing a list of existing locks in the database;

DataTable dt = db.EnumLocks();

  • Display the locks information to the user.

The code must be similar to the following:

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

if ((svr != null) && (svr.Databases.Count > 0))
{
    /// Retrieves the database from the server class instance.
    Database db = svr.Databases[“School”];

    if (db != null)
    {
        /// Retrieves a list of locks on the database
        DataTable dt = db.EnumLocks();

        if ((dt != null) && (dt.Rows.Count > 0))
        {
            /// Displays column names
            for (int col = 0; col < dt.Columns.Count; col++)
            {
                Console.Write(dt.Columns[col].ColumnName + “\t”);
            }

            Console.WriteLine();

            /// Loops through each row to display the lock information.
            for (int row = 0; row < dt.Rows.Count; row++)
            {
                /// Loops through each column to display the value.
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    Console.Write(dt.Rows[row][col].ToString() + “\t”);
                }

                Console.WriteLine();
            }
        }
    }
}

Advertisements

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: