How to populate SQL Database from App Service

Question

How to populate SQL Database from App Service

Stefan Novak 46

Hello,

I'm sorry for what's going to be an extremely simple question I'm guessing. I've been having trouble finding the right docs for what I want.

My problem is that I have a .NET application that connects to a locally hosted SQL Server container. It works great.

For production, I deploy my application to App Service, and I've created a SQL Database in Azure that I want to be the production DB. In App Service, I've set the DefaultConnection to the connection string I've gotten from the Azure SQL Database.

I thought this would just work. However when I do requests, I get 500s, SQLExceptions, showing 'Invalid Column Names'. When I look at the tables in the SQL Database, there are none, it's empty.

How do I get the tables populated from my App Service? I've been doing a code first approach, so I don't want to have to create the tables by hand. Although maybe this is the way it's supposed to be done?

Thanks all

SSingh-MSFT 16,276 Reputation points

2022-12-14T12:28:31.127+00:00

Hi @Stefan Novak ,

Welcome to Microsoft Q&A platform and thanks for using Azure services.

As I understand from the question, you are getting exception in connecting App Service and Azure SQL Database.

Could you please let us know if you are following the steps mentioned in the documentation app-service-web-tutorial-dotnet-sqldatabase

At what step you are facing exception, please share screenshot.

Also, please let us know if the Database connected is actually empty or you have migrated and has data.

Thanks
Stefan Novak 46 Reputation points

2022-12-14T19:23:16.79+00:00

Hey Shakti, thanks for the response. I've found out now that I needed to generate some scripts to populate my tables as expected. All is well now! Thank you

Accepted answer

0 additional answers

Your answer

SSingh-MSFT 16,276 Reputation points

2022-12-14T12:28:31.127+00:00

Hi @Stefan Novak ,

Welcome to Microsoft Q&A platform and thanks for using Azure services.

As I understand from the question, you are getting exception in connecting App Service and Azure SQL Database.

Could you please let us know if you are following the steps mentioned in the documentation app-service-web-tutorial-dotnet-sqldatabase

At what step you are facing exception, please share screenshot.

Also, please let us know if the Database connected is actually empty or you have migrated and has data.

Thanks
Stefan Novak 46 Reputation points

2022-12-14T19:23:16.79+00:00

Hey Shakti, thanks for the response. I've found out now that I needed to generate some scripts to populate my tables as expected. All is well now! Thank you

Answer 1

Bjoern Peters 8,901

Hi @Stefan Novak

How do you connect to your local database? Using SQL Server Management Studio?

In SSMS, you can just right-click on your database => Tasks => Generate Scripts.
This will generate all the required statements for creating your database with all your table structure (maybe you only mark the structure with the database itself)

https://learn.microsoft.com/en-us/sql/ssms/scripting/generate-and-publish-scripts-wizard?view=sql-server-ver16

I hope my answer is helpful to you,

Your
Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Stefan Novak 46 Reputation points

2022-12-14T16:35:46.92+00:00

Hey Bjoern, thanks for the response!

Do you mean that I should be generating a script and generating the required tables from that?

So for example, if I do this now and get my tables, then later I create a new table and change some columns in existing tables.
Then I deploy those changes to App Service.
Then after, I should generate scripts and update my database accordingly?

Thanks!
Bjoern Peters 8,901 Reputation points

2022-12-14T17:16:11.37+00:00

I suggest you create a base script now (call it v1) and deploy it on all relevant (empty) databases.

In your app, implement a function that checks, for example, the version of your app and your data structure and, eventually, if there is a new file in an update folder...

For every change you are doing to your (development) database structure, create a change script (Create a new table, Alter a table, and so on...) call them V1.1, V1.2... and apply them in the correct order with that function within you application => check that status once a day, every 12h, or every 1.000 web request... whatever fits your needs
Stefan Novak 46 Reputation points

2022-12-14T19:21:54.25+00:00

Fantastic thanks Bjoern! That's great advice. I made a script and all the tables are there now. Now just fighting OTHER exceptions haha, as is the way of production vs development environments. Thanks very much!

Share via

How to populate SQL Database from App Service

0 additional answers

Your answer