How to retrieve image from database

In ADO.NET, a .NET Data Provider implements the base System.Data classes to expose objects, properties, methods, and events for database connectivity and interaction. These providers serve as intermediaries between your application and the underlying database system.

SQL Server, being a popular relational database management system, offers a set of system data types that define the various kinds of data that can be used within SQL Server.

Large object (LOB) data type

The Image data type in SQL Server is categorized as a large object (LOB) data type. It is designed to store variable-length binary data, such as images or other large files. The Image data type can hold a significant amount of binary data, ranging from 0 to 2^31-1 (2,147,483,647) bytes.

byte[] image = (byte[])command.ExecuteScalar();

One important aspect to note about the Image data type is that direct conversion to another data type is not supported, either implicitly or explicitly. You cannot directly convert an Image data type to another data type within SQL Server.

However, it is possible to perform indirect conversion of Image data. This can be achieved by utilizing other data types or using techniques like binary serialization or encoding to transform the Image data into a different format or representation suitable for the desired use case.

The following C# source code shows how to retrieve an image from SQL Server.

Full Source C#
using System; using System.Data.SqlClient ; using System.Drawing; using System.IO; using System.Windows.Forms; namespace WindowsFormsApplication1 { public partial class Form1 : Form { SqlConnection cnn ; string connectionString ; public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { connectionString = "Data Source=servername; Initial Catalog=databasename; User ID=sa; Password=password"; cnn = new SqlConnection(connectionString); MemoryStream stream = new MemoryStream(); cnn.Open(); SqlCommand command = new SqlCommand("select img from imgtable where id=2", cnn); byte[] image = (byte[])command.ExecuteScalar(); stream.Write(image, 0, image.Length); cnn.Close(); Bitmap bitmap = new Bitmap(stream); pictureBox1.Image = bitmap; } } }


You need to take special attention in scoping data size, performance, and storage since it is essential to do it in a safe way. Through an intelligent and discerning application of data type features and apt approaches, large binary data can be well dealt with by SQL Server.