.NET Core, .NET Framework, Microsoft Corporation, Microsoft Visual Studio How To Connect NET Application To Azure SQL With No Password – WSB Website Part 3
This is the third video on creating the wall street bed scraper and, if you’re not following it, then this is just a video where we’re gon na connect our azure uh sql database to our.net application for this video. We assume you already have the database and you have your microsoft identity connected to it, if not make sure to check out the video that i’m gon na put up here in a second and down below in the description right next to all the github code. And if you want to do this with localdb for development, make sure to check out that video as well, it will be down in description down below so now, let’s get started. So we have our database we’re going to need our server name and our database name to create our connection string. So we’re going to go first to app settings connections and i already went ahead and did it just because it’s just copying and pasting. So basically i did a connection strings section created one called sql db connection string and the regular connection string server equals tcp. Then the name of the database and then the port and then the database name and then encrypt it because hey let’s encrypt our stuff and then i have commented out the local db1 so in case you’re watching that video. Just you can just comment or can comment that stuff and then, after that we have to add some uh nugget packages.
I already went ahead and added them because it’s not super interesting to see me click through this stuff or just do the net ad down below and so basically we’re gon na need the microsoft azure services app authentication. So this one, what it does is it uses an msi which is a managed service identity when you’re in the cloud that is basically a password that azure manages for you and, if you’re developing. Obviously your computer doesn’t have that so it uses your identity that you have in in visual studio setup. To do that, you can do options. I already have it open because i just checked it, but if not you scroll down here to azure service authentication, account selection and make sure you have the right account the one that has access to the database selected here. So then, after that, we have to install a bunch of entity framework core nugget packages and they will all be down in the description down below if you just want to copy and paste them so it’s framework core core design, sql server, core tools and system data That sql client, so we have that covered then, after that we have to create our database context. So for this one um, i already have it so. First, we have to inherit from db context and that’s going to ask us to have the reference to entity framework and then, after that, we have to just let me copy and paste the code real quick.
So then we have two parts of it. So first i’ll talk about the connection and i’ll talk about this one. So first in the constructor we’re going to pass some db context options and then we pass it to the base class, which is to be context. And we have our connection which we’re um casting as a sql client connection, because we’re connecting to a sql database. And if the connection string contains local db, then we don’t have to get a token to actually authenticate to it, because if you saw the connection string, we don’t have a password, because in here we use magic or microsoft identity. However, you want to look at it. So if you don’t have to get token, we just return there’s no point of doing that and if you need to get the token, then you go and get the with the azure service token that it’s the node package i talked about, we just say get access. Token – and they only have an async one, but since it’s a constructor, you have to do dot result and then this is another. So that covers kind of the connection for it. And i guess i can go to startup and add the the code that we do to to enable this and we’re just going to do a db context. And we are passing the context that we just created and we’re passing the connection string that we just put in the startup and now you’re.
Seeing everything come together, then one of the options that i always enable is enable retry and failure. This is for transient transient error, so try to read retry instead of just failing actually like when you do it, it gives. If you don’t, do it it gives you a warning to add it, so you should add it because there’s a warning for it. So then, going back to the context now we have to create our tables because we don’t have any tables. So if i open the database here, you can see, tables is expanded and there are zero tables. So we have to add another table and for this case we’re using the reddit post model because that’s the project we’re working – and i in here you can see the the post model that we have it’s. It just has a stock. I, the only thing i added is a key, so i made it that the post id is a key because it’s a unique value for for each post and then we have the number of comments. Sorry, the number of votes, downloads and number of comments so we’re going to use that to create a new table in the next video, so make sure to check that sorry yeah a table for the users to see in the next video so make sure to subscribe. To see that, in this case, we just want to create the table in the database and make sure we we can connect.
So now that we have everything and everything looks beautiful and it should just work. We’Re gon na do in the packet manager down here, we’re gon na do add migration and you have to name it something with no spaces. So i always my first migration i’m very creative and i call it first migration, so we built and we created this file and they created a folder here with migrations. And so here we can see that it’s creating a table called posts and it has all the data and how many characters each has it defaults to maximum as you specify and then it has some ends and then the primary key is post id because we set It as post – and we like how it looks so we’re gon na – do update database, so here it’s gon na build it’s gon na connect to the database and it’s gon na create the table for you. So now, if we go to the database, we refresh. We have two tables, but we only created one. So we have our regular table, the one that we created, that is posts as you can see, it’s empty, but it has all the columns that we want and then they created this one called ef migrations history which, by the name we can see that it’s entity Framework, migrations and history, so in here we can see the migration id and the name of it that it’s, the one that we said: first, migration and the and the version of the ef core.
So we, the the reason for this is because if you modify or you want to add another table or anything, you just do what we did with the post table. So we just create a new model and then we go here and we create another db set with another table and you just do the same process of add migration and it will create another document which is the migration down here and then you do update database And it will grab kind of like the previous. It will check that table to see at what version you are because you might have to you know like you, might not update the database right away and it will have to update two migrations at once. So it goes through and it adds all the migrations that it’s missing so that’s how you connect your database to azure think in the next video we’re, gon na actually add values and display the and then the video after that we’re going to display the data.