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

问题:

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

回答1:

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

回答2:

How are you planning on putting the data back? I have yet to use EF code first, and I am quite comfortable in SQL server, so I would definitely settle on a solution using either SSMS or good ol T-SQL if I were in your shoes.

What is the nature of the schema changes you are making? Are you dropping columns, rearranging columns, or something more complex? If you are dropping columns, those columns can be dropped in T-SQL rather trivially. If you are rearranging columns, then you will have to push the data to a temp (or standard/permanent) table, make your changes, then script the changes back. This is fairly easy to do as well. If you expand on your needs, perhaps someone can help you come to a simple sql solution.

回答3:

public void CreateScriptTable() 
{
    ServerConnection serverConnection = new ServerConnection(".");
    Server server = new Server(serverConnection);
    Database database = server.Databases["ff"];
    Table tb = database.Tables["t"];
    Scripter scripter = new Scripter(server);
    scripter.Options.ScriptData = true;
    var sb = new StringBuilder();
    foreach (string s in scripter.EnumScript(new Urn[] { tb.Urn }))
    sb.Append(s);

    System.IO.StreamWriter fs = System.IO.File.CreateText(@"ScriptTable");
    fs.Write(sb);
    fs.Close();
}