POSTGRESQL大对象ID操作例子，文件上传到POSTGRESQL数据库

环境：

POSTGRESQL 9.4.8

.net 4.5.1

PM> Install-Package Npgsql -Version 3.1.4

Install-Package EntityFramework6.Npgsql

-- ----------------------------
-- Table structure for seekhap_task
-- ----------------------------
DROP TABLE IF EXISTS "public"."file_task";
CREATE TABLE "public"."<span style="font-family: Arial,Helvetica,sans-serif;">file_task</span>" (
"id" int8 NOT NULL,"title" varchar(512) COLLATE "default" NOT NULL,"description" varchar(255) COLLATE "default","user_id" int8 NOT NULL,"attachment" bytea,"file_oid" oid,"file_name" varchar(1024) COLLATE "default","file_length" int8,"file_md5" varchar(256) COLLATE "default"
)
WITH (OIDS=FALSE)

;

"file_oid" oid,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Npgsql;
using Npgsql.Logging;
using System.IO;
using System.Data.Common;

namespace Seekhap.hbms.win
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        NpgsqlLogger logger = NpgsqlLogManager.Provider.CreateLogger("seekhap_hbms");
        static readonly System.Configuration.ConnectionStringSettings connstr = System.Configuration.ConfigurationManager.ConnectionStrings["pg94Context"];

        private void btnPut_Click(object sender,EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "JPG文件(*.jpg)|*.jpg|JPEG文件(*.jpeg)|*.jpeg|BMP文件(*.bmp)|*.bmg|PNG文件(*.png)|*.png";
            ofd.Title = "选择要上传的文件";
            ofd.Multiselect = false;

            if (ofd.ShowDialog() != DialogResult.OK) return;

            //MessageBox.Show(ofd.FileName,"Test"); 
            using (FileStream fileStream = File.OpenRead(ofd.FileName))  //打开txt文件
            {
                using (NpgsqlConnection connection = (NpgsqlConnection)NpgsqlFactory.Instance.CreateConnection())
                {

                    connection.ConnectionString = connstr.ConnectionString;
                    try { connection.Open(); }
                    catch (NpgsqlException ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                        throw ex;
                    }

                    NpgsqlTransaction npg_trans = connection.BeginTransaction();
                    NpgsqlCommand npg_cmd = connection.CreateCommand();
                    try
                    {
                        byte[] filebyte = new byte[fileStream.Length];
                        NpgsqlLargeObjectManager npg_lom = new NpgsqlLargeObjectManager(connection);
                        // Create a new empty file,returning the identifier to later access it
                        uint oid = npg_lom.Create();
                        // Open the file for reading and writing
                        using (var stream = npg_lom.OpenReadWrite(oid))
                        {
                            //int 
                            while (fileStream.Position < fileStream.Length)
                            {
                                int dc_read = fileStream.Read(filebyte,(int)fileStream.Length);
                                stream.Write(filebyte,filebyte.Length);
                                // reported to the corresponding event ( fs.Position / FileLength )
                                double PercentageOfFileSoFar = (double)fileStream.Position / (double)fileStream.Length;
                                System.Diagnostics.Trace.WriteLine(string.Format ("文件{0}  大小{1}  进度 {2}",ofd.FileName,fileStream.Length,PercentageOfFileSoFar));
                            }
                            stream.Flush();
                            stream.Close();
                        }

                        //写入文件名，文件大小，md5,原有路径
                        string cmdsql = "INSERT INTO "<span style="font-family: Arial,sans-serif;">file_task</span>" (id,title,description,user_id,file_name,file_length,file_oid) VALUES ( @id,@title,@description,@user_id,@file_name,@file_length,@file_oid);";
                      
                        npg_cmd.CommandText = cmdsql;
                        npg_cmd.Parameters.Add("id",NpgsqlTypes.NpgsqlDbType.Bigint).Value = 1;
                        npg_cmd.Parameters.Add("title",NpgsqlTypes.NpgsqlDbType.Varchar).Value = "test";
                        npg_cmd.Parameters.Add("description",NpgsqlTypes.NpgsqlDbType.Varchar).Value = Path.GetFullPath(ofd.FileName);
                        npg_cmd.Parameters.Add("user_id",NpgsqlTypes.NpgsqlDbType.Bigint).Value = 1;
                        npg_cmd.Parameters.Add("file_name",NpgsqlTypes.NpgsqlDbType.Varchar).Value = ofd.SafeFileName;
                        npg_cmd.Parameters.Add("file_length",NpgsqlTypes.NpgsqlDbType.Bigint).Value = filebyte.Length;
                        npg_cmd.Parameters.Add("file_oid",NpgsqlTypes.NpgsqlDbType.Oid).Value = oid;

                        npg_cmd.Transaction = npg_trans;
                        npg_cmd.ExecuteNonQuery();
                        npg_trans.Commit();

                    }
                    catch (NpgsqlException ex)
                    {
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                        npg_trans.Rollback();
                        throw ex;
                    }
                    catch (DbException ex)
                    {
                        npg_trans.Rollback();
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                        throw ex;
                    }
                    catch (Exception ex)
                    {
                        npg_trans.Rollback();
                        System.Diagnostics.Debug.WriteLine(ex.Message);
                        throw ex;
                    }
                    finally
                    {
                        connection.Close();
                    } 

                }//end connection
            }
        }
     

        private void btGet_Click(object sender,EventArgs e)
        {
            using (NpgsqlConnection connection = (NpgsqlConnection)NpgsqlFactory.Instance.CreateConnection())
            {

                connection.ConnectionString = connstr.ConnectionString;
                try { connection.Open(); }
                catch (NpgsqlException ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                    throw ex;
                }

                try
                {
                    NpgsqlCommand npg_cmd = connection.CreateCommand();
                    npg_cmd.CommandText = "SELECT file_name,file_oid FROM  file_task where file_oid=16706 ";
                    NpgsqlDataReader pg_reader = npg_cmd.ExecuteReader();
                    uint oid = 0; string fname = "" ; long fsize=0;
                    while (pg_reader.Read())
                    {
                        if (pg_reader.GetValue(2) == null) continue;
                        fname = pg_reader.GetString(0);
                        fsize = pg_reader.GetInt64(1);
                        oid = (uint)pg_reader.GetValue(2); 
                    }
                    pg_reader.Close();
                    //An operation is already in progress.need  pg_reader.Close(); first

                    NpgsqlTransaction npg_trans = connection.BeginTransaction();
                    byte[] filebyte = new byte[fsize];
                    NpgsqlLargeObjectManager npg_lom = new NpgsqlLargeObjectManager(connection);
                    // Open the file for reading and writing
                    using (var stream = npg_lom.OpenRead(oid))
                    {
                        int frsize = stream.Read(filebyte,(int)stream.Length);
                        string w_path = AppDomain.CurrentDomain.BaseDirectory + "" + fname;
                        File.WriteAllBytes(w_path,filebyte);
                    }
                    npg_trans.Commit();
                }
                catch (NpgsqlException ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                    //npg_trans.Rollback();
                    throw ex;
                }
                catch (DbException ex)
                {
                    //npg_trans.Rollback();
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                    throw ex;
                }
                catch (Exception ex)
                {
                    //npg_trans.Rollback();
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }

            }
        }

        private void btnDel_Click(object sender,EventArgs e)
        {

        }
    }
}

感谢下面前人的付出，他们的参考给我启发

/** * * http://blog.csdn.net/pipi0714/article/details/44993089 http://bbs.csdn.net/topics/310190940 http://stackoverflow.com/questions/34245254/cant-store-and-then-read-files-in-postgesql-as-large-objects http://www.postgresql.pw/thread-9147-1-1.html http://bbs.csdn.net/topics/310190918?list=lz async Task<uint> 异步操作await https://social.msdn.microsoft.com/Forums/office/en-US/c6ddee98-97f4-4267-891c-b07c925b2861/does-static-methods-interfere-with-each-other-?forum=csharpgeneral ***/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!