MSSQL CREATE and DROP Database – SQL for Programmers

G05B0047 I thought it would be appropriate as we embark on the SQL for Programmers series to start at the beginning.  And while most of our production databases are already created for us, the programmer is the one who is responsible (or should be responsible) for creating databases for testing purposes.  Anyhow, at some point you will need to know how to programmatically create and delete a database and you’ll be glad you saw this information.

Several months ago, this is exactly where I was.  I wanted to create a dotnetnuke database programmatically so that I could test a set of modules I had written.  I wanted to test the installation process as well as the functionality once the modules were installed into the system.

To do that, I needed to be able to drop a database if it existed and then recreate it so that I could re-install DotNetNuke and the module I was testing.

You may already be familiar with the SQLDMO object.  And if you are, you know that it already has functions available for creating and deleting a database.  But to create a database using the object model requires several lines of code.

I actually had it all working using that code only to find out that you can do the exact same thing in one line of code.  (OK, it’s really 3 total, but I’m not counting establishing the connection.)

The SQLDMO.SQLServer object has a method hanging off of it that takes any valid SQL as a string.

To create a database, you just need to establish a connection:

SQLDMO.SQLServer server = new SQLDMO.SQLServer();
server.Connect(m_serverLocation, m_userName, m_passWord);

and issue a CREATE Database command:

object o = null;
server.ExecuteImmediate("CREATE DATABASE " +
    databaseName,
    SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,
    o);

If you need a more complex CREATE Database statement, it’s just a matter of using SQL.

To drop a database, you can just issue a DROP Database command:

SQLDMO.SQLServer server = new SQLDMO.SQLServer();
server.Connect(m_serverLocation, m_userName, m_passWord);
object o = null;
server.ExecuteImmediate("DROP DATABASE " + databaseName,
    SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, o);

Most Commented Post

One Response to “MSSQL CREATE and DROP Database – SQL for Programmers”

Leave a Reply

Comment Policy:

  • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
  • Leave me pure spam and I'll delete it.
  • Leave a general comment and I'll remove the link but keep the comment.

Notify me of followup comments via e-mail

Bear