环境:
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, 0, (int)fileStream.Length);
stream.Write(filebyte, 0, 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, Helvetica, 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_length,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, 0, (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)
{
}
}
}
实现代码如上,删除OID 大对象方法使用 Unlink(uint oid),省略代码实现
感谢下面前人的付出,他们的参考给我启发
/**
*
* http://blog.youkuaiyun.com/pipi0714/article/details/44993089
http://bbs.youkuaiyun.com/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.youkuaiyun.com/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
***/