how can i store and retrieve the images in mysql database using asp.net with c#?

Question

how can i store and retrieve the images in mysql database using asp.net with c#?

Answer 1

Hello,



What you are trying to do is a little difficult because you have to convert a Bitmap to a byte array, in memory. Here, I have filled a dataTable from a database, where I have BLOB column.





Here is the code:



MySqlConnection connection = databaseConnection.Connection;



SceneMetaData metaData = metaDataList[0] as SceneMetaData;

Bitmap start = metaData.SnapshotStart;



byte [] bytes = BmpToBytes_MemStream(start);



String query = "INSERT INTO thumbnail VALUES(NULL,1,@First,@Middle,@End," +

"@FirstTime,@SecondTime,@ThirdTime)";

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = connection;

cmd.CommandText = query;

cmd.CommandTimeout = 10000;





cmd.Parameters.AddWithValue("@First",bytes);

cmd.Parameters.AddWithValue("@Middle", bytes);

cmd.Parameters.AddWithValue("@End", bytes);

cmd.Parameters.AddWithValue("@FirstTime", "000000");

cmd.Parameters.AddWithValue("@SecondTime", "000000");

cmd.Parameters.AddWithValue("@ThirdTime", "000000");

cmd.ExecuteNonQuery();







The problem was originally that being used was a BLOB which has a capacity of 64K, not enough for the images. If you change this to MEDIUMBLOB everything should work.







Good Luck!



Sean Colicchio

Server Engineer

Host My Site

http://www.hostmysite.com/?utm_source=bb

Answer 2

You can use BLOBs (Binary Large Objects) in MySQL. You also have to store the MIME type in a different field, and filename if you need it.



You can use a SQLCommand to do the query and SQLDataReader to do the read.

Handle the image as a byte[];





Edit:

justJR, I respectfully disagree that it's always a bad idea to do so. There are advantages and disadvantages.



The big down side is that it can be slower and take more space to insert and retrieve binary data from a database than a file, though recent DBs have greatly improved their performance so there's not much in it.



The advantage is that it improves data integrity, security, and often performance if conditional querying is needed. In other words, it's good for binary and other data that needs a database!



For integrity, it's useful to bundle any binary data with other associated data that requires relationality. It avoids having to track and manage references to files in a separate filing system. For backups, clustering, virtualising, deploying and portability, that's a big advantage.



It's an additional barrier to any security threat that would have to burrow inside a complex leaf-structured datafile than an ordinary disc file.



It allows greater flexibility for managing generic binary file types. There's no way a filing system could handle that.



I have tried both methods, and for all but the simplest requirements, I find DB binary storage significantly superior.

Answer 3

It is always a bad idea to store the IMAGE files in a database: very inefficient, uses huge amounts of space, and slows the queries considerably.

What you store in DBs are the FILENAME (or some id), and store the files in a given folder on your server/computer.

Answer 4

On a respond to the word above, you are able to in reality shop pictures in databases with the column form "blob", inspite of the undeniable fact that you'll base64_encode the imade to conver to a string and then shop as a VARCHAR or textual content.

Answer 5

depends what you are using , I would probably suggest a module which will do this for you