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);
object o = null; server.ExecuteImmediate("CREATE DATABASE " + databaseName, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, o);
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 post in SQL For Programmers
- SQL For Developers - 9 Reasons to bother - August 13th, 2008
- MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
- MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
- SQL For Programmers - ALTERing the TABLE - September 1st, 2008
- SQL For Programmers - Finding IN a List - September 8th, 2008
- SQL For Programmers - Stored Procedure Basics - September 16th, 2008
- Basic SQL Commands - SQL For Programmers - September 18th, 2008
- SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
- Temporary Tables - SQL For Programmers - October 7th, 2008
- SQL CURSOR - SQL For Programmers - October 13th, 2008
- SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
- Random in SQL - SQL For Programmers - November 4th, 2008
- SQL - Filtering WHERE condition on two rows - November 26th, 2008
- SQL - Transactions - April 15th, 2009
- SQL For Programmers – New Question - July 6th, 2009
- SQL SELECT CASE Instead of IIF - October 20th, 2009
- SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012
- SQL for .NET Programmers–The Book - July 29th, 2013
- SQL For Programmers - Finding a String - December 4th, 2013
- SQL WHILE - SQL For Programmers - February 12th, 2014