How to insert image in a database

The data provider in ADO.NET consists of essential components such as the Connection, Command, DataReader, and DataAdapter objects. These components work together to facilitate database connectivity, query execution, and data retrieval.

Image data type

In SQL Server, the Image data type is used to store variable-length binary data ranging from 0 through 2^31-1 (2,147,483,647) bytes. It is commonly used to store images, files, or any other binary data.

The following sql script help you to create a table with Image Datatype column.

CREATE TABLE [dbo].[imgtable]( [id] [int] NULL, [img] [image] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The above script will create a table named as imgtable and define two columns , first column is id ,an integer datatype and second column is image, an image(img) datatype.

The following C# source code read the image from phisical path D:\picfile.jpg and stores it to a byte array and then insert it into database.

Full Source C#
using System; using System.Data; using System.Drawing; using System.IO; using System.Windows.Forms; using System.Data.SqlClient ; namespace WindowsFormsApplication1 { public partial class Form1 : Form { string fName ; SqlConnection cnn ; string connectionString = null; 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); fName = "D:\\picfile.jpg"; if (File.Exists(fName)) { int id = 2; byte[] content = ImageToStream(fName); cnn.Open(); SqlCommand cmd = new SqlCommand("insert into imgtable (id,img) values ( @id,@img)", cnn); cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@img", content); cmd.ExecuteNonQuery(); cnn.Close(); MessageBox.Show ("Image inserted"); } else { MessageBox.Show(fName + " not found "); } } private byte[] ImageToStream(string fileName) { MemoryStream stream = new MemoryStream(); tryagain: try { Bitmap image = new Bitmap(fileName); image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg); } catch (Exception ex) { goto tryagain; } return stream.ToArray(); } } }

Conclusion

Adapting this program to your specific database and table structure, you can successfully insert an image into a SQL Server database using C#.