.net core 从数据库读取图片和保存图片到数据库

功能需求：

1、把一张图片（png bmp jpeg bmp gif）转换为byte数组存放到数据库。

2、把从数据库读取的byte数组转换为Image对象，赋值给相应的控件显示。

3、从图片byte数组得到对应图片的格式，生成一张图片保存到磁盘上。

 以下函数分别实现了上述需求：

using Microsoft.Data.SqlClient;
using System.Data;

namespace PhotoApp;

internal class PhotoHelper
{
    string connectionString = "Server=192.168.xxx.xxx;User Id=xxx;Password=xxxx;Database=MyTest;Trusted_Connection=false;TrustServerCertificate=True";
    public string getPhotoPath = @"D:\001.jpg";
    public string savePhotoPath = @"D:\";
    public string createTableSql2 = @"CREATE TABLE [dbo].[EmpPhoto](
                                        [Id] [UNIQUEIDENTIFIER] NOT NULL,
                                        [Photo] [IMAGE] NULL
                                    )";

    //把单个图片文件保存到数据库中
    public void SavePhotoToSqlServer()
    {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            string insertSql = "INSERT INTO EmpPhoto (Id, Photo) VALUES (@Id, @Photo)";
            using (SqlCommand sqlCommand = new SqlCommand(insertSql, sqlConnection))
            {
                sqlCommand.Parameters.AddWithValue("@Id", Guid.NewGuid().ToString());

                FileInfo finfo = new FileInfo(getPhotoPath);
                byte[] btImage = new byte[finfo.Length];
                FileStream fStream = finfo.OpenRead();
                fStream.Read(btImage, 0, btImage.Length);
                fStream.Close();

                SqlParameter imageParameter = new SqlParameter("@emp_image", SqlDbType.Image);
                imageParameter.Value = btImage;
                sqlCommand.Parameters.Add(imageParameter);

                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
            }
        }
    }

    //把图片文件批量保存到数据库中
    public void SaveBulkPhotosToSqlServer()
    {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString))
        {
            DirectoryInfo dir = new DirectoryInfo(savePhotoPath);
            //循环读取文件夹内的png文件
            foreach (FileInfo item in dir.GetFiles("*.png"))
            {
                string insertSql = "INSERT INTO EmpPhoto (Id, Photo) VALUES (@Id, @Photo)";
                using (SqlCommand sqlCommand = new SqlCommand(insertSql, sqlConnection))
                {
                    sqlCommand.Parameters.AddWithValue("@Id", Guid.NewGuid().ToString());

                    FileStream fs = new FileStream(item.FullName, FileMode.Open);
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] byData = br.ReadBytes((int)fs.Length);
                    fs.Close();

                    sqlCommand.Parameters.Add("@Photo", SqlDbType.Binary, byData.Length);
                    sqlCommand.Parameters["@Photo"].Value = byData;

                    sqlConnection.Open();
                    sqlCommand.ExecuteNonQuery();
                    sqlConnection.Close();

                    sqlCommand.Parameters.Clear();
                }
            }
        }
    }

    //从数据库中读image字段，并保存为图片文件
    public void ReadPhotoFromSqlServer()
    {
        using (SqlConnection sqlconnection = new SqlConnection(connectionString))
        {
            sqlconnection.Open();
            string sqlQuery = @"Select [Id],[Photo] From [EmpPhoto]";

            //从数据库中读取数据
            SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlconnection);
            using (SqlDataReader reader = sqlCommand.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    //每次从数据库读一条记录，就保存一张图片
                    while (reader.Read())
                    {
                        long id = (long)reader["Id"];
                        byte[] photo = (byte[])reader["Photo"];

                        //保存图片文件
                        BytesToImage(id, photo);
                    }
                }
            }
        }
    }

    //把byte[]转成图片文件
    public void BytesToImage(long id, byte[] photo)
    {
        //图片文件的名称（必须指定后缀名）
        //实际情况需要根据base64判断文件后缀名
        string path = Path.Combine(savePhotoPath, "pictures", string.Format("{0}{1}", id.ToString(), ".jpg"));
        File.WriteAllBytes(path, photo);

        //FileInfo fileInfo = new FileInfo(path);
        //if (fileInfo.Exists && fileInfo.Directory != null && fileInfo.Directory.Exists)
        //    Directory.CreateDirectory(fileInfo.Directory.FullName);
    }

    //把base64字符串转换成图片，并保存图片文件
    public void SaveImageFromBase64(string base64string)
    {
        byte[] b = Convert.FromBase64String(base64string);
        //实际情况需要根据base64判断文件后缀名
        string path = Path.Combine(savePhotoPath, "test", "abc.jpg");
        File.WriteAllBytes(path, b);
    }

    //根据图片路径，把图片文件转换成byte[]
    public byte[] GetPictureData(string imagePath)
    {
        FileStream fs = new FileStream(imagePath, FileMode.Open, FileAccess.ReadWrite);
        byte[] byteData = new byte[fs.Length];
        fs.Read(byteData, 0, byteData.Length);
        fs.Close();

        return byteData;
    }

    //把byte[]转成图片文件
    public void BytesToImageWithStream(byte[] imageByte)
    {
        //转换为文件流
        MemoryStream ms = new MemoryStream(imageByte);
        ms.Seek(0, SeekOrigin.Begin);
        using (var output = File.OpenWrite(savePhotoPath))
        {
            ms.CopyTo(output);
        }

        ////.net core 主要用于服务器，不支持客户端的图片处理类，如：Image类
        ////将文件流转换为图像
        //Bitmap bitmap = new Bitmap(ms);
        //pictureBox1.Image = bitmap;
    }

    ////这里的Image是System.Drawing.Image
    ////Convert Byte[] to Image
    //public Image BytesToImage(byte[] buffer)
    //{
    //    //Get an image from file
    //    Image image = Image.FromFile("D:\\test.jpg");
    //    Bitmap bitmap = new Bitmap("D:\\test.jpg");

    //    MemoryStream ms = new MemoryStream(buffer);
    //    Image image = Image.FromStream(ms);
    //    return image;
    //}
}

 

参考资料：

Saving an Image to SQL Server - CodeProject

SQLserver用Image格式储存图片 - Murphy丶悦 - 博客园 (cnblogs.com)

关于c＃：将MemoryStream转换为FileStream时FileStream数据不完整 | 码农家园 (codenong.com)

C# byte数组与Image的相互转换 - 阿凡卢 - 博客园 (cnblogs.com)