Use MySQL With .NET Core and Entity Framework

in this video we'll learn to use mysql database with our dotnet core web application here i have my visual studio 2020 to open and let's start by creating a new project i'll be creating a new modal view controller based web application so let me select that template and the name of the project is dot net core my sql so this is the name of the project and here i have to select a framework a dotnet framework version for the project i'll be using dotnet framework 6.0 as it's the latest version of dotnet framework available at the time of recording this video if you want to use a different version of toner then you can select that from this drop down but if you select any versions other than dotnet 6.0 you will see significant changes in the star file of the project so i'll select donor 6.0 and we are not going to use any authentication or any features related to that so i'll set the authentication type to none and make sure you have selected configure for https let's create the project here we have a new blank dot net core mvc web application first we have to install some packages if you want to use mysql database with dotnet core so right click on the project and select manage nuget packages option now get to the browse tab and search ef code the first package that we are installing now is entity framework core so select this package and click install okay now we have microsoft or any framework package installed in our project and the next package that we have to install is microsoft dot nd framework code dot tools select this package and click install will require this package if you want to run migrations and all and if you want to use mysql with nd framework core in our dotnet project you have to install a mysql database provider for any framework if you are planning to use sql server instead of mysql then you have to install this package also now we won't be using sql server in this project instead we are using mysql so we have to install any mysql database provider for nt framework core and one of the most popular database mysql database provider foreign framework is pomelo mysql provided for nt framework select this package and click install now we have pomelo entity framework dot mysql package that is a database provider for nt framework or install in our project so we have now successfully installed all the required packages now let's go to the next step that is to create a new model right click on the models folder and select add a class the name of the class is employee e-m-p-l-o-y-e this is a table that we are creating we're going to create in the database so in the employee class i have to add a few properties and the first property is the primary key of the table so probe control space tab tab end id if you want to identify this column as a primary key of the database you can give this name here or you can add a key attribute here but if you're giving the name id here entity framework core will automatically identify this field as the primary key column of the table so we can avoid that key attribute from this property and the next property that i'll add to this table is the name of the employee and this name field is required so let me add a required attribute here i q u i i d so we have to do a control door and we have to use this namespace we have to import this namespace using system.componentmodel.data annotations if you are using that namespace frequently in most of your classes then in dotnet6 or starting with c-sharp version 10 you can write a global using file so i'll right-click on this project and select add and i will create a new class here and i'll call this class global usings this is where i'll write all the common or frequently used using statements in my project uh we have to write our imports here starting with the global keyword and now we can write the required namespaces in our application so we can write global using and system dot component model dot data annotations and if you want to use any other attributes like a foreign key then you should also import global using system dot component model dot data annotations dot schema and also we'll be using microsoft entity framework or in our project so we can import that here global using system sorry it's not a system it's microsoft dot energy framework code so we have all the required name spaces here and i'll save this file now and come back to employee.cs and here i can write a required attribute we don't have to import the using system.componentmodel.data annotations namespace here because we have already imported that in the global usings file if you are using any older versions of c sharp then you have to manually import that in this file so now we have added a required attribute and also we have to provide a string length attribute string length in the brackets i will provide the maximum length and i'll set it to 100 and also the minimum length should be greater than 2 and we can give a custom message here if you want that is error message equal to name field is required so that's the error message that will be displayed if we are not providing any name from the webpage the next property is the email of this employee so i'll copy that and paste it here and i'll change this to email and the error message will be email field is required okay now we have that error message here and the string length will be the same and we have to add one more attribute here that is email address to validate the email address that is provided by the user and the next property is for phone number we have to add a property to store that so phone and let's change this to phone the maximum length will be 15 and the minimum length of the phone number should be greater than or equal to 10 and let's add one more property here that's an address field and let's make this optional so i'll add a question mark over here to make it nullable and we have to remove this request from here and the string length the maximum string length will be 250 and the minimum length is we can remove that from here you don't have to specify the minimum length of this property so this is our table and if you want to make this email unique then you can add index and we have to use the name of otherwise you can directly specify that name of the property here but i commend you to use this name of here name of email so it's an index and it is unique is unique equal to true so we have our email set to unique now that means it won't allow any duplicate values in the email field let's save this file and next we have to provide a connection string so we can provide that connection string either in the app settings.development.json file or in the app settings.json so i'll open this app settings.json and i recommend you to write that in the development file but to make things similar write it here and the thing that i have to add is this connection strings attribute you can do the same in app settings.development.json2 and the name of the connection string is default connection so this is the name of our connection stream and the connection string is server equal to local host and the name of the database is we can set it uh to the same name of the project so dotnet sorry dotnet core mysql so we have the server and the database name here and we have to provide the username and password to connect to the database so user equal to root this is a default user of my mysql database and the password password equal to empty there's no password for this user so i'll leave it blank so we have server database name and username and password with this we can now connect with the database now we have to add a db context class in order to do that i'll first create a new folder here and that folder will be called data and in this folder i'll create a new file a class file and that is application db context so we have our application db context class now and this class should inherit from db context now we have to add a constructor to this class so let me type c g o r tap tap we have a constructor here and we have to provide a db context options all right db context options opt db context options and i have to provide the db contacts name that is application db context and the variable name is options next i have to call the base with the options now we are set to connect with the database next we have to add one more line here that is probe tab tab db set and we have to provide the table here that is employee so i have to do a control door here or i can add that namespace here in the global usings file so global sorry global using and i have to provide the namespace here so the name of the project should be provided first and then dowd models and we can add the namespace of our db context class to here i'll copy and paste that and i'll change this models to data so now we have a db context class and its namespace imported here so i can use that anywhere in the project so employee employee double e i missed an e here okay now it's correct and this will be called employees the next thing that we should do is we have to configure the application to use mysql database in order to do that i'll come back to this program.cs file and we have to make a few changes here if you are new to dotnetframer6 you will see significant changes here so anyway uh now we are going to configure this project to use our mysql database and i will create a migration file and create this table in our mysql database so first i have to find the connection string from our app settings.json file in order to do that i'll first create a variable here var connection string equal to builder dot configuration dot get connection string of that connection string name we have to provide that name here that is default connection so that's the name of the connection string and now we have the connection string from the app settings.json file and the next thing that we have to do is we have to add the db context to the services so builder dot services dot add db context and the name of the db context is or the db context class of our application is application db context so this is the db context class of our application and i have to pass a few options to the db context so i write a curly brackets there and here in the options i will pass options.use mysql sorry it's use my sql you have to select this option if you are using sql server then you have to use use sql server after installing the sql server database provider for entity framework so now in our project we'll be using mysql so we have to use this method instead use mysql and first i have to provide a connection string so that is connection string and the next parameter that we have to provide is the server version in order to provide the server version you can write something like this server version dot auto detect and you have to pass the connection string as the parameter to this function so i'll put a semicolon here and we have oh we have an error here that's because i forgot to add a semicolon here now we have successfully configured our project to use mysql database now let's run our first migration in order to run the migration go to tools and select nougat package manager option and from this sub menu you have to select package manager console so i have a package manager console opened here and in order to create a migration i write add hyphen sorry it's not underscore it's hyphen migration and the name of the migration is initial migration so now we have uh instructor the entity framework to generate a migration file so we have our migration file here with the properties of our employee class identified as the columns of a table we have id name email phone and address so now we can bring these changes to our mysql database before running the next command or before uh creating this migration file you have to make sure that you have mysql running in the machine so i have uh installed mysql with the help of sam and i have that mysql instance running on post 3306 so i can run the next command here that is update hyphen database so after running this command the entity framework will bring these changes to the database now it has successfully completed the migration that means this table will be now available in the database so let me check that you know to do that i will open the phpmyadmin in my browser and here we have a php my admin page uh load here and see we have the employees table and we have all the columns here so this is how we can connect our donate application with mysql database

Channel: Geekinsta

Views: 19,218

Keywords: dot net, .net, dot net core, .net fore, mysql, database, ef, core, entity framework, entity framework core

Id: JzfWpiowtqI

Length: 19min 8sec (1148 seconds)

Published: Thu Dec 23 2021