SqlParameterCollection (ADO.NET)
-
I had a command that inserted two tables, both using identity columns as primary keys. The identity value obtained from the first insert was used in inserting the second table, and two output parameters on the command should reflect the value of the two primary keys after insertion. The command text was hence something like
INSERT A VALUES (@p1, @p2);
SET @A_ID = SCOPE_IDENTITY();INSERT B VALUES (@p3, @p4, @A_ID);
SET @B_ID = SCOPE_IDENTITY();I had added sql parameters for the identity values, and set the parameter direction to Output. After executing the command, I found that
cmd.Parameters["@B_ID"].Valuehad been correctly set, but the @A_ID parameter invariably had the value zero. Looking at the inserted data however what was really made me wonder what was going on here. The B record contained the correct foreign key, demonstrating that @A_ID had indeed been assigned correctly! After hours of headscratching I stumbled upon the explanation: The code creating the command first added a parameter @A_ID as an input parameter, then added another parameter also named "@A_ID" as an output parameter. I don't know how exactly the provider communicates the variable declarations to SQL Server, but it appears that the second declaration simply overwrote (redeclared) the already-defined @A_ID. Then, when my code attempted to get the value, the SqlParameterCollection probably did a linear search returning the first parameter with a matching name, which of course was the input parameter. While not checking for duplicate declarations may save a few microseconds, it would have been nice if by default such validation was performed and could be disabled instead via the ado.net configuration (for production). I certainly lost a lot of time, and probably some hair, before I found out. If it sounds like I was just being slow, let me add it wasn't that easy to check everything when the command had about 100 parameters. -
I had a command that inserted two tables, both using identity columns as primary keys. The identity value obtained from the first insert was used in inserting the second table, and two output parameters on the command should reflect the value of the two primary keys after insertion. The command text was hence something like
INSERT A VALUES (@p1, @p2);
SET @A_ID = SCOPE_IDENTITY();INSERT B VALUES (@p3, @p4, @A_ID);
SET @B_ID = SCOPE_IDENTITY();I had added sql parameters for the identity values, and set the parameter direction to Output. After executing the command, I found that
cmd.Parameters["@B_ID"].Valuehad been correctly set, but the @A_ID parameter invariably had the value zero. Looking at the inserted data however what was really made me wonder what was going on here. The B record contained the correct foreign key, demonstrating that @A_ID had indeed been assigned correctly! After hours of headscratching I stumbled upon the explanation: The code creating the command first added a parameter @A_ID as an input parameter, then added another parameter also named "@A_ID" as an output parameter. I don't know how exactly the provider communicates the variable declarations to SQL Server, but it appears that the second declaration simply overwrote (redeclared) the already-defined @A_ID. Then, when my code attempted to get the value, the SqlParameterCollection probably did a linear search returning the first parameter with a matching name, which of course was the input parameter. While not checking for duplicate declarations may save a few microseconds, it would have been nice if by default such validation was performed and could be disabled instead via the ado.net configuration (for production). I certainly lost a lot of time, and probably some hair, before I found out. If it sounds like I was just being slow, let me add it wasn't that easy to check everything when the command had about 100 parameters.Good find bro. I would surely try out this and see. If it really happens, it wouldnt be good.
dojohansen wrote:
I was just being slow, let me add it wasn't that easy to check everything when the command had about 100 parameters.
Definitely. :(
Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->** Simplify Code Using NDepend
Basics of Bing Search API using .NET
Microsoft Bing MAP using Javascript