How To Return a Record ID from SQL to C# on Insert

C# and SQL are often used together. But if you are using Dapper to insert records into SQL, how do you get back the ID of the newly-created record? In this video, I will show you how to access that ID, how to return it from SQL, how to create output parameters, and how to capture return values. And we will do all of that in 10 minutes or less.
Full Training Courses: IAmTimCorey.com
Source Code: leadmagnets.app/?Resource=Ret...
Mailing List: signup.iamtimcorey.com/

[@caseyspaulding]

Much appreciated! These DBA type videos are super helpful

[@IAmTimCorey]

Glad you like them!

[@wesleymrwetyana4289]

Love your content Tim big fan indeed.

[@IAmTimCorey]

Thank you!

[@danielledehart466]

I can't thank you enough for these 10 minute videos!

[@IAmTimCorey]

Glad you like them!

[@andergarcia1115]

god bless you master, infinitely grateful for your work.

[@IAmTimCorey]

Thank you!

[@pavfrang]

Thank you Tim for the great video! I suspect that returning the ID is one of the reasons that GUID is preferred nowadays instead: you can create them and know their value, before calling the insert procedure..

[@webdeveloperninja9220]

GUIDs are great until you get fragmentation problems with millions of rows. I use GUIDS most of the time but have to rebuild the index every once and a while

[@DeeArr]

Why would you do that? If you want a GUID then use newid() or newsequentialid as a default in the table definition and return it as a string output parameter. The OUTPUT clause in the insert or merge statement will return it for you.

[@webdeveloperninja9220]

If you are using DDD it is nice for the application to create the id.

[@pavfrang]

@@DeeArr it depends on what you want to do. For a single record, the newid() is ok, BUT if you want to insert some thousand records, then what is the point of returning all the new ids? It's more practical to have them outside and just insert them. The only need for having the value outside, is when you want to add some thousand child records that use all these ids. It's much faster, if you just generate them outside and use them for the child records as well, than messing up with return statements.

[@DeeArr]

@pavfrang My basic example was for a single row update or insert. however, the only true source of the ids is sql server itself. BTW, Not everyone loves guids. A merge statement will give you a dataset of inserted and/or updated ids and it's atomic. Let Sql Server do the work. Unfortunately not many sql devs understand/trust the power of the merge statement.

[@torrvic1156]

I really like not just the video itself but the comments section. One can get so many useful considerations from there also.

[@IAmTimCorey]

I am glad they are helpful.

[@rajeshprosad6016]

I like your teaching style.

[@IAmTimCorey]

I'm glad.

[@rikudouensof]

Thanks for the video

[@IAmTimCorey]

You are welcome.

[@ChadiHD112]

Amazing tutorial Tim as always. However, I have a question about why you had to specify the @id in the DynamicParameters as Output direction and then add a new parameter as @output and specify it as ReturnValue? Isn't it better to just remove the "@output" and get the value from "@id" directly?

[@conbag5736]

I always like watching these short-form videos from Tim. Great work as always. The method you've shown is definitely valid, but in this situation, I prefer to keep things simple: var sql = "INSERT INTO People (FirstName) VALUES (@FirstName); SELECT SCOPE_IDENTITY();" person.Id = await connection.ExecuteScalarAsync(sql, person); This way, I don't end up with zillions of stored procedures for every CRUD operation, and I don't have to do the context-switch between SQL-world and C-Sharp world where I have to confirm the SP name, and the names and types of my input & output parameters. Admittedly, the experience of typing out SQL and/or formatting the query inside a string variable in C# isn't great, but I still think it's a worthy trade off.

[@IAmTimCorey]

Well, there are a few things to consider here. The first one is that you are making raw SQL calls from C#. That's not great because it makes it harder for yourself when you try to track down issues from the SQL side. Instead of having a name of a procedure to go to, you have just a raw SQL statement. That means you cannot optimize it on the SQL side - you need to figure out who called it, from where, and then modify that source code. Also, you are making it tougher on SQL to optimize that query. SQL can usually handle it, but not always. Finally, and the biggest issue, is that you are no longer making a call inside of a scope. You are making two separate calls, even though they are "together". That means that "Scope_Identity" isn't actually guaranteed to give you the ID you just created. There is a chance, and it isn't big but it is possible, that you could get the ID of a different record that was recently created (from any table). You wouldn't know until you tried to use the ID and found it invalid. Even worse, you might get back a valid ID, just not the ID of the record you just inserted. You see, a stored procedure is a transaction. That means that the scope is the procedure. Everything inside of it is inside that scope. Therefore, the Scope_Identity returns the most recent ID from that scope so you are guaranteed to get the correct ID.

[@conbag5736]

@@IAmTimCorey Great resposne, Tim. Thanks. I have been using this pattern pretty extensively, and I have never witnessed Scope_Identity() give me the incorrect Id - I did some more reading on this and I'm confident that the two statements will be executed in one 'batch' and therefore one 'scope' - assuming there are no triggers or anything messing with the scope context. I take your point about the query optimization - I tend to make a lot of in-line functions or stored procedures whenever I'm doing anything a bit more complex.

[@arnotek]

Another consideration is that by using stored procedures, you hide the internal implementation of the database. If the case arises that you need another interface to the database, it is straight forward where multiple interfaces use the same stored procedures. SP such as addPerson, etc. where the name of the stored procedure indicates what it does. In this case, the SP can return the ID of the record just inserted. There is also the security issue. SPs can be in different schemas and you can prevent outside applications from directly accessing the underlying database tables which opens a security hole that you can drive a truck through. Also, if you have a team of developers, one segment of the team can be working on the application and another segment on the database. By having a defined interface to the database makes development, debugging, and maintenance much easier. Additionally, if you have a change to the database structure, it is hidden from the application and the application does not need to be redeployed. The list goes on. On a closing note, I had the unique opportunity to write the same application both ways - one with the SQL in the application code and another version that used stored procedures. (I used to put the SQL in the application since the team was well versed in both SQL and C#.) After that experience, hands down, using stored procedures instead of putting the SQL in the application was by far the better approach for a multitude of reasons.@@IAmTimCorey

[@cissemy]

Thanks Tim How to take in consideration parameterDirection in the generic : SaveData(string storedProcedure, T parameters) ?

[@IAmTimCorey]

You can't. You need to use the specific type for that, not the generic T.

[@DeeArr]

I have a pretty generic proc that takes a nullable id parameter along with other specific parameters and, if an id is not specified, it performs an insert. If it is, it does an update, all with a merge statement that provides the identity back as an output parameter.

[@IAmTimCorey]

Just be careful - the merge statement has a LOT of issues that can burn you. I know quite a few SQL DBAs that have gone away from using merge because of the issues.

[@dennisvandermeer8238]

What would be the reason for using stored procedures instead of just writing the insert statement in the code? I always put everything in the code as there is more likely more logic in the code that will not end up in a stored procedure and I would rather have everything at one place rather then split up into 2 places. Besides, having stored procedures would make migrating to a different database more complex as syntax might be different

[@musicman6294]

I view this as a separation of concerns strategy; let the DB and the application do what they're designed to do. When I design an application, I put a lot of thought into the exact dataset needed to accomplish a task. Once I've got that squared away, I write a stored procedure to give me that dataset. This way, you aren't writing SQL in your C#, just manipulating what the DB gives you. Another consideration is that you can create a service account that can only execute stored procedures and cannot do anything else. Just my two cents...

[@pavfrang]

Security and authorization. You block all queries and you leave only some roles to access some procedures. If you ever deal with security in your database, you will realize that this is the safest approach.

[@IAmTimCorey]

Well, there are a few things to consider here. The first one is that you are making raw SQL calls from C# is not great because it makes it harder for yourself when you try to track down issues from the SQL side. Instead of having a name of a procedure to go to, you have just a raw SQL statement. That means you cannot optimize it on the SQL side - you need to figure out who called it, from where, and then modify that source code. Also, you are making it tougher on SQL to optimize that query. SQL can usually handle it, but not always. Finally, and the biggest issue, is that you are no longer making a call inside of a scope. You are making two separate calls then to get the "Scope_Identity", but it isn't actually guaranteed to give you the ID you just created. There is a chance, and it isn't big but it is possible, that you could get the ID of a different record that was recently created (from any table). You wouldn't know until you tried to use the ID and found it invalid. Even worse, you might get back a valid ID, just not the ID of the record you just inserted. You see, a stored procedure is a transaction. That means that the scope is the procedure. Everything inside of it is inside that scope. Therefore, the Scope_Identity returns the most recent ID from that scope so you are guaranteed to get the correct ID. Don't try to put your database code inside your C# application in order to put your code in one place. They are different systems and you need to treat them differently or you will get burned. Use SSDT to create your database inside of Visual Studio so you can put your stored procedures in source control if you want, but don't put SQL code in C# if you can help it.

[@chrisjohansson9971]

I usually use connection.InsertAsync(model); without stored procs and does the return quite easily. But yes I see your demonstrating using stored procs.

[@conbag5736]

Valid, but keep in mind that InsertAsync isn't vanilla "Dapper" - this comes from one of the Extensions like Dapper.Contrib or something.

[@IAmTimCorey]

I find that those things sound good and they work well at first and in small applications, but that they end up being less useful when you get into a more real-world type application.

[@sirus49]

Why does it matter vanilla or not? You use what you need.

[@chrisjohansson9971]

@@IAmTimCorey might need to expand on that answer with "less useful". I have written some "real world" applications using this model with generics and it works just fine

[@kevingates5851]

Tim, why not just “SELECT SCOPEDIDENTITY()”, and have your dapper expect an int / GUID as a result of the query? Is that bad practice?

[@IAmTimCorey]

Some people do that. The only issue is that some people return an int that represents the number of records affected. They could assume that the returned value was the number of records rather than the ID. Conversely, if you expect it to be an ID and it is the number of records, you also will have the wrong value.

[@kevingates5851]

Wouldn’t “SELECT” and RETURN be two different things thought? Sorry if I am misunderstanding you. I thought return is normally a code or status (could be records affected) whereas SELECT would be the result of your query - in this case the new identity.

[@IAmTimCorey]

Sorry, I was thinking you were returning that value as a return. Yes, if you did a select then you would return that value as a record. That is definitely a valid option.

[@JohnPeter-yf5jf]

Good video topic!

[@IAmTimCorey]

Thanks!

[@fideis86]

Insert into target-table (columns-to-insert) Output (columns-inserted) into (table-variable) Select/values clausule

[@dmitrybelov5703]

I prefer this because of possible using of sequencies when inserting new values