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#.






Related Topics
  1. What is C# ADO.NET Dataset
  2. C# Datset with Sql Server Data Provider
  3. C# Datset with OLEDB Data Provider
  4. Find Tables in a Dataset - Sql Server
  1. Find Tables in a Dataset - OLEDB
  2. How to Dataset rows count - Sql Server
  3. How to Dataset rows count - OLEDB
  4. How to find Column Definition SqlServer
  5. How to find Column Definition OLEDB
  6. How to Dyanamic Dataset in C#
  7. C# Dataset with multiple tables - Sql Server
  8. C# Dataset with multiple tables - OLEDB
  9. C# Dataset table relations
  10. C# Dataset merge tables - Sql Server
  11. C# Dataset merge tables - OLEDB
  12. Bind a dataset to a combo box in C#, bind enum to combobox , bind dictionary to combobox
  13. How to find tables in a Database in C#

Related Links
  1. How to retrieve image from database