SAVE VARBINARY IMAGE IN DATABASE

Question

Hello, I have a windows form application, I already have a button to upload via Filedialog to my picture box, displaying it. Follows the code

private void button2_Click(object sender, EventArgs e)
    {
        OpenFileDialog dialog = new OpenFileDialog();
        dialog.Filter = "JPG Files(*.jpg)|*.jpg|PNG Files(*.png)|*.png|AllFiles(*.*)|*.*";

        if(dialog.ShowDialog() == DialogResult.OK)
        {
            string foto = dialog.FileName.ToString();
            pictureBox1.ImageLocation = foto;
        }

    }

My question is the following, how I can save in my sql database this image that is in this picture box, using another button, because I have 4 VARBINARY columns, and I want to upload each one at a time and then save everything in a " register " only. Follow the form image to understand my idea.

someone would help me ?

Answer 1

Before for an example of how to do this I leave a few considerations that you should take into account before moving to that approach. And as at everything in life it is not easy to decide.

Choose that path if:

• You are storing images that will change dynamically
• Legal need to keep a file for a period of time.
• Images saved in the database do not require a backup strategy.
• It is easier to control access to them.

Don’t choose that path if

• Require additional code to map the disk image with the database identifier
• Latency may be slower than direct file access
• Server load and response time is critical.
• There is no restriction on the Image Size. In case there is no control on the size of the images.

Example of how to do this:

private void Save()
{
    string sourcefilePath = @"C:\temp\foto.jpg";
    string storageFilePath = @"C:\storage\";
    string cnString = @"Integrated Security = SSPI; Initial Catalog = <Your Data Base>; Data Source = <server>";
    string uniqueidentifier = Guid.NewGuid().ToString();

    using (SqlConnection connection = new SqlConnection(cnString))
    {
        string command = $"INSERT INTO Images VALUES ('{uniqueidentifier}', '{sourcefilePath}')";
        SqlCommand cmd = new SqlCommand(command, connection);
        cmd.Connection.Open();
        cmd.ExecuteNonQuery();

        File.Copy(sourcefilePath, Path.Combine(storageFilePath, uniqueidentifier) + ".jpg");

    }

Answer 2

Below is an example not yet tested, but with the necessary code to convert the Picturebox control image and save it to a table in the Sql server database. Make your adaptation to support saving more than one image. In the example below I considered the image in Jpeg format.

string nomeFoto;
string connectionString;

    private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "JPG Files(*.jpg)|*.jpg|PNG Files(*.png)|*.png|AllFiles(*.*)|*.*";

            if(dialog.ShowDialog() == DialogResult.OK)
            {
                string foto = dialog.FileName.ToString();
                nomeFoto = foto;
                pictureBox1.ImageLocation = foto;

            }

        }

     //Handle do botão Enviar para salvar uma imagem na Tabela CadastroFoto.
     // (Adaptar para as 4 imagens que você quer salvar)
    private void btnEnviar_Click(object sender, EventArgs e)
    {
         byte[] fotoByte = ImageJpegToByte(pictureBox1.Image);
         using (SqlConnection connection = new SqlConnection(
               connectionString))
         {
            // Comando insert
             SqlCommand addImageCmd= new SqlCommand(
                    "INSERT INTO CadastroFoto("+
                    "Nome, Imagem) "+
                    "VALUES(@Nome, @Imagem)",connection );

            addImageCmd.Parameters.Add("@Nome", SqlDbType.NVarChar, 20).Value = nomeFoto;
            addImageCmd.Parameters.Add("@Imagem", SqlDbType.Image, fotoByte.Length).Value = fotoByte;

            //Abre a conexão e faz o INSERT do registro no banco
            addImageCmd.Connection.Open();
            addImageCmd.ExecuteNonQuery();
          }
    }

    //Método para conversão de uma imagem .Jpeg para um array de bytes
    public byte[] ImageJpegToByte(System.Drawing.Image img)
    { 
       MemoryStream ms = new MemoryStream();
       img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
       byte[] img = ms.ToArray();
       return img;
    }