C# CRUD Operations With SQLite Database Using Entity Framework

welcome to tactic dev today we take a look at how to implement crude operations in c sharp in relation to an sqlite database with the help of entity framework crude operations this simply means to create read update and delete these are the four basic operations when it comes to working with data in a database now microsoft has provided a framework called entity framework to automate all these database related activities by using entity framework we hand over the responsibility of creating tables executing sql commands and managing database connections to the entity framework in this example we take the code first approach this means we create a data model in code then entity framework uses that data model to create database tables we are going to create an application that stores personal information like name and address the app will be able to create which means to store new information and it will be able to read that information update that information as well as delete that information so let's see how we can implement this so i have created a wpf project and in the main window.zamo file i have defined a simple ui that i'm going to use to illustrate crude operations so right here i have a list view and this will be responsible for displaying the information that we create and save to the database so i have defined three columns id name and address so right here is the implementation of these columns and each column is going to display a binding property so we have id name and address property here now here i have two text boxes and these will be useful whenever we want to edit and update existing information now at the bottom here i have four buttons and i'm going to implement click event handlers on each button to implement the create read update and delete operations now right here is a small checkbox and this will be used to enable and disable these two text box controls now i wanted to add in a button that would be used to clear the list view here but what i'm going to do is i'm going to instead use a context menu so a context menu is whenever i right click on this list view a menu is going to pop up and i can just click an item on that menu to clear the list view so i'll do that here so in the list view i'm going to add in the context menu so i'm going to add in a context menu and include a menu item and i'm going to define its header i'll just set that to clear so it's going to display a text clear here and later on i'm going to implement a click handler on this menu item so that the list view can be cleared now here i have a checkbox here and i'm going to implement so that whenever it's checked these two boxes here can be enabled and when it's unchecked they can be disabled now here i wrapped these two boxes as well as these labels here inside a grid so what i can do is i can bind the grids is enabled property i can bind that property to the check box is checked property now because the list view has its children here so whenever i set this to force it's going to basically apply that to all of its children here so i'm going to create a binding and i'll define what element and in this case i'll be binding it to the property of this check box element here and i've already given it a name and this name is enable check box so here i can see it on the list and i'm going to bind it to its property and in this case i'm going to bind it to the is checked property okay now with my four buttons here i've already given them names here create read update as well as delete so i'll be implementing this the event handlers and these buttons in the code later on in this tutorial now because we are going to be using entity framework to communicate with the database it's important to add in the assembly reference to our project's dependencies now i have already done that for this project using the nougat package manager and the name of the package is microsoft entity framework call sqlite now before we can access and manipulate data using entity framework it's important that we must first create a context class the context class is very essential when working with entity framework it represents a session every time we try to communicate with the database and it is used to perform the crude operations and it's also used to configure the database object mapping features that entity framework offers so i'm going to go ahead and add in a class to my project and i'll give it a name data context i'm going to change the access modifier to public and this class inherits from the db context class and it's found in the entity framework namespace so i'm going to add that namespace now we must configure this context class and that can be done by overriding a method that's found in our db context class here and the name of that method is called the on configuring method and here this method gives us a db context options builder so we're going to use this object to configure our context class so there's a method called use sqlite and it takes in a string and this string is the connection string to our database so our data source will be set to userdata.db so db is the extension name of a database file now since this context class is used to map to the database we need to define the tables in our database and that is done by setting some properties of the type db set so db set and i'll give it a name users so this property will represent a table in our database and that table's name will be users here now i need to specify the schema of that table that's basically the fields that are going to be found in that table and i can do that by specifying a type here which is our our data model how we model our data so i'm going to specify a type user and right now we don't have this class here so i'll just generate this class in a new file so here in our solutions explorer we have a file here user and it's our class here so i'm going to define the properties each user is going to have or the fields that the user table is going to have all right so our user is going to have id name and address now i'm going to add in an attribute on this user id property here and i'm going to add in an attribute with the name key so this attribute will be used when creating a database to indicate that this property id is the key of the database table which means it's going to be unique for every user entry and it's going to be automated so whenever we create an object we don't need to specify this because it will be self incrementing all right now we have created our context class that we use to access the database now at the moment i have defined a connection string here that specifies a certain file called user data to db so when trying to access the database it's going to look for this file but apparently this file doesn't exist so we need to create this class but thanks to entity framework we can do that simply by just implementing a few lines of code so what i'm going to do is i'm going to open this file which is the app.jamo file now this file contains the app class which is the main class of our application i'm going to override the method called the onstartup method so this method executes every time the application starts up so right here i'm going to write a few lines of code that are going to create a database file if it doesn't exist now entity framework has got a namespace called infrastructure so we're going to be using that so inside this namespace is a class called database facade so i'll create an instance of this class and this class will take in a context class here inside its construct so i'll use the context class i created which is the data context here so this class contains some methods that are very useful and we want to use a method called ensure created so this method will ensure that the database file is created if it doesn't exist and since our facade object here takes in the data context class that we created this method is going to create a database and it's going to add in the tables to that database based on what we have specified in our context class so that's going to happen every time the application starts up now i'm going to go to my main window here and i'm going to implement the functions of our crude operations in the code behind of this main window.zamo file so in the code behind here i'm going to define four methods that's create read update and delete so starting with the create method i'm going to implement this so i'm going to add in a using statement and define a data context object our resource now what i'm going to do is i'm going to get the text property of these two text boxes and i'll store them inside two variables so using our context object i'm going to access the users table so right here we have a property called users that's representing our table in the database so go ahead and add and in this case since we specified that the schema of the table is of the type user that's our entity and how you create a new user and i'll define the name or assign it to the name property here that we got from the name text box i'll do the same for the address so once this user is added to the users table i will save the changes to the database by calling the save changes method so that's it for implementing the create method now one thing i need to ensure is that the text box which is the name text box and the address text box are not empty so i'll just write an if statement to say if name is not now and address so that's to ensure that we do not have an empty string so that's it for the create method so i'm also going to implement the read method here so what i'll do is i'll just copy and paste so i'll get rid of this so using the context here i'll access the users table now query that information and get a list here so this line of code is going to retain a list of the users inside our users table so i'm going to store that inside the variable so i'll say database users now since i haven't defined a field so just go ahead and do that so this database users is now defined here as a list of users now once that is is done i would like to bind that list to the list view here in the ui and i've given it a name items list so i'll say items list then set the items source property to the database user so that will query from the users table and turn that to a list save it inside this variable and we assign the items list item source to the data that we just got from the table now i'm going to implement the update function here or copy this code now what i'm going to do is i'm going to get the name and the address from the text box just like we did earlier on but this time i won't be adding now for us to update we need to select an object inside the list here so when the objects appear here which are the users we select one user and then we update the user's information and then assign that to the database so what i'm going to do is item list then i'll get the selected item then i'll cast that item to the type user so this simply means it'll get the selected item and convert that to the user's type now i'm going to store this inside variable so that's the selected user here so what i'm going to do is using the context i'm going to query for the user so i'm going to find this user from the database table and i'm going to specify the id here now because when defining our users class here we specified the id as the primary key of this table so i'm going to use this key now since we have selected this user here and we want to edit this user so i'll get this user's id property so i'll look for a user in the table based on the selected user and i'll get this user and i'll change the user's name and address i'll set it to the name that we have specified in the text box and to the address specified so what's happening here is i select a user from our list and i cast it to a user and the reason for that is to just get the id of the selected item in the list so once i get that id i look for this user inside our database table and once i retrieve that user i change the name and the address so once that's done using the context object i save the changes now i'm going to implement the delete function so what i'll do is i'll copy now this time i don't need the name so i'll start from the user statement this time i don't need the name so what i'll do is using the if statement i need to make sure that a user is selected so if the selected user is not now then we can execute this code safely so using our context object i'm going to remove now in our remove function we know we can just add in the entity we want to remove so in this case since we are retrieving this user from the database based on the selected user id so just specify that i would like to remove this very user from our database so once that is done save the changes and when the changes are saved we would like to update the list and make sure that the user is no longer in the database so we do that by calling the read function same for update whenever we update we're going to read from the database again and see if truly the changes have been made okay so that's it for implementing this now i'm going to add in event handlers for the four buttons i created in the ui so for the create button add new event handler same for the read button the update button as well and finally the delete button okay now if you remember earlier i said i would like that when i right click on this list a context menu pops up and there's an option to clear the list so i need to implement a click event for this item menu in our context menu here so i'll do that and create a new handler menu item click so if we go back to the code behind we see that their methods added here so in our create i'm just going to call the create method here same for our read as well as delete now to clear the list what i'm going to do is just select item list and i can say it's items property what i can do is just clear that list so let's go back to the ui here now what i would like to do is that every time we select a user in the list i would like that that user's information pops up here so that we can edit it and update if we want to update so i'll do that by binding this text box text property to the selected item so what i'm going to do is right here so i'll say text property and create a binding and element name so we know that its items list and the path is selected item so we are going to get the selected items name property i'll do the same for the address text box here and in this case i'll bind that to the address i would also like to specify the mode and i'd like to say it's it's a one-way data binding so this will only read the information based on what we select in our list there but it won't modify that because it's a one-way binding i'll do the same here one way binding now before i can test the code i need to make a quick modification so back to the code behind here inside our delete method i'm going to change this find method to a different type of method now the reason for that is because i think it's safer to use the other method because this one tends to be error prone so i'll change that find and i'll change it to single and this takes in if we check here it will take in a function and this function should return a boolean value so what i'm going to do is use a lambda expression so what this function does it returns the only element in a sequence that's satisfies the specified condition so what's the condition this is the condition here so this is an anonymous function so it's a function that takes in x as the parameter and we know that x is a single user so it will take in a user and it will check if the user's id is equal to the selected user's id and if that's true it's going to retain that user and we're going to remove that user from our database and save the changes so with these changes being made i'll now go ahead and test the code all right so the application is up and running so just go ahead and resize the window now the first operation i'm going to test is the create operation i'm going to do that by clicking the edit checkbox here to enable these two text boxes so i'll create a user with the name james and i'll give him an address second street parklands so i'll create this user now apparently we can't see any user in the list and that's because we need to read the database and display the information here so we will now be testing the read operation and we see a new user james with a new address and he has been assigned with an id one i'm going to create another user with the name mark so let's say mark and james leave at the same address and i'll create and read so we see mark appears in the list now how do we update the information so let's say james decides to get married and moves to a new location so when i click on james you are going to observe that this name here is going to change and if i click on mark it changes as well now the reason for that is because we created data binding on the text property of these two text boxes here so i'll click on james and i'm going to change his address to 2nd street freedomway so i'm going to update his address so we can't see the changes because we need to read the database once more and we see james has a different address now i'm going to delete an entry in the database so i'll click on mark and i'll click on delete and i'll read and we see mac no longer exists i'll do the same for james delete read and we see that that's deleted as well so i'll close the application now i defined a function inside our app dot zamo.cs file that creates a database file whenever the app is starting and that only happens if the database file doesn't exist now if i navigate here and show all the files now we know that our application runs from the debug folder so if i scroll down here so there's a file called userdata.db so this file was created by entity framework using the database facade class so guys thanks for watching today's tutorial i'll see you in the next one

Channel: Tactic Devs

Views: 2,255

Keywords: C#, Database, .net 5, enitiyframework

Id: fnyiDMvhJOc

Length: 40min 5sec (2405 seconds)

Published: Fri Aug 13 2021