Simple C# Data Access with Dapper and SQL - Minimal API Project Part 1

minimal apis are now a default in net six i thought we should take some time and look at what they are how they work and what they might look like in the real world to that end we're going to spend two videos covering this topic this video is going to cover setting up a sql database and configuring dapper on the c sharp side for easy data access we'll set up a simple but full crud data layer so that when we build our minimal api in part two we can concentrate on the api itself and not the setup the great thing is that this lesson will work with almost any user interface in c sharp not just a minimal api now if it's the first video you watched of mine my name is tim corey and it's my goal to make learning c-sharp easier i provide videos at least twice a week here on youtube to help you grow as a developer i also have a full set of training courses on i am timcory.com i encourage you to check out all the resources that i have to offer now in this video as with most of my videos i'm gonna create some source code and if you'd like a copy of that source code use a link in the description all right let's go over to our visual studio and let's create a new project now first let's filter by our language c sharp and then we can do a search for api and choose the asp.net core web api hit next and recall this minimal api demo and we'll call this minimal api demo app as a solution name hit next and we're going to create a net six long term supported no authentication yes to https no to docker all right we are going to uh uncheck this notice here the scroll bit at least i did we have the zoomed in text for using controllers we're not going to use controllers we're going to use minimal apis that's we're going to uncheck that and we are going to still enable the open api support so let's hit create so we're creating the minimal api now but we're going to wait to work with it until later all right so we've got the minimal api installed but now let's right click on the solution and say add new project and we're going to search for the sql and since i've got c sharp selected it's not in this list but down here sql server database project is selected down below for other search results that's kind of convenient that i can leave c-sharp selected even though i'm looking for a project type outside of c sharp hit next and we're going to choose a database for this we're just going to call this the user db for the project name this will be the user database that we just put some simple stuff in we're not going to go crazy on sql we're gonna have just enough to work with um to understand the full crud operations for sql database now if you've never used a sql database project before i highly encourage you to watch another video i have on this channel on the sql server data tools that will go in more depth but really this is my recommended way to create sql databases now if you're using entity framework of course that's different but i tend not to use entity framework i have a whole other video that covers nd framework best practices and i talk some about why i don't use entity framework much but this way right here is awesome it puts your sql server development in source control so you can roll things back you can also have upgrade paths with your database you can even work with an existing database to then upgrade it from there on out using your um your project you can also do things like refactoring your database so if you change a a field name you can then have it go through all the stored procedures and views and all the rest and update that field name intelligently through that as well so really cool stuff you can do with this um i really like it a lot we're going to use it to build out our our database project so let's get started with the database first we're gonna sit right click on the database we're gonna say add new folder we'll call this dbo that would be for the the schema name dbo is the standard here and then inside there we're going to create a folder for tables we're going to create one table for this database like i said really simple so add table and we'll call this table just user pretty simple and in here we're going to mark first of all this is the reason why i like this designer you can make changes up in the design like you get in ssms the sql server management studio or you can go to the properties and make changes or you can come down here to the t sql section and make changes any of those changes will reflect everywhere so different changes for me end up in different places so i like to do identity down here because what this does is it says okay not only is this a primary key which has already been marked as a primary key and that's good but a primary key on its own is not enough because all it does is says okay that that value cannot be the same as any other value per row every row has to have a unique value in the id column but that's it so if you go to insert a record it's gonna say hey you've got a problem in your column because you're trying to insert a null value and you're like what do you mean i don't even insert anything in that column yeah that's right because you insert anything in that column so it says hey can't be a null value which is what you're trying to do since you didn't give me a value for the id column this is a pretty common error actually if you go to imtimcore you'll find a blog post i have about that exact error because it happens so frequently this happened this week someone asked me about it so what you need to do is mark your column as identity and what that does is says start with the number one and just count up for every record and normally in ssms what you need to do is come to your properties for that particular record and then you have to [Music] come to identity specification and then say yes it's identity and then say increment and seed value the seed value is the value start from and the increment value is how um much to go up per record so in this case we're going to start the number one we're gonna go up one number per row so sorry one then go two three four five six whereas if you said this number was five for that increment then i'd start at one and go a six and then 11 and so on so with that we now have that makes like i said this is how you do an ssms it's a little bit of a longer process where i just type identity down here and it solves that problem but we're going to use that for our id it's a pretty common thing to do and now we're just going to have a first name which is going to be an nvar char 50 not null and then a last name and varchar50 not null that's it that's our entire table specification for the user table so it's just an id a first name and a last name like i said we're not concentrating on having a complex database here we're just concentrating on having enough of a database in order to have full credit operations with that let's create some stored procedures to contact or work with this data now i use store procedures let's right click on dbo say add new folder we'll say stored procedures i use stored procedures because the fact that they are pre-scripted logic and i tried to do too much with the actual business logic in there it's just basic basic sql insert update delete and and get records or select records but with that sql can then compile them and it can optimize the query plan for them which makes them a little faster than just running your ad hoc sql statements so sql server's got a lot better about running ad hoc sql statements and so it's getting closer but it's still a bit optimized to run stored procedures the other thing is it's very easy then when there is a performance issue in sql to find out where that performance issue is because your log statements will have the stored procedure name instead of this big long sql statement that you have to figure out who called that sql statement this way you can just go oh it's that store procedure i can optimize that table or whatever i need to do so there's a little bit of you know sql insider knowledge there so right click and say add stored procedure and we're going to call this first store procedure sp user underscore get all now it gives us a structure for this where i get rid of all of our parameters and i like to have a begin and end in here what the begin end is it's like curly braces for sequel so it's saying start at line three end it right now line five everything in between is what is really the the actual meat of the t sql statement the rest is just kind of set up stuff so select star from dbo dot user and actually i need to put user in square brackets because of the fact they called it user and that's a can be reserved used name so square brackets takes care of that for us all right now um sometimes you can get the intellisense to work here with with this it's not always the case about problems in uh the new 2022 but we can also do just in telesense here and and get our our list of things so like i said normally you can say hey select star then change that refactor that to be the three names but um it's giving me some quirks in this one so i'm just gonna type them out so we have here our first statement this is selecting all the records from the user table so if we wanted to say hey give me all the users this will do it next up we're going to create another store procedure and we're going to say sp user underscore get and what this will do is it will get one record i'm going to copy this from beginning to end or begin to end so we have this but now instead of just from user whereas say where i id equals at id which we don't have yet we're going to set that up at id int so we're saying here is i want you to look up one particular user by their id so we're going to pass in that variable which is going to go right here and we're going to say give me all the users where their id matches this id which will only return one row or zero rows if there's no match so there is our our um select one record we'll right click again and say add new store procedure and we'll say sp user and then we'll say let's do our um let's do our delete first underscore delete because the fact that delete is pretty close to our our search for one so copy the whole thing here and paste it here but unlike where we're just doing a lookup we're going to say delete from user where id equals id so we're going to do a delete that's based upon the the id being passed in say delete this record now be very careful here with a delete because if you forget the where's the image and just run this then you'll delete every record in the user table it's something that pretty much every sql developer does at some point in their life but um i want to point that out so it may save you some pain so that's the delete record now i do want to point out as well that we don't often do this the actual delete where normally what we do is we'll say archive where we we flagged the record as no longer being an active record or we move it to a archive table or in some other way we de-reference the the record we don't typically delete records in tables because of the fact that that can ruin history it can cause other issues so we don't typically just delete records that's not something that business does a lot but i do want to show it here in our full credit operation just so you know how to do it that's a question i often get is well okay you've showed me an insert you showed me a get but how do i do an update or delete well we're going to see all of these in this example so next up right click on the store procedure folder say add store procedure sp user underscore and let's do our insert now for insert it's a little different we need to insert is two things we insert the first name and that is an nvar char 50 and last name and that's an nvarchar 50. and now we can say insert into dbo.user which that's actually user like that and we're gonna say insert into dbo user first name last name values at first name at last name like that what we're doing here is we're creating a new record notice we're not passing in the id because we don't give the id of an inserted user we just essentially let the system generate it for us so we've asked for the first and last name we have said we're going to insert into the user table these are the fields we're inserting into and then we have the order specific values being passed in so first name goes in the first name field last name goes in the last name field because that's the order we do it in so we could not swap last name for first name and have this work unless we also swapped this last name that the one in the definition to the first name in front of the first name so that's our insert and we have one more so we're adding a new stored procedure we'll say sp user underscore and this is the update and for this one we're gonna need three parameters id int first name nvar char 50 last name nvar char 50 and then we do our begin end where i say update dbo dot user set first name equals first name comma last name equals at last name where id equals id so what we're doing now are passing in all three values the id the first name and the last name because it's an existing record and we're going to be updating some values now note we're not updating the id the id never changes so we're not updating that but we are using it to locate the record that we're trying to update and we say okay overwrite the first name value with whatever they passed in for the first name and the same last name for the last name so that's our very simple crud operations where we are going to be doing some insert work some update work some delete work and so on now i am going to do another thing that's really cool with our um with our database project that is i'm going to right click on it i'm going to say add and we're going to do a script in this case we're going to do a post deployment script so it's going to run after we deploy our script let's take the one off here because script.postdeployment.sql is fine for the naming you can only have one post deploy script you can't have multiple post deploys so therefore everything has to go into the script now it has this little flower box here of all things you can do inside of this or how to you know examples we can get rid of that and we're going to do instead is we're going to have an if statement now this is t sql code this is for running on sql server so the syntax is not c sharp if not exists saying okay if this does not exist i'm going to put in parentheses select uh 1 from dbo dot user begin end so what i'm saying here is hey if you don't find any records if not exist we do this query now why one well we don't care about any columnist return and this in theory can be just a tiny bit faster than saying select star so we're saying we don't care about anything we're not going to ask for any columns we're just going to give it a a hard code column that is one but we're just saying hey for every row in users return a one okay so if it's a hundred rows it's going to turn a hundred rows that each say one now why do we do that instead of saying select top one well because top ones actually slows things down a little bit more and slice star technically tries to figure out all the columns and that's i think why it's a little slower so select one just seems to work that seems a bit the simplest thing to do so we're going to say hey just just give me all the rows and users and the column doesn't really matter so with that if there are rows then it will exist therefore this is going to be false therefore it will not run whatsoever between these begin and but if it does not have rows then it does not exist then it will row what's run whatever between begin and end well we're gonna have inside out here insert into dbo dot user first name last name values we're gonna hard code some values in here this is for our demo purposes to get us up and running so let's start with tim and corey like so and then i'll put comma and put another set of parentheses this is a pretty cool feature because we can insert not just one row like here and like what our insert sql statement is doing but instead we can put a comma and put multiple rows in one insert statement so run each of these as an insert record so sue storm and then we'll say let's go with uh john and smith and last one we'll do mary and jones like so and that just gives us some sample data to work with but here's the cool part is that we can even leave this alone for production because if the production table already has records then it will not run this script remember it has to check it checks first to see if they already exist if they do if any record exists then it will not do this insert so this will be fine in production unless you're starting from a blank database in production and then it would do an insert um still so you have to either delete those records or you'd have to not run this script but once you've got something in the user table then this will not execute and it will only execute once because once it executes the four records in the user table therefore this will no longer be true therefore will no longer run this code so it's a nice little uh way to get your database up and running when you're starting a new database and to have some sample data to get started with okay so that is the kind of first part of our demo now let's go ahead and publish this we're going to right click on this and say publish and it's going to say okay where do you want to publish to we're going to say edit waivers come up go to browse select the local we're going to choose our ms sql local db which is going to set up some basic information we just hit ok down here and there is now our connection string to what is a local database now this is a local database that's installed with visual studio if you don't have this you probably install the sql tools um with your visual studio that means you just you go and modify your visual studio installation to include the sql tools and then you can follow along with this demo you probably already um hopefully you follow along this point because you have to have sql tools installed in order to do the sql database type or database project but now that we have this connection string we can name this database whatever we want in this case i will choose our minimal api user db just be very clear as what it is because i've got so many sample databases that accumulate over time that i'm very clear as to what it is now the publish script name is just userdb.sequel that's fine and now we can either publish it if you want or here's what i do i save profile as and it's going to choose to save it somewhere in this case by default it saves it into the root of my project i'm going to say save and now notice that i hit cancel here notice there's a a publish file down here now if i double click on it it's going to bring that published dialog back up with everything filled in so it's already set up it's already ready for us to go the only thing to be a warned of is if you have source control on this that this by default will not be included in source control if you're using a proper git ignore file and you want to not include this but that does mean that every user has to recreate it on their own and that's okay because the fact that it will be user specific anyway and especially if you had any kind of credentials in there you don't want that so you would not want to have this in source control so with that let's go over to the view menu and we're going to look for sql server um object explorer i believe yep there we go and it's going to open up for us the sql server databases on my computer notice they're all local db essentially these are just files that can be executed like sql databases so we chose the the first one the ms sql local db server and in here in databases we have right now a few of them including a user db so that's a good thing i chose something different for the name i chose the minimal api userdb so with that let's go ahead and now you have this open we can double click on publish we can watch it actually publish this is going to create our scripts for publishing and that's going to publish that script against our server which is going to create a new database so now you can right click on here to hit refresh we have the minimal api user db and in here under tables we have a user table so we can open this up with view data and see that we actually have users in here so and they're numbered one through four these are the first four records we also have under programmability stored procedures we have our store procedures which are essentially the same thing we just we stated before so nothing really crazy new there but now we can use this database and call against it with our c-sharp code so that gives us a good starting point for our api when it comes to the database but now we need to talk through hey how do we get this set up so that we can talk to it well what i want to do is kind of minimize this and we're going to right click on the solution and say add new project and we're going to add a class library so we could search in here and it's actually right here so i'll just select it or you can use a search box but class library and i'm going to choose let's call this our just data access is fine and yes.net 6 which is the lts or long term supported version i'm going to delete class 1 that's my standard thing to do because i don't want to have to rename things every time i'm going to delete that class 1. now i before going further let's close out of everything here i do want to add one more thing that i'm going to cover in another video but i've talked about already once you right click on solution explorer not a project but solution explorer and say add new edit or config and it it causes this error the first time when it tries to load it just close it out we can unpin that and then double click it again and it opens up with this this dialog now this is a really nice dialogue for us because it gives us our options it allows us to make some choices about our project our projects and how they should work so editor can phase really cool for allowing teams to standardize for a solution or a project and this allows the team to have even different standards per solution or per project and be okay because the editor config will say okay for this project you're going to have a tab size of 2 instead of 4 or the indent size and so on but we're going to go over to code style and we're going to expand out these these options here and one of them and noticed that the severity is hey just refactoring but i want the namespace declaration instead of block scoped i want to file scope the difference there is whether the namespace wraps the entire file with curly braces or whether we have the namespace up top as just a using namespace name and that's it so we're going to use the file scope which is the newer way of doing things and by doing that we now whenever we create a new class in here we'll have that new um that new layout for our file i like it because it saves us about four spaces for every line in the entire project or every time sorry entire file there we go um so we'll be using that from now on probably but if you decide hey i don't like that go to edit config go to code style and change the namespace declaration over to be block scoped and then you're done now if it's already files have been created a different way you'd have to change those over to the way you want them yourself but there is a quick actions and refactoring to make that a lot easier i can show you that in a minute so let's create a folder structure in data access so right click and say add folder and the first folder will be models and then we'll have another one that we'll call how about db access for database access and then another one we will call data okay and that's going to be our basic structure for our class library yes you could get more in depth here depending on your project type but we're going to keep it really clean and simple for our data access i think you're going to like how this lays out now before we get any further here let's create the model let's just create the model because that way you can see the the structure here so let's right click and say add new class and we'll call this model the user model and it did not create the right structure for me that's interesting because i do have my editor config applied and my code style is file scoped it's interesting it did not create the model properly i i'm not happy about that but here's the thing i can show you the quick actions and refactoring so i've selected the namespace and i can hit the control dot and say convert to filescope namespace like it should be and how it is that namespace is specified with a semicolon at the end and it can indent or move these back to the the far left hand side instead of indenting them four times but again if you didn't like this you could use the quick actions refactoring to convert back to block scoped okay it's going to just indent everything and wrap everything that curly braces okay so the editor config should apply it didn't i don't know why um i think that's a bug but it will apply in the future it should apply so with that let's create a user model so prop tab twice whereas say int id and we're going to say prop string first name and we'll hit uh enter and because i hit enter the intellicode says hey you know what it looks like you might want to have a last name as well i'd hit tab to accept and i'm done okay so it has taken care of for me i could keep going on email and so on but it's taken care of for me all of the the settings for that because it says you know what i think that's what you want so i'm going to guess that and allow you just hit tab if you wanted and if you don't then you know do whatever you want but that's a really nice feature of our intellicode now so there's our user model there is the file scoped namespace as opposed to the block scoped and now we're ready to start adding some nuget packages to this data access project now we want to do is talk to sql and we want to get data from our sql server or send it to our sql server in as easy as way as possible so we're going to use three nuget packages so right click and say manage nuget packages on our data access project the first one is dapper and this is from the oops not installed let's go to browse this is from a team who builds stack overflow they create dapper to make data access really fast and it's a micro orm okay so latest version i'm gonna install is version 2.0.123 we'll hit okay and now we're going to go back to um install and look we just have just the one project installed but now let's come back to browse or select the system.data dot hey hey it's already in the list um it was on the list before i started searching for it because of the fact it said hey you know what you've installed dapper so you probably want this one that's a pretty common one that people are installing nowadays so yes this is the only want um and so we're going going to install this the latest version is 4.8.3 we'll hit install and yes that's system.data.com see i agree and the last one we want to install is the microsoft if i can spell right microsoft.extensions dot configuration i'll stop there for typing because this one right here microsoft.extensions.configuration.abstractions this will allow us to talk to the appsettings.json or wherever else our project loads in so we're going to choose the latest stable version which is 6.0.0 and hit ok so now with those three things installed let's go back to our installed those are the three things we have installed note the version numbers just in case you install a different version number or note the names specifically because if your name is slightly different that's not the same thing so those are three we have installed and now we're ready to start doing some uh data access or talking to sql server so with that let's right click on our db access and we're going to say add new class and we'll call this our c our sql data access in this class what it does is it talks to talk to sql i notice it got the name space right look at that i'm not sure why it didn't for the models but this talks to sql directly this talks to sql through dapper and so i create a generic interface for this so that our code doesn't have to have all of the setup and teardown of talking to sql not there's a lot with dapper but i want to keep it as simple as possible so let's make this public we're going to be talking outside of this class library and we can get rid of all these usings instead just add a using for system dot data dot sql client okay and then down here we can say i want to have a constructor ctor that's a snippet for constructor hit tab twice and then in here we're going to say i configuration we need to have a control dot here for using microsoft.extensions.configuration so it's going to add a second using statement up there and then we're going to do is we're going to say let's call it config that's the value being passed in we're going to hit ctrl dot i'm going to say hey create and assign a field for this now i have not yet configured my visual studio correctly i have a tweak for this notice it did not put an underscore here which i prefer so we're gonna do it manually this time but i will show you in a future um 10 minute training probably how to make it properly do the underscore for our private read only that's my convention it's a pretty common convention to use but that's the convention we're going to use for private variables that come over from our dependency injection system and that's what's going to happen here is this will come through dependency injection so it's eye configuration is where we get our data from appsettings.json but it's more than that because it also gets settings from secrets.json appsettings.development.json.production.json it also gets information from the environmental variables and other locations as well you can add things like uh key vault and so on i actually have a full course on all the cool stuff the app settings can do because they're way beyond what we had in dot-net framework so much better than we had dot-net framework that's not even funny it's just so powerful right out of the box and then you can add even more power to it if you want so down here let's uh actually you know what let's add a couple more using statements but i did forget a couple uh using dapper or i used dapper and also using system dot data so i'll use those four uh i forgot to add a couple of them that's why this one wasn't here when we had to fill it in okay now with that let's add our two different um our two different methods we're going to use one is load data and one is save data now we're going to use async methods for these if you're not asynchronous calls essentially the the very basics of it are the async and await what it does is it basically abstracts away all of the worry about threading and figuring out how to optimize different threads and callbacks and all the rest into a really simple set of keywords so for the most part we don't have to worry about all that setup instead we just get the benefit of not running on the main thread potentially so the benefit though is we're not locking the user interface up while we're waiting for calls to happen so we're going to not lock up the ui while we're calling to our sql database so we're going to say public async and it's going to be a task of type i innumerable of type t and nope does not get table we're going to call this load data and the types will be t and u and yes we're using generics here and you may say i have no clue how to use generics well the good thing is i have a whole video on this channel on generics so feel free to watch that video it's really powerful stuff you can do with generics so next up we're going to say insiders for parameters we're going to say string stored procedure that's the name of the stored procedure u parameters the nice thing is that's a generic which means we can pass in pretty much any type there and then we're going to say string connection we'll say connection id and we're say equals default now i'm going to let's unpin this first for a minute i'm going to shrink this down because that's just too long we'll use the quick actions refactoring here ctrl dot to bring that up and we can um we can do the the wraps here wrap every parameter and they can see how it's going to look in the different ways it can do it we can um wrap it like this we can wrap it like this i think that i like this one better just because it's gonna give us a little more space or we can indent all three so that's now our wrap there makes things a little easier to see without having to scroll horizontally especially since i'm zoomed in here okay so now we have this call it's a little complex it's not exactly a beginner level call but let's walk through what the the signature of this method is first of all it is asynchronous which means that it'll it will run something in here will run asynchronously then it's going to return task which is what all async methods should do and then the task is of type i innumerable of t what is t it's our first generic and it is whatever type we want to return to pass in a model and say hey i want you to fill in this model in our case it's going to be the user model we're going to pass that user model in and say hey i want an i innumerable which is a set of this model returned from a database call to my sql server so we'll pass in user model and then we'll say the stored procedure name is and we'll choose one of the where's our procedures here we'll choose one of these store procedures here probably uh sps or get all so we'll say sp user get all and return me a set of user model and it will populate that with all the rows in the database and then we have our parameters which that is going to be hey if you want to do a get instead of a get all well then we have to have this get requires an id so you have to pass that id in as a parameter and finally the connection string i or the connection id and that is the name of the connection string so in our our minimal api demo we have our app settings.json in here we're going to have a connection string section and in there we'll have a connection string that whoops um i closed the wrong one but in that they'll have connection string that will have a name of default and then the value of whatever the connection string is to this database so that we'll do for that and then if we decide to use a different connection string name we just have to pass the value in a connection string or connection id instead of default but by default the name is default so let's write this code using idb connection call it connection equals new sql connection and we'll pass in the underscore config dot get connection string for connection id so what are we doing here well first of all right here we're doing a lookup on our appsetting.json file or or that whole stack of files we're looking for the connection string that's a special keyword lookup there looking for the connection string with the name of by default its default but whatever name we give it it's gonna return the actual connection string that it finds we're gonna take that value and we're going to send that as a new sql connection so we're going to pass in a connection string of sql it's a create that sql connection and that's going to put it into this connection object and we're using a using um a using method here so that what it will do is it will say okay when this curly brace right here online currently on line 23 when that curly brace gets hit i want you to properly shut down the connection and if there's any type of disruption along the way an exception or the app shutting down or whatever it is i still want you to properly shut down that connection that's what this using is helping us do right here normally we'd have a set of curly braces that wraps our our use of using we don't have to do that anymore we can now just say using and it will shut down at the end of the method instead of at the end of the curly braces around our using so that's very important to have when talking to a database because you do not want to leave a connection open accidentally so now we have it using we're going to say return await connection dot query async of type t we're going to pass in store procedure and our parameters and then we're gonna have a command type be i a uh command type dot store procedure and again we'll we'll go ahead and just hit enter here and make this a little prettier like so that line what it's going to do is asynchronously talk to our connection which is our sql server and say hey execute that stored procedure the one we just passed in here's the parameters to pass into it and yes that's a stored procedure not just sql text we're executing when it does that it's going to return an i innumerable of type this which in this case is t so we're going to get that i numeral back we're going to wait for it we're going to return that i enumerable so that's what the load data does is it connects a sql it executes the query and returns an i enumerable this is why i love dapper because really we've got two lines of code here that that's all it takes to connect a sql send a a command over a sql have that command run get back a set of of rows put those rows into an i innumerable of our our type we give it our model and return that data back so really simple to talk to sql that's all our load data does and now we're going to create another public async task called save data notice it's just task and type t string in fact you know what um let's do this let's i'm going to copy all of this because it's the same thing we're passing in um all of our whoops this is to be t not u we're passing it everything else like normal so for save data it's just like load data except we're not passing in the t which is the model type because we don't need it we're just calling a stored procedure which would be our insert our update or our delete queries and we're passing in the parameters which we now call type t these name these names can be any letter you want um but by convention the first one is called t so i'm just using that same convention so we only have one there for t is the first one whereas up here we had two so t and the next letter in the alphabet is u you can call them a and b you call them x and z if you want it doesn't matter but the convention says t so the t is now the parameters being passed in and again that connection id so again we're going to use this same line right here to have our using statement connect to our database and you might say hey you know what that seems like repetition is there a way to kind of abstract that out and not really not not really i mean potentially maybe but it seems like a whole lot more work than it really is saving because really what this is is abstracting out everything into this method called actually this class with the connection with the um constructor but it's creating that whole connection for us in one line therefore trying to abstract out this one line in to something else in order to not repeat one line so you have a new one line that calls the one yeah it doesn't make sense so we're not gonna abstract it out so dry is not being violated here by doing this because we're already we've already abstracted out our code into one line now down here with our task we can say await connection now notice it's a little confused is what we're trying to do here it's okay execute async the intel code's not always gonna get it right but the more you work with it the better it will get especially as your code base grows so execute async because in this one for save data we're not asking for data back we're not getting set of records back we're just saying hey do this work so execute async where i pass in our stored procedure and our parameters and then also the command type oops command type of stored procedure and again we can align this better we just delete it f4 there we go so now we have our save data and again same thing it's really simple it's just basically two lines of code and we're doing we're calling our start procedure which is going to be an insert update or delete and then we're saying in our parameters which would be for doing insert be first and last name if we're doing update it's id first and last name and if we're doing a delete it's just id and then we're saying yes this is a stored procedure not just a bit of sql text that we've written in c-sharp so that's our data access library that's all we have to do for talking to data access now i am going to select my class and hit control dot and i want to extract an interface this will allow me to put this in a dependency injection very easily and i've done that now and notice it's now implementing that interface and that interface it just has two types the load data and save data but now i can reference it everywhere as i sql data access instead of data access and that will allow me to use our dependency injection easily okay speaking of which let's right click on data and say add new class and we're going to say let's call it user data now what's going to happen here is we're not going to talk to the sql data access directly in our api project instead we're going to talk to these things in the data folder so the user data will be where we talk to to get our user data let's make it public and where i create a constructor we'll say um you know what we can do is we can come up here and say private read only i sql data access control dot to add that using up there and then we can call this underscore db and then we can say control dot and we can say generate constructor for this like so we can actually get rid of our constructor down here um that's the other way of going to the reverse way of what we did okay so before with the sql data access we said i configuration config then said control dot here and created this private read-only eye configuration over here we said created this and then said hey i want this from the constructor and so it creates a constructor with this in it so you can go either direction on that which is kind of cool now again i sql data access is the interface we just created for our sql data access which means we now have access to both the the load and the um and the save data so now in this user data we can start creating our different methods for talking to our database i'm going to do this a few different ways but i'm going to try and keep them as compact as possible now with minimal apis one of the the cool things is how few lines of code you can write in order to really make something pretty powerful we're going to kind of stick with that i typically am more verbose with my code but i do want to show off that you can be pretty lean with your code and still write understandable and readable code okay so we're going to do that with this but i do want to show off the way you do this but i just want you to note that you can be more verbose for sure and it's not a problem and in some ways if it works better for you to make it more readable then go for it so this first method is going to be the get users so public task i innumerable of type user model control dot to add that using and we'll say get users okay so this this method right here is going to be to get all of the users now we're going to use the arrow here and then i'm going to do a new line because it's too long otherwise underscore db this is a talk to our i sql data access and we have load data user model and pass in a dynamic okay and dynamic means that we could pass in with any type of of parameters with values which in our case mean none so we have our stored procedure name which is dbo.spuser underscore get all and whoops we're gonna say new open close curly brace the reason we're doing that is because this is our parameters we don't have any parameters but we have to pass something that's why i said dynamic and we're passing an empty anonymous object so with that semicolon at the end we now have our entire method call for git users so i'm not sure why it's yelling at me here um inconsistent accessibility type guess what we probably have our user model is internal aha so whenever you get that message where it says inconsistent accessibility and it says return type is less accessible than the method what it's saying is either something in here the task innumerable user model something in there is not public if this method is public so in this case the only that's really modifiable by me is not the task or i innumerable it's the user model and that's marked as internal public my mistake and now we're good so this right here this essentially one line but i put in two because it's always too long this is a entire method now notice there's no curly braces that's because this one line right here until a semicolon is all i need and so it's going to do is this call right here is going to take that return and send it out so the load data returns a task of i innumerable of user model well this get users is expecting a task of i enumerable of user model so therefore this is good this is going to return that and since we have just this one liner with a semicolon at the end it's going to assume that the value being produced on this one line is the return type and it is therefore we're good now this right here this is why i love to do a little bit more abstraction around dapper because now my load data method is one line that's it i have one line that says hey load the data from this stored procedure and put it into an innumerable of this model done one line we're done so that's the entirety of getting the users from the sql database loading it into our innumerable and returning them to the caller next up is our public async task of user model i'm going to say this is a nullable user model get user of type int id now i am going to put curly braces here because we have to do two lines of work so um that's that's why i'm doing the two line or the multiple lines for the curly braces now i could make this a one-liner but it just makes it a little too confusing to do so i'll show you why so var results equals await db dot load data user model dynamic oops and we'll put this on a separate line here we'll say dbo dot sp user underscore get and i'll put the next line we'll say new and we'll pass in our id but we're going to change the casing of it like that id equals id so what are we doing here well we're loading the data we are passing we're asking for back a i innumerable of user model even though we're only get one record back or zero it's still gonna return i innumerable which means a set of user model we're passing still passing in dynamic for the parameter type so you can just say hey new anonymous object and the id will be this value which is when being passed in and there's a store procedure name now it's going to give us back a i innumerable of type user model and it is going to be nullable because it might not have a value now and let's actually back this up one we're going to say return results dot first or default what that will do is it will either return the first record in our innumerable or it will return the default value for our user model which is null so since this is a a set we're not returning a set we have to return the first value but if the first value of our if our i innumerable is null then our first value can't be found because the whole set is null well in that case we're gonna return the default value again no so that's why i put clear braces because it'd be two lines if i want to make this one line what i would do is wrap from here all the way the end in parentheses and say dot first or default because you have to await it first before you then say first or default so that's how you do it in one line i'm going to choose to do it in two in two lines and that's wrap it with curly braces but you could do it in one line next up we're going to have public task insert user and we're going to say user model user and we'll again have our arrow and a new line we'll say underscore db dot save data it's dbo dot sp user underscore insert new anonymous object and we'll say user dot first name and user dot last name like so because that's our insert let's hit tab there that's our what our insert needs it's first name and last name now that comes from the user object but the user object has first name last name and id we don't want to pass that additional value through because the id will be zero because we're not going to fill in that id but even if we filled it in we don't want to take it because of the fact it's an insert an insert should not pass in the id therefore we have to create a new anonymous object with the first name and last name but notice i didn't say like up here i said id equals id here i didn't do that because the fact that the parameter names are first name and last name the capitalization is the same so i pass in user.firstname if i don't say equals like firstname equals user.firstname if i don't do that then what happens is it assumes that the val the name of the parameter will be the property name so it's going to assume the property name is first name therefore i can shorten it like this that's all we do for insert now for update public task update user notices it's learning from my pattern so i just hit tab update user user model user and then we're going to hit enter here and say db dot save data and we'll pass in dbo dot sp user underscore update oops and we'll say user now why did i do it that way why did i pass in the whole object instead of a new anonymous object well because i'm going to use all parts of that model i'll use the id the first name and the last name therefore i pass the whole model in because i can pass in whatever type i want and it will pull out the properties and their values so this case is going to pull the properties of id first name and last name use those in the parameters for the correct parameter values finally let's look at the delete and look at anticipated public task delete user yes that's exactly what i want to do and then i'm going to say db dot save data and we're going to say dbo dot sp user underscore delete and we're going to say new anonymous object whereas say id equals id and that's all we have to do for the delete user so we have now created all of the methods for our crud access we have two different gets a get all and a get one we have our insert our update and our delete that's all the code we have to write and now in our api we don't have to know about dapper we don't have to know about sql all we have to know about is that we want user data so we'll say hey instantiate the user data class and i want userdata.getusers and it's going to return us back a awaitable enumerable of user model and the same thing with all the other methods we don't have to know with a database which means that down the road if you want to change this over to say mongodb or cosmos db or sqlite or mysql whatever you want to change it to not a problem we would just change the i sql data access over we could even make this more generic and say this is just i data access or i relational data access probably because it's a relational database type structure and we can use it for sql sqlite mysql and quite a few others but we'll leave it like this for now and the only thing left to do now is on this class this user data class we can hit control dot and say extract interface hit okay and now we have our interface for our eye user data this will allow us to put this into our dependency injection system in our minimal api which will allow us to just call for this eye user data and get access to these five methods which align very nicely with our crud operations so that was a lot of work and there's a long video that's why i broke it into two but this we have now created our user database for our sql server we have created our data access to talk to that sql database and we've abstracted away even knowing that it's sql and now we just say user data now why is this so very powerful is because no other project now needs to know that we're talking to sql no other project needs to have access to dapper no other project needs to have access to anything except for really the um the user data class that's pretty much it and if we had more tables we'd have more classes in the data folder for those tables or for those interactions and then we just call those and again it's so it's so abstracted away that we just say hey delete the user i don't care how it happens just do it and it will same thing with git users just keeping all this users i don't care how it works just do it and therefore our minimal apis can be very clean and simple and we can now reuse this class library this data access library in razer pages mvc wpf win forms console applications services worker services pretty much anywhere blazer server we can't use it in blazer webassembly it's the only user interface type we can't use it in because of the fact that it talks directly to sql and that's something that blazer webassembly can't or at least should not do because of the fact that it's not secure since it's fully client-side but it could talk to the api which then can talk to sql server so pretty much any user interface type the exception again is our blazer web assembly can use this data access folder and talk to our sql server the same way okay so we're all set up we're all ready to have a an application use our data access talk to our sql server our sql server even has data already in it ready to go now in part two of this series it's just a two-parter this is part one part two we will wrap this up by using our minimal api to talk to our our user data and use that full crud operation in a really simple way i hope you stay tuned until then thanks for watching and as always i am tim cory [Music] [Applause] you

Channel: IAmTimCorey

Views: 32,229

Keywords: .net, C#, Visual Studio, code, programming, tutorial, training, how to, tim corey, C# training, C# tutorial, C# app start to finish, .net core, dependency injection, .net 6, minimal api, dapper, c# dapper, sql, sql database, sql crud, micro-orm, orm, dapper crud in .net core web api, c# dapper tutorial, web api crud using dapper

Id: dwMFg6uxQ0I

Length: 74min 8sec (4448 seconds)

Published: Mon Nov 22 2021