Adding text and image to database with C# and SQL

Question

The following windows form application takes an input for name, age, gender, description, and an image.

However when clicking the "Save Information" button the following exception error occurs:

System.Data.SqlClient.SqlException: " Operand type clash: nvarchar is incompatible with image"

Not particularly sure why the script is interpreting the image as a nvarchar datatype.

Here is the stored procedure for the function.

ALTER PROCEDURE [dbo].[usp_Students_InsertNewStudent]
(
    @StudentName NVARCHAR(200)
    ,@Age NVARCHAR(50)
    ,@Gender NVARCHAR(50)
    ,@Description NVARCHAR(MAX)
    ,@Image IMAGE
    ,@CreatedBy NVARCHAR(50)
)
AS
    BEGIN
        INSERT INTO [dbo].[Students]
           ([StudentName]
           ,[Age]
           ,[Gender]
           ,[Description]
           ,[Image]
           ,[CreatedBy]
           ,[CreatedDate])
     VALUES
        (
            @StudentName
            ,@Age
            ,@Gender
            ,@Description
            ,@Image
            ,@CreatedBy
            ,GETDATE()
        )

    END

And the relevant code.

        private void SaveButton_Click(object sender, EventArgs e)
        {
            if(IsFormValid())
            {
                //Do Update Process
                using (SqlConnection con = new SqlConnection(AppConnection.GetConnectionString())) 
                {
                    using (SqlCommand cmd = new SqlCommand("usp_Students_InsertNewStudent", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.AddWithValue("@StudentName", StudentNameTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Age", AgeTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Gender", GenderTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Description", DescriptionTextBox.Text.Trim());
                        cmd.Parameters.AddWithValue("@Image", IdPictureBox.ImageLocation);
                        cmd.Parameters.AddWithValue("@CreatedBy", LoggedInUser.UserName);


                        if (con.State != ConnectionState.Open)
                            con.Open();

                        cmd.ExecuteNonQuery();

                        MessageBox.Show("Student is successfully updated in the database.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ResetFormControl();
                    }
                }
            }

        private bool IsFormValid()
        {
            if (StudentNameTextBox.Text.Trim() == string.Empty)
            {
                MessageBox.Show("Student name is Required.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                StudentNameTextBox.Focus();
                return false;
            }

            if (StudentNameTextBox.Text.Length >= 200)
            {
                MessageBox.Show("Student Name length should be less than or equal to 200 characters.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                StudentNameTextBox.Focus();
                return false;
            }
            return true;
        }

        private void UploadButton_Click(object sender, EventArgs e)
        {
            String ImageLocation = "";
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "Image Files(*.jpeg;*.bmp;*.png;*.jpg)|*.jpeg;*.bmp;*.png;*.jpg";

                if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                   ImageLocation = dialog.FileName;
                   IdPictureBox.ImageLocation = ImageLocation;
                }
            }
            catch (Exception)
            {
                MessageBox.Show("An Error Occured", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

Answer 1

Your issue is this line:

cmd.Parameters.AddWithValue("@Image", IdPictureBox.ImageLocation);

This is a string of where the image is located. Which is getting translated from C# string to SQL nvarchar.

You want to insert the actual image. I suspect this may work alone:

cmd.Parameters.AddWithValue("@Image", IdPictureBox.Image);

But I always try to avoid AddWithValue because it infers the type.

Here's another way:

cmd.Parameters.Add("@Image", SqlDbType.Image).Value = IdPictureBox.Image;

Note this explicitly defines the SQL type, where AddWithValue infers it.

For this to work make sure you specify the SqlDbType that matches the schema.

Here's how to do the same, for example, using a byte array:

var image = IdPictureBox.Image;
using (var ms = new MemoryStream())
{
    image.Save(ms, image.RawFormat);
    cmd.Parameters.Add("@Image", SqlDbType.VarBinary).Value = ms.ToArray();
}

In either case the data type is explicit using this method.

Answer 2

The type in the database is IMAGE, which is a binary data type. But then you are trying to save the path to the image file in that field. The path to the image file is text (VARCHAR).

Do you want to save the image in the database, or a reference to the path of the image? If the former, the database field would be better as VARBINARY, and then you will actually have to save the byte array of the image into the field, not the path to the image.

If the latter, the field type should be VARCHAR and then you can proceed as you have.