.Net core Web Api CRUD With SQLite & Dapper

hello there everyone it's ravindru here in this video we will learn how we can access data from sqlite database we will create a cut app with dotnet core API and Dapper if you find this video helpful then please hit the like button make sure to subscribe this channel to get more videos like this so let's get started first of all we are going to create a new.net core project and I am using visual studio code you can use visual studio also but steps will be different so if you are using visual studio code then please first of all you need to install this extension called C sharp dev kit and after installing it you can follow along so let's create a new application so here you will see a option create.net core project and after a while you will see something here yeah it is present here we are going to create asp.net core empty project yeah we're going to create an empty project so let's click on this guy and here it will ask us a location where we want to create a project so I am going to create it somewhere documents and here projects and dotnet and c-sharp data access so I'm going to install this project here okay so select folder and now it will ask us the name of project so our project will be dot net sqlite demo let's enter and soon it will create our new project so yeah it has created a new project here it takes little bit of time on loading a project so it still is loading projects here and after a while you will see this option of solution Explorer here inside it we have this dot Echo empty project so let's run this project and let's see what is going on with this project so click on this debugger option here we will see that create a new launch.json file and click on.net5plus.net core it will create a new launch.json file and now you can learn not learn you can run your project easily so as you can see our project has been run in the logo 7012 port and we can close it now so we will come back to it later so here we have our first project and we will create another project here so click on this plus icon and we'll create a class Library project so it will be let's name it data access that's it and here default directory okay now we have to project inside this solution so what we will do here I will write click on the sqlite demo project and I will click on the add project reference and I will add reference of data access so now we have added its reference to sqlite demo it means we have added our class libraries reference to the API project so how you can see that am I doing right or not so here you will see that we have this reference of project it is here so we have reference data access dot CS project file here okay so we are done with this and we have created all the projects so now what we will do here we will install some packages so I'm going to go here View and terminal and in the terminal you will see uh this new Power Cell terminal I'm going to kill this one so I do not need it okay now we need to enter into the data access folder so I'm going to change the directory here CD data access oops CD data oops I have messed with spelling a c e s s data SS let's see d i r c d ATA a c e s s okay now we are good so we will install few packages here first package we need to install is Dapper so just right here Dot netpad package let's name it Dapper so first package we will install Tapper .net add package oops I'm doing lots of mystery right now so dot net at package dapper and it will install a Dapper for us we'll create we will not create we will install one more package.net and package this will be system.data dot sqlite dot core oops dot net add package here should be a typo so let me see here here is a mistake PAC k a g e package and one last package we need to install is this one Microsoft dot extensions dot configuration dot abstractions so we need this package to access our configuration file which is app setting dot Json file okay now we have installed all the necessary packages so we can exit from this directory we do not need to be here anymore okay so now we will go here in the dot net SQL write demo but before that we will double click on data access and here you can see in the data access dot CS proj file you'll see that we have installed these three packages so it was Dapper 2.1.4 and it is this package 7.0.0 and this package is 1.0.118 Okay so now here what we will do we will right click here and click on reveal in File Explorer okay and now we will copy this path okay and what you have to do if you do not have this thing DB browser if you do not have installed DB browser for sqlite so you need to install it first so click on here and you will see we have uh executable file here so you need to install it from here so first go and install it so you can go here in the download section and you will see all the executable you need to download using Windows so I have installed this one DB browser for sqlite standard installer for 64-bit windows so you can install any one of it and it is a portable app so we are going to install it in our projects directory once you have installed it you will see this icon and you need to click on this icon okay here you will see the new database option and you will create a new database in a certain location so I'm going to create in my project so that's why I have copied a location earlier and let's name it person or a person DB it will be fine or just name it person okay inside it we will create a table person and here we will add some field so first field would be ID and it will be a primary key and Auto incremented okay next field we will add a name and name will be text not in the label okay text and not null that's it okay next column will be where it is at next column will be email and it will also be uh text and not null okay so I can click on OK here and you can see that we have this table so here you can see we can browse its data so if I go here in this in this folder data access you will see that we have this project person dot DB here sorry not project this file it is R SQL light database so I'm going to close it from here and let's save it that's it person.dbs or database and inside rehab person table okay so now we will write a connection string here so it will be oops connection strings and let's name it default let's change it to small d and here we will Define our connection string okay since our application sorry file in the root directory we will just can access is like this data source equal to person dot DB okay so that's it that's how we can access this file let's close it for now and now we do not have to do anything here for a while so I'm going to close this solution Explorer and I'm gonna go here in the dotnet normal Visual Studio Explorer whatever it is so here what I'm going to do I'm going to create a new file here so sorry new folder so I can also go from here but I think you will get confused if I use sometime this one sometime this one so let's go with this one okay so here what we will do we will create a new folder models okay one more folder repositories inside the models we will create a new file which will be plus oops class it will be a person so it will take a little while and now we are good here so let's change its name space to this and here Define some fields so now I have created this class so three Fields ID name and email ID is integer type name is string type and email existing type and this field and this field is required field and it will add this package or namespace here it's not a package its name is space yeah so we will write here the using keyword so this part is done now we will go here and we will create a new file here oops I accidentally have predict on record we'll create a class here and it will be person Ripple victory dot no that's it person Repository so our class has been created let's change it to repositories okay and here we will do some work first thing we'll do here we will type ctor and it will create a Constructor for us next thing we will do here we will create a private field private read only and let's name it config config equals to sorry not equals to I configuration config that's it one more thing private read-only string and it will be connection [Music] this string that's it so here we will write I configuration config and inside we will write config equals to config and connection history equals to config Dot get connection string and default okay I will create few methods here so first method will be uh private sorry not private public async task and it will be I enumerable inevitable and here we what we will do we will right persons okay and let's name it get people async thank you okay now let's write here using before that here we will create a private method private get connection is string sorry not get connection string get connection using I DB connection connection equals to SQL new SQL light connection inside it will pass the connection string and we will return this connection and it will be a string type sorry not a string type idb connection type okay so this using keyword will automatically dispose the connection that we have opened here so it will handle it this using keyword okay now here what we'll do our connection equals to get connection oops get private idb connection so get connection so I think I am doing some typos here and the Okay so it's here now here we get the connection is string now let's write here string SQL equals to select as trick from person okay now what we will and here we will write a connection dot query async and here we'll write person and before that here we'll write if we were people equals to a weight okay now we are fine and here we will pass the sequel that's it and here we will return people so that's it now here when we will create one more method get people by IDs sync ID and it will return just a single object not a list task person from person where I D equals to at the rate ID sorry ID okay and here we pass Anonymous object ID sorry not id id so make sure this this field and this field should be safe even it is a case sensitive so it should be exactly same small I small D small I small d and here we will try it first or default async so it will written as a single object and it will be a person that's it and it will return a person here so now we have to read it two methods so we will create one two three three more so our next one will be so let's create a method for uh create or add a new person it will be async task and it will also written uh object person let's name it add person a sink that's it person person equals to get connection and here we will write string SQL equals to insert into person it is our table name serve it into person and it is our name and email these are our field so into person name email value is equals to name and email select last insert row ID and it will return a newly created ID so here we can write int created ID equals to a weight and a weight connection dot execute scalar as sync it will be a person and here will be SQL and here we will pass our Anonymous data so we will pass a person dot name and person Dot email execute scalar async person SQL uh what I'm doing wrong here yeah it's uh integer type okay so now we have our created ID so what we will do here we'll just try person dot I equals to created ID and we will return a person that's it now we will create a update method here so let's just change it to update person sync it will be the same and it will be different and this one will be a different also and here it will be different also so it won't return anything so here we will write update table name person and set name equals to at the rate name email equals to at the rate email where ID equals to at the rate ID connection dot execute async and here we will write SQL and we will pass a new Anonymous object here and we will just pass a person here because person have all these three properties name email and ID that's why we can pass it as a whole object so the asset last thing we want to do here we are just going to copy this guy and paste it here and let's change this to delete person I think it will also take end ID and update we will change this query delete from person where ID equals to ID and here we will pass ID and we will change it to small I because we need it to be same as this one so this thing and this thing should be same okay so if we haven't changed it to small I then we have to pass it like this okay so it is rather a lengthy approach I'm just going to change it like this okay so we have created all of these methods so what I'm going to do right now I'm going to create a new interface here so here I press on Repository and what we are going to do here we are just going to copy and paste the name of our method so first method is this one so we are going to copy it and paste it here press Ctrl Dot and it will add this line here so we will do this same here just copy this guy paste it oops copy this guy paste it here press semicolon paste this guy here uh next thing is that person sink one more thing this one update and the last one is delete so just copy this guy and paste this guy here and now we have one two three four five methods just copy its name and and where ER here so now we are using interface with this person depository because we want to use uh dependency injection so it's better to create a interface here okay so now just open this file program dot CS you can also find it here program.cs so here we are going to write Builder dot Services dot a transient I person repository person Repository that's it and since it is a web API project it is using minimal apis right now but we want to use controller API so we need to add a service here Builder Dot uh Builder dot Services dot add controller something like this yeah and since we have added our controllers so we are going to use it here also app dot map controllers okay and we are going to comment this guy since it is a blank application so we do not have these things added so we have to add these things manually now what we will do here here we will create a new folder so it will be controllers and inside the controllers we will create a new file which will be a class definitely let's name it people controller or percent controller whatever you want to name it person controller that's it and it will create a new class for us present controller.cs first thing what we are going to do here we are going to change it to API controller and it will inherit uh controller base now it is our full present controller one more thing we have to define a route here and let's name it API slash oops API slash peoples so I'm confused right now is it like this or this so let me check so first of all we are going to create uh controller API method so I have created this net short sorry snippet not a snapshot snip it for it it is my custom made snippet for creating a method which you don't have just go there and create one it will make things easy so it will be just a get method get people something like this or just get whatever you want to name it get people will be fine s t t p get and here we will just write hello everyone so let's run this guy here so right now if I type here apis slash people what I will see I don't see anything it means I am doing something wrong here and mean okay it is people's not people so let me run it again so I have refreshed this page and you can see that hello everyone means everything is working fine our controller is working absolutely fine so we will create few methods here but before that we are going to inject our repository here so type here's the tor press enter and it will create other structure for us so what we are going to do here we are going to create a few private fields of private read only logger it is just a basic default logger by Microsoft we will define a class name here person controller um logger okay so here one more thing private read only I person Repository um person repo so just copy this guy paste it here name it logger copy this guy paste it here name it person repo okay let me press Ctrl Z alt Z sorry so logger equals to logger and person repo equals to person repo and we are done here so let's check one method here and let's see our configuration is working fine or not so here just type try we'll do it later okay let's do it right now so try and here we'll see try catch exception EX logger DOT log e x dot message it should be log error okay and here we will return status code status codes Dot internal server error and here we will return Anonymous object so it will be status code equals to five double zero message equals to ex Dot message and here in the end we put a semicolon so it is our exception here and here which we will do we will write War people equals to await person repo Dot get people async and return okay inside we will pass the people now we will test our methods so let's run this guy so as I run this method or endpoint I have seen this error message it means I am doing something wrong so after reviewing an error I have come back so I have come to a solution so why I am getting error here because because I have done something terribly here and if you go here in the person repository so if you are seeing that we are using a connection like this so I have used this kind of connection with my SQL databases and that was fine it was working absolutely fine but in the case of this sqlite connector it is not working as expected so what we are going to do we are going to copy this whole line we are just going to comment this guy and we are going to paste it in the every every method so just copy cut it here and where was we here here so we need to paste this line everywhere we are using this thing so just go here and paste it here paste it here so we should be fine for now so here let's run this project so now you can see there is no error message here is just a blank error which is expected and I'm not expecting any errors so let's define few all methods here okay so let's define a method for add a new person so I have created few methods so first of all we need to remove this guy and now I will explain what I was doing here so first of all we have created this HTTP post method which is public async task create person it is taking an object person person and here we have this try catch block inside it we are calling this person repo dot add person asking method and we are passing this person here it will return a person object so we will store it in our created person object and we'll create we will just return a created at accent which will return 201 status code and you will return the name of create person method and this object created person so it will return 201 status code along with this object uh it is a newly created object so we will return it to the client and here it is a typical exception handling which we had done in the previous method we are returning a five double zero status code and message along with this 500 internal server error next thing is we have this HTTP put method so it will update our person so here person person to update first of all we will find a person which we need to update so we will find a person so with this method we can find it with ease we will check if person is null or not so if person is null so we will return 404 status code along with this message and we will return this not found it means we are returning a status code 404 when up whenever a person does not exist otherwise we will update a person and we will return no content means 204 status code without any body okay so it is our update method I hope I am not doing anything wrong here next thing we will do we will find people by ID so it is just a simple method we are calling this get people ID people by IDs actually it should be get person by ID async or find person by ID async not people so anyway that's not a big deal here we will find a person if it is null we will written not found what we have done a previous otherwise we will return uh this exception in a catch block here we have this HTTP delete method and here first of all we are checking the existing person then we are checking is it another or not if it is a null then we will return not found exception along with this object or then we will call this delete personalizing method and it will also written a no content and here we are handling the catch exception so I'm assuming that I will get few errors since I am not I haven't tested this code I hope for the best so what could be the error let me figure out so yeah as I was expecting it was giving error here so what I have done here I have passed Anonymous object like this m equals to person dot name email equals to person dot email and ID equals to person dot ID and now it is not giving any error so I had updated it recently so you can see that now ID equals to 3 is ravindra Android gmail.com so let's change it window and let's change my email ID so as you can see now we have two zero four status code and here you can see that I have changed my email address what if I pass here the ID that does not exist let's check this condition here as you can see that we have 404 status code and this object so it means we cannot update eight okay so last thing we are going to do we are going to test the delete endpoint so we are going to delete this third one now let's delete this guy and you can see we have deleted it so if I check it now now it is not present here and what if I want to try it again if I want to try it again it will say that it's not found because person is already deleted so we do not have any of the person which have ID3 so now we have tested all of our endpoint so let's do it again now we can see that we have only two records so we are done here that's how we can use a SQL Lite database as a backend in.net application and we are using a Dapper here so it was not that much complicated although I have done some mistakes uh but that's okay that's a part of development job so that's it for now see you next time

Channel: Ravindra Devrani

Views: 1,206

Keywords:

Id: JDHaeDlNqOI

Length: 43min 2sec (2582 seconds)

Published: Tue Oct 03 2023