.NET SQL new table doesn't exist

NOTE: This issue has been resolved, but lead to a new question.

---

I'm getting this error:

System.Data.SqlClient.SqlException "Invalid object name [tablename]"

I'm working on a DB update to ship with an update to a deployed product. The first time the newly updated application runs, the database update runs, and it has two primary stages.

1. Run script update.sql to add new tables to the database

2. Call some methods to copy some data from existing tables to popuplate the new tables, and do some math/make some adjustments.

Step one works flawlessly. Step two throws the exception above whenever it references any of the new tables. The code called where the exception pops up is used several times throughout the (new version of the)application, and is generally fine. One would expect that this problem would happen if this code ran with the old version of the database (since the tables it uses don't exist), but in this case, the tables have just been added.

Update code:

internal void Update()
{
    RunScript(); //runs the SQL script, adds tables to the db

    OtherClass oc = new OtherClass();
    oc.PrepData(); //no error, just makes some minor tweaks to existing table
    oc.CopyData(); //throws exception, none of the new tables appear to exist
    oc.AdjustData(); //manipulates the data in the new table, probably throws exception but currently unreachable
}

public class OtherClass
{
    private AppEntities db;

    public OtherClass()
    {
        db = new AppEntities();
        //other constructor activity
    }

    internal void CopyData()
    {
        foreach(DataItem di in db.DataItems) //This throws the exception (with any of the new tables)
        {
        }
    }
}

As shown above, the exception is thrown by an entity set that is initialized after the tables are added to the database, yet it still does not acknowledge that any of them exist.

Has anyone encountered anything like this? Is there a way around it?

UPDATE:

I've discovered something, which I thought must be the problem. The declaration of AppEntities db in OtherClass had been changed to private AppEntities db = new AppEntities();, and it was no longer initialized in the constructor, resulting in it being created before the script is run. Unfortunately, fixing this still yields the same issue.

UPDATE:

In order to assure the data context knows about the new table, I've altered the way I run the script.

Previously (properly executed script against database, application can't find new tables):

StreamReader sr = new StreamReader(File.Open(String.Format("{0}/Scripts/CURRENTVERSION.sql", AppDomain.CurrentDomain.BaseDirectory), FileMode.Open));
string UpdateScript = sr.ReadToEnd();
sr.Close();

//Here connectionstring was the database's connection taken from the .edmx file and trimmed of arguments that caused exceptions as invalid
SqlConnection connection =  new SqlConnection(connectionstring);

SqlCommand command = new SqlCommand(UpdateScript);
connection.Open();
command.ExecuteNonQuery();
connection.Close();

Currently:

StreamReader sr = new StreamReader(File.Open(String.Format("{0}/Scripts/CURRENTVERSION.sql", AppDomain.CurrentDomain.BaseDirectory), FileMode.Open));
string UpdateScript = sr.ReadT开发者_如何转开发oEnd();
sr.Close();

System.Data.Common.DbCommand command = db.Connection.CreateCommand();
command.CommandText = UpdatScript;
//command.CommandText = @UpdateScript;
db.Connection.Open();
command.ExecuteNonQuery();

I've tried both the commented and un-commented lines (with/without the @), but it claims a syntax error on every line in the script (which is the same script the first method executes flawlessly). db.Connection.Close();

UPDATE:

Using this question and answer, I was able to successfully execute the SQL script using the AppEntities connection, and the tables did appear in the database. However, AppEntities (an object of the class initialized after the script has run) still throws the exception of invalid object name.

Is there a way to force the data context to update itself from the database at runtime?

UPDATE (with solution):

Digging into AppEntities (as per Chris Lively's suggestion) was a huge headache, but it did lead me to digging into some of the config files as well. It was there I found that the new tables were being mapped to with a singular name, rather than plural (TableForEntity rather than TableForEntities), where previously they had been plural (when it worked). The old tables were all plural as well. Changing the new tables to plural here caused all sorts of errors, so I ended up just changing the SQL script to name them in the singular. Surprisingly, this worked.

Considering the SQL script which named them in the plural was autogenerated based on a database that actually worked (where they were named plurally), why should changing the naming work? If anything, that should cause the problem it fixed.

---

I'm not sure what AppEntities is but I'm pretty confident that it does not reflect the structure of your new database.

I'm going to take a guess that it is some form of generated code that is based purely on the initial version of the database and has not been recodegen'd (sp?) to hold information about the new tables. Hence, the error.

This would also explain why the application still fails at that same location when you have created the tables before it even runs.

To sum up, investigate AppEntities and figure out exactly how that thing works.