Guid (UUID) and varbinary(16) just fails

[Espen Berglund]

I think I've been trying most things to get this working. Should be relatively forward to generate a GUID in C#, store it in the database (MySQL 5.0.18) and retrieve it. The database is running on a linux machine (OpenSUSE 10.0) and .NET 2.0. I wrote up some code to actually test it, so if anyone can see any flaws here, please let me know.

MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Database=mydb;Uid=user;Pwd=password;");
MySqlDataReader reader = null;
MySqlCommand cmd = null;
MySqlTransaction trans = null;
byte[] o = null;
string sql = "CREATE TABLE IF NOT EXISTS test (id binary(16),stuff varchar(255))";
Guid guid = null;

try
{
conn.Open();
trans = conn.BeginTransaction();
cmd = new MySqlCommand(sql, conn, trans);
cmd.ExecuteNonQuery();
sql = "DELETE FROM test";
cmd = new MySqlCommand(sql, conn, trans);
cmd.ExecuteNonQuery();
guid = Guid.NewGuid();
Console.WriteLine("Generated Guid: " + guid.ToString());
bArray = Convert(guid);
Console.WriteLine("Length of Guid bytearray: " + bArray.Length.ToString());
Console.WriteLine("Converted bytearray: ");
foreach (byte b in bArray)
Console.Write(b);
Console.WriteLine();
sql = "INSERT INTO test VALUES(?guid,'my little test')";
cmd = new MySqlCommand(sql, conn, trans);
MySqlParameter param = new MySqlParameter("?guid", MySqlDbType.TinyBlob , 16);
param.Value = bArray;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

//So far so good, "select hex(id) from test" will return the correct GUID.
//However, I can't use the HEX function, so I have to read it raw and convert it.

sql = "SELECT * FROM test";
cmd = new MySqlCommand(sql, conn, trans);
reader = cmd.ExecuteReader();
while (reader.Read())
{
bArray = new byte[16];
reader.GetBytes(0, 0, bArray, 0, 16);
//could've put some more code here, but GetBytes doesn't work so I do some other things in catch
}
}
catch(Exception e)
{
Console.WriteLine(e.Message);
//GetBytes failed because return value is considered a string, so lets get that
string s = reader.GetString(0);
Console.WriteLine("String converted to bytearray: ");
//The convert this string to a bytearray
for (int i = 0; i < s.Length; i++)
{
bArray = (byte)System.Convert.ToInt16(s);
Console.Write(bArray);
}
Console.WriteLine();
guid = Convert(bArray);
Console.WriteLine("Guid read from database: " + guid.ToString());
}
finally
{
if (reader != null)
{
reader.Close();
reader = null;
}
}
Console.ReadLine();

//Some conversion functions
private static void swap(ref byte a, ref byte b)
{
byte c;

c = a;
a = b;
b = c;
}

internal static Guid Convert(byte[] b)
{
if (b.Length == 16)
{
swap(ref b[0], ref b[3]);
swap(ref b[1], ref b[2]);
swap(ref b[4], ref b[5]);
swap(ref b[6], ref b[7]);
}
return new Guid(b);
}

internal static byte[] Convert(Guid x)
{
byte[] b = x.ToByteArray();

swap(ref b[0], ref b[3]);
swap(ref b[1], ref b[2]);
swap(ref b[4], ref b[5]);
swap(ref b[6], ref b[7]);
return b;
}


What's even more annoying, is that even with the string conversion to bytearray (since GetBytes don't work), this seems to work approximately 10% of the times. I'm really clueless at this point...tried to figure it out for the past 3 days so any help would be appreciated.