Storing PHOTOS in Mysql database through C# .Net

[InnocentVamp]

Hi all.

I want to ask about storing the photos in a MySql database. I am developing a Software for Tours & Traveling company in that i need to store the photos of the customer. The further requirement is to export the database to an Excel sheet including the photo.

So how I should store the photos, using MEDIUMBLOB of MySql or any other way out?

I know this is a very general question. Please reply as soon as possible.

Thank you in advance...

[sknake]

Storing files inside of SQL server has always been a controversial topic but yes, mediumblob should be just fine. You need to ask more specific questions if you want more detailed responses.

As for exporting to Excel the source doesn't really matter. You can use .NET to pull the data from MySQL and create the Excel spreadsheet. More than likely you will want to do something other than a "straight table dump" so it will be handy to have the application create the Excel files.

[MeSampath]

Hi,

Here is the MySQL-C# code to store images;

MySqlConnection mcon = null;
MySqlCommand cmd = null;
FileStream fsObj = null;
BinaryReader binRdr = null;
try
{
 //converting image to bytes
 fsObj = File.OpenRead(pictureBox1.ImageLocation);
 byte[] imgContent = new byte[fsObj.Length];
 binRdr = new BinaryReader(fsObj);
 imgContent = binRdr.ReadBytes((int)fsObj.Length);
 mcon = new MySqlConnection("server=localhost;user=root;pwd=root;database=test;");
 mcon.Open();
 
 //inserting into MySQL db
 cmd = new MySqlCommand("insert into users  (userid,username,userphoto) values (@userid, @username,  @userphoto)", mcon);
 cmd.Parameters.Add(new MySqlParameter("@userid",  (object)textBox1.Text));
 cmd.Parameters.Add(new MySqlParameter("@username",  (object)textBox2.Text));
 cmd.Parameters.Add(new MySqlParameter("@userphoto",  (object)imgContent));
 MessageBox.Show(cmd.ExecuteNonQuery().ToString() + " rows  affected");
}
catch (MySqlException mex)
{
 MessageBox.Show(mex.Message);
}
finally
{
 if (binRdr != null)binRdr.Close();
 binRdr = null;

 if (fsObj != null)fsObj.Close();
 fsObj = null;
  
 if (cmd != null)cmd.Dispose();
 cmd = null;

 if (mcon != null)
 {
  if (mcon.State == ConnectionState.Open)
  mcon.Close();
 } 
}

As Sknake said exporting MySQL data to excel doesn't matter. You can do pretty easily.

Good luck.

[InnocentVamp]

Thank you very much...

It is now clear that as per my requirement I can even use the MEDIUMBLOB datatype of MySql for storing the photos in the database...

Thank you...

[sknake]

Sounds good. Be sure to post back if you have any more questions

Please mark this thread as solved if you have found an answer to your question and good luck!

[noobsaibot]

Wsup the code above works well...but i have several queries how about displaying the saves image(in binary form) in a PictureBox and Crystal reports and also writing it from mysql to disk using the File.Write method? Please help out thanx and waiting for a reply.

[noobsaibot]

Wsup what about displaying the saved image(in Binary Format from MySQL) in a PictureBox and Crystal reports and also writting the file using ythe File.Write mehtod. please help help. Thank you and waiting for a reply. Cheers