B. Clay Shannon February 2016

Why is my table not being created?

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

Answers


Igor February 2016

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


Verbon February 2016

Open SQL Server configuration manager and check if your server is running. If it's not - then run it:enter image description here

Post Status

Asked in February 2016
Viewed 2,625 times
Voted 7
Answered 2 times

Search




Leave an answer