Adding a Guid Parameter into SqlCommand

This is just a random Tip.

It seems like recently some people I know started to have problems passing a Guid objects as a parameter of a SqlCommand object. I already had this problem so I decided to post about it in case someone else is trying the same thing. Apparently it doesn't work if you pass the Guid directly as shown below:

Guid theGuid = new Guid();
SqlCommand command = new SqlCommand("select * from someTable where SomeField = @GuidParameter", someConnection);
command.Parameters.Add("@GuidParameter", SqlDbType.UniqueIdentifier).Value = theGuid;

When the Guid object is passed directly, it's not parsed and Sql Server cannot handle it. The correct way to pass a Guid object as a SqlParameter would be slightly different:

Guid theGuid = new Guid();
SqlCommand command = new SqlCommand("select * from someTable where SomeField = @GuidParameter", connection);
command.Parameters.Add("@GuidParameter", SqlDbType.UniqueIdentifier).Value = System.Data.SqlTypes.SqlGuid.Parse(theGuid.ToString());

Note that now, when passing the value, I'm converting the Guid to a string value and using SqlTypes.SqlGuid.Parse to perform the actual parse. The method parse returns a SqlGuid object, which is the right match for SqlDbType.UniqueIdentifier.

The alternative to Parse, would be creating a new instance of SqlGuid and pass this instance to the Value property of the SqlParameter, but then you would create one more object to do the exact same thing. In this case it would be something like this:

Guid theGuid = new Guid();
System.Data.SqlTypes.SqlGuid theSqlGuid = new System.Data.SqlTypes.SqlGuid(theGuid);
SqlCommand command = new SqlCommand("select * from someTable where SomeField = @GuidParameter", connection);
command.Parameters.Add("@GuidParameter", SqlDbType.UniqueIdentifier).Value = theSqlGuid;

Well, that's it. I hope it helps someone out there. Cheers.

Published Tuesday, January 13, 2009 2:01 PM by tsantos
Filed under: , ,

Comments

# re: Adding a Guid Parameter into SqlCommand

Tuesday, January 13, 2009 2:28 PM by RichardD

Strange - I've never seen this problem, and I've been passing Guid parameters directly to SQL Server for years. The built-in SqlMembershipProvider class also uses Guid keys, and passes them directly to SQL Server without any problems:

command.Parameters.Add(this.CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, providerUserKey));

Are you sure you're passing the correct value? "theGuid = new Guid()" is the same as "theGuid = Guid.Empty", so you shouldn't expect to get any records back.

Note: "SqlGuid.Parse(theGuid.ToString())" is equivalent to "new SqlGuid(new Guid(theGuid.ToString()))", so "new SqlGuid(theGuid)" actually creates one *less* object than "SqlGuid.Parse", and doesn't involve the overhead of converting from a Guid to a string and back again.

# re: Adding a Guid Parameter into SqlCommand

Tuesday, January 13, 2009 3:34 PM by sodablue

When you do a parse you are creating a new object, it's just not as explicit.

The parse statement basically ends up doing something like this:

command.Parameters.Add("@GuidParameter", SqlDbType.UniqueIdentifier).Value = new System.Data.SqlTypes.SqlGuid(theGuid);

A combination of both examples. :-)

# re: Adding a Guid Parameter into SqlCommand

Tuesday, January 13, 2009 3:45 PM by tsantos

Yeah, well, it happens sometimes with me, not always tho. I never had a crash or anything. SqlServer just doesn't do anything, such as insert when I pass a guid directly.. a proper guid.. And please note that "theGuid = new Guid();" is just for the sample code. You can tell by the variable names and they way the code was written.

But thanks for the input.

# re: Adding a Guid Parameter into SqlCommand

Tuesday, January 13, 2009 5:25 PM by AndrewSeven

providerUserKey isn't a good reference, it is of type object.

Leave a Comment

(required) 
(required) 
(optional)
(required)