C# guid and SQL uniqueidentifier
C#SqlGuidUniqueidentifierC# Problem Overview
I want to create a GUID and store it in the DB.
In C# a guid can be created using Guid.NewGuid(). This creates a 128 bit integer. SQL Server has a uniqueidentifier column which holds a huge hexidecimal number.
Is there a good/preferred way to make C# and SQL Server guids play well together? (i.e. create a guid using Guid.New() and then store it in the database using nvarchar or some other field ... or create some hexidecimal number of the form that SQL Server is expecting by some other means)
C# Solutions
Solution 1 - C#
Here's a code snippet showing how to insert a GUID using a parameterised query:
using(SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using(SqlTransaction trans = conn.BeginTransaction())
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Transaction = trans;
cmd.CommandText = @"INSERT INTO [MYTABLE] ([GuidValue]) VALUE @guidValue;";
cmd.Parameters.AddWithValue("@guidValue", Guid.NewGuid());
cmd.ExecuteNonQuery();
trans.Commit();
}
}
Solution 2 - C#
SQL is expecting the GUID as a string. The following in C# returns a string Sql is expecting.
"'" + Guid.NewGuid().ToString() + "'"
Something like
INSERT INTO TABLE (GuidID) VALUE ('4b5e95a7-745a-462f-ae53-709a8583700a')
is what it should look like in SQL.
Solution 3 - C#
You can pass a C# Guid value directly to a SQL Stored Procedure by specifying http://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx">SqlDbType</a>.UniqueIdentifier</code>;.
Your method may look like this (provided that your only parameter is the Guid):
public static void StoreGuid(Guid guid)
{
using (var cnx = new SqlConnection("YourDataBaseConnectionString"))
using (var cmd = new SqlCommand {
Connection = cnx,
CommandType = CommandType.StoredProcedure,
CommandText = "StoreGuid",
Parameters = {
new SqlParameter {
ParameterName = "@guid",
SqlDbType = SqlDbType.UniqueIdentifier, // right here
Value = guid
}
}
})
{
cnx.Open();
cmd.ExecuteNonQuery();
}
}
See also: SQL Server's uniqueidentifier
Solution 4 - C#
Store it in the database in a field with a data type of uniqueidentifier.
Solution 5 - C#
// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(GentEFONRFFConnection);
myConnection.Open();
SqlCommand myCommand = new SqlCommand("your Procedure Name", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@orgid", SqlDbType.UniqueIdentifier).Value = orgid;
myCommand.Parameters.Add("@statid", SqlDbType.UniqueIdentifier).Value = statid;
myCommand.Parameters.Add("@read", SqlDbType.Bit).Value = read;
myCommand.Parameters.Add("@write", SqlDbType.Bit).Value = write;
// Mark the Command as a SPROC
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myConnection.Close();