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

原创于 2022-04-21 17:23:18 发布 · 320 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#c#

这段代码展示了如何使用ODBC连接到PostgreSQL数据库，读取指定目录下的文件，将文件内容插入数据库，并从数据库中读取数据，然后将数据写入到本地磁盘上。


using System.Data.Odbc;




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

//Connection string for Connector/ODBC 3.51
string MyConString = "DSN=PostgreSQL35W;" +
                           "UID=postgres;" +
                           "PWD=postgres;";

//Connect to MySQL using Connector/ODBC
OdbcConnection conn = new OdbcConnection(MyConString);


OdbcCommand cmd = new OdbcCommand();


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 = "set names 'utf8'";

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

            cmd.ExecuteNonQuery();

            SQL = "INSERT INTO bindata(name,data) VALUES( ?,  ?)";

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



            cmd.Parameters.Add("name", OdbcType.NVarChar).Value = i.Name;
            cmd.Parameters.Add("data", OdbcType.VarBinary).Value = rawData;

            cmd.ExecuteNonQuery();

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

            conn.Close();
        }
    }
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
    for (int i = 0; i < MyOdbcException.Errors.Count; i++)
    {
        Console.Write("ERROR #" + i + "\n" +
                      "Message: " +
                      MyOdbcException.Errors[i].Message + "\n" +
                      "Native: " +
                      MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                      "Source: " +
                      MyOdbcException.Errors[i].Source + "\n" +
                      "SQL: " +
                      MyOdbcException.Errors[i].SQLState + "\n");
    }
}


using System.Data.Odbc;




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

//Connection string for Connector/ODBC 3.51
string MyConString = "DSN=PostgreSQL35W;" +
                           "UID=postgres;" +
                           "PWD=postgres;";

//Connect to MySQL using Connector/ODBC
OdbcConnection conn = new OdbcConnection(MyConString);


OdbcCommand cmd = new OdbcCommand();

OdbcDataReader myData;


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(0), FileMode.OpenOrCreate, FileAccess.Write);
        fs.Write(rawData, 0, (int)FileSize);
        fs.Close();

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

    }

    myData.Close();
    conn.Close();
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
    for (int i = 0; i < MyOdbcException.Errors.Count; i++)
    {
        Console.Write("ERROR #" + i + "\n" +
                      "Message: " +
                      MyOdbcException.Errors[i].Message + "\n" +
                      "Native: " +
                      MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                      "Source: " +
                      MyOdbcException.Errors[i].Source + "\n" +
                      "SQL: " +
                      MyOdbcException.Errors[i].SQLState + "\n");
    }
}