Save GUID in MySQL from C #

Question

Trying to save Guid in MySQL db from C # (.NET). The MySQL column is of type BINARY (16). Any decision on how to transfer the manual and then get the manual back from MySQL? Search code here :-)

Answer 1

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

Answer 2

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

Answer 3

1) , @Tim Skauge. .Net- . v 5.2.1, :

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

.NET Guid. , r[0]. , .. 6.5.4, byte[].. .. db . , :

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

, , . Guid , [], : Old Guids=true .

2) , , , MySQL , .

:

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

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

:

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 only thing you need to notice is that if you insert Guides by the method hex, then you should read it with an approach unhex. If you embed them relying on the .NET method ToByteArray(), you should read the same way. Otherwise, you will receive incorrect hints, since .NET has a special way of arranging bytes according to the content. Catch something about this here in the context of inserting and reading guides in .NET