[ACCEPTED]-Store GUID in MySQL from C#-mysql

Accepted answer
Score: 15

Figured it out. Here's how ...

Database schema

CREATE TABLE `test` (                                            
     `id` BINARY(16) NOT NULL,                                      
     PRIMARY KEY  (`id`)                                            
)

Code

string connectionString = string.Format("Server={0};Database={1};Uid={2};pwd={3}", "server", "database", "user", "password");

Guid orgId = Guid.NewGuid();
Guid fromDb = Guid.Empty;

using (MySqlConnection conn = new MySqlConnection(connectionString))
{
    conn.Open();

    using (MySqlCommand cmd = new MySqlCommand("INSERT INTO test (id) VALUES (?id)", conn))
    {
        cmd.Parameters.Add("id", MySqlDbType.Binary).Value = orgId.ToByteArray();
        cmd.ExecuteNonQuery();
    }

    using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
    {
        using (MySqlDataReader r = cmd.ExecuteReader())
        {
            r.Read();
            fromDb = new Guid((byte[])r.GetValue(0));
        }
    }
}

0

Score: 5

Apparently, the GetGuid() method in MySQL .NET Connector 1 v5.2.6+ should be fixed so you can use this example.

Score: 2

1) You can insert it the way @Tim Skauge does. But 24 while selecting the .Net connector version 23 is important. When I had used v 5.2.1, I 22 needed to do only this:

using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
{
    using (MySqlDataReader r = cmd.ExecuteReader())
    {
        r.Read();
        Guid id = (Guid)r[0];
    }
}

Here the reader itself 21 reads the binary value to .NET Guid type. You 20 can see it if you check the type of r[0]. But 19 with newer version, ie, 6.5.4, I found the 18 type to be byte[].. ie, it gets the binary value 17 from db to its corresponding byte array. So 16 you do this:

using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
{
    using (MySqlDataReader r = cmd.ExecuteReader())
    {
        r.Read();
        Guid id = new Guid((byte[])r[0]);
    }
}

You can read why is it so here in documentation. The 15 alternative to read Guid type directly and 14 not as byte[] is to have this line : Old Guids=true in 13 your connection string.

2) Additionally you 12 can do this straight away to read the binary 11 value as string by asking MySQL to do the 10 conversion, but in my experience this method 9 is slower.

Insert:

using (var c = new MySqlCommand("INSERT INTO test (id) VALUES (UNHEX(REPLACE(@id,'-','')))", conn))
{
    c.Parameters.AddWithValue("@id", Guid.NewGuid().ToString());
    c.ExecuteNonQuery();
}

or

using (var c = new MySqlCommand("INSERT INTO test (id) VALUES (UNHEX(@id))";, conn))
{
    c.Parameters.AddWithValue("@id", Guid.NewGuid().ToString("N"));
    c.ExecuteNonQuery();
}

And select:

using (MySqlCommand cmd = new MySqlCommand("SELECT hex(id) FROM test", conn))
{
    using (MySqlDataReader r = cmd.ExecuteReader())
    {
        r.Read();
        Guid id = new Guid((string)r[0]);
    }
}

The one 8 thing you need to notice is that if you 7 are inserting Guids by hex method then you 6 got to read it by the unhex approach. If you 5 are inserting them relying on .NET's ToByteArray() method, then 4 you got to read similarly. Otherwise you 3 will get incorrect guids read since .NET 2 has a peculiar way of ordering the bytes 1 according to endianness. Catch something about it here in the context of inserting and reading Guids in .NET

More Related questions