Home » SQL For Programmers » MSSQL CREATE and DROP Database – SQL for Programmers

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

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • DotNetNuke Modules – Creating Base ModulesDotNetNuke Modules – Creating Base Modules Now that we have DotNetNuke installed into Visual Studio we can go ahead and create our first modules. Actually, creating the modules is pretty simple. But it is even easier to do it […]
  • SQL For Programmers – ALTERing the TABLESQL For Programmers – ALTERing the TABLE One of the main problems we seem to be stuck with at this point in programming history is, how do we version our databases? You could put the whole database into version control, I […]
  • Temporary Tables – SQL For ProgrammersTemporary Tables – SQL For Programmers You may think that you don't need a temporary table.  But if you've ever retrieved data from your database or retrieved data from a table and put it in a list of some sort simply to […]
  • SQL for .NET Programmers–The BookSQL for .NET Programmers–The Book Today, I finally announce the availability of my new book.  SQL For .NET Programmers.This book is based on the series of post I wrote in 2008.  If those post have been valuable to […]
  • SQL For Developers – 9 Reasons to botherSQL For Developers – 9 Reasons to bother If you are a developer like I am, you've probably gotten by with pretty simple SQL for quite a while. In fact, my experience has been that developers don't get much past SELECT, INSERT, […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

One Pingback/Trackback