CODEWITHSUNDEEP
c#mysql
Tim Skauge
Tim Skauge

Reputation: 1844

Store GUID in MySQL from C#

Trying to persist Guid's in a MySQL db from C# (.NET). MySQL column is of type BINARY(16). Any sugestion on how to persist the guid and later get the guid back to from MySQL? Looking for a code answer here :-)

Upvotes: 9

Views: 15695

Answers (3)

Magnus Johansson
Magnus Johansson

Reputation: 28325

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

Upvotes: 5

Tim Skauge
Tim Skauge

Reputation: 1844

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));
        }
    }
}

Upvotes: 16

nawfal
nawfal

Reputation: 73283

1) You can insert it the way @Tim Skauge does. But while selecting the .Net connector version is important. When I had used v 5.2.1, I 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 reads the binary value to .NET Guid type. You can see it if you check the type of r[0]. But with newer version, ie, 6.5.4, I found the type to be byte[].. ie, it gets the binary value from db to its corresponding byte array. So 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 alternative to read Guid type directly and not as byte[] is to have this line : Old Guids=true in your connection string.

2) Additionally you can do this straight away to read the binary value as string by asking MySQL to do the conversion, but in my experience this method 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 thing you need to notice is that if you are inserting Guids by hex method then you got to read it by the unhex approach. If you are inserting them relying on .NET's ToByteArray() method, then you got to read similarly. Otherwise you will get incorrect guids read since .NET has a peculiar way of ordering the bytes according to endianness. Catch something about it here in the context of inserting and reading Guids in .NET

Upvotes: 2

Related Questions