.NET SQL新表不存在 [英] .NET SQL new table doesn't exist
问题描述
注意：此问题已得到解决，但导致新问题 <。 / P>
我得到这个错误：
System.Data.SqlClient.SqlException无效的对象名称[表名]
我工作的一个数据库更新附带的更新部署的产品。在第一时间新近更新的应用程序运行，该数据库更新运行，而且它具有两个主要阶段。
- 运行脚本update.sql新表添加到数据库
- 在调用一些方法，一些数据从现有表这样就把新表复制，并做一些数学/做一些调整。
第一步完美的作品。第二步抛出时它引用的新表的上述异常。在code叫那里的异常弹出在整个应用程序（的新版本）多次使用，一般罚款。人们所期望的，这个问题会发生，如果此code跑与旧版本的数据库（因为表，它使用不存在），但在这种情况下，表刚刚添加
更新code：
内部空隙更新（）
{
了RunScript（）; //运行SQL脚本，增加了表格的分贝
OtherClass OC =新OtherClass（）;
OC prepData（）。 //没有错误，只是做了一些小的调整，现有的表
oc.CopyData（）; //抛出异常，没有任何新的表似乎存在
oc.AdjustData（）; //操纵在新表中的数据，可能会抛出异常，但当前无法访问
}
公共类OtherClass
{
私人AppEntities分贝;
公共OtherClass（）
{
DB =新AppEntities（）;
//其他构造活动
}
内部空隙了CopyData（）
{
的foreach（DataItem的迪db.DataItems）//这引发异常（与任何新表）
{
}
}
}
如上图所示，异常是由初始化实体集合中抛出的在的表被添加到数据库中，但它仍然不承认任何人存在。
有没有人遇到过这样的事？有没有变通的办法？
更新：
我已经发现了一些东西，我认为应该是这个问题。 AppEntities分贝的 OtherClass 的声明已改为私人AppEntities DB =新AppEntities（ ）; ，并不再在构造函数初始化，导致它被脚本运行之前创建。不幸的是，修复这个还是得到了同样的问题。
更新：
为了保证上下文知道新表中的数据，我已经改变了我运行该脚本的方式。
previously（对数据库正确执行脚本，应用程序无法找到新的表）：
StreamReader的SR =新的StreamReader（File.open方法（的String.Format（{0} /Scripts/CURRENTVERSION.sql，AppDomain.CurrentDomain.BaseDirectory），FileMode.Open）） ;
字符串UpdateScript = sr.ReadToEnd（）;
sr.Close（）;
//这里的ConnectionString是数据库的连接取自.edmx文件和修剪的引起异常的参数无效
SqlConnection的连接=新的SqlConnection（ConnectionString的）;
SqlCommand的命令=新的SqlCommand（UpdateScript）;
connection.Open（）;
command.ExecuteNonQuery（）;
的Connection.close（）;
目前：
StreamReader的SR =新的StreamReader（File.open方法（的String.Format（{0} /Scripts/CURRENTVERSION.sql，AppDomain.CurrentDomain.BaseDirectory），FileMode.Open）） ;
字符串UpdateScript = sr.ReadToEnd（）;
sr.Close（）;
System.Data.Common.DbCommand命令= db.Connection.CreateCommand（）;
command.CommandText = UpdatScript;
//command.CommandText = @UpdateScript;
db.Connection.Open（）;
command.ExecuteNonQuery（）;
我（不带@ /）试用过的注释和未注释行，但它声称在每一行脚本（这是相同的脚本第一种方法完美地执行）语法错误。 db.Connection.Close（）;
更新：
使用<一个href="http://stackoverflow.com/questions/1576067/entity-framework-objectcontext-raw-sql-calls-to-native-dbms/1579205#1579205">this问答，我能够成功执行使用AppEntities连接的SQL脚本，以及表也出现在数据库中。然而，AppEntities（类的对象初始化的在的脚本已运行）仍然抛出异常无效的对象名称。
有没有办法迫使数据上下文从数据库在运行时自动更新？
更新（与解决方案）：
挖掘AppEntities（根据克里斯活泼的建议），是一个巨大的头痛，但它确实使我挖掘到一些配置文件，以及。在那里我发现，新的表被映射到一个单一的名称，而不是复数（ TableForEntity ，而不是 TableForEntities ），其中previously他们已经复数（当它的工作）。旧表都多为好。更换新表在这里多造成的种种错误，所以我最终只是改变了SQL脚本，以他们命名的单数。出人意料的是，这个工作。
考虑其复数形式命名他们根据实际工作数据库（在那里他们被命名为多个的）是自动生成的SQL脚本，为什么要改变命名的工作？如果有的话，应该的事业的问题是固定的。
我不知道是什么AppEntities是，但我pretty的信心，这并不能反映你的新数据库的结构。
我要采取猜测，这是某种形式的产生code，它纯粹是基于数据库的初始版本并没有被重新codegen'd（SP？）举行关于新表的信息。因此，误差
这也解释了为什么该应用程序仍然不能在当你已经创建的表，甚至运行在此之前，同样的位置。
综上所述，调查AppEntities并找出究竟是如何说的事情工作。
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.
- Run script update.sql to add new tables to the database
- 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.ReadToEnd();
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.
这篇关于.NET SQL新表不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
