.NET Answers

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

Other Related Items:

Nylon Shin and Instep Protector Medium

Leather Shin and Instep Protector Small

Don't Mess With Me... Programmer on Long Sleeve Women's Cotton T-Shirt (in 9 colors)100% preshrunk heavyweight cotton; double-needle stitching throughout; seamless rib at neck; shoulder-to-shoulder tape; heather grey is 90% cotton, 10% polyester; fashion cut; 5/8" rib collar; fitted tapered sleeve.

Most Commented Post

• Object Oriented Programming has Failed Us
• Microsoft’s Biggest Mistake with ASP.NET
• WordPress w/ Forms Authentication on IIS6
• ASP.NET AJAX using JSON – Here’s how.
• Creating DNN Modules – The Tools

Bookmark to:

Hide Sites

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