postgre 二进制存储

show client_encoding;

set client_encoding='UTF8';

show server_encoding;


SELECT E'\\xDEADBEEF';

 

CREATE TABLE tbl_image_t
(
  id bigserial NOT NULL,
  name character varying(32),
  thumb bytea
)

insert into "tbl_image_t" values(4,'中文', E'\\x15191a1b')

SELECT id, name,  encode(thumb, 'hex')  FROM "tbl_image_t";

.net写入读取

 public void Save(string imageName, string imageFilePath)
        {
            using (NpgsqlConnection pgConnection = new NpgsqlConnection(ConnString))
            {
                try
                {
                    using (FileStream pgFileStream = new FileStream(imageFilePath, FileMode.Open, FileAccess.Read))
                    {
                        using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))
                        {
                            byte[] pgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));
                            using (NpgsqlCommand pgCommand = new NpgsqlCommand("INSERT INTO \"tbl_image_t\" (name, thumb) SELECT @Name, @Image", pgConnection))
                            {
                                pgCommand.Parameters.AddWithValue("@Name", imageName);
                                pgCommand.Parameters.AddWithValue("@Image", pgByteA);
                                try
                                {
                                    pgConnection.Open();
                                    pgCommand.ExecuteNonQuery();
                                }
                                catch
                                {
                                    throw;
                                }
                            }
                        }
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public Image Get(string imageName)
        {
            using (NpgsqlConnection pgConnection = new NpgsqlConnection(ConnString))
            {
                try
                {
                    using (NpgsqlCommand pgCommand = new NpgsqlCommand("SELECT thumb FROM \"tbl_image_t\" WHERE name=@imageName;", pgConnection))
                    {
                        pgCommand.Parameters.AddWithValue("@imageName", imageName);
                        try
                        {
                            pgConnection.Open();
                            Byte[] productImageByte = (Byte[])pgCommand.ExecuteScalar();
                            if (productImageByte != null)
                            {
                                using (Stream productImageStream = new System.IO.MemoryStream(productImageByte))
                                {
                                    return Image.FromStream(productImageStream);
                                }
                            }
                        }
                        catch
                        {
                            throw;
                        }
                    }
                }
                catch
                {
                    throw;
                }
            }
            return null;
        }

 

blob

  private static void WriteLargeObject()
        {
            newcon.Open();
            NpgsqlTransaction transaction = newcon.BeginTransaction();
            LargeObjectManager largeObjectManager = new LargeObjectManager(newcon);

            int noid = largeObjectManager.Create(LargeObjectManager.READWRITE);
            LargeObject lo = largeObjectManager.Open(noid, LargeObjectManager.READWRITE);

            FileStream fs = File.OpenRead(FileName);

            byte[] buf = new byte[fs.Length];
            fs.Read(buf, 0, (int)fs.Length);

            lo.Write(buf);
            lo.Close();
            transaction.Commit();


            transaction = newcon.BeginTransaction();

            lo = largeObjectManager.Open(noid, LargeObjectManager.READWRITE);

            FileStream fsout = File.OpenWrite(FileName + "database");

            buf = lo.Read(lo.Size());

            fsout.Write(buf, 0, (int)lo.Size());
            fsout.Flush();
            fsout.Close();
            lo.Close();
            transaction.Commit();
            newcon.Close();

            DeleteLargeObject(noid);

            Console.WriteLine("noid: {0}", noid);
        }

        public static void DeleteLargeObject(Int32 noid)
        {
            //NpgsqlConnection conn = new NpgsqlConnection("server=localhost;user id=npgsql_tests;password=npgsql_tests");

            newcon.Open();
            NpgsqlTransaction t = newcon.BeginTransaction();
            LargeObjectManager largeObjectManager = new LargeObjectManager(newcon);
            largeObjectManager.Delete(noid);

            t.Commit();

            newcon.Close();

        }