如何使用 C# 从 PostgreSql 插入和检索图像
我正在尝试将图像插入 Postgres 并使用 C# 从 postgresql 中检索该图像.
I am trying to insert an image into Postgres and retrieve that image from postgresql using C#.
我的表中有两列:memberid
(字符变化)和 member_photo
(bytea)
I have two columns in my table: memberid
(character varying) and member_photo
(bytea)
这是我插入图像的代码:
Here is my code to insert the image:
using (FileStream pgFileStream = new FileStream("D:\\Capture.jpg", FileMode.Open, FileAccess.Read))
{
using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))
{
NpgsqlCommand command = new NpgsqlCommand();
byte[] ImgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));
command.CommandText = "insert into membermaster (memberid, member_photo) VALUES ('65', @Image)";
command.Connection = Program.conn;
Program.conn.Close();
Program.conn.Open();
//command.Parameters.Add(new NpgsqlParameter("Image", ImgByteA));
command.Parameters.Add("@Image", ImgByteA).Value = ImgByteA;
command.ExecuteNonQuery();
Program.conn.Close();
}
}
这是我检索图像的代码
NpgsqlCommand command = new NpgsqlCommand();
command.CommandText = "select member_photo from membermaster where memberid='65'";
command.Connection = Program.conn;
try
{
Program.conn.Close();
Program.conn.Open();
byte[] productImageByte = command.ExecuteScalar() as byte[];
if (productImageByte != null)
{
using (MemoryStream productImageStream = new System.IO.MemoryStream(productImageByte))
{
ImageConverter imageConverter = new System.Drawing.ImageConverter();
pictureBox1.Image = imageConverter.ConvertFrom(productImageByte) as System.Drawing.Image;
// image.Save(imageFullPath, System.Drawing.Imaging.ImageFormat.Jpeg);
pictureBox1.Image = System.Drawing.Image.FromStream(productImageStream);
}
}
}
catch
{
Program.conn.Close();
throw;
}
插入图像的代码工作正常,但我无法在图片框中显示此图像.
The code to insert the image is working fine, but I can't show this image in a picturebox.
我收到一个错误:
参数无效
请帮我解决这个问题
AFAIK 您无法使用 ExecuteScalar 检索 byte[].您应该改用 ExecuteReader.为了在插入参数时安全起见,我更喜欢自己指定类型,所以我的插入看起来像这样:
AFAIK you cannot retrieve a byte[] using ExecuteScalar. You should use ExecuteReader instead. Just to be on the safe side when inserting parameters, I prefer to specify types myself, so my insert looks like this:
using (var conn = new NpgsqlConnection(connString))
{
string sQL = "insert into picturetable (id, photo) VALUES(65, @Image)";
using (var command = new NpgsqlCommand(sQL, conn))
{
NpgsqlParameter param = command.CreateParameter();
param.ParameterName = "@Image";
param.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
param.Value = ImgByteA;
command.Parameters.Add(param);
conn.Open();
command.ExecuteNonQuery();
}
}
然后我可以像这样检索和加载图像:
I can then retrieve and load the image like this:
using (var conn = new NpgsqlConnection(connString))
{
string sQL = "SELECT photo from picturetable WHERE id = 65";
using (var command = new NpgsqlCommand(sQL, conn))
{
byte[] productImageByte = null;
conn.Open();
var rdr = command.ExecuteReader();
if (rdr.Read())
{
productImageByte = (byte[])rdr[0];
}
rdr.Close();
if (productImageByte != null)
{
using (MemoryStream productImageStream = new System.IO.MemoryStream(productImageByte))
{
ImageConverter imageConverter = new System.Drawing.ImageConverter();
pictureBox1.Image = imageConverter.ConvertFrom(productImageByte) as System.Drawing.Image;
}
}
}
}
我不知道在插入时指定数据类型是否有任何区别,因此请先尝试使用 Reader 进行检索.如果这不起作用,那么我建议将您的插入程序更改为类似我的程序.
I do not know if specifying the datatype on insert makes any difference, so try just retrieving using a Reader first. If that doesn't work, then I suggest changing your insert routine to something like mine.
请注意,在我的示例中,id 是一个整数,而不是一个变化的字符!
Please note in my example id is an integer, not a character varying!