Create a SQL Database Programmatically

In this article I will explain how to use MS SQL Management Objects (SMO) to create a MS SQL database from C# code. In this sample will

Enumerate all the SQL servers in the network and fill a list box where the user can select one of the servers.
The user will enter the Database Name to create.
The user will enter an AD account to grant right to.
When the user click "Create Database" the system will check for the existence of the database.

Background

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

Enumerating SQL Servers

The SmoApplication.EnumAvailableSqlServers() enumerates a list of available instances of Microsoft SQL Server. it returns a Datable object value that contains an enumerated list of information about the available instances of SQL Server. The table describe the different columns of the returned DataTable.

Column	Data type	Description
Name	String	The name of the instance of SQL Server.
Server	String	The name of the server on which the instance of SQL Server is installed.
Instance	String	The instance of SQL Server.
IsClustered	Boolean	A Boolean value that is true if the instance is participating in failover clustering, or false if it is not.
Version	String	The version of the instance of SQL Server.
IsLocal	Boolean	A Boolean value that is true if the instance is local, or false if the instance is remote.

The computer running the instance SQL Server might not receive responses to the EnumAvailableSqlServers method in a timely manner. The returned list might not show all the available instances of SQL Server on the network. When you call the EnumAvailableSqlServers method in subsequent tries, more servers might become visible on the network.

MessageBox.Show("Going to look for all SQL Server in the network this might take some time. Do you want to proceed", "SQL Servers", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)DataTable dt = SmoApplication.EnumAvailableSqlServers();foreach (DataRow dr in dt.Rows)this.cbServers.Items.Add(dr[0]);

Connecting to SSQL Server

First we check if the user selected the local server or a SQL serevr

//Connect to the local, default instance of SQL Server.

string srvname = this.cbServers.SelectedItem as string;

Server srv;

if (srvname == null)

{

srv = new Server();

sb.AppendLine("Connected to local SQL server");

}

else

{

srv = new Server(srvname);

sb.AppendLine(string.Format("Connected to 01908433-d6b4-4413-939a-d55d1e4ee364", srvname));

}

Checking if the Database Exists

It is easy to check if the database already exists or not, just use the Databases attribute of the server as follows

//Define a Database object variable by supplying the server and the database name arguments in the constructor.

Database db = srv.Databases[this.tbDBName.Text.Trim()];

if (db != null)

{

if (MessageBox.Show(string.Format("The '01908433-d6b4-4413-939a-d55d1e4ee364' already exists do you want to drop it?", this.tbDBName.Text), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)else

{

db.Drop();

}

{

if (MessageBox.Show(string.Format("Create the Tables and Stored Procedures for BT Error Manager on '01908433-d6b4-4413-939a-d55d1e4ee364'?", this.tbDBName.Text), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

{

sb.AppendLine("Creating the Tables and Stored Procedures.");

this.tbProgress.Text = sb.ToString();

db.ExecuteNonQuery(dbstring);

sb.AppendLine(string.Format("Created the Tables and Stored Procedures for BT Error Manager on '01908433-d6b4-4413-939a-d55d1e4ee364'", this.tbDBName.Text));this.tbProgress.Text = sb.ToString();this.tbProgress.ScrollToCaret();"Proceed or select another database");this.tbProgress.Text = sb.ToString();this.tbProgress.ScrollToCaret();return;

}

sb.AppendLine(

}

Creating the Database

To create a database all you have to do is create a new Database object with the Server and the Name of the database then call the Create Method.

db = new Database(srv, this.tbDBName.Text);

this.tbProgress.Text = sb.ToString();

this.tbProgress.ScrollToCaret();//Create the database on the instance of SQL Server.

db.Create();

sb.AppendLine("Created the database.");

sb.AppendLine("Creating the Tables and Stored Procedures.");

this.tbProgress.Text = sb.ToString();this.tbProgress.ScrollToCaret();

Running the SQL Script

The last step is to run the script that will create the tables, views , stored procedures etc.

//'Reference the database and display the date when it was created.

db.ExecuteNonQuery(dbstring);

Notes

If you want to use this code in your project, you will need to add references to the SMO assemblies. You can locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll

News List

Technical Articles