Azure Api/Web App with Entity Framework - SQL database connection string

Question

I'm adding an SQL database to my Azure API App. I have an empty SQL database which I created separately via portal.azure.com. My problem is I don't know how to set up the connection string so that my app uses the Azure database.

I have followed the Code First Migrations article, but I'm stuck on the deployment phase. I cannot see any connection configuration in any of the files in the project.

How do I set the connectionString to be used by the app when it's deployed in Azure?

---

More info:

To be precise, I can see 2 things:

1. Commented out connectionStrings sections in Web.Debug/Release.config files.

2. Some EF configuration in Web.Config:

      <entityFramework>
           <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
             <parameters>
               <parameter value="mssqllocaldb" />
             </parameters>
           </defaultConnectionFactory>
          (...)
   

When I execute tests locally I can see Database.Connection.ConnectionString is

Data Source=(localdb)\mssqllocaldb;Initial Catalog=XDataAPI.Models.MyContext;Integrated Security=True;MultipleActiveResultSets=True

BTW. The publish window states that no database have been found in the project. (This doesn't really bother me, it's a secondary issue)

---

Edit:

DbContext, as requested:

public class MyAppContext : DbContext 
    { 
        public DbSet<Organisation> Organisations { get; set; } 
    }

Answer 1

Pass in the connection name as param to your constructor, and then use the same connection name when setting up your connection string in your web.config, like this:

public class MyAppContext : DbContext 
{ 
    public MyAppContext():base("MyConnectionName"){}
    public DbSet<Organisation> Organisations { get; set; } 
}

And then, in web.config:

<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="MyConnectionName" connectionString="Server=tcp:test.database.windows.net,1433;Database=testdb;User ID=test@test;Password=p4ssw0rd!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
....
<configuration>

If you want to run from a local machine, remember that you need to allow incoming connections from your IP on your Azure database server firewall.

Answer 2

If you set up the SQL Server VM, then

<add name="DataContext" connectionString="Data Source=VMname.cloudapp.net; Initial Catalog=catalog; User ID=login;Password=password; MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />

If you set up the SQL Azure, then that tutorial should be used.

As for the connection string place, please refer to some documentation. You use LocalDB, instead of that you should use the SQL Server.

Answer 3

You should be able to just update the connection string for your data context in the web.config to use you Azure SQL Database. For my testproject it is just at the top of web.config:

<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="WebApplication4Context" connectionString="Server=tcp:test.database.windows.net,1433;Database=testdb;User ID=test@test;Password=p4ssw0rd!;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
....
<configuration>

Don't forget to also update the firewall settings of your Azure SQL Database Server to make it accessible for your application.

Edit: You can also change the database connection for just your Azure environment by adding the your Azure SQL DB in the Publish dialogue:

Answer 4

If the connection string is missing web.config, then it is using the default name which is DefaultConnection and it refers to the localdb instance that gets installed with SQL or SQL Express.

To configure it, you have to first create a SQL DB on Azure, from the Portal, create a new database and give it a name and make sure it exist in the same resource group and region to decrease the latency and improve the performance.

Open the created database and you will find the connection string for many platforms, copy the one for .Net and go to the Web App settings, you should find a place for connection strings, add a new one and name it DefaultConnection and add the value a the connection string you just copied from the database blade

When you run the application for the first time, code first will connect to the database and apply the migration if you specified that during the publish wizard which adds some configuration in web.config as well.

Answer 5

For .Net FW 4.5 or above:
1. Your DbContext class:

public class MyAppContext: DbContext
{
    public MyAppContext() : base("YourConnectionStringKey") { }

    public DbSet<Organization> Organizations{ get; set; }
}

2. Your Web.config:

<connectionStrings>
    <add name="YourConnectionStringKey" connectionString="DummyValue" providerName="System.Data.SqlClient" />
  </connectionStrings>

3. In your Azure WebApp settings, add the connection string (which will be automatically injected into your Web.config at runtime)

If you're not developing using the .Net framework, see https://azure.microsoft.com/en-us/blog/windows-azure-web-sites-how-application-strings-and-connection-strings-work/ for further details.