Npgsql 读写大对象

Npgsql管理大对象

1.写大对象

using (NpgsqlConnection conn = new NpgsqlConnection(GetConnectString()))
{
try
{
conn.Open();


int noid;


using (NpgsqlTransaction trans = conn.BeginTransaction())
{
try
{
LargeObjectManager lom = new LargeObjectManager(conn);
byte[] buf = new byte[8192];
int readIn = 0;
int noid = lom.Create(LargeObjectManager.WRITE);
LargeObject lo = lom.Open(noid, LargeObjectManager.WRITE);
using (FileStream fs = File.OpenRead(fileName))
{
while ((readIn = fs.Read(buf, 0, buf.Length)) > 0)
{
lo.Write(buf, 0, readIn);
}
}
lo.Close();
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "数据库错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

2.读对象

using (NpgsqlConnection conn = new NpgsqlConnection(GetConnectString()))
{
try
{
conn.Open();
using (NpgsqlTransaction trans = conn.BeginTransaction())
{
LargeObjectManager lom = new LargeObjectManager(conn);
LargeObject lo = lom.Open(rfi.LargeObject, LargeObjectManager.READ);
using (FileStream fs = File.OpenWrite(@"D:\1.png"))
{
int tempmax = lo.Size();
int tempread = 0;
int temppos = 0;

do
{
byte[] buf = lo.Read(8192);
tempread = buf.Length;
if (tempread > 0)
{
fs.Write(buf, 0, tempread);
temppos += tempread;
lo.Seek(temppos);
}
} while (tempread > 0);
fs.Flush();
}
lo.Close();
trans.Commit();
}
}
catch (NpgsqlException ex)
{
MessageBox.Show(ex.Message, "数据库错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统错误", MessageBoxButton.OK, MessageBoxImage.Error);
}
}

//注意，读也必须执行事务，否则执行lo.Size()会出现错误。返回无效的大对象描述

3.删除对象

 using (NpgsqlConnection conn = new NpgsqlConnection(GetConnectString()))
            {
                try
                {
                    conn.Open();


                    using (NpgsqlTransaction trans = conn.BeginTransaction())
                    {
                        LargeObjectManager lom = new LargeObjectManager(conn);
                        lom.Delete(noid);
                        trans.Commit();
                    }
                }
                catch (NpgsqlException ex)
                {
                    MessageBox.Show(ex.Message, "数据库错误", MessageBoxButton.OK, MessageBoxImage.Error);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "系统错误", MessageBoxButton.OK, MessageBoxImage.Error);
                }
            }

1,2经过测试，visual Studio 2012 .net 4.0.3