Database In ASP.Net Core
Hello! So basically we all know when we create a SQL Database in Visual Studio Community with EF and SQLServer and we can store data there. My question is this: when we add the app to a website like Heroku or a Hosting App in order to publish it to the web, does the database with all the data gets added too or is there a separate process for that? Thanks!
33 Replies
No you will have to host the database aswell.
If it's an in memory database then no. You don't want an in memory database though.
You can for instance use Azure Sql Database.
Then your server will connect to it using a connection string.
(localdb)\MSSQLLocalDB;
if it's like this
it is memory built in or not?
No not a memory db, that is a lightweight db shipped as part of vs.
You can us SqlLite.
The SQL database that is a file you can use in your Visual Studio solution is SQLite. SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It stores the entire database in a single file, making it easy to manage and deploy within your solution.
@Mihneas22 what is your application for and how many people are going to use it at the same time?
Also, how is your application using your database? Do you have a large amount of data and do you need multiple users to be able to access the same database at the same time?
Depending on how you answer these questions, we can tell you what kind of RDBMS you want/need to use.
Well
My application is an API done with Clean Arhitecture

I plan to post my APP on Rapid API so I can't really determine how well it is going to do
My application is using the database by retrieving data in order so show to the use. For example let's say the use wants to get a nutritional fact from my API. It will access the database and the retrieve the data
you will probably be fine with sqlite unless you are getting many thousands of users, assuming you have it configured correctly and are using patterns like async/await well
azure sql database would be very scalable though, it's just sql server hosted in the cloud for you
yes
i did
Hello! I am getting error 500 when I posted my api to Azure App Service, the database connection seems to be fine as I check if it works on my local host
can anyone help
Is there any useful information in the app service logs?
[Error] Microsoft.EntityFrameworkCore.Database.Connection: An error occurred using the connection to database 'MyCalisthenicsAPIDatabase' on server 'tcp:mycalisthenicsapidatabaseidbserver.database.windows.net'
I'm guessing the database is accessible from public if you connect to it from you dev machine. Is the firewall on Azure SQL configured to allow access from Azure services?
i dont know
wait
no it is not
but how can I do it
on the SQL server under networking, firewall
there should be a checkbox to allow azure services access

It should be there - one second, I'll spin one up real quick
okay, thanks a lot

it's underneath your rules
oh
that
it's checked)
it has been like that for default
Is that the only log message? If so, can you change the log level to something more verbose? Is the App Service Linux or Windows? If Linux you could e.g. set an environment variable
it is windows
you know
when I created the database
Then you'd have to replace the double underscores
__
with a colon :
. We can also hop in a voice call if you want toI got a warning message that I have to set something about Managed Identities in order for it wokr
work
that depends on how you authenticate to Azure SQL
Name and Password
it supports both SQL Authentication (username + password) as well as Entra ID with normal users and managed identities
i do it with SQL Auth
then you don't need a managed identity
in my connection string I also have the name and the password introduce
d
and this is why I am saying that on my local host is working
but when I try to access it on the website/app service created with azure
it does not work
or Postman
Can you hop on a quick call and show me the configuration?
sure
Tutorial: Access data with managed identity - Azure App Service
Secure Azure SQL Database connectivity with managed identity from a sample .NET web app, and also how to apply it to other Azure services.