C # operate Image data in SQL Server

This is an example of SQL Server Data Type operation, with the write and read functions.

1: Prepare the database

1) Create a database Test

2) Create Table Table_1 (which has two fields: id (Int) and photo (Image ))

 

2: Use C # For read/write operations. The complete code is as follows:

View plaincopy to clipboardprint?
Using System;
Using System. Collections. Generic;
Using System. ComponentModel;
Using System. Data;
Using System. Drawing;
Using System. Linq;
Using System. Text;
Using System. Windows. Forms;
Using System. Data. SqlClient;
Using System. IO;
 
Namespace imageTest
{
Public partial class Form1: Form
{
Public Form1 ()
{
InitializeComponent ();
}
 
Private void btn_brewse_Click (object sender, EventArgs e)
{
OpenFileDialog op = new OpenFileDialog ();
Op. Title = "browse image files ";
Op. Filter = "image file (*. jpg) | *. jpg ";
Op. ShowDialog ();
Txt_ImageAddress.Text = op. FileName;
}
 
Private void btn_Insert_Click (object sender, EventArgs e)
{
FileStream fs = new FileStream (txt_ImageAddress.Text, FileMode. Open, FileAccess. Read );
Byte [] byteArray = new byte [fs. Length];
Fs. Read (byteArray, 0, Convert. ToInt32 (fs. Length ));
Fs. Close ();
 
String connectionStr = "Server = ..; Database = Test; Uid = sa; Pwd = 123456 ";
SqlConnection conn = new SqlConnection (connectionStr );
Conn. Open ();
SqlCommand cmd = new SqlCommand ("insert into Table_1 (photo) VALUES (@ photo)", conn );
SqlParameter parmeter = new SqlParameter ("@ photo", SqlDbType. Image );
Parmeter. Value = byteArray;
Cmd. Parameters. Add (parmeter );
Int result = cmd. ExecuteNonQuery ();
If (result> 0)
MessageBox. Show ("inserted successfully ");
Conn. Close ();
}
 
Private void btn_ReadImage_Click (object sender, EventArgs e)
{
String connectionStr = "Server = ..; Database = Test; Uid = sa; Pwd = 123456 ";
SqlConnection conn = new SqlConnection (connectionStr );
Conn. Open ();
SqlCommand cmd = new SqlCommand ("SELECT * FROM Table_1", conn );
SqlDataReader dr = cmd. ExecuteReader ();
Dr. Read ();
Byte [] image = (byte []) dr ["photo"];
Conn. Close ();
If (image. Length = 0)
Return;
String photoUrl = Environment. CurrentDirectory + "\ 1.jpg ";
FileStream fs = new FileStream (photoUrl, FileMode. OpenOrCreate, FileAccess. Write );
BinaryWriter bw = new BinaryWriter (fs );
Bw. BaseStream. Write (image, 0, image. Length );
Bw. Flush ();
Bw. Close ();
 
Picbox_image.ImageLocation = photoUrl;
}
 
}
}
Using System;
Using System. Collections. Generic;
Using System. ComponentModel;
Using System. Data;
Using System. Drawing;
Using System. Linq;
Using System. Text;
Using System. Windows. Forms;
Using System. Data. SqlClient;
Using System. IO;

Namespace imageTest
{
Public partial class Form1: Form
{
Public Form1 ()
{
InitializeComponent ();
}

Private void btn_brewse_Click (object sender, EventArgs e)
{
OpenFileDialog op = new OpenFileDialog ();
Op. Title = "browse image files ";
Op. Filter = "image file (*. jpg) | *. jpg ";
Op. ShowDialog ();
Txt_ImageAddress.Text = op. FileName;
}

Private void btn_Insert_Click (object sender, EventArgs e)
{
FileStream fs = new FileStream (txt_ImageAddress.Text, FileMode. Open, FileAccess. Read );
Byte [] byteArray = new byte [fs. Length];
Fs. Read (byteArray, 0, Convert. ToInt32 (fs. Length ));
Fs. Close ();

String connectionStr = "Server = ..; Database = Test; Uid = sa; Pwd = 123456 ";
SqlConnection conn = new SqlConnection (connectionStr );
Conn. Open ();
SqlCommand cmd = new SqlCommand ("insert into Table_1 (photo) VALUES (@ photo)", conn );
SqlParameter parmeter = new SqlParameter ("@ photo", SqlDbType. Image );
Parmeter. Value = byteArray;
Cmd. Parameters. Add (parmeter );
Int result = cmd. ExecuteNonQuery ();
If (result> 0)
MessageBox. Show ("inserted successfully ");
Conn. Close ();
}

Private void btn_ReadImage_Click (object sender, EventArgs e)
{
String connectionStr = "Server = ..; Database = Test; Uid = sa; Pwd = 123456 ";
SqlConnection conn = new SqlConnection (connectionStr );
Conn. Open ();
SqlCommand cmd = new SqlCommand ("SELECT * FROM Table_1", conn );
SqlDataReader dr = cmd. ExecuteReader ();
Dr. Read ();
Byte [] image = (byte []) dr ["photo"];
Conn. Close ();
If (image. Length = 0)
Return;
String photoUrl = Environment. CurrentDirectory + "\ 1.jpg ";
FileStream fs = new FileStream (photoUrl, FileMode. OpenOrCreate, FileAccess. Write );
BinaryWriter bw = new BinaryWriter (fs );
Bw. BaseStream. Write (image, 0, image. Length );
Bw. Flush ();
Bw. Close ();

Picbox_image.ImageLocation = photoUrl;
}

}
}

 

 

Effect

 

Author: "Andrew's Blog"