[C#] Attempting to store an image in mysql (longblob) is failing...

[sathenzar]

Please tell me what stupid thing I am doing. Here is my code:

private void storeImg()
        {
            FileStream fs = new FileStream(this.xMainImgPathTxt.Text, FileMode.Open, FileAccess.Read);
            BinaryReader reader = new BinaryReader(fs);
            byte[] imgData = reader.ReadBytes((int)fs.Length);
            try
            {
                MySqlCommand mCmd = new MySqlCommand(string.Format("INSERT INTO testimg(img_data) VALUES('{0}');", imgData), this.gl_mCon);
                MySqlParameter mParm = new MySqlParameter("img_data", MySqlDbType.LongBlob);
                mParm.Size = imgData.Length;
                mParm.Value = imgData;
                mCmd.Parameters.Add(mParm);
                int rAff = mCmd.ExecuteNonQuery();
                System.Diagnostics.Debug.WriteLine("Rowcted: {0}", rAff);
                mCmd.Dispose();
                mCmd = null;
            }
            catch
            {
            }
            reader.Close();
            reader.Dispose();
            fs.Close();
            fs.Dispose();
            fs = null;
            reader = null;
        }

I just want to store an image in a mysql field. I know this is possible. But when the record posts it just shows as [bLOB-13] with 13 bytes of data instead of the actual image.

[72001]

  On 01/01/2012 at 21:01, sathenzar said:

Please tell me what stupid thing I am doing. Here is my code:

private void storeImg()
		{

			try
			{
			FileStream fs = new FileStream(this.xMainImgPathTxt.Text, FileMode.Open, FileAccess.Read);
			BinaryReader reader = new BinaryReader(fs);
			byte[] imgData = reader.ReadBytes((int)fs.Length);
				MySqlCommand mCmd = new MySqlCommand(string.Format("INSERT INTO testimg(img_data) VALUES('{0}');", imgData), this.gl_mCon);
				MySqlParameter mParm = new MySqlParameter("img_data", MySqlDbType.LongBlob);
				mParm.Size = imgData.Length;
				mParm.Value = imgData;
				mCmd.Parameters.Add(mParm);
				int rAff = mCmd.ExecuteNonQuery();
				System.Diagnostics.Debug.WriteLine("Rowcted: {0}", rAff);
				mCmd.Dispose();
				mCmd = null;
			}
			finally
			{			
			reader.Close();
			reader.Dispose();
			fs.Close();
			fs.Dispose();
			fs = null;
			reader = null;
			}
		}

I just want to store an image in a mysql field. I know this is possible. But when the record posts it just shows as [bLOB-13] with 13 bytes of data instead of the actual image.

The code above has been corrected, declare all the variables in the try scope since you don't need them outside also don't scope a catch block just to have it there, use a finally instead... or better yet do a using on the FileStream and then it will save you from having to close it...

Then to show the image you need to set the ContentType = "image/bmp" get a Bitmap instance from the bytes using new Bitmap(bytes) and then use the bitmap instances Save method on the Response Stream where you are serving the image from....

[sathenzar]

I had it that way before, however it just says in the record [bLOB - 13B] and when I try to read it it says it can't do anything with the data.

MySqlCommand mCmd = new MySqlCommand(string.Format("SELECT * FROM testimg LIMIT 1;"), this.gl_mCon);
			MySqlDataReader mReader = mCmd.ExecuteReader();
			while (mReader.Read())
			{
				byte[] buffer = (byte[])mReader["img_data"];
				using (var stream = new MemoryStream(buffer))
				{
					var imageSource = BitmapFrame.Create(stream); // <---------------------------- THROWS ERROR HERE
					this.xMainImg.Source = imageSource;
				}
			}
			mReader.Close();
			mReader.Dispose();
			mReader = null;
			mCmd.Dispose();
			mCmd = null;

EDIT: The buffer being read in is only 13 bytes. So something is going wrong with the post. The database shows it as:

[Jay1969]

I am not too familiar with SQL but string.Format("INSERT INTO testimg(img_data) VALUES('{0}');", imgData) will print the following. "INSERT INTO testimg(img_data) VALUES('System.Byte[]');"

I think you want the actal values in that that string.

[GreenMartian]

Read up on using Parameterized Queries. I don't know what library you use, but usually the syntax should be something like the following:

MySqlCommand mCmd = new MySqlCommand("INSERT INTO testimg(img_data) VALUES(@SomeData);", this.gl_mCon);
MySqlParameter mParm = new MySqlParameter("@SomeData", MySqlDbType.LongBlob);
mParm.Size = imgData.Length;
mParm.Value = imgData;
mCmd.Parameters.Add(mParm);[/CODE]

[sathenzar]

+GreenMartin you are correct, I had it that way but didn't get the results I needed b/c of the parameter method I was using. I figured it out. The examples on the internet on google btw, are terrible examples if you are using WPF. Here is the code (it requires a @ symbol for the variable names). For future reference THIS is the correct code to write an image to a database record (below is the included code to read it).

FileStream fs = new FileStream(this.xMainImgPathTxt.Text, FileMode.Open, FileAccess.Read);
                BinaryReader reader = new BinaryReader(fs);
                byte[] imgData = reader.ReadBytes((int)fs.Length);
                MySqlCommand mCmd = new MySqlCommand("INSERT INTO testimg(img_data) VALUES(@img_data);", this.gl_mCon);
                mCmd.Parameters.AddWithValue("@img_data", imgData);

                int rAff = mCmd.ExecuteNonQuery();
                System.Diagnostics.Debug.WriteLine("Rowcted: {0}", rAff);
                mCmd.Dispose();
                mCmd = null;
                reader.Close();
                reader.Dispose();
                fs.Close();
                fs.Dispose();
                fs = null;
                reader = null;

To read it from a database:

MySqlCommand mCmd = new MySqlCommand(string.Format("SELECT * FROM testimg LIMIT 1;"), this.gl_mCon);
            MySqlDataReader mReader = mCmd.ExecuteReader();
            while (mReader.Read())
            {
                byte[] buffer = (byte[])mReader["img_data"];
                    BitmapImage bi = new BitmapImage();
                    bi.BeginInit();
                    bi.StreamSource = new MemoryStream(buffer);
                    bi.EndInit();
                    this.xMainImg.BeginInit();
                    this.xMainImg.Source = bi;
                    this.xMainImg.EndInit();
            }
            mReader.Close();
            mReader.Dispose();
            mReader = null;
            mCmd.Dispose();
            mCmd = null;