The easiest way to create a SQL db from Code

(We’ll get back to the series on Reflection next week.)

So, I’ve started experimenting with replacing my TestComplete test scripts with CSharp test scripts.  This shouldn’t be too big of a deal since most of what I’m scripting are web applications and I can control that code via the WebBrowser control.

Well, the first thing my current script does is delete a database and recreate it.  When I went looking for how to create a database, all I could find was code similar to this:

Creating a Database Using Lots of Code

SQLDMO.DBFile dbData = new SQLDMO.DBFile();
SQLDMO.LogFile dbLog = new SQLDMO.LogFile();
SQLDMO.SQLServer server = new SQLDMO.SQLServer();
try
{
    server.Connect(m_serverLocation, m_userName, m_passWord);
    SQLDMO.Database db = new SQLDMO.Database();
    db.Name = databaseName;
    dbData.Name = databaseName;
    dbData.PhysicalName = server.Registry.SQLDataRoot + 
        "\\DATA\\" + databaseName + "_Data.mdf";
    dbData.PrimaryFile = true;
    dbData.Size = 2;
    dbData.FileGrowthType = SQLDMO.SQLDMO_GROWTH_TYPE.SQLDMOGrowth_MB;
    dbData.FileGrowth = 1;

    //Add the DBFile object
    db.FileGroups.Item("PRIMARY").DBFiles.Add(dbData);

    dbLog.Name = databaseName + "Log";
    dbLog.PhysicalName = server.Registry.SQLDataRoot + 
        "\\DATA\\" + databaseName + "_Log.ldf";
    dbLog.Size = 2;
    db.TransactionLog.LogFiles.Add(dbLog);
    server.Databases.Add(db);
}
catch (Exception )
{
    return false;
}
finally
{
    server.DisConnect();
}
return true;

 

Dropping The Database

Which WILL run, but man!  What a lot of code for something so simple.  Then I went to find how to drop a database when I found this code:

SQLDMO.SQLServer server = new SQLDMO.SQLServer();
try
{
    server.Connect(m_serverLocation, m_userName, m_passWord);
    object o = null;
    server.ExecuteImmediate("DROP DATABASE " + 
        databaseName, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, o);
}
catch (Exception)
{
    return false;
}
finally
{
    server.DisConnect();
}
return true;

 

And I thought, “Hey, I bet I can do the same thing to create the database!”  And sure enough, I can.

Creating A Database With a Little Code

SQLDMO.SQLServer server = new SQLDMO.SQLServer();
try
{
    object o = null;
    server.Connect(m_serverLocation, m_userName, m_passWord);
    server.ExecuteImmediate("CREATE DATABASE " + 
        databaseName, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, o);
}
catch (Exception )
{
    return false;
}
finally
{
    server.DisConnect();
}
return true;

 

SO much easier.  I wish I could have found this before I wrote the first chunk.

Related Post

  • Storing An Image To a Database in .NETStoring An Image To a Database in .NET
    Several weeks ago I mentioned that I store the images that the user uploads to the system into the database. Some of you have expressed an interest in how I do that.  So I plan to cover th...
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers
    OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, update or d...
  • DotNetNuke – Retrieving Host AccessDotNetNuke – Retrieving Host Access
    One of the most frustrating events that can happen in any system is forgetting your password and not being able to retrieve it.  DotNetNuke has a retrieval system built in, but you have to ...
  • Silverlight – Navigating DataSilverlight – Navigating Data
    Last week I demonstrated how to access data from a web service in Silverlight and display it on the page.  Today we are going to continue on with that demo and look at how to navigate through...
  • Internationalization – The DatabaseInternationalization – The Database
    Over the last several weeks we’ve been examining the various aspects of internationalization using ASP.NET. But it doesn’t help to have your resources and images set up for internationalization i...