Inserting an image into a SQL databse

  • (2 Pages)
  • +
  • 1
  • 2

27 Replies - 4537 Views - Last Post: 30 March 2010 - 08:32 AM Rate Topic: -----

#1 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Inserting an image into a SQL databse

Posted 29 March 2010 - 07:50 AM

Hi,

I recently asked about inage data types and how I could insert an image into a column in a table....

I'm still not having any luck, I've spent hours trying queries, initialising sqlConnections and everything, I'm just really lost and stressed that cant do something which should be so simple :(

Basically, I want to store an image in one column in my table, then call that image to be displayed in a picturebox when a data is typed in a textbox (txtScan) and a button is pressed (btnScan).

I have tried queries similar to the following:

INSERT [image file path]
INTO ItemImage (which is the column name)

My application I am developing is not a web application and I dont inted users to upload their own pictures, I just need my system to display that particular image when the row ID has been called if that makes sense?

Please can somebody help me, as I am losing my mind on this one :(

Thanks in advance guys. I hope someone can help, its so frustrating because it should be something so easy to implement.

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting an image into a SQL databse

#2 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 07:51 AM

Hi,

I recently asked about inage data types and how I could insert an image into a column in a table....

I'm still not having any luck, I've spent hours trying queries, initialising sqlConnections and everything, I'm just really lost and stressed that cant do something which should be so simple :(

Basically, I want to store an image in one column in my table, then call that image to be displayed in a picturebox when a data is typed in a textbox (txtScan) and a button is pressed (btnScan).

I have tried queries similar to the following:

INSERT [image file path]
INTO ItemImage (which is the column name)

My application I am developing is not a web application and I dont inted users to upload their own pictures, I just need my system to display that particular image when the row ID has been called if that makes sense?

Please can somebody help me, as I am losing my mind on this one :(

Thanks in advance guys. I hope someone can help, its so frustrating because it should be something so easy to implement.
Was This Post Helpful? 0
  • +
  • -

#3 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:02 AM

Still haven't managed to do that?! :-) And there is so many tutorials and examples that are written step by step. OK... What is your table schema where you want your image to be inserted. I'm interested in types of your columns. One of them needs to be of type image...
Was This Post Helpful? 0
  • +
  • -

#4 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:16 AM

This is self explanatory code for inserting image into your database:

private void InsertImage()
{
    SqlConnection conn = new SqlConnection(@"Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=testdb;User Id=test;Password=test;");
    Bitmap myImage = new Bitmap(@"C:\Pictures\image.jpg");
    ImageConverter converter = new ImageConverter();

    object temp = converter.ConvertTo(myImage, typeof(byte[]));
    byte[] imageBytes = (byte[])temp;

    string insertQuery = "INSERT INTO Employees (FirstName, LastName, Picture) " +
                         "VALUES(@FirstName, @LastName, @Picture)";

    SqlCommand cmd = new SqlCommand(insertQuery, conn);
    cmd.Parameters.AddWithValue("FirstName", "John");
    cmd.Parameters.AddWithValue("LastName", "Doe");
    cmd.Parameters.AddWithValue("Picture", imageBytes);

    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}


Was This Post Helpful? 4
  • +
  • -

#5 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:18 AM

I know, I feel so stupid about it :S, althought all of the tutorials I have found so far are about the user manually uploading images using a dialog. I odnt want to do that, I just want to display the image in a picturebox when the ItemID or ItemBarcode has been called.

Right, my table is called tblItem it contains 7 columns:

ItemID - int
ItemName - varchar(50)
ItemExpiry - datetime
ItemBarcode - nvarchar(50)
ItemImage - image
RecipeID - int
ItemLocation - varchar(50)


I want the image to be inserted/displayed in the ItemImage column.

Here is the code which I have so far which is used for calling the correct item via the ItemBarcode:

string Barcode; // { get; set; }
            string ImagePath; // { get; set; }
            string code;
            
            code = txtScan.Text;

string query = @"SELECT tblItem.ItemBarcode, tblItem.ItemImage
                    FROM tblItem WHERE tblItem.ItemBarcode = " + code;
            
            string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\Ad\Documents\Final Year Project\BlindAutoProject\BlindAutoProject\itemDatabase.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(query, con);

            con.Open();

  SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    //itemCollection.Add(new item() { Barcode = reader["ItemBarcode"].ToString(), ImagePath = reader["ItemImage"].ToString() });
                Barcode = reader["ItemBarcode"].ToString();
                ImagePath = reader["ItemImage"].ToString();

                }


This code is working correctly at the moment.

All I need now is for it to display the image but as you know I have no idea how to even get it in the database :(

Many thanks again.

Oh thanks, I didnt see your reply because I was still typing mine :S

Would this code be placed within my btnScan event?
Was This Post Helpful? 0
  • +
  • -

#6 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:27 AM

And here's the code for getting the image from database and displaying it in a picturebox:

private void GetImage()
{
    SqlConnection conn = new SqlConnection(@"Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=testdb;User Id=test;Password=test;");

    string query = "SELECT Picture FROM Employees WHERE EmployeeID = @EmployeeID";

    SqlCommand cmd = new SqlCommand(query, conn);
    cmd.Parameters.AddWithValue("EmployeeID", 5);

    conn.Open();
    object temp = cmd.ExecuteScalar();
    conn.Close();

    Stream stream = new MemoryStream((byte[])temp);
    Bitmap image = new Bitmap(stream);

    pictureBox1.Image = image;
}



Look at the code I posted... There is not really much more to say... Use my code and change it so that it suites your needs and your application... If you get stuck, drop a line and we'll try to help you out... Don't forget to click a '+' button to say thanks...
Was This Post Helpful? 4
  • +
  • -

#7 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:34 AM

Ok, thanks.


I have tailored the code so it contains the correct names for my fields.

I am having an error on 'Stream' and 'Memory Stream'?

The error reads,
Error 1 The type or namespace name 'Stream' could not be found (are you missing a using directive or an assembly reference?)
Was This Post Helpful? 0
  • +
  • -

#8 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:38 AM

You need to add the using statement at the top of your form class:

using System.IO;

Was This Post Helpful? 0
  • +
  • -

#9 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:51 AM

well there are no errors!

Now need to work out how to actually display the picture....

But thanks very much for the help it is much appreciated!

It is amazing how something so simple can become so tedious...

thanks again though :)
Was This Post Helpful? 0
  • +
  • -

#10 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 08:56 AM

For the record storing your images in a database table is just a horrible idea. Now keep in mind I'm not telling you to not do it, just pointing out some issues with it.

First it's a huge performance hit. Whenever you have to show an image you have to show an image you have to use a ton of resources just to display it. Second, if something were to happen then you got no images to display.

Just some things to think about.
Was This Post Helpful? 0
  • +
  • -

#11 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 09:01 AM

I can't seem to get the image to display still.

Here is my code which you gave me after I have tailored it:
            private void InsertImage()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\Ad\Documents\BlindAutoProject\BlindAutoProject\itemDatabase.mdf"";Integrated Security=True;User Instance=True");
            Bitmap myImage = new Bitmap(@"C:\Pictures\image.jpg");
            ImageConverter converter = new ImageConverter();

            object temp = converter.ConvertTo(myImage, typeof(byte[]));
            byte[] imageBytes = (byte[])temp;

            string insertQuery = "INSERT INTO tblItem (ItemImage) " +
                                 "VALUES(@ItemImage)";

            SqlCommand cmd = new SqlCommand(insertQuery, conn);
            cmd.Parameters.AddWithValue("ItemImage", imageBytes);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private void GetImage()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\Ad\Documents\Final Year Project\BlindAutoProject\BlindAutoProject\itemDatabase.mdf"";Integrated Security=True;User Instance=True");

            string query = "SELECT ItemImage FROM tblItem WHERE ItemImage = @ItemImage";

            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("ItemImage", 5);

            conn.Open();
            object temp = cmd.ExecuteScalar();
            conn.Close();

            Stream stream = new MemoryStream((byte[])temp);
            Bitmap image = new Bitmap(stream);

            pictureBox1.Image = image;


        }
           


Is this correct?

Admin Edit: Please use code tags when posting your code. Code tags are used like so => :code:

Thanks,
PsychoCoder :)
Was This Post Helpful? 0
  • +
  • -

#12 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 09:03 AM

I posted you also a code how to get an image from database and display it in a picture box... Just scroll up this thread to find it...

Do you get any exception thrown?
Was This Post Helpful? 0
  • +
  • -

#13 Sn0wBum  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 178
  • Joined: 04-February 10

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 09:13 AM

Have you tried changing your image type in your DB to varbinary(max)?
Was This Post Helpful? 0
  • +
  • -

#14 toff182  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 08-December 08

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 09:20 AM

View PostFlashM, on 29 March 2010 - 08:03 AM, said:

I posted you also a code how to get an image from database and display it in a picture box... Just scroll up this thread to find it...

Do you get any exception thrown?


No I didnt have any exceptions or errors. But I dont understand how it pulls the correct image out and how it does it?

Also, nothing is being diplayed. I type the item barcode into txtScan hit btnScan and nothing appears....

View PostSn0wBum, on 29 March 2010 - 08:13 AM, said:

Have you tried changing your image type in your DB to varbinary(max)?


No, I assumed I would have to keep the datatype as image.

If I was to change the datatype to varbinary would it affect the code I have just done...

I just want a simple way to type in a item number in a text box, hit the button and it displays the correct image.
Was This Post Helpful? 0
  • +
  • -

#15 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Inserting an image into a SQL databse

Posted 29 March 2010 - 09:21 AM

Topics merged, please don't create duplicates :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2