There’s a bunch of info on this already out there, but this is pretty specific – we have an existing set of Microsoft SQL Server databases on an AWS instance and I needed to connect to them in a new WebApp service. Here’s how I did it.
This assumes you have an Azure account all set up and ready to go, if not, do that first.
I’m using Visual Studio 2017 here. To set up a new project, choose this option – ASP .NET Core Web Application
Next we want to choose the MVC option:
Then after it’s all setup, build and run it to check it’s fine, the Publish, and under publish we’re going to choose Azure:
Now configure it. For App Name choose something suitable, your subscription will probably be the free trial or pay as you go – something you need to check and setup in the Azure portal. Resource Group is a grouping of that your WebApp will be in, again check resource groups in the portal or just click create here. I’m not going into too much Azure details here but can if anyone’s interested – I can write something about it. Hosting Plan again, select a free/shared one for now, you can get shared resources with limited computing time. Insights select none for now.
Click publish and pretty soon it will open a new browser window showing your running web app!
Let’s now go into manage NuGet packages for this solution, here’s what we need to see:
To install missing ones, click on browse and then type the names of each and add them in to the project:
Microsoft.AspNetCore.App
Microsoft.AspNetCore.Razor.Design
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
Microsoft.NETCore.App
Microsoft.VisualStudio.Web.CodeGeneration.Design
Let’s jump right in and scaffold some data structures in, open the NuGet Console, the format of what we need is like this:
Scaffold-DbContext “Server=***IPorSERVERNAME***;Database=***DBNAME***;user id=***SQLUSERNAME***;password=***PASSWORD***;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables table1,table2,table3
You can just leave off the -Table argument if you want to import all the tables. Hit enter and, fingers crossed, we get a whole new bunch of Models and a DBContext.cs file in the Models folder too. Take a look in the Models folder and have a looksee.
Let’s open up startup.cs and add the database context to the App. Like this:
Where in the image above, it would be <yourDatabaseNameContext>
You’ll see this startup is a bit different to the default one, I’ve added in a couple of lines to pass the httpcontext into the controllers, and the configuration (so you can read appsetting variables too), which can be useful, but it’s not essential here.
Let’s move into the HomeController and add the database context to it so we can access it and do some queries:
You see we add a context variable at the top, and then in the HomeController() we initialise it.
Next we can query some data, add this into your index():
Where the TableName would be a table in your database. Put a breakpoint on the return line and run the app. You should see that the download2 var here contains a row of data from your table.
Possible problems and errors here are access to your remote SQL DB – permission – is your IP allowed? This is especially true if this works locally and doesn’t on the Azure domain once it is deployed. Give this a go – publish the WebApp again and view it online.
To find the IP addresses that your WebApp uses (so you can allow them on your SQL server, go into Azure, and Home -> All resource -> YourWebAppName -> Properties:
Just add these IPs to your access control on SQL server for connections. Note that these change if you alter your plan!
Comments