C#将一个文件夹的文件装载到MySQL数据库BLOB列,并将BLOB列下载到另一个文件夹

本文链接：https://blog.youkuaiyun.com/allway2/article/details/124217178

该博客介绍了如何使用 C# 语言操作 MySQL 数据库，存储和检索 BLOB 类型的文件。首先，通过读取指定目录中的文件并将其内容转换为字节数组，然后将文件数据插入到数据库的 BLOB 列中。接着，从数据库中检索这些文件，并将数据写入到本地磁盘上。示例代码展示了完整的 CRUD 操作流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using MySql.Data.MySqlClient;

MySqlConnection conn;
MySqlCommand cmd;

conn = new MySqlConnection();
cmd = new MySqlCommand();

string SQL;
long FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "SERVER=127.0.0.1;DATABASE=blob;UID=root;PASSWORD=root;charset=utf8;";

try
{
    // Get the directory
    DirectoryInfo place = new DirectoryInfo(@"InputFiles\");

    // Using GetFiles() method to get list of all
    // the files present in the Train directory
    FileInfo[] Files = place.GetFiles();
    Console.WriteLine("Files are:");
    Console.WriteLine();

    // Display the file names
    foreach (FileInfo i in Files)
    {
        Console.WriteLine("File Name - {0}", i.Name);
        {
            fs = new FileStream(i.FullName, FileMode.Open, FileAccess.Read);
            FileSize = fs.Length;

            rawData = new byte[FileSize];
            fs.Read(rawData, 0, (int)FileSize);
            fs.Close();

            conn.Open();

            SQL = "INSERT INTO bindata(name,data) VALUES( @FileName,  @File)";

            cmd.Connection = conn;
            cmd.CommandText = SQL;
            cmd.Parameters.Clear();

            cmd.Parameters.AddWithValue("@FileName", i.Name);
            cmd.Parameters.AddWithValue("@File", rawData);

            cmd.ExecuteNonQuery();

            Console.WriteLine("File Inserted into database successfully!",
                "Success!");

            conn.Close();
        }
    }
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error");
}

using MySql.Data.MySqlClient;

MySqlConnection conn;
MySqlCommand cmd;
MySqlDataReader myData;

conn = new MySqlConnection();
cmd = new MySqlCommand();

string SQL;
long FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "SERVER=127.0.0.1;DATABASE=blob;UID=root;PASSWORD=root;charset=utf8;";

SQL = "SELECT name, data FROM bindata";

try
{
    conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = SQL;

    myData = cmd.ExecuteReader();

    while (myData.Read())
    {

        FileSize = myData.GetBytes(myData.GetOrdinal("data"), 0, null, 0, 0);


        rawData = new byte[FileSize];

        myData.GetBytes(myData.GetOrdinal("data"), 0, rawData, 0, (int)FileSize);

        fs = new FileStream(@"DownLoadFiles\" + myData.GetString("name"), FileMode.OpenOrCreate, FileAccess.Write);
        fs.Write(rawData, 0, (int)FileSize);
        fs.Close();

        Console.WriteLine(myData.GetString("name"),"File successfully written to disk!", "Success!");

    }

    myData.Close();
    conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message, "Error");
}

CREATE TABLE "SCOTT"."BINDATA" (
    "ID"   NUMBER(10, 0),
    "NAME" VARCHAR2(512 BYTE),
    "DATA" BLOB
)
 
ALTER TABLE bindata ADD (
    CONSTRAINT bindata_pk PRIMARY KEY ( id )
);
 
CREATE SEQUENCE bindata_sequence;
 
CREATE OR REPLACE TRIGGER bindata_on_insert BEFORE
    INSERT ON bindata
    FOR EACH ROW
BEGIN
    SELECT
        bindata_sequence.NEXTVAL
    INTO :new.id
    FROM
        dual;
 
END;