C# SMO - Scripting Table Data to File. Throwing Error

Question

I'm trying to create a backup of the data contained within my database before I re-create the tables. I've got so far as to connect to the server and create the file to store the SQL. The problem is its throwing an error on the last line.

"Object reference not set to an instance of an object"

I've been at this project all day so might be missing something that a fresh pair of eyes would help with.

Here's the code:

public void scriptTables()
    {
        string folder = HttpContext.Current.Server.MapPath("/Scripts/SQLScripts/");

        Server myServer = new Server(".\\SQLEXPRESS");
        Database CMSDB = myServer.Databases["CMSDB"];

        Scripter script = new Scripter(myServer);
        ScriptingOptions so = new ScriptingOptions();
        so.ScriptData = true;
        so.ScriptSchema = true;
        so.ScriptDrops = false;

        foreach (Table table in CMSDB.Tables)
        {
            string tables = table.ToString();
            string filename = folder + table + ".sql";
            FileStream fs = File.Create(filename);
            so.FileName = filename;

            CMSDB.Tables[tables].EnumScript(so);
        }
    }
}

Some background to what I'm doing:

I want to pull the data that already exists in the DB and then change the entity models. When the application restarts the EF framework will drop and re-create the database at which point I'll re-enter the data that was previously there. Not too sure if the whole process will work out but this will have to do until they release the migration feature in EF.

Hope someone can help. Thanks

Answer 1

I think your problem is probably table.ToString(). Try using table.Name instead.