C # Read the MySQL blob Field

Development Environment: Windows XP Professional SP3, vs2008, winform, mysql5.0, MySQL. data6.2.3.0

 

1. Read an image from the hard disk, convert it to a stream, and store it in this blob field.

View plaincopy to clipboardprint?

1. Private void button#click (Object sender, eventargs E)
2. {
3. Byte [] bytes = NULL;
4. Bytes = file. readallbytes (@ "C:/Documents and Settings/user/My Documents ents/my pictures/11.jpg ");
5. Using (MySQL. Data. mysqlclient. mysqlconnection conn = new mysql. Data. mysqlclient. mysqlconnection ())
6. {
7. Conn. connectionstring = configurationmanager. connectionstrings ["test"]. connectionstring;
8. MySQL. Data. mysqlclient. mysqlcommand cmd = new mysql. Data. mysqlclient. mysqlcommand ();
9. Cmd. commandtext = "insert into test (ID, picture) values (@ ID, @ picture )";
10. Cmd. commandtype = commandtype. text;
11. Cmd. Parameters. Add ("@ ID", MySQL. Data. mysqlclient. mysqldbtype. int32 );
12. Cmd. Parameters. Add ("@ picture", MySQL. Data. mysqlclient. mysqldbtype. Blob );
14. Cmd. Parameters [0]. value = 15;
15. Cmd. Parameters [1]. value = bytes;
16. Cmd. Connection = conn;
17. Conn. open ();
19. Int affectedrows = cmd. executenonquery ();
21. Cmd. Dispose ();
22. Conn. Close ();
23. }
25. }

Private void button#click (Object sender, eventargs e) <br/>{< br/> byte [] bytes = NULL; <br/> bytes = file. readallbytes (@ "C:/Documents and Settings/user/My Documents/my pictures/11.jpg"); <br/> using (MySQL. data. mysqlclient. mysqlconnection conn = new MySQL. data. mysqlclient. mysqlconnection () <br/>{< br/> Conn. connectionstring = configurationmanager. connectionstrings ["test"]. connectionstring; <br/> MySQL. data. mysqlclient. mysqlcommand cmd = new MySQL. data. mysqlclient. mysqlcommand (); <br/> cmd. commandtext = "insert into test (ID, picture) values (@ ID, @ picture)"; <br/> cmd. commandtype = commandtype. text; <br/> cmd. parameters. add ("@ ID", MySQL. data. mysqlclient. mysqldbtype. int32); <br/> cmd. parameters. add ("@ picture", MySQL. data. mysqlclient. mysqldbtype. BLOB); </P> <p> cmd. parameters [0]. value = 15; <br/> cmd. parameters [1]. value = bytes; <br/> cmd. connection = conn; <br/> Conn. open (); </P> <p> int affectedrows = cmd. executenonquery (); </P> <p> cmd. dispose (); <br/> Conn. close (); <br/>}</P> <p>}

 

2. Read this blob field and convert it to the image display on the picturebox control.

View plaincopy to clipboardprint?

1. Private void button2_click (Object sender, eventargs E)
2. {
3. Using (MySQL. Data. mysqlclient. mysqlconnection conn = new mysql. Data. mysqlclient. mysqlconnection ())
4. {
5. Conn. connectionstring = configurationmanager. connectionstrings ["test"]. connectionstring;
6. Conn. open ();
8. MySQL. Data. mysqlclient. mysqlcommand cmd = new mysql. Data. mysqlclient. mysqlcommand ();
9. Cmd. commandtype = commandtype. text;
10. Cmd. commandtext = "select ID, picture from test where id = 11 ";
11. Cmd. Connection = conn;
13. System. Data. Common. dbdatareader reader = cmd. executereader ();
14. Byte [] buffer = NULL;
15. If (reader. hasrows)
16. {
17. Reader. Read ();
18. Long Len = reader. getbytes (1, 0, null, 0, 0); // 1 is picture
    
19. Buffer = new byte [Len];
20. Len = reader. getbytes (1, 0, buffer, 0, (INT) Len );
22. System. Io. memorystream MS = new system. Io. memorystream (buffer );
23. System. Drawing. Image iamge = system. Drawing. image. fromstream (MS );
24. Picturebox1.image = iamge;
25. }
26. }
28. }

Private void button2_click (Object sender, eventargs e) <br/>{< br/> using (MySQL. data. mysqlclient. mysqlconnection conn = new MySQL. data. mysqlclient. mysqlconnection () <br/>{< br/> Conn. connectionstring = configurationmanager. connectionstrings ["test"]. connectionstring; <br/> Conn. open (); </P> <p> MySQL. data. mysqlclient. mysqlcommand cmd = new MySQL. data. mysqlclient. mysqlcommand (); <br/> cmd. commandtype = commandtype. text; <br/> cmd. commandtext = "select ID, picture from test where id = 11"; <br/> cmd. connection = conn; </P> <p> system. data. common. dbdatareader reader = cmd. executereader (); <br/> byte [] buffer = NULL; <br/> If (reader. hasrows) <br/>{< br/> reader. read (); <br/> long Len = reader. getbytes (1, 0, null, 0, 0); // 1 is picture <br/> buffer = new byte [Len]; <br/> Len = reader. getbytes (1, 0, buffer, 0, (INT) Len); </P> <p> system. io. memorystream MS = new system. io. memorystream (buffer); <br/> system. drawing. image iamge = system. drawing. image. fromstream (MS); <br/> picturebox1.image = iamge; <br/>}</P> <p>}

 

Database-related files are configured in APP. config. If you do not need a configuration file, you can write it as follows:

String remote = "Persist Security info = false; database = lhwtouch; server = Server IP address; user id = user name; Pwd = password ";

Then conn. connectionstring = remote.

 

 

Postscript:

 

The MySQL database used in. NET is mysqldrivercs, but it has never been done, and reading BLOB fields officially failed. Use MySQL. Data instead. Note that mysql. data5.0 does not support reading BLOB fields. Therefore, a higher version is required. I use MySQL. data6.2.3.0.

 

MySQL. data6.2.3.0: http://download.csdn.net/source/2968152

MySQL. data5.0.9.0: http://download.csdn.net/source/2968157

 

Code provider:Http://hi.csdn.net/DobzhanskyThank you!

 

From: http://blog.csdn.net/config_man/article/details/6123191