.NET SQL新表不存在 [英] .NET SQL new table doesn't exist

问题描述

注意：此问题已解决，但会导致新问题。

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

我收到此错误：

System.Data.SqlClient.SqlException无效的对象名称[tablename]

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. 运行脚本update.sql将新表添加到数据库
   


2. 调用一些方法从现有表中复制一些数据来弹出新表，并做一些数学/

第一步完美无瑕。每当它引用任何新表时，第二步抛出上面的异常。调用异常弹出的代码在整个（新版本）应用程序中被使用了好几次，并且通常很好。如果这个代码与旧版本的数据库一起运行（因为它使用的表不存在），那么会发生这个问题，但是在这种情况下，刚刚添加了这些表。

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.

更新代码：

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?

更新：

我发现了一些我以为必须问题。在 OtherClass 中的 AppEntities db 的声明已更改为 private AppEntities db = new AppEntities（ ）; ，它不再在构造函数中初始化，导致它在脚本运行之前被创建。不幸的是，修复这个问题仍然产生同样的问题。

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.

以前（针对数据库正确执行的脚本，应用程序找不到新表）： p>

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();

目前：

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

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

我已尝试过注释和未注释的行（带/不带@），但是它在脚本中的每一行声明了语法错误（这是第一种方法完美执行的相同脚本）。
db.Connection.Close（）;

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();

更新：

使用这个问题和答案，我能够成功执行使用AppEntities连接的SQL脚本，并且这些表确实出现在数据库中。但是，AppEntities（脚本已经运行的之后初始化类的对象）仍然抛出无效对象名称的异常。

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?

更新（带解决方案）：

挖掘AppEntities（根据Chris Lively的建议）是一个巨大的头痛，但它也导致我挖掘一些配置文件。在那里我发现新的表被映射到一个单一的名字，而不是复数（ TableForEntity 而不是 TableForEntities ），以前它们是复数（当它工作时）。老桌子也是复数。将新表更改为复数形式会导致各种错误，所以我最终只是更改SQL脚本，以单数形式命名。令人惊讶的是，这是有效的。

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.

考虑到以复数形式命名的SQL脚本是基于实际工作的数据库（多重命名的数据库）自动生成的，为什么要更改的命名工作？如果有什么事情，那么应该导致修正的问题。

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.

推荐答案

我不知道AppEntities是，但我很有信心，它不反映您的新数据库的结构。

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

我将猜测它是某种形式的生成代码这完全基于数据库的初始版本，并且还没有被recodegen'd（sp？）保存有关新表的信息。因此，错误。

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.

总而言之，调查AppEntities，并确定这些内容的有效性。

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

这篇关于.NET SQL新表不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！