private static byte[] GetBytes(NpgsqlDataReader reader, int ordinal)
{
using (var s = reader.GetStream(ordinal)) {
if (s is MemoryStream ms)
{
return(ms.ToArray());
}
using (var s2 = new MemoryStream()) {
s.CopyTo(s2);
return(s2.ToArray());
}
}
}
static void TestPostgres()
{
string connstr = "Host=localhost;Username=postgres;Password=H22oqi%bGerS;Database=postgres";
using (NpgsqlConnection conn = new NpgsqlConnection(connstr))
{
conn.Open();
using (FileStream fs = new FileStream("C:\\Temp\\files\\512.rar", FileMode.Open, FileAccess.Read, FileShare.None, 64 * 1024 * 1024, FileOptions.SequentialScan))
{
using (NpgsqlCommand cmd = new NpgsqlCommand("UPDATE b SET d = @d WHERE id = @id", conn))
{
cmd.CommandTimeout = 0;
NpgsqlParameter dParam = new NpgsqlParameter("@d", NpgsqlDbType.Bytea, -1);
dParam.Value = fs;
cmd.Parameters.Add(dParam);
NpgsqlParameter idParam = new NpgsqlParameter("@id", NpgsqlDbType.Integer);
idParam.Value = 1;
cmd.Parameters.Add(idParam);
Stopwatch sw1 = Stopwatch.StartNew();
cmd.ExecuteNonQuery();
sw1.Stop();
Console.WriteLine("PostgreSql. Write. " + sw1.Elapsed.ToString());
}
}
using (FileStream fs = new FileStream("C:\\Temp\\files\\512_3.rar", FileMode.Create, FileAccess.Write, FileShare.None, 64 * 1024 * 1024, FileOptions.WriteThrough))
{
using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT d FROM b WHERE id = @id", conn))
{
cmd.CommandTimeout = 0;
NpgsqlParameter idParam = new NpgsqlParameter("@id", SqlDbType.Int);
idParam.Value = 1;
cmd.Parameters.Add(idParam);
Stopwatch sw1 = Stopwatch.StartNew();
using (NpgsqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
if (dr.Read())
{
dr.GetStream(0).CopyTo(fs);
}
}
sw1.Stop();
Console.WriteLine("PostgreSql. Read. " + sw1.Elapsed.ToString());
}
}
}
}
/// <summary>
/// Opens a view for reading.
/// </summary>
private BinaryReader GetViewReader(string path)
{
string sql = $"SELECT contents FROM {GetTableName(DataCategory.View)} WHERE path = @path LIMIT 1";
NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
cmd.Parameters.AddWithValue("path", NormalizePath(path));
NpgsqlDataReader reader = null;
bool postponeClose = false;
void CloseAction()
{
reader?.Close();
conn.Close();
Monitor.Exit(conn);
}
try
{
Monitor.Enter(conn);
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
if (reader.Read())
{
if (reader.IsDBNull(0))
{
return(new BinaryReader(new MemoryStream(0)));
}
else
{
postponeClose = true;
return(new ViewReader(reader.GetStream(0), CloseAction));
}
}
}
finally
{
if (!postponeClose)
{
CloseAction();
}
}
throw new FileNotFoundException(string.Format(CommonPhrases.NamedFileNotFound, path));
}
