Solved

store retrieve images.c#/mysql

Posted on 2008-10-09
7
6,224 Views
Last Modified: 2008-10-10
hi
i am playing with c# and mysql
i would like to save image from my application to mysql and then retrieve an image abd display it on an image control?
i am working with windows application
i have a database table images: id,path,image?
thanks
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.IO;
 
namespace saveimagetodatabase
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            byte[] data = null;
 
            //Use FileInfo object to get file size.
            FileInfo fInfo = new FileInfo(sPath);
            long numBytes = fInfo.Length;
 
            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open,
                                                    FileAccess.Read);
 
            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);
 
            //When you use BinaryReader, you need to 
 
            //supply number of bytes to read from file.
            //In this case we want to read entire file. 
 
            //So supplying total number of bytes.
            data = br.ReadBytes((int)numBytes);
            return data;
        }
       
        private void button1_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.Filter =
        "Images (*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|" +
        "All files (*.*)|*.*";
 
            // Allow the user to select multiple images.
            this.openFileDialog1.Multiselect = true;
            this.openFileDialog1.Title = "My Image Browser";
             DialogResult dr = this.openFileDialog1.ShowDialog();
             if (dr == System.Windows.Forms.DialogResult.OK)
             {
                 // Read the files
                 foreach (String file in openFileDialog1.FileNames)
                 {
                     // Create a PictureBox.
                     try
                     {
                         byte[] imagedata = ReadFile(file);
                             string myConnectionString=("DRIVER={MySQL ODBC 5.1 Driver};Database=klay;SERVER=localhost;USER=root;PASSWORD=;OPTION=3;");
                             string myExecuteQuery="insert into images (path,image) values ('"+file+"','"+imagedata+"')";
                             OdbcConnection myConnection = new OdbcConnection(myConnectionString);
                             OdbcCommand myCommand = new OdbcCommand(myExecuteQuery, myConnection);
                             myCommand.Connection.Open();
                             myCommand.ExecuteNonQuery();
                             myConnection.Close();
 
                             
 
                             
 
                         }
                         catch (Exception Ex)
                         {
                             MessageBox.Show(""+Ex);
                             
                         }
                        
                    
                 }
             }
 
        }
        public byte[] getImage()
        {
            OdbcConnection objConn =new OdbcConnection ("DRIVER={MySQL ODBC 5.1 Driver};Database=klay;SERVER=localhost;USER=root;PASSWORD=;OPTION=3;");
            string strSql = "SELECT * FROM images";
            DataSet ds = new DataSet("Image");
            OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql, objConn);
            OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
            tempAP.Fill(ds, "Table");
 
            try
            {
                objConn.Open();
                byte[] buffer = (byte[])ds.Tables["Table"].Rows[0]["image"];
                return buffer;
            }
            catch { objConn.Close(); return null; }
            finally { objConn.Close(); }
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
            byte[] buffer = getImage();
            System.IO.MemoryStream stream1 = new System.IO.MemoryStream(buffer, true);
            stream1.Write(buffer, 0, buffer.Length);
            Bitmap m_bitmap = (Bitmap)Bitmap.FromStream(stream1);
            
            
 
        }
 
        private void button2_Click(object sender, EventArgs e)
        {
            
        }
    }
}

Open in new window

0
Comment
Question by:klay8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:angus_young_acdc
ID: 22677694
Ok so are you getting an error message?  Or what is your question?
0
 
LVL 15

Accepted Solution

by:
angus_young_acdc earned 500 total points
ID: 22677714
That post submitted too early!

First, consider that loading images (and files in general) into a database is probably bad regarding the performance. If at all, you should store them in a separate database...

Besides that, you need to read about the .AppendChunk and .GetChunk to write/read to IMAGE (and TEXT) fields in SQL Server.
Another way to do this is store the file location in the database and simply place image files on the hard drive.  As far as removing the file just remove the record holding the location and then use the kill statement to delete the file off the hard drive.


Handling BLOB Data With Connector/NET
http://dev.mysql.com/doc/refman/5.0/en/connector-net-using-blob.html
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 22678701
I'm not sure if its still the case, but msql used to have a performance issue if blob fields were used in the same table as normal data. The query would load the blob into memory even if it was not part of the query.

The work around was to seperat blob data into a joined table so you could load normal data with no performance hit and only load the blob when needed.

I've watched a few descussions on the performance and advantages of database based images V files and not seen any conclusive arguments that makes one win over the other.  Theres pros and cons for both so its down to whats important on a case by case basis.
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 6

Expert Comment

by:alex_paven
ID: 22684571
Tiggerito nailed it; I just wanted to note that using MySql Connector/.NET is probably a better idea than odbc, and it may make this easier.
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 22687499
I just noticed your putting your byte[] called imagedata straight into a string. If I'm right this will set the field to "byte[]" as thats what byte[].ToString() does.

You need to convert your data into a format valid for the sql statement.

Here's some code I've used to do it...
public static string SqlString(Byte[] bytes)
{
 
    return "'" + ByteArrayToString(bytes).Replace("\\", "\\\\").Replace("'", "\\'") + "'";
}
private static string ByteArrayToString(byte[] b)
{      
	int len = b.Length;
 
	char[] chars = new char[len];
	for (int j=0; j<len; j++)
	{
		chars[j] = (char)b[j];
	}
 
	return new String(chars);  // this line is the kicker 
}

Open in new window

0
 

Author Comment

by:klay8
ID: 22691791
when it's saved to database i have this : System.Byte[]
and i dont have the imageso
1. how to save the image as an image?

2.how to display the image in a picturebox, can i have the code please?

thanks
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 22691983
See my comment above for saving bytes to the database.

This question has been closed so you should probably start a new one if you want another answer.

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question