Appharbor, scripting sql server table creation using ado.net

So ive got a simple appharbor installation running, but need a database.
I was half expecting a nice API I could use similar to herokus that would let me run my scripts against the hosted DB and thus deploy my DB at the command line, but not.

What you need to do is connect to your DB through SQL server management studio (if using sql server) and do your DB work through that. All good and easy, but at work our corporate firewalls prevent us from getting out, or from external sources getting in, makes sense, and i as a lowly developer will never be able to change that, not that i would want to, it would be quite dangerous.

So what to do?
well i decided to write a page in my MVC app that (given the right credentials) will run a script that has been checked in, Here are the basics:

• Create a DB on appharbor (use the web interface to create it, really simple)
• Copy the connection settings and paste them into your web.config, here are mine (details changed to protect the innocent)


<connectionstrings>
    <add connectionstring="Server=db002.appharbor.net;Database=db1234;User ID=db1234;Password=lotsofrandomcharacters" name="mydb"></add>
</connectionstrings>



• now we need a controller with an action

public ActionResult RunTheScript(string filename)
{
    try
    {
        string connStr = ConfigurationManager.ConnectionStrings["mydb"].ToString();
        using (var conn = new SqlConnection(connStr))
        {
            conn.Open();
            string filePath = Server.MapPath("\\databasescripts\\" + filename);
            FileInfo fileInfo = new FileInfo(filePath);

            string script = fileInfo.OpenText().ReadToEnd();

            SqlCommand cmd = new SqlCommand(script, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        return View();
    }
    catch (Exception e)
    {
        return View("ExceptionView", e);
    }
}

• Dont forget your routes in global.asax, Add the following before the default route.


routes.MapRoute(
    "admin_runthescript",
    "admin/runthescript/{filename}",
    new { controller = "admin", action = "runthescript" } // Parameter defaults
);


And add a couple of views (your know how to do that), one for success and one for an exception.
Yes there are better ways of doing this but its a quick and dirty demo, i expect people to use properly designed code when doing this live (Please dont mix your DB logic into your controllers people...)

A note on the exception handling, you had better do this properly too as appharbor will not give you any logging out of the box, and will not spit back helpful errors to the user, not errors that can be used to debug anyway..

• Almost there, add a script, and save it in your databasescripts folder


CREATE TABLE [dbo].[locations](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](256) NOT NULL
) ON [PRIMARY]

INSERT INTO [dbo].[locations] ([name]) VALUES ('London')
INSERT INTO [dbo].[locations] ([name]) VALUES ('Manchester')
INSERT INTO [dbo].[locations] ([name]) VALUES ('Bristol')
INSERT INTO [dbo].[locations] ([name]) VALUES ('Bath')


• Now add all the files to your git repo
• commit
• git push appharbor master
• Run the migration through your web app using the url Controller/Action/Filename (scripts/runthescript/add_location_table.sql)

Thats pretty much it, its upto you to secure the page that can be used to create and destroy your db,dont blame me if someone gets in an messes with unsecured pages,

Maybe there is something in the pipeline at app harbor that will help us out when scripting databases, but one thing is for sure using the management studio for building your applications database is not the way forward in the long run.