为什么不创建我的表? [英] Why is my table not being created?

问题描述

我已在Winforms应用程序中获得此代码，以在现有数据库中创建表，(通过遵循

它运行时没有任何抱怨，但是没有创建表.在Visual Studio Community 2015的服务器资源管理器中刷新数据连接"及其表"文件夹均未显示任何表.

我在上面的代码中缺少什么吗?

注意:出于格式化目的，我在以上几行中断开了connStr；在实际的代码中，connStr都在一行上.

我也无法通过LINQPad，使用"SQL的默认LINQ"和SQL Server Provider连接到.mdf文件，并导航到我项目中的.mdf文件(C:\ AYttFMApp \ AYttFMScheduler \ AYttFMScheduler \ AYttFM.mdf).它告诉我存在网络错误:

无论使用默认服务器.\ SQLEXPRESS"还是将其设置为机器名称(这是Visual Studio服务器资源管理器中服务器"下方的唯一条目)，我都知道.

更新

我重新启动了笔记本电脑，但没有帮助.即使我刷新并尝试添加一个以我的计算机名作为服务器名的服务器资源管理器，其数据连接也没有显示任何内容(毕竟，这就是服务器资源管理器中所说的服务器)，然后选择.mdf文件，与LINQPad一样，在测试连接时出现相同的错误.

更新2

越来越好奇:现在，当我运行我的应用程序时，当它涉及到创建表代码时，我得到一个异常味精，它表示已经创建了AssignmentHistory表.但是，如果我看一下Server Explorer，尽管数据库本身又回来了，但是它的Tables文件夹仍然是空的.桌子怎么可能既在那里又不在那里?

我仍然想知道.mdf文件上设置的属性是否错误；正如我在下面的评论中所写，这些属性均为默认值:复制到输出目录"设置为始终复制"，构建动作"设置为内容"是否应该更改其中任何一个?

解决方案

如果代码没有在行command.ExecuteNonQuery()上引发任何异常，则查询确实完成了，并且该表应该在该位置.可能是您在使用LocalDb时只是查看了错误的数据库.如果项目已将.MDF作为文件包括在内，并且标记为始终复制到目标目录，则说明VS始终在查看未更改的副本，并且Execute语句始终在调试过程中完成，因为未更改的副本是始终替换运行时使用的副本.

DataDirectory指定通常在应用程序启动时分配的位置的占位符.您可以像这样获得使用的实际.mdf文件的完整路径:

var fullFilePath = System.IO.Path.Combine(AppDomain.CurrentDomain.GetData("DataDirectory").ToString(), "AYttFM.mdf");

我已将.mdf LocalDb的名称附加到路径的末尾.

您可以添加以下代码行并获取文件的路径，然后使用Visual Studio表设计器打开此实例.

或者，您可以更改连接字符串，并将其硬编码为.mdf文件的特定实例，以确保在项目上进行构建时不会更改.

---

修改 基于您的最新修改

我得到的是对象引用未设置为对象的实例" 在运行您提供的行之后.

很抱歉，我假设您是手动设置DataDirectory位置.如果您没有手动设置变量，则对于Windows应用程序，默认位置是.exe路径.因此，代码应更新为以下内容:

var fullFilePath = System.IO.Path.Combine(System.Reflection.Assembly.GetExecutingAssembly().Location.ToString(), "AYttFM.mdf");

这将可能解决为.\yourProjectFolder\debug\bin\AYttFM.mdf之类的问题.

所有属性均为默认值:设置了复制到输出目录" 始终复制"并将构建操作"设置为内容"

所以肯定了我之前写的内容.每次进行构建时，它都会将.mdf复制到您的可执行目录中，并基本上将数据库刷新到其原始状态.请注意，您可以在Visual Studio中进行多次运行，这意味着如果所有内容都已编译并且没有任何更改，则新的.exe和内容将不会在现有的.exe中重新复制.这就是为什么有时会看到异常，而有时却没有异常的原因，这仅取决于.mdf是否被覆盖.

这两个都应该更改吗?

这应该可以，但是要取决于每次是否都可以以干净的方式开始.这完全取决于您，这是您可以使用的选项:

来自 MSDN-文件属性

复制到输出目录属性

此属性指定所选源的条件 文件将被复制到输出目录.如果出现以下情况，请选择不复制 文件永远不会复制到输出目录.选择始终复制 如果文件总是要复制到输出目录.选择 如果仅在文件更新时才复制文件，则如果更新则复制 输出目录中已有一个同名的现有文件.

如果要打开.mdf文件，只需在VS项目中双击它，它应在Server Explorer-> Data Connections中打开.没有理由在设计器中打开输出文件( .mdf 的副本)，我只会在您的项目中编辑该文件，就像您当前的设置一样，这是将覆盖的主文件每次输出文件.

我没有使用LINQPad的经验，所以我不能说这个应用程序是否可以附加到LocalDb实例.这是有关如何通过上一个SO问题的方法来实现此目的的帖子.. >

I've got this code in my Winforms app to create a table in an existing database, (which I created by following what is written here):

private void CreateTables()
{
    string connStr = @"Data Source=
      (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|
      \AYttFM.mdf;Integrated Security=True";
    using (var connection = new 
System.Data.SqlClient.SqlConnection(connStr))
    {
        try
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("CREATE TABLE [dbo].[AssignmentHistory] ");
                sb.Append("(");
                sb.Append("[Id] INT NOT NULL PRIMARY KEY, ");
                sb.Append("[WeekOfAssignment] DATE NOT NULL,");
                sb.Append("[TalkType] INT NOT NULL,");
                sb.Append("[StudentID_FK] INT NOT NULL, ");
                sb.Append("[AssistantID_FK] INT NOT NULL, ");
                sb.Append("[CounselPoint] INT NOT NULL");
                sb.Append(")");

                command.CommandText = sb.ToString();
                command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

It runs without complaint, but no table is created. Refreshing both the Data Connection and its Tables folder in Server Explorer in Visual Studio Community 2015 show no tables.

Am I missing something in the code above?

Note: I broke the connStr over several lines above for formatting purposes; in the actual code, connStr is all on one line.

I'm also not able to connect to the .mdf file via LINQPad, using the "Default LINQ to SQL" and SQL Server Provider and navigating to the .mdf file in my project (C:\AYttFMApp\AYttFMScheduler\AYttFMScheduler\AYttFM.mdf). It tells me there is a network error:

I get that whether I use the default Server ".\SQLEXPRESS" or whether I set it to my machine name (which is the only entry beneath "Servers" in Visual Studio's Server Explorer).

UPDATE

I rebooted my laptop, but it didn't help. Server Explorer's Data Connections show nothing, even after I refresh, and trying to add one, with my machine name as the Server Name (after all, that is what it says the Server is there in Server Explorer) and selecting the .mdf file gives me the same error when testing the connection as LINQPad does.

UPDATE 2

Curiouser and curiouser: Now when I run my app, when it gets to the Create Table code, I get an exception msg that says the AssignmentHistory table has already been created. Yet if I look at Server Explorer, although the database itself is back again, its Tables folder is still empty. How can the table both be there and not be there?

I'm still wondering if the properties set on the .mdf file are wrong; as I wrote in a comment below, the properties are all the defaults: "Copy to Output Directory" is set to "Copy Always" and "Build Action" is set to "Content" Should either of these be changed?

解决方案

If the code does not throw any exceptions on line command.ExecuteNonQuery() then the query did finish and the table should be there. It could be that you are just looking at the wrong database as you are using the LocalDb. If the project has included the .MDF as a file and it is marked to always be copied to the destination directory then what is happening is that VS is always looking at the unaltered copy and the Execute statement always completes during debugging because the unaltered copy is always replacing the copy that is used at run time.

The DataDirectory specifies a placeholder for a location that is usually assigned in the application startup. You can get the full path to the actual .mdf file being used like so:

var fullFilePath = System.IO.Path.Combine(AppDomain.CurrentDomain.GetData("DataDirectory").ToString(), "AYttFM.mdf");

I have appended the name of your .mdf LocalDb to the end of the path.

You can add this line of code and get the path of the file and then open this instance up with the Visual Studio table designer.

Alternatively you can change the connection string and hard code it to a specific instance of your .mdf file that is guaranteed not to change when you do a build on the project.

---

Edit based on your latest edits

"Object reference not set to an instance of an object" is what I get after running the line you provided.

I made the assumption you were setting the DataDirectory location manually, my apologies. If you do not set the variable manually then for a windows application the default location is the .exe path. So the code should be updated to the following:

var fullFilePath = System.IO.Path.Combine(System.Reflection.Assembly.GetExecutingAssembly().Location.ToString(), "AYttFM.mdf");

This will probably resolve to something like .\yourProjectFolder\debug\bin\AYttFM.mdf.

The properties are all the defaults: "Copy to Output Directory" is set to "Copy Always" and "Build Action" is set to "Content"

So that affirms what I wrote earlier. Every time you do a build it will copy the .mdf to your executable directory and basically refreshes the database to its original state. Note that you can do multiple runs from visual studio which means that if everything has already been compiled and nothing has changed a new .exe and content will not be re-copied over the existing one. This is why you are sometimes seeing an exception and other times you are not, it just depends on if the .mdf was overwritten or not.

Should either of these be changed?

This should be OK but it depends on if it's ok to start with a clean slate every time. That is really up to you, here are the available options you have:

From MSDN - File Properties

Copy to Output Directory Property

This property specifies the conditions under which the selected source file will be copied to the output directory. Select Do not copy if the file is never to be copied to the output directory. Select Copy always if the file is always to be copied to the output directory. Select Copy if newer if the file is to be copied only when it is newer than an existing file of the same name in the output directory.

If you want to open the .mdf file all you have to do is double click on it in the VS project, it should open up in Server Explorer -> Data Connections. There is no reason to open the output file (the copy of the .mdf) in your designer, I would only edit the file in your project as with your current setup this is the master file that will overwrite the output file every time.

I have no experience with LINQPad so I can't say if this application can attach to a LocalDb instance or not. Here is a post about how to possibly do this from a previous SO question.

这篇关于为什么不创建我的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！