C# Saving Image to database (Not working)

Question

Im trying to save a cover of the song to a database with row type image. Currently its not saving anything I fill into this query into the database but my other querys are working fine.

 public void AddSong(string names, string artists, string bands, string album, string strFilePath, string strlyrics, string strmp3)
    {


        if (strFilePath != null)
        {
            Image temp = new Bitmap(strFilePath);
            MemoryStream strm = new MemoryStream();
            temp.Save(strm, System.Drawing.Imaging.ImageFormat.Jpeg);
            ImageByteArray = strm.ToArray();
        }

        SqlConnection con = new SqlConnection(ConnectionString);

        //SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Ruben\Documents\dbPlatenCompany.mdf;Integrated Security = True; Connect Timeout = 30");
        string query = "INSERT INTO [tblSongs] ([Name], [Artist], [Band], [Album], [Cover], [lyrics], [mp3]) VALUES ('" + names + "','" + artists + "','" + bands + "','" + album + "', @IMG,'" + strlyrics + "','" + strmp3 + "')";
        SqlCommand cmd = new SqlCommand(query, con);

        try
        {
            con.Open();
            cmd.Parameters.Add(new SqlParameter("@IMG", ImageByteArray));
            cmd.BeginExecuteNonQuery();
            MessageBox.Show("gelukt");

        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

EDIT: I get that storing the image in a database probally isn't the best way to go about it. But it's a small project for school so it will only be around 4 images.

BTW, you have an SQL injection vulnerability there. Use SQL parameters to avoid it. — dymanoid
– dymanoid, Commented Dec 17, 2018 at 16:56
Storing images into a database it's not a good idea. Upload the image instead and save the URL or filelocation of the image — H. Pauwelyn
– H. Pauwelyn, Commented Dec 17, 2018 at 17:06
Like the others said, Storing images is not a good, because it can take a lot of database storage, and databases are not really for storing huge data types like this really. The best you could do is just storing the picture path and then display it from that path(you have to upload it on a server first of course) — Boy pro
– Boy pro, Commented Dec 17, 2018 at 17:09
Try to use ExecuteNonQuery instead of BeginExecuteNonQuery. If you need asynchronous processing you also shold use EndExecuteNonQuery or better use the newer task based version ExecuteNonQueryAsync — H.G. Sandhagen
– H.G. Sandhagen, Commented Dec 17, 2018 at 17:09
That + has + not +been +the + right + way + to compose SQL for a very long time. It is tedious, hard to read and very error prone. Always use SQL Parameters — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Dec 17, 2018 at 17:33

Caius Jard · Accepted Answer · 2018-12-17 17:02:34Z

1

Declare the parameter to have an Image or VarBinary type, and a length large enough to hold it. You could also try declaring the length of the parameter as 0..

cmd.Parameters.Add("@IMG", SqlDbType.Image, ImageByteArray.Length).Value =  ImageByteArray);
cmd.Parameters.Add("@IMG", SqlDbType.VarBinary, ImageByteArray.Length).Value =  ImageByteArray);

Note that SqlDbType.Image is nothing to do with picture data (png, jpeg, etc) - it's SQLServer speak for "up to 2 gigabytes of binary data". You can store an MP3, Word doc etc in an Image type parameter

You should consider parameterizing the other variables too, while you're at it...

Edit: pay some good heed to the resource linked in the comment, and consider NOT storing this data in a binary column at all

edited Dec 17, 2018 at 17:02

answered Dec 17, 2018 at 16:54

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Polyfun Over a year ago

The image datatype is also deprecated, see stackoverflow.com/questions/444072/…. And the SqlConnection could be in a using block (for timely disposing).

im_ Ruben Over a year ago

Thanks alot ill definettly look into it im new to working with databases. But this doesn't seem to fix the problem for me.

Gaurav · Accepted Answer · 2018-12-17 18:37:45Z

0

See below code. I am hopeful that it will help- (I have only added the code to save the image in DB)

        FileStream fs = new FileStream(selectedFile, FileMode.Open, FileAccess.Read);
        byte[] bimage = new byte[fs.Length];
        fs.Read(bimage, 0, Convert.ToInt32(fs.Length));

        SqlConnection cn;
        cn = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated 
        Security=True");
        cn.Open();
        SqlCommand cmd = new SqlCommand("insert into MyTable (Image) 
        values(@imgdata)", cn);
        cmd.Parameters.AddWithValue("@imgdata", SqlDbType.Image).Value = bimage;
        cmd.ExecuteNonQuery();
        cn.Close();

answered Dec 17, 2018 at 18:37

Gaurav

6236 silver badges11 bronze badges

Collectives™ on Stack Overflow

C# Saving Image to database (Not working)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related