How to: VS2017 project with Azure SQL Server

[ericbrunner]

Hi,
Could you point me to a sample that uses an Azure SQL Server or show me the difference to the SQLite example from your tutorial?
Thanks,
Eric

[damienbod]

Hi Eric according to this Azure SQL Server can be used with EF Core.
http://stackoverflow.com/questions/38485246/ef-core-and-azure

This means that you just need to change the connection string in the app settings.

https://github.com/damienbod/AspNet5Localization/blob/master/src/AspNetCoreLocalization/Startup.cs#L36

[ericbrunner]

Hi Damien,

Thanks for your quick reply. Well it was not so easy, To contribute , I summarized what was required to get the sample from your tutorial working for a MS SQL Server database:

Migration Steps from SQLite Server to MS SQL Server setup:

1. In Startup.cs comment out (or removed if unused) the SQLite related code statements and add that:

       // init database for localization
      var sqlConnectionString = Configuration["DbStringLocalizer:ConnectionString"];

       //services.AddDbContext<LocalizationModelContext>(options =>
       //     options.UseSqlite(
       //         sqlConnectionString, 
       //         b => b.MigrationsAssembly("AspNetCoreLocalization")
       // )
       //);
		
        services.AddDbContext<LocalizationModelContext>(options => 
        options.UseSqlServer(sqlConnectionString, sqlServerOptions => sqlServerOptions.MigrationsAssembly("AspNetCoreLocalization")));

Note: Remove that action delegate b => b.MigrationsAssembly("AspNetCoreLocalization") when including the Localization.SqlLocalizer as nuget! That action delegate is only required here in the source code project where the Localization.SqlLocalizer is added as a project reference to WebApp "AspNetCoreLocalization". The EF Migration Assembly is by default allways the project where the DbContext is located.

2. Attempt an initial EF migration

I like the Package Manager Console but that can be done from command line , too.
When you attempt to create an initial migration you will fail with that:

PM> Add-Migration Localization-Initial -Context LocalizationModelContext
Change your migrations assembly by using DbContextOptionsBuilder. E.g. options.UseSqlServer(connection, b => b.MigrationsAssembly("Localization.SqlLocalizer")). By default, the migrations assembly is the assembly containing the DbContext.
Change your target project to the migrations project by using the Package Manager Console's Default project drop-down list, or by executing "dotnet ef" from the directory containing the migrations project.
Your target project 'Localization.SqlLocalizer' doesn't match your migrations assembly 'AspNetCoreLocalization'. Either change your target project or change your migrations assembly.

3. Get rid of all EF SQLite package (if not used!)

4. Add that EF MS SQL Server Runtime and Designer/Tools packages:
- Microsoft.EntityFrameworkCore.Tools v1.1.2 (at time of writing)
- Microsoft.EntityFrameworkCore.SqlServer v.1.1.2 (at time of writing)
- Microsoft.EntityFrameworkCore.SqlServer.Design v.1.1.1 (at time of writing)

5. Try again to start initial Migration

PM> Add-Migration Localization-Init -Context LocalizationModelContext
To undo this action, use Remove-Migration.
PM> Update-Database -Context LocalizationModelContext
Done.

Now it succeeds.

6. Verify that 3 Tables are created in your MS SQL Server database
- LocalizationRecords
- ExportHistoryDbSet
- ExportHistoryDbSet

[damienbod]

@ericbrunner excellent, thanks, I'll add this to the docs if it's ok with you.

Greetings Damien

[ericbrunner]

@damienbod Sure. I put that sample at https://github.com/ericbrunner/AspNetCoreLocalizationWithMSSQL

The connectionstring is templated:

  "DbStringLocalizer": {
    "ConnectionString": "Server=tcp:<YOUR-DATABASE>.database.windows.net,1433;Initial Catalog=<YOUR DATABASE>;Persist Security Info=False;User ID=<YOUR USERNAME>;Password=<YOUR PASSWORD>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }

I verified that it is working with an Azure MS SQL Server database.

Greetings Eric

[damienbod]

Hi @ericbrunner want to reopen this, will close it when I have included it in the docs. Will use your github name to reference you, unless you want else.