none
What is the correct connection while adding a mdf file to the project? RRS feed

  • Question

  • I use Visual Studio 2013 Pro. In the solution explorer, right-click the project node, click Add | New Item | Data | Service-based Database, and name the file Maintenance.mdf. Click Add. So in the app.config:

     <connectionStrings>
        <add name="db" connectionString="Data Source=(localdb)\v11.0;AttachDbFilename=|DataDirectory|Maintenance.MDF; Integrated Security=True; User Instance=True"/>
      </connectionStrings>
    Then I have a bunch of code to add tables to the database.
    private void CreateTablesIfNotExisting()
            {
                try
                {
                    using(var cn=new SqlConnection(cnString))
                    using(var cmd=cn.CreateCommand())
                    {
                        cn.Open();
                        cmd.CommandText =
                            "IF NOT EXISTS ( "
                            + " SELECT * FROM sys.Tables WHERE NAME='Vehicles')"
                            + " CREATE TABLE Vehicles( "
                            + " VIN varchar(20) PRIMARY KEY, "
                            + " Make varchar(20), "
                            + " Model varchar(20), Year int)";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText =
                           "IF NOT EXISTS ( "
                           + " SELECT * FROM sys.Tables WHERE NAME='Repairs')"
                           + " CREATE TABLE Repairs( "
                           +" ID int IDENTITY PRIMARY KEY, "
                           + " VIN varchar(20), "
                           + " Description varchar(60), "
                           + " Cost money)";
                        cmd.ExecuteNonQuery();
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
    However when I check the file Maintenance.mdf, there are no tables found. Why?

    Thursday, March 19, 2015 5:51 PM

Answers

  • Hello ardmore,

    >>However when I check the file Maintenance.mdf, there are no tables found. Why?

    It is by default that a local database file would be copied to the directory. If you do not want it generates a copied file, change the Copy to Output Directory(it is a property of the mdf file) to Do not copy.

    >>Its physical location is C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\, why I can't find the mdf file there?

    The “=(localdb)\v11.0” just shows this database would be attached to the (localdb)\v11.0 sql server instance, the original .mdf file is still in the project's root folder, please check if you could find it.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ardmore Friday, March 20, 2015 12:28 PM
    Friday, March 20, 2015 1:56 AM
    Moderator

All replies

  • Because you are looking at the wrong file, look at a copy of the file on your bin/debug folder.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Thursday, March 19, 2015 6:31 PM
  • It looks good. But I still don't understand the datasource. In the app.config, it points to
    Data Source=(localdb)\v11.0
    Its physical location is C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\, why I can't find the mdf file there?

    • Edited by ardmore Thursday, March 19, 2015 7:16 PM
    Thursday, March 19, 2015 7:16 PM
  • Hello ardmore,

    >>However when I check the file Maintenance.mdf, there are no tables found. Why?

    It is by default that a local database file would be copied to the directory. If you do not want it generates a copied file, change the Copy to Output Directory(it is a property of the mdf file) to Do not copy.

    >>Its physical location is C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\, why I can't find the mdf file there?

    The “=(localdb)\v11.0” just shows this database would be attached to the (localdb)\v11.0 sql server instance, the original .mdf file is still in the project's root folder, please check if you could find it.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by ardmore Friday, March 20, 2015 12:28 PM
    Friday, March 20, 2015 1:56 AM
    Moderator