>In this blog, I will try to explain a basic image storing and retrieval technique in PostgreSQL Server using Microsoft.net and Npgsql.
Prerequisites:
- PostgreSQL Server
- Npgsql
- Visual Studio 2005/2008/2010
- Creating an image-containing table
Create an image-containing table:
Selec All Code:1
2
3
4
5
6
| CREATE TABLE products
(
product_id SERIAL NOT NULL PRIMARY KEY,
product_name VARCHAR(40) NOT NULL UNIQUE,
product_image bytea NOT NULL
); |
Bytea stores binary information in PostgreSQL database. Further information,
read documentation.
Save a product along with image
References
- System
- System.Data
- System.Drawing
- System.Text
- System.Windows.Forms
- Npgsql
- System.IO
CSharp
Selec All Code:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| public void SaveProduct(string productName, string productImageFilePath)
{
using (NpgsqlConnection pgConnection = new NpgsqlConnection("server=localhost;user id=postgres;password=binod;database=postgres"))
{
try
{
using (FileStream pgFileStream = new FileStream(productImageFilePath, FileMode.Open, FileAccess.Read))
{
using (BinaryReader pgReader = new BinaryReader(new BufferedStream(pgFileStream)))
{
byte[] pgByteA = pgReader.ReadBytes(Convert.ToInt32(pgFileStream.Length));
using (NpgsqlCommand pgCommand = new NpgsqlCommand("INSERT INTO products(product_name, product_image) SELECT @ProductName, @ProductImage", pgConnection))
{
pgCommand.Parameters.AddWithValue("@ProductName", productName);
pgCommand.Parameters.AddWithValue("@ProductImage", pgByteA);
try
{
pgConnection.Open();
pgCommand.ExecuteNonQuery();
}
catch
{
throw;
}
}
}
}
}
catch
{
throw;
}
}
} |
VisualBasic
Selec All Code:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| Sub SaveProduct(ByVal productName As String, ByVal productImageFilePath As String)
Using pgConnection As NpgsqlConnection = New NpgsqlConnection("server=localhost;user id=postgres;password=binod;database=postgres")
Try
Using pgFileStream As FileStream = New FileStream(productImageFilePath, FileMode.Open, FileAccess.Read)
Using pgReader As BinaryReader = New BinaryReader(New BufferedStream(pgFileStream))
Dim pgByteA As Byte() = pgReader.ReadBytes(CInt(pgFileStream.Length))
Using pgCommand As NpgsqlCommand = New NpgsqlCommand("INSERT INTO products(product_name, product_image) SELECT @ProductName, @ProductImage", pgConnection)
pgCommand.Parameters.AddWithValue("@ProductName", productName)
pgCommand.Parameters.AddWithValue("@ProductImage", pgByteA)
Try
pgConnection.Open()
pgCommand.ExecuteNonQuery()
Catch
Throw
End Try
End Using
End Using
End Using
Catch
Throw
End Try
End Using
End Sub |
Get image from PostgreSQL Server
CSharp
Selec All Code:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| public Image GetProductImage(string productName)
{
using (NpgsqlConnection pgConnection = new NpgsqlConnection("server=localhost;user id=postgres;password=binod;database=mintgres"))
{
try
{
using (NpgsqlCommand pgCommand = new NpgsqlCommand("SELECT product_image FROM products WHERE product_name=@ProductName;", pgConnection))
{
pgCommand.Parameters.AddWithValue("@ProductName", productName);
try
{
pgConnection.Open();
Byte[] productImageByte = (Byte[])pgCommand.ExecuteScalar();
if (productImageByte != null)
{
using (Stream productImageStream = new System.IO.MemoryStream(productImageByte))
{
return Image.FromStream(productImageStream);
}
}
}
catch
{
throw;
}
}
}
catch
{
throw;
}
}
return null;
} |
VisualBasic
Selec All Code:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| Function GetProductImage(ByVal productName As String) As Image
Using pgConnection As NpgsqlConnection = New NpgsqlConnection("server=localhost;user id=postgres;password=binod;database=mintgres")
Try
Using pgCommand As NpgsqlCommand = New NpgsqlCommand("SELECT product_image FROM products WHERE product_name=@ProductName;", pgConnection)
pgCommand.Parameters.AddWithValue("@ProductName", productName)
Try
pgConnection.Open()
Dim productImageByte As Byte() = TryCast(pgCommand.ExecuteScalar, Byte())
If productImageByte IsNot Nothing Then
Using productImageStream As Stream = New System.IO.MemoryStream(productImageByte)
Return Image.FromStream(productImageStream)
End Using
End If
Catch
Throw
End Try
End Using
Catch ex As IOException
Throw
End Try
End Using
Return Nothing
End Function |
Tags: bytea, c#, image, npgsql, postgresql, vb
Very nice and very usefull.. Thanks man.
Causing a problem of parameter is incorrect when selecting from database