Operations on the image data type in SQL Server

Preparations: Create a table im_info in the im_test database. The table has two fields: pr_id (INT) and pr_info (image), which are used to store the graphic numbers and information. The syntax is as follows:
Create teale im_info (
Pr_id int null,
Pr_info image null
)

Step 1: Insert a record to the table and initialize the pr_info field. The syntax is as follows:
Insert into im_info values (1, 0 xffffffff)

Step 2: Write graphic information to the table. The syntax is as follows:
Declare @ ptrval varbinary (16)
Select @ ptrval = textptr (pr_info)
From im_info
Where pr_id = 1
Writetext im_text.im_info
@ Ptrval 0x624fd543fd .....

0x624fd543fd ..... Is the hexadecimal data of the image, which can be obtained through C, Java, and other tools.

 

Note: before writing graphic information, you must set the 'select into/bulkcopy' attribute of the database to true. The syntax is as follows:
Use master
Exec sp_dboption im_test, 'select into/bulkcopy', true

 

C # Read image data type:

 

(1) console applicationsProgramThe following example shows how to insert an image.

Public void insertimg ()
{

// Read the image to be stored as a data stream
Filestream FS = new filestream (@ "E: \ c.jpg", filemode. Open, fileaccess. Read );
Byte [] btye2 = new byte [fs. Length];
FS. Read (btye2, 0, convert. toint32 (FS. Length ));
FS. Close ();

Using (sqlconnection conn = new sqlconnection (sqlconnstr ))
{
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Cmd. commandtext = "insert into t_img (imgfile) values (@ imgfile )";
Sqlparameter par = new sqlparameter ("@ imgfile", sqldbtype. Image );
Par. value = Bt;
Cmd. Parameters. Add (PAR );

Int t = (INT) (CMD. executenonquery ());
If (T> 0)
{
Console. writeline ("inserted successfully ");
}
Conn. Close ();
}
}

(2) read and generate images under the console application to a physical location

Public void read ()
{
Byte [] mydata = new byte [0];
Using (sqlconnection conn = new sqlconnection (sqlconnstr ))
{
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Cmd. commandtext = "select * From t_img ";
Sqldatareader SDR = cmd. executereader ();
SDR. Read ();
Mydata = (byte []) SDR ["imgfile"]; // read the bitstream of the first image
Int arraysize = mydata. getupperbound (0); // obtain the dimension upper limit of the bit stream array stored in the database, which is used as the upper limit for reading streams.

Filestream FS = new filestream (@ "C: \ 00.jpg", filemode. openorcreate, fileaccess. Write );
FS. Write (mydata, 0, arraysize );
FS. Close (); // -- write to c: \ 00.jpg.
Conn. Close ();
Console. writeline ("read succeeded"); // view files on the hard disk
}
}

(3) The picshow. ASPX page on the web reads the image and writes it to the browser for rendering.

Public void read ()
{
Byte [] mydata = new byte [0];
Using (sqlconnection conn = new sqlconnection (sqlconnstr ))
{
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Cmd. commandtext = "select * From t_img ";
Sqldatareader SDR = cmd. executereader ();
SDR. Read ();
Mydata = (byte []) SDR ["imgfile"];
Response. contenttype = "image/GIF ";
Response. binarywrite (mydata );
Conn. Close ();
Response. Write ("read successful ");
}

(4) You can read and display the image on the picshow. ASPX page on the web. The following is an example when the image is actually referenced:

(5) the method used to write an image to an image field of the SQL database under winform is basically the same as that used in the preceding method. It is only different from the method used to help select and store images, each attribute can be easily used.

(6) Reading images in winform is displayed in the picturebox control.

Method 1: Use memorystream and system. Drawing. Image

Public void read ()
{
Byte [] mydata = new byte [0];
Using (sqlconnection conn = new sqlconnection (sqlconnstr ))
{
Conn. open ();
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = conn;
Cmd. commandtext = "select * From t_img ";
Sqldatareader SDR = cmd. executereader ();
SDR. Read ();
Mydata = (byte []) SDR ["imgfile"];

Memorystream mystream = new memorystream (mydata );
// Create an image using the specified data stream
System. Drawing. Image IMG = system. Drawing. image. fromstream (mystream, true );

System. Windows. Forms. picturebox picbox = new picturebox ();
Picbox. Image = IMG;
Picbox. Left = 30;
Picbox. Top = 80;
Picbox. width = 800;
Picbox. Height = 500;
This. Controls. Add (picbox );

Mystream. Close ();
Conn. Close ();
}
}

Method 2: directly read the stream as an image and write it to the physical location. Then, use the image to present it.

void read ()
{< br> using (sqlconnection conn = new sqlconnection (sqlconnstr)
{< br> Conn. open ();
sqlcommand cmd = new sqlcommand ();
cmd. connection = conn;
cmd. commandtext = "select * From t_img";
sqldatareader SDR = cmd. executereader ();
SDR. read ();

Byte [] image_img = (byte []) SDR ["imgfile"];
If (image_img.length = 0)
{
Return;
}
Int filelength = image_img.length;
String imagename = "1.jpg ";
String myurl = environment. currentdirectory + "\" + imagename;
Filestream FS = new filestream (myurl, filemode. openorcreate, fileaccess. Write );
Binarywriter BW = new binarywriter (FS );
Bw. basestream. Write (image_img, 0, filelength );
Bw. Flush ();
Bw. Close ();
System. Windows. Forms. picturebox picbox = new picturebox ();

// Method 1 for adding an image to the picbox
// Picbox. imagelocation = myurl;
// Picbox. width = 800;
// Picbox. Height = 300;

 

// Method 2
Bitmap bitmap = new Bitmap (myurl );
Picbox. width = 100; // bitmap. width;
Picbox. Height = 80; // bitmap. height;
Picbox. Image = (image) bitmap;
Picbox. sizemode = system. Windows. Forms. pictureboxsizemode. stretchimage;
Picbox. Left = 20;
Picbox. Top = 30;

This. Controls. Add (picbox );
Conn. Close ();

}
}