As I said in the comments, I use the column as bytea (byte array) which can store not only images, but also files of any format.
Follow the code as I use it, however this is just an example using the System.Data.ODBC .NET library. Other excerpts were discarded irrelevant to the situation, focusing only on the selection, insertion and updating of the column in bytea . If you use another method to connect to the bank, it is very similar and I believe you will have no problem converting the code to your reality.
A Class For the example object:
public class Cadastro
{
public byte[] Foto { get; set; }
}
A Class where you would communicate with the bank:
public class CadastroRepositorio
{
public List<Cadastro> Select()
{
List<Cadastro> retorno = new List<Cadastro>();
string sql = @"Select foto from tabela where id = 1;";
using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
{
conexao.Open();
using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
{
using (OdbcDataReader dr = cmd.ExecuteReader())
{
Cadastro obj;
while (dr.Read())
{
obj = new Cadastro();
obj.Foto = new byte[0];
obj.Foto = (dr[0].ToString().Length != 0 ? (byte[])dr[0] : obj.Foto);
//obj.Foto = (dr["foto"].ToString().Length != 0 ? (byte[])dr["foto"] : obj.Foto);
retorno.Add(obj);
}
dr.Close();
}
}
conexao.Close();
}
return retorno;
}
public int Insert(Cadastro obj)
{
int r = 0;
string sql = @"insert into tabela (foto) value (?);";
using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
{
conexao.Open();
using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
{
OdbcParameter param = new OdbcParameter("foto", OdbcType.Binary);
param.Value = (obj.Foto == null ? null : ( obj.Foto.Length == 0 ? null : obj.Foto));
cmd.Parameters.Add(param);
r = cmd.ExecuteNonQuery();
}
conexao.Close();
}
return r;
}
public int Update(Cadastro obj)
{
int r = 0;
string sql = @"update tabela set foto = ? where id = 1;";
using (OdbcConnection conexao = new OdbcConnection("string de conexao"))
{
conexao.Open();
using (OdbcCommand cmd = new OdbcCommand(sql, conexao))
{
OdbcParameter param = new OdbcParameter("foto", OdbcType.Binary);
param.Value = (obj.Foto == null ? null : (obj.Foto.Length == 0 ? null : obj.Foto));
cmd.Parameters.Add(param);
r = cmd.ExecuteNonQuery();
}
conexao.Close();
}
return r;
}
}
Finally, if necessary, you can convert byte[] to Image using the following function: (This is not my own)
/// <summary>
/// Converte um array de bytes em um objeto System.Drawing.Image
/// </summary>
/// <param name="pic">byte[]</param>
/// <returns>System.Drawing.Image</returns>
public static Image ConvertByteToImage(byte[] pic)
{
if (pic != null)
{
try
{
MemoryStream ImageDataStream = new MemoryStream();
ImageDataStream.Write(pic, 0, pic.Length);
ImageDataStream.Position = 0;
pic = System.Text.UnicodeEncoding.Convert(Encoding.Unicode, Encoding.Default, pic);
System.Drawing.Image img = System.Drawing.Image.FromStream(ImageDataStream);
return img;
}
catch
{
return null;
}
}
else return null;
}
And the reverse function, to convert an image to byte []:
/// <summary>
/// Converte um objeto System.Drawing.Image em um array de bytes
/// </summary>
/// <param name="foto">System.Drawing.Image</param>
/// <returns>byte[]</returns>
public static byte[] ConvertImageToByte(System.Drawing.Image foto)
{
if (foto == null)
return null;
Bitmap bmp = new Bitmap(foto);
MemoryStream stream = new MemoryStream();
bmp.Save(stream, ImageFormat.Png);
stream.Flush();
byte[] pic = stream.ToArray();
return pic;
}
Now the backup and restore part:
To back up, I use the following command:
C:\Progra~2\PostgreSQL.1\bin\pg_dump -h [host] -p 5432 -U postgres --inserts -c -f D:\PostgreSQL\Backups\[arquivo de backup].dump [nome database]
To do the restore, I use the following command:
C:\Progra~2\PostgreSQL.1\bin\psql -U postgres -d [nome database] -f D:\PostgreSQL\Backups\[arquivo de backup].dump
It works perfectly, not only for images but also for files (.doc, .zip, .exe, etc). I hope I have helped.
Note: Working in environment Windows
