Setup PostgreSQL in .NET with Entity Framework

hello and welcome to this short video on how you set up postgresql inside your.net project so as long as you use Entity framework you can set it up in a Blazer server app or in a asp.net core web application or a asp.net core API but first of all let's go and create a new project and as I told earlier it doesn't really matter what project you're using just as long as we use Entity framework so let's just go and take a asp.net core web app in this case and it doesn't really matter what we call the project and I will use the.net7 version but you can also use the.net6 then I'll just show you what nougat packages that you have to install if you use.net6 instead so I'll just go and hit create so first of all when we have our project open we want to go to the manage nougat packages and then we want to go to browse and let's install Entity framework code so I'll just type Entity framework call in one word and then we will go and install this package called Microsoft dot Entity framework call and because I'm using.net7 I want to use the latest 7 version so if you use.net6 you want to go and choose the sixth version and just the newest of them so I'll just go and take the 7.0.3 I'll just hit install and I accept I also want to go install the tools so we can just say.net core Dot tools and then it's this package and what this allows us to do is just to go and say that we want to add a migration to the database and then update the database if we have made a model or something that we want to create a table of then this is a good tool to use so I'll go and install the same version as The Entity framework call that I installed before we will go and hit I accept and then the final package that we want to install is this package called npg SQL dot entity frameworkcall Dot postgresql and I just want to go install the first package here and again I want to choose the same version as my.net version of the project so let's go and say install so now we actually set up to be able to connect to our postgres SQL database so I'll just open the PG admin in my case I use the full version that is the newest one right now so in these servers you can see I already have this test server so when I try to expand it it will ask for the master password in my case I just said one two three four and say okay and then also for the user that we're trying to log in with so like this and now we can go to the databases and I have a database called company and if we go down to the schemas and underneath the public we have our tables and right now we don't have any tables because we're going to create that by using Entity framework so now let's go back to visual studio and let's go to our project to create a new DB context file so first of all I want to add a new folder we just want to call that data you can call it whatever you want but I will just call mine for data and then right click say add and add a new item and I will call this app DB context and then say add so now we have our app DB context file right now I just copy and pasted all the code that we need but what it's basically is doing is that we're going to say that we're going to use Entity framework call then we also want to use some configuration and that's just because as you can see down here we have our configuration where we get the connection string from the web API database you can call this whatever you want it is really just the string that we're going to fetch from the app settings.json so this file over here so I'll just copy and paste in the connection string and you can actually go and get all the code from my blog in the link in the description so you don't have to sit and type all this you can just go and copy and paste it but basically what this web API database is called we could also call it connection string just like this and then say the database connection instead makes most sense maybe but what we have in here is the host is the localhost our database in my case it was company I have a username called postgres and then my password was one two three four so I actually think this is the most simple connection string I have ever seen But now let's just go back and look at the app DB context again so now that we know where the get connection string is coming from and we know that this parameter that we put in here is the connection string to our postgres SQL database then we can see here that this is in the unconfiguring method in our DB context so this is the method that is going to be run when it has to configure the connection to the database and then we use this context option Builder where we just say option and then we use the npt SQL which come from the nuget package that we installed earlier as you can see here we have the npgsql dot Entity framework code.postgress SQL and then we can see it also comes from the infrastructure and then again from this class and of course this code is just the dependency injection for our configuration so that we can use it down here where we set the variable or the property so that we can use it inside the appdb context and then by dependency injected into the Constructor then we can go and put the configuration or actually the eye configuration as we have here we can set that equal to the configuration that we can use in the whole class and then finally we are going to create a new model that is called employee and by saying DB sit and then employee and say that this should be the employees table we can just go and create the employee model and when that is done we are going to migrate the changes to the database so that would actually create this table so the easiest way we can do it is just by hovering over this employee and say that we want to generate class employee a new file so let's do that then it will create the new file well in the folder that we was in and it was the data folder and then I'll just go and copy and paste the three properties that I will include to our employee model so now we have an ID we have a name and we have a title for the employee so the last part should actually just be to go and create the migration file so we want to open the package manager console and then we say add migration and in this case I'll just call it for in it and then we can hit enter so that it will create the migration file so as we can see now it created the migration file where it will create a new table called employees and we can also go and see that when I use the npg SQL nougat package that we just installed to actually create the columns in the database and in this case it's actually just the ID column that is going to create I want to say that it's an integer it is not nullable so we cannot make this small and then it put this annotations to say that this is the identity by default column it also creates the name and the title I can actually see down here it says that the primary key should be the ID and if you scroll a bit down we also have if we're gonna roll this back it will just go and drop the table so now over in our solution Explorer we do have this migrations folder which has just been created and then we have our first migration file which is this one and this is the file that we're looking here right now so the only thing we have to do now is to go and say update database and let's run this to see if it works and now you can see it said build succeeded applying the migration and data set is done so let's go back to postgres SQL or the PG admin and then I'll just go and right click the tables and say refresh and let's open the tab and now you can see we have the employees table and we also have a migrations history table from Entity framework so that it can keep track on what migrations have been run but let's just go and expand this and then just say columns and then we can see in our inside our employee table that we have the ID a name and a title so I think that's pretty much it to establish a connection to your postgres SQL database using Entity framework and this example one was internet 7 but you could also use.net6 if you just install these six packages and maybe also the the version number five if you have.net 5 and so on I hope you enjoyed this video and let's just have a nice day bye

Channel: ZetBit

Views: 22,959

Keywords: entity framework core, asp.net core tutorial, postgresql, postgres, .net, postgresql .net, asp.net core, entity framework c#, ef core, .net core

Id: z7G6HV7WWz0

Length: 8min 45sec (525 seconds)

Published: Mon Mar 13 2023