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)
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();
}
}
WHERE clause. It tries to compare the lowercase guid from c# to the uppercase uniqueidentifier and that doesn't match.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.
uniqueidentifier SQL datatype (recommended in comment by @TorHaugen) translates to a System.Guid C# datatype, which is what OP asked about
You can pass a C# Guid value directly to a SQL Stored Procedure by specifying SqlDbType.UniqueIdentifier.
Your method may look like this (provided that your only parameter is the Guid):
int StoreGuid(Guid guid)
{
using SqlConnection connection = new("YourDataBaseConnectionString");
using SqlCommand command = new() {
Connection = connection ,
CommandType = CommandType.StoredProcedure,
CommandText = "StoreGuid",
Parameters = {
new() {
ParameterName = "@guid",
SqlDbType = SqlDbType.UniqueIdentifier, // right here
Value = guid
}
}
};
connection.Open();
return command.ExecuteNonQuery();
}See also: SQL Server's uniqueidentifier
Store it in the database in a field with a data type of uniqueidentifier.
// 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();
