.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:

Developing Microsoft ASP.NET Server Controls and Components (Pro-Developer)DEVELOPING MS ASP NET SVR CONTROLS/COMPONENTS

Beginner's ASP.NET in VB.NET 2003 on DVDASP.NET (VB.NET) 2003 represents an important technology for building enterprise level web applications. Learn the basics of ASP.NET development as yo... Read More >

MCTS Self-Paced Training Kit (Exam 70-562): Microsoft .NET Framework 3.5-ASP.NET Application Development: Microsoft(r) .Net Framework 3.5 ASP.Net Application Development (Pro - Certification)

Ace your preparation for the skills measured by MCTS Exam 70-562—and on the job. Work at your own pace through a series of lessons and reviews... Read More >

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”