CODEWITHSUNDEEP
c#sql-serverentity-frameworkdatabase-connection
Dan Csharpster
Dan Csharpster

Reputation: 2732

Why won't connection string work for EF migration?

I have created a database for use with a NuGet Gallery implementation. I can see the database in sql manager 2012 and I can access it from a test program I wrote, using my connection string. However, when I try to run the Update-Database command in the package manager console, to have EF set up the database, I keep getting the error: "The server was not found or was not accessible.".

Here's more detail:

PM> Update-Database -Verbose

Using StartUp project 'NuGetGallery.Operations'. 
Using NuGet project 'NuGetGallery'. 
Specify the '-Verbose' flag to view the SQL statements being applied to the target database. 

System.Data.ProviderIncompatibleException: 
An error occurred while getting provider information from the database. 
This can be caused by Entity Framework using an incorrect connection string.
Check the inner exceptions for details and ensure that the connection string is correct. ---> 

System.Data.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. ---> 

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26

- Error Locating Server/Instance Specified)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Here's my connection string in the NuGet Gallery MVC site:

    <add name="Gallery.SqlServer" connectionString="Server=(localdb)\v11.0;Database=NuGetGallery11;User ID=testuser;Password=notmyrealpassword;Connect Timeout=60" providerName="System.Data.SqlClient"/>

And here's my test program. The connection strings are identical in both applications.

            var sqlConnection2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Gallery.SqlServer"].ConnectionString);
        sqlConnection2.Open();
        using (sqlConnection2) {
            var sqlCmd = new SqlCommand("select * from Foo", sqlConnection2);

            var foo = sqlCmd.ExecuteReader();
            while (foo.Read()) {
                int id = foo.GetInt32(0);
                var name = foo.GetString(1);
                var email = foo.GetString(2);

                Console.WriteLine("ID:{0}, Name:{1}, Email:{2}", id, name, email);

            }
        }
        Console.Read();

Any help would be greatly appreciated. Thanks!

Upvotes: 3

Views: 4874

Answers (2)

Dan Csharpster
Dan Csharpster

Reputation: 2732

My problem was solved in this other post here. You have to set the default project in the manager console AND set the startup project for the solution in order for the update command to find the connection string and also what Aydin said, but in this case that part was already set up for me.

Upvotes: 4

Aydin
Aydin

Reputation: 15284

Your problem is that EntityFramework doesn't know that your connection string is named Gallery.SqlServer...

Here's how you can setup entity framework from scratch...

  1. Open VS with Administrative rights (just being cautious)
  2. Create a new Console application
  3. Open the package manager console
    • Type in: `PM> Install-Package EntityFramework

  4. Open the App.Config file

    • Add the following 

      <connectionStrings>
  <add name="Gallery.SqlServer" connectionString="Server=(localdb)\v11.0;Database=GalleryExample;" providerName="System.Data.SqlClient"/>
</connectionStrings>

  5. Create a new class called Foo 

    public class Foo
{
    public Foo()
    {
        this.Id = Guid.NewGuid()
                      .ToString();
    }

    public string Id { get; set; }
}

  6. Create a new class called EfContext

    public class EfContext : DbContext
{
    public EfContext()
        : base("Gallery.SqlServer")
    {

    }
    public DbSet<Foo> Foos { get; set; }
}

  7. Open up the package manager console again

    • PM> Enable-Migrations
    • PM> Add-Migration InitialMigration
    • PM> Update-Database

  8. Enjoy your new db....

Upvotes: 1

Related Questions