Video: LINQ to SQL: Updating with Stored Procedures

Presenter:Scott Stanfield

Override the generated ORM update/delete/insert logic with your own through stored procedures. The video also shows in detail how to use the SQL Profiler to monitor the changes in the ORM from generated SQL to our custom SQL stored procs. Since we're just writing SQL, the same techniques apply to both C# and VB developers.


Click here to go to original video page

The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
Will rule I am Scott Stanfield show you how to use your custom stored procedures with link if you can follow along in a sound series of videos on a doughnut fiber pointed this out but a ripoff of Scott Guthrie s blog on even though we are on part eight this reflects part seven so if you want to follow along you can look in his blog but we thought it might be good for you to see how this works without to think you were to watch this video and one thing you notice you didn t if you ve been following along throughout this whole series is that refused us the same Northwind database on these tables we ve dragged onto this service before and by doing that were you deconstructing the off in the all around model essentially to create its own a set of SQL statements select insert update delete to modify the database so
Minute 1
Used a simple test page to do an insert and update and know we will replace the regular SQL logic of acting generates with their own custom stored procedures so I ve going written some code at a time just to help us out we have some some helper code for a catacombs concert earlier putting business logic and a special file we have belittles a little bit code that runs before the phone number changes in the customer object sure you are an excuse and custom code here and it s due to some simple regex against against a US centric phone number we have a validate for the order is required is a lesson in order to indicate that thing out and filing of a helper for the Northgate context itself where we can call somebody customer returning customer Andy the reason I m showing this helper code is that when we make the switch to stored procedures all the stuff still works just fine this code stays as is so let s start to flush out the page on
Minute 2
Them in in my HTML if two buttons insert and update area update insert and selling just update now explain the reason to have that bunch is the second we have two grids will fill this one with the customer of interest and this one down here will have the customers orders let s go look at the code for that I want to fill in the code for hoops go for about a one film the code for non actually does she work right now without code to bring up no Saudi road couple things here were we filled a grid with the customer and the customers orders so press F5 to run the rights of looks like this is the default healthy customer suffers customer in it in the database Maria Anders is her name were to change this name retains a phone number were born to add a new order consistent to notice the last order was 1998 with 2007 posted it s been around for almost a decade were going to write the code to fill in this click right now
Minute 3
Let s find out so that the update and insert button click announced his code again and this million tons an orphaned in the context of creating an instance does not but I will use little helper method that we disagree that you showed you didn t get customer to customer and that requires us to pass a string for customer ID and his castles is hardcoded for healthy counts can affect and once we have that must change the contact name to something else like teens can change the phone number to my phone number and finally let s create a new order object new order command panel said he ordered the city to the geeks said that equal to its so that equal to what on the
Minute 4
Time that now 5 1 4 days before Tuesday time I now know I had three days to see clothes on all of its nice IntelliSense because when you order object let s add that to the customer to customer s collection of orders cool and I will update this letter changes and then I got a call or refresh grids to show the changes okay so before unless I want to listen central going to replace some degree generated code with stored procedures out of the section working in it that will work and you can trust me by actor for use of SQL profiler so let s bring that up now if you have uses SQL Server profile before Michigan has set it up to get some basic information seekers who it s going on its
Minute 5
And it s really useful to when using link saw connect to the database in my box now choose the oldest defaults here but under the event selection this is too much information as does turn all that off now to show all of that s the one we want that I found to be the best information and information as ASCII statement completed in the column I want from the went on here is the statement completed his allotted day you can get in why one would ever hear undertaxed arrogant and text data transfer the ship so we ll run it now with respect to my code and hit F5 we just were there we haven t any of the 800 which is filled in these two bits of information to select statements by switch back there ago there are two select statement terrain is the database and what a talk with the customer and the other to the order IDs so now
Minute 6
Cuties who in line when I press the update and insert is going to change this name here and the phone number and add a new liner tackles such as did it if we switch back to the profiler you can see that after these two select statement stairs or insert statement and we had in the orders table now we value or it s going to craving order ID and it has to return value order object and that s what this on school buddies will be right that seem totally retro and custom stored procedure and here s the update statement in no skeptics in ministry clever it s only updating the name and phone number on its smart enough to know because I modified this table to the version tag it did need a pass in every variable and say only update if he sings to change so it s pretty clever to succumb to know where you are a goal now is to replace these discount code your stored procedures but one other thing want to show you while this is not let s modify
Minute 7
Stopped us on a force it to break just exercise some of our logic and a parlor especially will break if that starts with a bomb if it does not start with a digit between two through 10 apparently that is their new area codes and US start with number one will see for original expression catches that update and sure enough it didn t sell out because an eye catching expression of the non exception of phone and sprinkling your selects a stop on a noticed a career in the profiler did not be making modifications to the database so I m business logic is still there okay so now let s change this to use stored procedures solely the profile running a company in are clear at his river with those guys look like a clear at Hamlet s clues in these windows and go back to the North when the view now now
Minute 8
We wanted to us is thrown and insert stored procedure and updates for procedure insert for orders an update for the customer table and Al Gore right here in the previous link for you and discuss how to use for products but not how to write custom insert and update which would right now so we ll start my own Insert order now have a stuff here because and he wanted to type all this but I am going to write the rest of the query nominee is use a little bit of a V I for those of you that are used to the old school text editor called V I there is a nice little tool called V I in new which is embedded inside of Visual Studio highlights use it if you re V I fan will use it on a hat VER the bottom of the show you a hyperlink or the URL to VIM Unicenter Visual Studio partner and I ve been really happy with this effect is inducing things you can t normally do something to work its lines of code needed to offer and paste them below Medusa magic
Minute 9
Per and record a macro that will allow me to do good in the line find backwards the space and delete of two in a move to stop the macro and play them Acrobat back at Q and that just explains his macro bottom of this one gives me the troublesome signal and such prequel that s the first part of the macro and now we want to do is commission joined much of these lines because this will form our statement river we are doing the insert order so I need to more lines summon a Yank the next two lines to make a copy for lines 19 to 20 and a search for TO REPLACE THEM WITH NOTHING AND REBEL SONG I VE INSERT INTO DEVITO BORDER
Minute 10
Connected in Gnostic insert values and this worked I can simply say that perfect compiled a love you cool soda whenever insert order statement what s do the same for her update customer same concept on the absolute call for more competition we have some extra columns to do so in such a buffer to go to the bottom of the file pasted in fact I can use that same macro recorded for the earlier one script will not have glitzy now want to because this is an update I have to want us to do something like the customer ID equals customer ID when you never macro here to take the customer ID into Reddy Cintron and repair vehicles so what s its record of one
Minute 11
See any percent to the word the beginning paste pattern skate stop macro and playback cool okay so this becomes our familiar doing update so the update statement looks like please did no customers sent them all out now here s where he uses of the columns media wanted it where the customer extract the customer equals the original IntelliSense original customer ID and were the version equals version timestamp was white going to make changes here if the version number matches
Minute 12
This is importance of what is good and optimistic concurrency here and I had to add the version timestamp to make this work which wasn t too hard I went into the table statement for customers at customers and did open table definition and scroll to the bottom innocently added version timestamp and if you do that to your default or for database you not want works with us so we never choose for procedures closing down now to add them so we can use or for more from our or in a model which drag them over insert order and update customer got to hook them up was first go to order no one uses insert order for Granny here are nurses in certain cities in run time let s change it to use our custom insert stored procedure opens her order and it is a pretty job on match
Minute 13
A based on case insensitivity matches up all beyond the parameters to school some good notes do the same thing for our customer table for update border collie update customers for procedure in their innocent little trickier because we have has a conversion which looks right in the original customer ID we went past method to the customer ID for the current actually cost writing original or ago I mentioned it ago now here s the key we only change the model we haven t changed any code I should be able to press F5 and have everything just work off while dyslexics can work the update won t because of updating to same thing this is exactly why and while we re at the back table book and not above a behavior and expect a hit update and insert it or says Emily Haines and that s not going to change
Minute 14
But notice s Ein who thinks that I forgot to put them back to make it valid places with five runs in me clear the profile and hit update insert snowsuit inserted this record but it did not need to do the update effect if you look back the profile to see the insert or their other way check out using a sure procedure upon that s a code we wrote in this as a return value but notice the update happen home that s because the update did not change central value so I was honestly it took me about 10 minutes now and not for hours what was going on is driving us I realize that it s smaller than expected into not need to do the update so for fun I wrote copied these
Minute 15
Because they just change the name to something else of their amendments increased to 100 copied and pasted here now now it s extended to the updates of interest update and change that in the profiler calls the update now I hit the big one update and insert it can change it back in a row and the profiler shows everything we re executing our insert into the orders tables for procedure and are up the customers table so there you go and you can see that the beauty of this is that you do not have to change any of the underlying code including our logic which is in Jackson hit about which is good illustrative anyway but were able to make the changes to it having or DB2 stored procedures on without changing your work is great scuttle the point is is that we have all that separates us from the
Minute 16
Augmentation system learned something from this and tuned for part nine all all all