如何在C#中将位图插入MySQL？

Question

我正在尝试将我的位图图像插入MySQL，但问题是位图不受支持。我的错误：“System.NotSupportedException：‘不支持参数类型位图；请参阅https：//fl.vu/mysql-param-type.value：System.Drawing.Bitmap’”。MySQL中的图片数据类型是blob。我想知道是否应该更改数据类型？

我的按钮注册表

private void buttonRegister_Click(object sender, EventArgs e)
{
    String email = textBoxEmail.Text;
    String username = textBoxUsername.Text;
    String password = textBoxPassword.Text;
    String reTypepassword = textBoxReTypePassword.Text;

    UsersClass user = new UsersClass();
    System.Security.Cryptography.AesCryptoServiceProvider keyMaker = new System.Security.Cryptography.AesCryptoServiceProvider();
    keyMaker.KeySize = 128;
    keyMaker.BlockSize = 128;
    keyMaker.GenerateKey();
    byte[] build = keyMaker.Key;
    String Key = Convert.ToBase64String(build);
    //MessageBox.Show(Key);

    string encryptPassword = user.EncryptString(Key, textBoxPassword.Text);


    char[] v = encryptPassword.ToCharArray();
    int c = 0;

    Bitmap bm = new Bitmap(Image);
    for (int w = 0; w < bm.Width; w++)
    {
        for (int h = 0; h < bm.Height; h++)
        {
            if (v.Length > c)
            {
                Color pixel = bm.GetPixel(w, h);
                bm.SetPixel(w, h, Color.FromArgb(pixel.R, pixel.G, Convert.ToInt32(v[c])));
                c++;
            }
        }
    }

    Color p = bm.GetPixel(Image.Width - 1, Image.Height - 1);
    bm.SetPixel(Image.Width - 1, Image.Height - 1, Color.FromArgb(p.R, p.G, Convert.ToInt32(c)));
    Image = (Image)bm;
    imageBox.Image = Image;
 
    

    myconn.openConnection();

    if (password == reTypepassword)
    {
        MySqlCommand cmd = new MySqlCommand("insert into customer values(@id, @username, @email, @password, @Customer_Request,@location,@address,@key, @picture)", myconn.getConnection());
        cmd.Parameters.Add(new MySqlParameter("@id", 0));
        cmd.Parameters.Add(new MySqlParameter("@username", textBoxUsername.Text));
        cmd.Parameters.Add(new MySqlParameter("@email", textBoxEmail.Text));
        cmd.Parameters.Add(new MySqlParameter("@password", encryptPassword));
        cmd.Parameters.Add(new MySqlParameter("@Customer_Request", ""));
        cmd.Parameters.Add(new MySqlParameter("@location", ""));
        cmd.Parameters.Add(new MySqlParameter("@address", textBoxAddress.Text));
        cmd.Parameters.Add(new MySqlParameter("@key", Key));
        cmd.Parameters.Add(new MySqlParameter("@picture", Image));
        cmd.ExecuteNonQuery();
        MessageBox.Show("Success to insert");
    }
    else
    {
        MessageBox.Show("Please enter the correct password");
    }
}

Answer 1

这段代码将一个PNG文件（但也可能是另一种类型）写入数据库，并检索它，并将其存储到'test.PNG'。

using System.IO;
using MySql.Data.MySqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            string filename = @"D:/MySQL Server 8.0/Uploads/d95251abfa0f532b0b332906de4d3181b033b35e76319b807c4948df4fa5aa95.png";

            string Server = "localhost";
            string DatabaseName = "test";
            string UserName = "test";
            string Password = "test";
            string connstring = string.Format("Server={0}; database={1}; UID={2}; password={3}", Server, DatabaseName, UserName, Password);

            MySqlConnection conn = new MySqlConnection(connstring);
            conn.Open();

            string sql = "INSERT INTO pic VALUES(@idpic,@caption,@image)";
            var cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@idpic",null);
            cmd.Parameters.AddWithValue("@caption","test");
            cmd.Parameters.AddWithValue("@image", File.ReadAllBytes(filename));

            var result = cmd.ExecuteNonQuery();
            var lastId = cmd.LastInsertedId;
            Console.WriteLine($"Executing insert resulted in {result}, with idpic={lastId}");

            sql = "SELECT img FROM pic WHERE idpic=@id";
            cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@id", lastId);
            MySqlDataReader reader = cmd.ExecuteReader();
            reader.Read();
            byte[] picture = (byte[])reader["img"];
            File.WriteAllBytes(@"d:\temp\test.png", picture);
            conn.Close();

        }
    }

MySQL表的创建如下：

 CREATE TABLE `pic` (
          `idpic` int unsigned NOT NULL AUTO_INCREMENT,
          `caption` varchar(45) NOT NULL,
          `img` longblob NOT NULL,
          PRIMARY KEY(`idpic`)
        ) ENGINE=InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE=utf8mb4_0900_ai_ci

至少有两个设置需要检查。

>

max_allowed_packet应设置为大于图片的最大文件大小。

secure_file_priv如果设置了此选项，则只能将指定目录中的文件插入到数据库中。

应向连接到数据库的用户授予文件访问权限