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

Comments are closed.