Image insert and retrieve from MySql Database.

Question

1.00/5 (1 vote)

See more:

I am developing a system and I have to store the images of the persons, tell me how should I do that. How can I insert the images in mysql and able to retrieve them in C# Windows Application. What should I do?

Posted 29-May-11 21:00pm

TMSRANA

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wamuti · Answer 1 · 2012-07-12T03:32:00

If you are using Windows Forms, its pretty easy.

For storing:

conn = new MySqlConnection("server=" + hostname + ";uid=" + username + ";pwd=" + password + ";database=databaseimage;Charset=latin1;");
                conn.Open();
                FileStream fs;
                Byte[] bindata;
                MySqlParameter picpara;
                cmd = new MySqlCommand("INSERT INTO mypic (pic) VALUES(?pic)", conn);
                picpara = cmd.Parameters.Add("?pic", MySqlDbType.MediumBlob);
                cmd.Prepare();

//txtPicPath is the path of the image, e.g. C:\MyPic.png

                fs = new FileStream(txtPicPath.Text, FileMode.Open, FileAccess.Read);
                bindata = new byte[Convert.ToInt32(fs.Length)];
                fs.Read(bindata, 0, Convert.ToInt32(fs.Length));
                fs.Close();

                picpara.Value = bindata;
                cmd.ExecuteNonQuery();

To retrieve it:

if (conn == null) // Just to make sure that the connection was not severed
            {

           
                    conn = new MySqlConnection("server=" + hostname + ";uid=" + username + ";pwd=" + password + ";database=databaseimage;Charset=latin1;");
                    conn.Open();
               
            }
            MemoryStream ms = new MemoryStream();
            FileStream fs;
            Byte[] bindata;

            cmd = new MySqlCommand("SELECT pic FROM mypic WHERE id=3", conn);
            bindata = (byte[])(cmd.ExecuteScalar());
            


            ms.Write(bindata, 0, bindata.Length);
            pb2.Image = new Bitmap(ms);
          
            fs = new FileStream(name, FileMode.Create, FileAccess.Write);
            ms.WriteTo(fs);

Notes:
pb1 is a picture box that allows me to see the preview and pb2 is where the retrieved image is shown.
The database name is databaseimage
The table name is mypic.
The table has two columns, pic (type is mediumblob) and id (type is int and it is autoincreament). The id column allows one to select different pictures in the db, e.g. for my case i am getting row 3:

cmd = new MySqlCommand("SELECT pic FROM mypic WHERE id=3", conn);

OriginalGriff · Answer 2 · 2011-05-29T21:09:00

Solution 1

There is an article here: Storing Images in MySQL using ASP.NET[^] - it is for ASP.NET rather than Winforms, but the database mechanism is the same.

Posted 29-May-11 21:09pm

OriginalGriff

Comments

xz21cn 31-May-11 6:04am

good solution, I think this can solve the problem.

arminamini · Answer 3 · 2011-05-29T21:20:00

if u want, u can Save images in DB as a binary like this :

MemoryStream msQ = new MemoryStream();
// pic is an image
pic.Save(msQ, ImageFormat.Jpeg);

                        SqlCommand cmd = new SqlCommand("USP_INS_Pic", new SqlConnection(new AppSettingsReader().GetValue("CS", typeof(string)).ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

And retrive it :

MemoryStream msPic = new MemoryStream();
 SqlCommand cmdPic = new SqlCommand("SELECT * FROM  tPic", new SqlConnection(new AppSettingsReader().GetValue("CS", typeof(string)).ToString()));

cmdPic.Connection.Open();
SqlDataReader sdr = cmdPic.ExecuteReader();
while (sdr.Read())
{
    byte[] BPic = (byte[])sdr["pic"];
    if (BPic.Length > 0)
    {
        msPic.Write(BPic, 0, BPic.Length);
// Pic is n Image
        Pic = Image.FromStream(msPic, true);
     }

sdr.Close();
            cmdPic.Connection.Close();

TMSRANA · Answer 4 · 2011-05-30T17:55:00

Solution 3

What Do you mean by "USP_INS_Pic","CS"

Posted 30-May-11 17:55pm

TMSRANA

Comments

arminamini 31-May-11 5:43am

USP_INS_Pic ,is an example for one store procedure that insert a pic as varbinar into database,and it means u should a table that has a varbinary field for image, and write insert query(as procedure o inside ur code behind).

and CS is the ky of ur connectionstring/appsettig. u can set ur appsettings like this in app.config :

<appsettings>
<add key="CS" value="server=local;data source=.;database=yourdbName;Integrated Security=True">

arminamini · Answer 5 · 2011-05-30T23:45:00

Solution 4

XML

<appSettings>
  <add key="CS" value="server=local;data source=.;database=OnlineExam;Integrated Security=True"/>
</appSettings>

Posted 30-May-11 23:45pm

arminamini