Load Data to a DataGrid in WPF With MSSQL And EntityFramework in C#

hi guys welcome to tactic devs in today's video i'll be showing you guys how to query data from an sql server database and then display that data inside a data grid control now i already have a project set up here so without further delay let's cut to the chase now before i can do anything the first thing that i need to do is to create a database on our saver now if you have visual studio installed it's likely that you also have sql local db installed on your machine now to verify that we can make use of command prompt and run some commands to check if there's an instance of local db running on the pc we are using so i'm just going to open up command prompt by using the shortcut keys which is the windows key and the arrow key so it's going to open up a window which is the run window and i'm going to type in a command c md so this will open up command prompt and inside command prompt i'll just check if there's an instance of local db installed on our pc so sql local db info now this command returns the name of the instance installed but another thing is i need to ensure that this instance of local db is actually running in the background so i can do that by just typing in local or sql local db start and it gives you the name started so this ensures that the instance of local db which is sql local db is actually running okay so i'll go ahead and close that and return to visual studio so inside visual studio just here i'll click on view and we have sql server object explorer here so i'll just click on that okay so here we have sql server and you can see there's a server called localdb and i'll just open that up and under databases we see that there's no database specified so i'm going to add in a new database and i'll just give it a name up data okay so the first step is completed i have a database now what i'm going to do is i'm going to create a table and i'm going to populate that table with some random data and we'll actually be reading that data and displaying it in the data grid control now there's a website called generate data and this website generates random data so we can actually use that website to generate our data so just go to my browser and i'll just type in generate data the website is generateddata.com okay so here i can specify the column titles so i'll specify the first one as name now because i used this earlier on so it remembers what i had inputted in address email and date of bite so i also need to specify the data type that i'll be storing in these columns so for name i'll just choose names and for address street address email the same and for date of birth i just choose date so these parameters are important it helps this website actually generate data and specify the types of data so for the name i'll set an example which is john smith and this has the it has a first name and a last name and for date of birth i'll give it a format type which is mysql date time format so i'll set that now this is com this is compliant with sql dat time types so it's okay to use that and here i can specify the period of time from which it can choose random dates but i'll just leave that set to the default now here under the export types there is csv excel json html and what i'm going to do is i'm just going to select sql okay so here it asks me to specify the table name i'm just going to give it a name employees okay now because i used it early on it actually remembers and the database type i'll change this from my sql to sql server and i'll just and click this now what this does it's going to create a script and we are going to use that script to create a table and actually populate data in that table so here basically it's asking us if we want to create a script that drops that table that deletes the table but we don't need that since we are just creating and populating that table and here we have insert batch size so basically that's how many let's how many times we want to run this query to insert the data how much data we want for each uh query so here i'll actually set that to 100 because i want hundred rows of random data and here it's already specified hundred rows okay now i'll just verify that everything is okay and then i'll generate this okay so what has happened now there's a script here and it'll create a table and then insert some data some random data generated by this website so just copy this script and then i'll go back to visual studio okay so in visual studio i just click on sql server object explorer and i'll right click on my database that i created earlier on and just click new query okay so here it creates a file which is an sql query file and it's empty and i'll just paste that sql code i generated on the website okay so here it creates a table employees and there are fields here specified now i want to change this date of birth field from var character to that time now because dead time is a keyword it's going to change that to blue and this is going to store our debts so i'm also going to change this employee's id or just change that to id okay now because this id is actually a primary key and it's specified here so that means i also need to change this okay so that's it now i'm going to execute this query and we should have data in our table so here we have 100 rows affected so i'll go to the object explorer and just check for any tables okay so here we see there's a table called employees and if i right click and view the data so i can see there's some random data that has been created based on the script that we got from that website okay so just go ahead and close this and i'll close that as well now here in the main window.zamo file i added in a data grid and i just gave it a name employees list and i set some margins around it and one other thing i did was i added in some styles just to give it a dark theme so here under window resources i added in two styles one targets the data grid column header and you can see it has been set to a dark blue color here i also set some properties on the data grid using the style basically to just give it a dark theme okay now i'm going to be using entity framework to access the data in our database so it's important that i add the namespace assemblies or the packages to the project now i've already done that so if i right click on my project and i go to manage nuget packages so here i added in two packages microsoft entity framework core sql server and microsoft entity framework core tools this is this is going to be very useful because we we're going to be using it to basically create some classes so that's the reason i had this added in so you can do the same okay now one of the commands we are going to be using using this package is the scaffold db context here okay now for us to access data using entity framework we need to create a context class and that context class needs to specify the tables in our database so that it can map that to class types now we can actually type in the classes manually or we can use entity framework tools to help us create those classes so i'm going to do that so i'll just go to tools and nougat package manager package manager console okay now back to the sql server object explorer so here on our database i'll just select the database and right here in the properties panel i'll look for a property which is connection string so here i have the connection string to this database specified so i'll just copy this string and i'll just open my mainwindow.xml.cs file and then i'll paste this string here so first i'll add in just paste it in there okay so i'm going to paste it as a string so just paste it like that now i only need this part of the connection string i don't need the the other parameters so i'm just going to get rid of that by holding shift pressing end and yeah so basically this is all i need from the connection string so it specifies the source which is local db and the initial catalog which is basically the name of the database inside the server so i'll copy this okay so inside the nougat package manager console i'm going to type in some commands and these commands are actually going to create the files and the classes that we're going to be using to access the data and what i'm going to do is what happens basically is entity framework will search for the tables that are existing inside the database and it's going to create classes based on the schema of those tables so i'm just going to type in scaffold db context and are pasting the connection string to the database okay and now specify the provider and in this case we are using microsoft entity framework sql server okay and the other parameter is i can specify the output directory so basically it's going to create a folder and then place those classes inside that folder but in my case i want them to just be created inside the root of the project so i won't specify that so just go ahead and click enter to run the command all right so with the help of ndd framework tools we have tried to reverse engineer the database and we've and we've created classes based on the table schemas inside so let's just go ahead and examine the files that have been created so here we have employee okay and we see the schema so based on the database table employees and we also have app data context okay so i'm just going to clean up this file and get rid of unnecessary stuff so here we have some methods let me get rid of this warning here okay so here we have some methods so we have on model creating so this is executed when we are creating the database for the first time and if we are trying to use the code first approach which means we first create the classes and then we create the database based on the classes now in our case since we already created the database we won't be needing this and we're only going to use one constructor here so we're going to get rid of this okay and one thing is to just um i just get rid of this virtual and make sure that the name of this property here is equal to the name of the database table here so we have employees and we have employee is here as well okay so we have employee class and data context so we'll be using this context class to access the data in our database so i'll go to the main window.xamar.cs file here and what i'm going to do is i'm going to create a list property and this list is going to hold employee class type so i'm going to just give it a name my employees okay so inside the constructor i'm going to now access the data in the database so i'll be using the using statement so i'll create a context class object so so that's app data context okay so i'll use this object that i've created here and the using statement basically means once this object is used it's going to be disposed and all the resources attached to this object are going to be released so i'm going to just query so using the context so context has got a property that's employees and we saw that earlier on in the context class so employees then there's a method which is to list method so basically what happens here is it's going to query all the employees from the database and it's going to return a list and that's the good part about entity framework we only we deal with queries like objects basically using link expressions so i'm going to now save this returned list inside this variable which is my employees so once those employees are stored inside the list i'm now going to bind them to the data grid control so that's employees list so this is the data grid control employees list and there's a property called item source so by setting the item source property to my employees list it's going to bind all the employees inside this list to the data grid control and we should be able to see that data so what i'm going to do now is i'm going to test and see okay all right so the application is up and running and we can see that the data has been populated inside the data grid control and when using the data grid it will automatically create the columns based on the properties of the objects being bound so we have id name address email and date of death okay and this is the same data that we got from the generate data website so guys thanks for watching and i'll see you in the next one

Channel: Tactic Devs

Views: 19,204

Keywords: MSSQL, Microsoft sql server, entityframework, datagrid, c#, wpf, .net5, controltemplate

Id: xNl-af-I0Rc

Length: 22min 7sec (1327 seconds)

Published: Tue Sep 07 2021