How to upload image to SQL Server using C#

Question

I originally had the data type for the image column in the database set to image, but I have since then changed it to VARBINARY(MAX). However, when I attempt to run my code and upload an image to store in that column, every record is showing NULL. I believe the database is fine and that the problem must be with how I am passing the image data into the SQL query. So my question is this: What do I need to modify to get an image uploaded to the database in the correct format? Image retrieval is planned to be implemented.

Here is what the database looks like. I included the column name, data type, and the results I get when selecting rows. No errors were returned when I attempted to upload the image. While the column DOES allow for null values, each of the records were attempts to upload an image. enter image description here

Below is the code for the "Add" button that handles communicating with the database. I have made an attempt to cut out anything unrelated to this problem, such as closing the form.

private void addButton_Click(object sender, EventArgs e)
        {
            string strConnect = @"Server=mainserver\SQLEXPRESS; Database=Inventory; Integrated Security=SSPI;";
            SqlConnection con = new SqlConnection(strConnect);
            DatabaseUtility db = new DatabaseUtility();
            string sqlStart = @"INSERT INTO Vehicles(manufacturer, model, date_Acquired, vin, year, cost";
            string sqlEnd = @" VALUES('" + makeBox.Text + "', '" + modelBox.Text + "', '" + datePicker.Text + 
                                        "', '" + vinBox.Text + "', '" + yearBox.Text + "', '" + costBox.Text;
            string sql;

            if (vinBox.TextLength != 17)
            {
                MessageBox.Show("The VIN entered is invalid", "Invalid VIN");
            }
            else if (String.IsNullOrWhiteSpace(makeBox.Text) || String.IsNullOrWhiteSpace(modelBox.Text) || 
                String.IsNullOrWhiteSpace(yearBox.Text) || String.IsNullOrWhiteSpace(vinBox.Text) || 
                String.IsNullOrWhiteSpace(costBox.Text))
            {
                MessageBox.Show("Not all required fields are filled", "Missing Information");
            }
            else
            {
                if (!String.IsNullOrWhiteSpace(askingBox.Text))
                {
                    sqlStart += ", asking_Price";
                    sqlEnd += "', '" + askingBox.Text;
                }
                if (!String.IsNullOrWhiteSpace(categoryComboBox.Text))
                {
                    sqlStart += ", category";
                    sqlEnd += "', '" + categoryComboBox.Text;
                }
                if (!String.IsNullOrWhiteSpace(additionalNotesBox.Text))
                {
                    sqlStart += ", additional_Notes";
                    sqlEnd += "', '" + additionalNotesBox.Text;
                }
                if (!openFileDialog1.CheckFileExists)
                {
                    sqlStart += @", image";
                    sqlEnd += "', '@image";
                }
                sqlStart += ")";
                sqlEnd += "')";
                sql = sqlStart + sqlEnd;
                SqlCommand insertCommand = new SqlCommand(sql, con);
                if (!openFileDialog1.CheckFileExists)
                {
                    SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@image", (object)GetImage(openFileDialog1.FileName));
                    sqlParam.DbType = DbType.Binary;
                }
                try
                {
                    MessageBox.Show(insertCommand.ToString(), "Invalid Input");
                    con.Open();
                    insertCommand.ExecuteNonQuery();
                    con.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("error in insertcommand" + ex, "Invalid Input");
                }
                finally
                {
                    insertCommand.Connection.Close();
                }
            }
        }

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Oct 23, 2014 at 6:37
@Soner Gönül I have come across a lot of posts giving the same advice. I do plan to correct this security flaw, but one problem at a time. — asd
– asd, Commented Oct 23, 2014 at 6:39
Is it really necessary to store image in SQL server? You can store the path instead, if it's not required to be secured & can allow direct access. — Raptor
– Raptor, Commented Oct 23, 2014 at 6:39
@jayvee, by documentation Image data type is marked as deprecated now. It will be removed in next version of SQL Server(2016). varbinary is recommended to use instead. — rufanov
– rufanov, Commented Oct 23, 2014 at 7:39

Stephan Bauer · Accepted Answer · 2014-10-23 06:56:53Z

2

You try to use FileDialog.CheckFileExists to check whether the selected file exists, but this property does not work that way - It gets a value indicating whether the dialog box displays a warning if the user specifies a file name that does not exist.

So if that property is set to true - and that's the default value for OpenFileDialog - , your code that adds the parameter @image will never be executed.

If you want to check whether the file exists, you could use File.Exists

answered Oct 23, 2014 at 6:56

Stephan Bauer

9,2565 gold badges39 silver badges61 bronze badges

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

2 Comments

asd Over a year ago

I did not notice this problem. I have made the change, but an error shows when attempted to include an image in the insert query. "Implicit conversion from data type varchar to varbinary(max) is not allowed." However, the data I am passing it is a byte[].

Stephan Bauer Over a year ago

Could you add your implementation of GetImage?

Collectives™ on Stack Overflow

How to upload image to SQL Server using C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related