Advanced Dapper in C# - SQL Transactions, Mulitple DataSets, UDTs, and more

if you've watched my videos that include data access you're probably familiar with dapper Dapper is a micro ORM built by a team behind Stack Overflow the purpose of getting data into and out of databases it's quick it's easy to use and it's easy to debug in this video I'm going to cover some of the advanced scenarios that you can use dapper with including user-defined table types transactions and multiple data sets and even more now if you do this channel my name is Tim corn it's my goal to make learning c-sharp easier there are a number of ways I try to do that but the main one is this YouTube channel I also ride training website where I have courses that will help you out as well if you come across a topic and c-sharp or a related area like sequel or git or another that you'd like me to cover let me know add to my suggestion list most of my videos are directly based upon feedback from my viewers okay let's get started in Visual Studio this week's source code is a bit different than most the codes are written now let's be going over it it's a couple reasons that first this code comes to my conference talk at the past summit we won't be covering everything from that talk but I've added some more content based on the feedback from that session second instead of typing a talk by walking you through the complete examples then I'll let me show you the differences rather than just typing similar things multiple times in this video now if you want this source code follow the link in the description to download it and to get other resources ok so we have a couple things in here we have our this is our sequel server data tools and if you're not familiar for data tools I've got a video on that on this channel and this allows me to create a small database that I can give you that you can deploy and work with right away so this is a really handy little tool then we're using a lot of my videos that need databases so just to kind of show you what it does we've got a couple of tables oops tables there we go we have a person table and we have a phone table so a person has ID first name last name cell phone ID and then the phone table just has an ID and phone number now we have a couple of store procedures the first one the easiest one is this search which just takes a search term and it looks for a person in the table where their first name or last name is like that search term and then we had a little bit more complex one in the SP person on our score insert set now this is what we're going to cover today and it uses a UDT or user-defined table type and what that is and I'll show you right here what that is is basically you create type kind like string or int or something else but in this case in sequel it's a table so that's the type so create type the name and as table and one that allows us to do is define a new data type that we can use a pass in to store procedures so if we look notice its first and last and its points who called we have in this UDT so in here we're saying SP person insert set we take in that table type so what that allows us to do is pass instead of passing in one row at a time for an insert so you call a store procedure like five times or entering five records instead we're going to insert into a table a whole tables worth of data so we'll just have give me the first last name from the people variable which is that UDT so we'll see that in a little bit now one last thing I want to show you in the the sequel server data tools project is this right here load data script so what this does it's it basically it's set up okay it's not something you have to know the syntax for this is just it just works and so we're I'll leave it alone the way it does is it clears out the person table and then it inserts five records in that table also deletes everything it's a phone table and then inserts some values and that wires them up to two users so the two users have phone numbers so it really just sets your data up we don't have to worry about it but just know that it's there and that's how that's the you know kind of magic that creates the tables with data already in them so we just run this publish scripts and double click here and say publish and that will apologize database for us and we should see over here in our database list in sequel server object Explorer that we have DAP our DB and if we open up the person table and view the data we'll see we have those five records and there's a cell phone IDs for Tim and Jane okay so that's a sequel project now the other project when I look at is the let's look at the helper project first just we can get out of the way it's pretty simple we have three models so we have the phone model which is just ID and phone number we have a person model which is this first and last name we also have this full person model and this is the one we use today it inherits from person model so it has the first and last name but also has the ID and it also has this cell phone which is a phone model object and that's one of things we're gonna do today as well is we're gonna populate this full person model from dabber which means we have to populate this object model inside of our main model so we'll see how to do that and we have tools down here which is just get connection string that's all it does and that connection string of course comes from the app config which is pointed at our local DB so it should just run rather gate as long as you built that database like I just did so now let's close all of this out and look at our demo one which we're not gonna cover today this is the basic stuff and this you've probably seen before but if not these are all labeled so reading with the app er there's a basic right you know ready and set basically right rheya set there's some pretty simple stuff here and some a little bit more advanced but it still kind of dapper basics so if you have a question about how to reuse announce parameters well there's your example for you and you come down here and open up this source code and see well you just do this right here and you pass it in so it kind of explains how you do it but this is not what we're in cover today instead we're gonna cover demo two which is the advanced stuff so in here I have a number of methods and each one does something a little different so I start from the top and move our way down so map multiple objects what I mean by that well this is that tricky one where we have a full person model then you want to populate it including that phone model inside of it so here is our sequel statement so let's read this through first just we understand what it's doing let's make it a little more readable there we go so we are selecting PE dot star and pH dot star what are PE and pH well those are those are aliases for our tables so from DB o dot person that's one of our tables we we named that we aliased it as PE so person is P so giving all the people columns first and then left join on phone which means we're still yet all the people records even if there's no matching phone but if there is a match then we'll have values in these columns and that link is based upon the cell phone ID matching the ID of the cell the phone table so let's just look what this would be let's just pin this over real quick and we'll do a quick query here so let's just click here and create a new query and so what we're doing we're saying select p/e dot star comma pH dot star from DB o dot person PE inner join DB o dot phone pH on let's do the on down here on PE dot cell phone ID equals pH dot I D so that's that's our query and if we run this the results are whoops I didn't enter join this is kind of good example so an inner join will only give me two records because there's only two people that have cell phones and that's Tim and Jane but they'll left join if I run this notice I get all the records from the person table all five but I get nulls here if they don't have a cell phone that's what you want we want to have nulls here if they don't have a cell phone but if they do we want the the ID and the phone number now really we don't need this column a self ID or the ID column we just need the ID column for the person and then the first name last name and phone number but that's okay we can we can deal with having extra information because that we will capture this and and our mouth count lines it so that's the the call of sequel but how do you do this in dapper so I already have a connection open let's just unpin these I already have a connection open to dapper and it gets my connection string which is that that helper method Nance is okay I want to do a query which were used to but we're used to passing in one type and say okay there's my model give me a list of that or actually an ienumerable of that but in this case are passing in three and then we'll slowly skip over that for just a minute then we pass it on sequel statement well we got that that's right here piece of cake and then we have this mapping right here and that's a little confusing so let's let's talk through what this is doing so it's saying we have two models that are coming back from this sequence data we had a full person model and we have a phone model okay so that would be the full person model this is the phone model these two are coming back but then the result we want is to have an ienumerable of full person model so that's the the end result model we don't want phone model with full person inside of it we want full person with phone model inside of it so that's that tells dapper okay here's the the priorities here we want these two models and this is the resulting model now what we're saying next is how do you map these two so we're saying okay this is a anonymous method that's it's a lambda okay so we have the arrow function here or the equal to greater than and so we're declaring these variables and these variables can be anything you want the X&Y they could be anything but I decide to call them person and phone because person and phone that's what it maps to and it makes sense as far as reading it to go okay instead of saying X dot I can say person dot they understand this is the person model so we're saying passed these two variables in these the two different models that are on our select statement and then we say here's how they map together person dot cellphone that's the that's the parameter or that's the property that has our phone model okay that's where it lives the film model lives person dot cell phone equals phone so put phone into the person property called Silva and then return person because that's what you want the full person model so we're returning a full personal object once he put the phone into the cell phone property okay I know it's it takes a little bit of time to kind of wrap your brain around it and I do think that having an example helps and then the other thing that helps is see in action so let's do that so we have our for each so I did I used VAR I always use a far but I'm I'm using varnas product because it's just easier than typing all these things out but he mouse over it does say full person model so very full person model P in the people list that's what gets returned from dapper we're gonna loop through and say what's what's the first name what's the last name cool and then the cell if there is one give us a phone number and that's the tricky part because remember there's three records that have nulls for cell phone okay so we go back over here we'll see no values for su for George and for Fred well hat what he do with that that's when we come over here and does a mapping it takes whatever is in phone and puts it in a cell phone well there's nothing in phone and that's what creates a null object and puts it in cell phone so that's why we can't just say p dot cell phone that phone number because there might not be anything the read here maybe no so therefore we have this question mark at the end so what that does is it says if this is not null then do the rest of stuff but if it is no stop here ignore it so P dot cell phone if is not null then give me a phone number otherwise nothing so let's run this notice i've uncomment adjust this one let's run this and see what it looks like and there we go so Tim Cory cell phone five five five one two one two and yet Sue Storm no cell phone George Smith same thing Jane Jones does have a cell phone but Fred does not so that's how he mapped those two object together and if we mouse over them and you know paused and looked at the object we'd see the full populated object including that cellphone property okay so that that's how you map with properties inside gets a little bit more tricky but the same time once with the pattern of these two objects and then this is what it goes into you're good to go now let's take it up just a little bit be a little bit more complex and that is what if you said that's great but we in passing parameters in so it's the same exact call the only difference is we're passing in a last name and we're gonna use that parameter so create an anonymous object P that holds lastname equals last name okay in fact let's actually capitalize that because last name is capitalized at last name so we're passing in last name and so anonymous object allows you to create any property we want and it just says okay sure I'll take that so it created this property called last name and then I assigned at the value those passed in and a string parameter now in my sequel string what's changed is I have is where statement where PE dot last name equals and then at last name and this is a parameter that can then be used by dapper so dapper will take our parameter list that's our anonymous out of P they'll say do I have a last name I better well yes I do take this value and put it in this sequel string safely this is what's called parameter injection it's different than just saying equals and then saying plus last name and closing it out that's not safe and the reason why is if I pass in sequel code in this last name parameter that would cause this sequel statement to crash if I didn't use parameter injection or sequel parameterization so this is the way you want to do it you want to create parameters and then pass in those values don't put it directly into your sequel string so the only difference in this call down here we still have the same three models being passed in the the first one the second one and the resulting one we still have a sequel statement we still have this mapping here the same mapping and the only to me add is comma and our parameters that's it and so the same thing will happen let's make sure we have it yep it's commented and I'm passing in Cori so if we run this there we go Cori oops so it's there's not much difference okay but I did want to show you this because I want you to see it doesn't have to be a whole lot add complexity once you get to a certain point yes that starts off a little complex with this stuff but then adding parameters is just adding the parameter pass in that's it okay so let's move on to what if you have multiple results sets coming back so you're returning maybe three different tables or two different tables what do you do then well let's look in this case our sequel statement is select star from DB o dot person and select star from DB o dot phone so gonna run both so what we did here is I created a list of person model which will be would hold this I create a list of phone model which would hold this and then I said this is all inside my connection I said using VAR lists okay that's the sequel map or dot grade reader so far is easier but var lists equals the connection dot query multiple so we're telling it hey you know what there's actually multiple things coming back not just one so he passing just our sequel and say okay now that you have that the next thing I want you to do is I want you to map it out and so here's our mapping lists which that's the variable that comes back from this query multiple list dot read and pass in our person model to list so it says give me a give me the people first okay that's the first one in our sequel call next give me the phone model and maps that out as well and that's all there is to it okay so what we're going to next is where I'll loop through each person and print them out and then loop through each phone and print that out let's just show to make sure that that and does in fact work and yes it does there's a lot of people there's the phone numbers so those the two different lists we pulled back with one query it's kind of efficient if you have multiple result sets to return go ahead and return multiple and as grab each in turn now one of the questions has come up that I want to point out is the order here does matter so you cannot switch the order here phones and people because person sly star from DB o dot person comes first therefore the mapping to person has to come first as well so you have to match up the order to what the order is in your sequel statement now again that's great but we're not passing in any parameters no worries I've got you covered we're gonna pass in last name of Smith and a phone number partial of one two one two so let's look at that so passing and last name and partial phone number and the reason I did this is because I show I'm putting a parameter here for the dbo person so where last name equals this last name and then to a different parameter for DB o dot phone where phone number like that partial number now just to be clear I could use at last name more than once in different sequel calls that's now I'm trying to say here you can use the same parameter in different calls but you can also use different parameters in the different calls and it's okay as well so nothing else is really changed here except we have this VAR p equals new anonymous method around this class sorry we have first name and partial phone number or last name and personal number which got a map to last name and partial phone number and we have that query multiple and we have a sequel statement and we're just comma P that's our parameter list that's the only difference really this right here this right here and of course our sequel statement has parameters let's run that again and this time we have person George Smith and person Fred Smith and the phone number is five five five one two one two so there's our matches okay so again once you get the bass call down adding parameters is just adding it onto the end that's really it next what if you want to pass out parameters so output parameters so you have data oops there you go you have data inside of your sequel and you want to get information out for example and this is a pretty common one when you insert a record into a table one of the things that does that usually if you have an auto incrementing ID field is it increments that ID you don't know if that number is but it crea a new ID number for this new person for instance well sequel has this thing called at-at identity and what this does is it gives you the last identity that was created in this transaction or in a transaction now just just in case you why I know identity is based upon a scope of the server so if you had some replication or trigger issues there's a whole road you can go down of well is it the right identity get or the scope identity or something else for our purposes that's worth great and for most purposes ill refine so this gets that identity ID there so that it gets the number of in the person table of our ID now notice our ID column is one two three four five in theory number six is the newly inserted record in theory it could be that if you delete row six it's actually seven or eight so you don't know for sure and this way it gets that identity and puts it into the at ID property which we passed in but we said it's Direction output and so what that means is it's going to get the value back out after this calls made so this right here select at ID equals I headed identity it puts the identity value into the property and then when we execute this that right here will have the new value instead of zero so let's walk through this we create dynamic parameters which this is a class that comes from dapper and it allows us to do some more advanced stuff including specifying types but in this case we're specifying the parameter direction so we're saying it's an int 32 which it typically is for an identity column and the direction is output we are passing in first name and last names those are inputs which is the default so there that statement insert into and then once we insert we get the identity and then we execute like normal so sequel and parameters that's it we're executing now once we've executed we can say P which is our dynamic parameter list P get and it's a type int it's gonna make sure it's that type when it comes out you get the @ ID value which ID you it starts off zero but we get the value passed back from this insert statement get that value and what we'll do here we'll say the new ID is and give that ID number but you know real application what you do is take that identity identity and use it for subsequent inserts so for example you insert a person and then you have their cell phone number well you might you know use that if that idea is using the cell phone table although in our case it's the opposite so you did the first insert you do mean of in the the phone table get that ID and then we create the person you get pass in that cell phone ID since it's theirs so but let's look at how this works just to see it let's run this and it says the new ID is of course is six and I say of course because that's we haven't done kind of messing on the database but we will see later on how that can get skipped so there you go ideas six and if we come back over here and we run this again we'll see the results of that person highlighted there we go so Peter Parker has been inserted with no cell phone and his ID is 6 so there we go so that's how you can get a value back or values doesn't happy as one you get two three four five different values back if you want to all you need to do is declare them as output fill them up somehow inside of your single statement and then use this code right here to get them back all right transactions now transactions typically it's a little more advanced equal thing by the same time it's me really important to do so the basic primer on transaction is this say you have your a bank application and you've got a person's checking account and they're saving account the person comes to you and says I want to move 100 dollars from my savings account into my checking account I mean your software says great I'll do that and so it withdraws from the state of the account cool so saving account value goes down by a hundred dollars but then the application crashes where is that hundred dollars because he didn't yet insert it into the checking account and so it's been taken out of one account but not put into the other account so it's basically just missing and no one likes a missing hundred dollars that's where transactions come in a transaction says we're gonna do multiple operations but if at any point we don't say that yes we're all done then we're gonna roll back that transaction which means it would put the hundred dollars back into the savings account so on a normal transaction we'd open a transaction up say this is the start then we say take out a hundred dollars from the savings account and then the we do an insert into the checking account for the same hundred dollars and then as long as those two transactions happen successfully we'd say commit that transaction and then as a whole that those actions would be completed therefore you never lose your hundred dollars however if we start the transaction and then we took out the hundred dollars from the savings account and the application crashes that transaction would never get commit therefore to get rolled back we never actually do that action so that's a basic primer on transactions let's see how we can use that inside of c-sharp so here I've I've written some bad code all right which wouldn't be the first time so we have our opening connection to sequel there's our connection and I have my parameters which I've created dynamically murmurs here and just had first name and last name and my first thing I'm going to do my sequel statement is insert into person first last name these values now just you can see the values are mister and nobody sort of create mr. nobody in the person table to use transactions outside a sequel in c-sharp we say CNN which is our connection dot open so you open the connection first and then we say using var trans which var is actually an ID B transaction equals CNN dot begin transaction that says okay we're ah start using a transaction now again this is a c-sharp thing we're in si using statement remember even if the application crashes it's going to close this connection either down here this closing curly brace or if the application crashes it crashes now if that happens just so you know it's going to roll back the transaction so if we don't specifically commit the transaction it will roll it back either at this curly brace or if the application crashes so we're safe so now down here we're going to execute our sequel there's icy claw right there and then our parameters and I passing in the transaction or a grab how many records were updated in this case should be one so records updated should say one because we're insert one record into the DB ODOT person table the next thing I do is I'm going to try a do something put in a try-catch so I'm gonna try to execute this sequel statement which is update DBA a person's set ID equal to one now there's a couple of problems with this sequel statement first of all the ID table is our auto incrementing value that's not something you should try and insert values into technically you can if you set certain things up but you shouldn't also that column can only have unique values because it's an identity column therefore you can't have two values with a 1 also notice there is no where clause here this is a very common mistake so with nowhere clause I'm gonna update every single record to have an ID of 1 this se statement will not run in fact if you wanted to we could run this over here and see that cannot update identity column ID so it's saying nope you just can't do that so this thing that should crash knows I'm passing in the transaction trans so if it succeeds we're gonna do trans commit now notice I'm doing this in a try-catch because if sequel has an error it will come back through dapper as an exception so I'm catching that exception I'm gonna write out what the message of an exception is and going to roll back the transaction so if it works I commit if it does not work I roll back now at the very end I'm going to call the map multiple objects method and the reason I'm going to do that is because this prints out all of my people and any attached cell phone numbers so it's just an easy way of showing he is missed or nobody in the database so let's run this make sure it's uncommented let's run this and see we get up records updated one so it did insert miss ANOVA in the database error cannot update identity column ID which that's the same air mass as we got in the the query window over here now we print out all people in database and notice Peter Parker is the last one mr. nobody's not here so it said we updated one record which we did we inserted mr. nobody but because we ran a bad sequel statement it rolled back that transaction and so mission nobody is no longer in this table just to see what happens if it works let's just say that we'll set the last name I don't gonna mess my data that's okay it's the last name of every person to one which will work so we'll commit the transaction let's write it again records updated one we have Peter we also have mr. which wanted to be nobody but now it's one so that worked so committed a data to our database okay so that's how transactions work the keys are don't just leave a transaction open forever which is why I had that's using statement here also be careful of things like if you do one thing and then do a whole bunch of processing in between before you do a second transaction that's not usually good either you don't want to keep a transaction open for long so be careful that from a performance standpoint you don't want to spend a lot of time doing work if you can avoid it if you can't you might want to think of different ways of going about this rather than open up a transaction and just doing a ton of work so that's how you do a transaction in dapper let's get to the big one insert data set so this right here the first thing I'll show you is get troopers so I've got a whole bunch of let's call them storm troopers maybe with their ID so a trooper and their ID number so I've done that create a data table now add add tables part of AD o--'s stuff we don't typically use data table as much anymore especially now a dapper doing our mapping for us but in this case I'm creating a day a table in memory that's called output so I add my columns first and last name and their types so it's a type of string type of string so that's the two columns and that matches up just here where that matches up with my UDT okay so first name and last name and they're both string types string is the equivalent of varchar' and varchar' in sequel so first in last name and then here's a values so when I add a row first name last name first name last name all the way down through so returns a data table so the first thing I do is I get my list of troopers there's none of them and then I say create a new anonymous object P or not as class an inside here gonna have one property people because that's what the person insert set takes that's right here it takes people of type basic UDT so people and I say here's my data set or data table sorry did a table and I say dot as table valued parameter and I say that's the the UDT to map it to so in this case it's basic UDT which that's or passing it so my data table use the extension method as table value parameter and pass in which UDT were mapping it to then everything else is just the same so we have CNN execute our store procedure our parameters and then I'm telling it yes this is command type store procedure that's it that's a normal execute statement with a store procedure the only difference is this right here and the fact that it's a data table they're passing in to our property rather than an actual value one value so in this case we're going to pass in nine troopers into the store procedure now why is this important versus a normal insert well a normal insert if we want to insert nine people we would do it one row at a time which is fine for small things but if you get into wanting to have large chunks of data inserted it might be more performance to instead of do it one at a time to do it all at once this execute will call this store procedure once not nine times just once it passes that bundle of data in now the insert statement takes that bundle of day like I show you earlier and it says insert into DB up person select from so instead of saying values and passing in values it's doing a whole tables worth of values insert into DB oh that person that whole table is our parameter or passing in so it takes that whole table it passed it in once and we're going to do our capture the records affected because when you do a CNN execute normally we're only inserting one record in which case capturing a 1 back just tells us that it did work but not much else where in this case we're gonna find out just how many records we insert it it should be 9 I believe one two three four five six seven eight and nine yes so nine records should be affected it will catalogue that right here and then we're going to again map multiple objects justice SEOs troopers are in fact in the database let's run this there we go records effective nine and there's all those troopers none of which had to sell them so that's how you are able to insert a whole table at once into our sequel database and with that that's all of the advanced demos alright so I'm gonna do I'm gonna give you this code and like I said it's on blog posts oops don't want to save that it's on blog post on my my blog so follow the link in the description you'll get it and just download it and try it out now some of these things you're not gonna use very often if you've tried you find yourself using all these things all the time maybe you're doing things that are a little too complex may just try and figure out a way to do it more simply but the problem with not doing things very often is you often forget how to do it when you try to do it so what I would recommend is just keep this product somewhere put it in a file folder somewhere and pull it out when you need to and we wanted use transactions just go that method and refresh memory and how to do it okay so this really is just a reference tool for later to come back to but at the same time it's also a learning tool as you're doing for the first time alright I hope you've enjoyed this video if you have any questions please leave them down the comments below or just ly know what you thought of the video alright have a great day as always I am Tim Corinne you [Music]

Channel: IAmTimCorey

Views: 103,781

Rating: 4.9520507 out of 5

Keywords: .net, C#, Visual Studio, code, programming, tutorial, training, how to, tim corey, C# training, C# tutorial, dapper, data access, sql, microsoft sql, microsoft sql server, microsoft sql server tutorial, transactions, sql transactions, advanced dapper, c# data access, ado.net, ado

Id: eKkh5Xm0OlU

Length: 44min 8sec (2648 seconds)

Published: Mon Nov 19 2018