Using SQL-DMO In C#

Welcome to my tutorial on Programming SQL-DMO in C#. In this tutorial you will get a taste of how to use SQL-DMO to help programtically administer a SQL Server database. Before we look at how these objects are used, I guess we need to spend a little time explaining what SQL-DMO is.

Distributed Management Objects (DMO) is a set of objects that come with SQL Server, that make is easier for one to administer their database via code, such as writing your own administration application. SQL-DMO is actually what Enterprise Manager is built upon. You can do do pretty much the same things with the objects that you can do with Enterprise Manager, such as:

Create Objects
- Databases
- Stored Procedures
- Tables
- Triggers
- etc.
Back up your database
Create Jobs & DTS Packages
Alter tables
And much more

SQL-DOM is a COM object, which means it can be used with any COM Friendly platform, this includes C++, VB, WSH, ASP, Delphi and any .Net Language, but in this tutorial we will be using C#.

First, we need a couple Globals that will be referenced throughout the tutorial (and your class file). The two variables needed are an instance of a SQLServer, and Database, both objects in the SQL-DMO set.

//Create a Server instance
SQLServer Server = new SQLServer();
//Create a Database instance
SQLDMO.Database DBase = new SQLDMO.Database();

Now, lets take a look at connecting to a SQLServer with code. We will create a method that returns a boolean value. We will pass this method the

Server Name
User Name
Password

The method will then use the Connect Method to connect to our server, if there are any exceptions we have a try...catch to account for those. We will also enable bulk copy with the objects on the server:

/// <summary>
/// Function to connect to a specific database server
/// </summary>
/// <param name="server_name">Server name to connect to</param>
/// <param name="u_name">User's username</param>
/// <param name="u_pwd">User's password</param>
/// <returns>True/False</returns>
/// <remarks></remarks>
public bool ConnectToServer(string server_name, string u_name, string u_pwd)
{
	System.Windows.Forms.Application.DoEvents();
	try
	{
		//enable the use of Bulk Copy
		Server.EnableBcp = true;
		//enable a secure login
		Server.LoginSecure = true;
		//Connect to the server with the specified
		//server nam, username and password
		Server.Connect(server_name, u_name, u_pwd);
		return true;
	}
	catch (Exception ex)
	{
		//Display any errors that occur
		MessageBox.Show(ex.Message);
		return false;
	}
}

In this method we call DoEvents of the System.Windows.Forms.Application class, this is used to process all Windows messages that are currently in the queue, this prevents your application from having the appearance of "freezing up" while its connecting.

Disconnecting is even easier, you simply call the Disconnect Method of the Server instance we created
with our Globals

/// <summary>
/// Function to disconnect from a server
/// </summary>
/// <returns>True/False</returns>
/// <remarks>True/False</remarks>
public bool DisconnectFromServer()
{
	try
	{
		//Call Disconnect to disconnect
		//from the server we're connected to
		Server.DisConnect();
		return true;
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
		return false;
	}
}

This method also returns a Boolean value, letting us know whether it was successful or not. So now, unless we immediately disconnect, we're connected to our SQL Server, lets list objects in the server, here we will list:

All Databases on the server
All Tables on the specified database
All Stored Procedures on a specified database

You could also list all User Defined Data Types, User Defined Functions, Triggers, etc. with the SQL-DMO. First we will list all the databases currently available on the server we're connected to. We will populate an ArrayList with the names of the database:

/// <summary>
/// Function to list all the databases for the server connected to
/// </summary>
/// <returns>An ArrayList with the database names</returns>
/// <remarks></remarks>
public ArrayList ListAllDB()
{
	//Create a new ArrayList to
	//hold the database names
	ArrayList DBList = new ArrayList();
	try
	{
		//Loop through each database on the server
		foreach (SQLDMO.Database db in Server.Databases)
		{
			//Check to see if the current database is in the list
			if (!DBList.Contains(db.Name))
			{
				//Its not so add it
				DBList.Add(db.Name);
			}
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
	//Return the populated ArrayList
	return DBList;
}

NOTE:I think it's always a good idea to use try...catch blocks to catch any unplanned exceptions, the debate on using them still rages today.

Now that you know all the Databases on your server, what if you wanted to see all the objects on a certain database. In our application you could have either a ListBox or a CheckedListBox, and couple select the database name, then call the following methods to retrieve all objects on said database.

First, retrieve all the tables on the selected database

NOTE: Notice we have if (!(tbl.Name.Substring(0, 3) == "sys")), thats because we don't want to list the system tables. If you wanted to list them as well take that check out.

We then get an instance of the database provided, from there we simply loop through all the tables (non system tables) and add them to an ArrayList, like so:

/// <summary>
/// Method to return all the tables on the specified database
/// </summary>
/// <param name="db_name">Database to search</param>
/// <returns></returns>
public ArrayList ListAllTables(string db_name)
{
	//create a new ArrayList
	ArrayList TableList = new ArrayList();
	try
	{
		//Retrieve the Database specified
		DBase = (SQLDMO.Database)Server.Databases.Item(db_name, null);
		//Loop through all the tables on the database
		//NOTE: we arent showing system tables
		foreach (Table tbl in DBase.Tables)
		{
			//Make sure the table doesnt
			//begin with "sys"
			if (!(tbl.Name.Substring(0, 3) == "sys"))
			{
				//Check to see if the table already
				//exists in our ArrayList
				if (!TableList.Contains(tbl.Name))
				{
					//It doesnt so add it
					TableList.Add(tbl.Name);
				}
			}
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
	//return the ArrayList
	return TableList;
}

Now you have a list of all the tables on that database, but we're not done yet. Now we're going to go ahead and list all the stored procedures on this database. This one is slightly more simple than the tables one as we don't need to check for system procedures. We create an ArrayList, then loop through all the Stored Procedures on the database adding them to our ArrayList.

NOTE: You don't have to stop here, you can list Triggers, Views, User Defined Functions, etc.

/// <summary>
/// Function to return all the stored procedures for a database
/// </summary>
/// <param name="db_name">Database to query</param>
/// <returns>An ArrayList with all the stored procedures</returns>
/// <remarks></remarks>
public ArrayList ListStoredProcedures(string db_name)
{
	ArrayList SPList = new ArrayList();
	try
	{
		foreach (StoredProcedure sp in Server.Databases.Item(db_name, null).StoredProcedures)
		{
			if (!SPList.Contains(sp.Name))
			{
				SPList.Add(sp.Name);
			}
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}		   
	return SPList;
}

Ok, so far we have covered:

Connect to a server
Disconnect from a server
List all databases on a server
List all tables on a specified database
List all stored procedures on a specified database

Lets take a look at creating objects on your server. In this method I will show you how to create a new database on the server you're connected to. When creating a database, even with a GUI like Enterprise Manager, you have to specify how the database is going to grow, whether it be by percent or MB size. For that part of creating a database in code I created an Enumeration to hold those values for me, here is that Enumeration:

public enum SQLDMOGrowthType
{
	 SQLDMOGrowth_MB = 0,
	 SQLDMOGrowth_Percent = 1,
	 SQLDMOGrowth_Invalid = 99
}

Now lets take a look at the method for creating a new database on your server:

/// <summary>
/// Function to create a database on the server you're connected to
/// </summary>
/// <param name="db_name">Database name to create</param>
/// <param name="fGrowth">DataFile Growth value</param>
/// <param name="lGrowth">Log File Growth value</param>
/// <returns>True/False</returns>
/// <remarks></remarks>
public bool CreateDB(string db_name, int fGrowth, int lGrowth, ManagerEnums.SQLDMOGrowthType growthType)
{
	SQLDMO.DBFile dataFile = new SQLDMO.DBFile();
	SQLDMO.LogFile logFile = new SQLDMO.LogFile();
	try
	{
		//Set the name of the new database
		DBase.Name = db_name;
		//Set the properties of the DataFile
		//which is the .MDF File
		dataFile.Name = db_name;
		dataFile.PhysicalName = Server.Registry.SQLDataRoot + "\\DATA\\" + db_name + "_Data.mdf";
		dataFile.PrimaryFile = true;
		dataFile.Size = 2;
		dataFile.FileGrowthType = (SQLDMO_GROWTH_TYPE)growthType;
		dataFile.FileGrowth = fGrowth;
		//Add the Data File object
		DBase.FileGroups.Item("PRIMARY").DBFiles.Add(dataFile);
		//Set the properties of the Log File
		//which is the .LDF file
		logFile.Name = db_name + "Log";
		logFile.PhysicalName = Server.Registry.SQLDataRoot + "\\DATA\\" + db_name + "_Log.ldf";
		logFile.FileGrowthType = (SQLDMO_GROWTH_TYPE)growthType;
		logFile.Size = lGrowth;
		//Add the log file
		DBase.TransactionLog.LogFiles.Add(logFile);
		//Add the database
		Server.Databases.Add(DBase);
		//Disconnect from the server
		Server.DisConnect();
		return true;
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
		return false;
	}
}

Now I know that method looks daunting, but really it isn't. Heres what it does:

Creates a Log File (.LDF)
Creates a Data File (.MDF)
Sets the name of the new database (db_name)
Sets the properties of the Data File
- Name
- Physical Name
- Primary File
- Size
- Growth Type (The Enumeration we looked at earlier)
- File Growth
Sets the properties of the Log File
- Name
- Physical Name
- Primary File
- Size
- Growth Type (The Enumeration we looked at earlier)
- File Growth
Adds the Log and Data File

So, now that its broke down, its pretty simple. Now lets take a look at creating a new stored procedure.For this I suggest having a multi-line TextBox on your UI which you can type the procedure into. Then in your buttons Click Event call this method

/// <summary>
/// Method to create a new stored procedure
/// </summary>
/// <param name="db_name">Name of the database to create it on</param>
/// <param name="proc_text">Text of the procedure</param>
/// <returns></returns>
public bool CreateStoredProcedure(string db_name, string proc_text)
{
	//Create a new StoredProcedure Object
	StoredProcedure sProcedure = new StoredProcedure();
	//Set its Text property to the
	//text of the procedure we're creating
	sProcedure.Text = proc_text;
	try
	{
		//Add the stored procedure to the database
		Server.Databases.Item(db_name, "dbo").StoredProcedures.Add(sProcedure);
		return true;
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
		return false;
	}
}

So here you pass the text from your TextBox (the body of your procedure) and the database name you want the procedure created on, you then create a new StoredProcedure object, then set its Text Property to the text we pass to the method. Then we add the stored procedure to the specified database.

We will look one last process you can do with the SQL-DMO Objects, and that is dropping a database. Here you pass the database name you want to drop to the ethod, you create an instance of that database, then call the Remove Method to drop the database, like this:

/// <summary>
/// Function to drop a database
/// </summary>
/// <param name="db_name">Database name to drop</param>
/// <returns>True/False</returns>
/// <remarks></remarks>
public bool DropDB(string db_name)
{
	try
	{
		DBase = (SQLDMO.Database)Server.Databases.Item(db_name,null);
		DBase.Remove();
		return true;
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
		return false;
	}
}

Remember, there is much more that you can do with the SQL-DMO Object, in this tutorial we have scratched the surface, or somewhat deeper, of what can be done in code to manage your SQL Server. With this tutorial I am attaching the class file that was created for this tutorial, since its under the GNU General Public License you can use it, modify it and distribute it, but you must keep the header in tact and keep the reference to the original creator in tact.

I hope you found this tutorial informative, and thank you for reading