Direct image upload to BD Mysql using C#

Question

I am creating a WEB application using C# and a Mysql BD. I need to upload an image to the bank and then retrieve it.

I’m a beginner in this world, but what I have so far:

I have a field fupCPF which receives the image, then run Insert below:

string comando = "SELECT imagem_cpf FROM tb_cpf WHERE id_usuario=@loginUsuario";
MySqlCommand cmd = new MySqlCommand(comando, mConn);

string comando = "INSERT INTO tb_cpf (id_usuario, numero, imagem_cpf) VALUES (@id_usuario, @numero, @imagem_cpf)";
MySqlCommand cmd = new MySqlCommand(comando, conexao);
//preenchimento dos parâmetros
cmd.Parameters.AddWithValue("@id_usuario", emailUsuario);
cmd.Parameters.AddWithValue("@numero", txtCodCPF.Text.ToString());

byte[] imageBytes = new byte[fupCPF.PostedFile.InputStream.Length + 1];
fupCPF.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length);

cmd.Parameters.AddWithValue("@imagem_cpf", imageBytes);
conexao.Open();
cmd.ExecuteNonQuery();
conexao.Close();

lblImgCPFErro.Text = "INSERIDO COM SUCESSO!!";

I insert something into the bank (a .bin file) but I don’t know how to test if it’s working... Could someone help me?

I created a page for viewing, page_Load has the following code (after connecting to the Database)

string comando = "SELECT imagem_cpf FROM tb_cpf WHERE id_usuario=@loginUsuario";
MySqlCommand cmd = new MySqlCommand(comando, mConn);

//preenchimento dos parâmetros
cmd.Parameters.AddWithValue("@loginUsuario", loginUsuario);
mConn.Open();

MySqlDataReader myReader = cmd.ExecuteReader();

if (myReader.Read())
{
    Response.ContentType = "image/jpeg";
   Response.BinaryWrite((byte[])myReader["imagem_cpf"]);

}

myReader.Close();
mConn.Close();

Someone can give me a light?

Answer 1

Hello alive as already indicated the best practice is to save the images in the folder published on the server or in a block.

I share additional details to compare based on my experience:

DB

The storage of blob in the database will cause the size of the storage to increase considerably, I was involved in a project that for an option of architecture, was so carried out and that same table in a few months grew to the 100GB, solution separate the table from the images to another DB, getting the db core at 3/4 GB, imagine every day was made a backup, which required a strategy of optimizing disk space, and had to be well defined strategy for a Restore for example, since 100GB is quite different from 3/4GB. In short, this architecture, image stored in the database, works, but is not the best approach by the problems/constraints that I listed among others, for example, sometimes asking for images of a particular record to analyze, had to be extracted with a SW and generate a PDF for example, while if it was a Storage blob, simply share the path of the original image.

Blob Sorage

For example with Microsoft technologies, Storage blob, and save in the database only the path (path) of the image. You can use to access, there are tools for this, check here.

Alternatively there is the AWS Bucket, S3, you have documentation here.

Answer 2

I do in Folder normally not to overload the database, although I have already done this and if I need I can search in my old projects.

Below is an excerpt from the source I upload to my current project

if (Request.Files["Arquivo" ] != null && Request.Files["Arquivo" ].FileName != "")
            {
                veiculoOcorrenciaAnexoViewModel.NomeArquivo = Path.GetFileName(Request.Files["Arquivo" ].FileName);
                string path = Request.PhysicalApplicationPath + "\\Arquivos\\Veiculos\\Ocorrencias\\" + veiculoOcorrenciaAnexoViewModel.VeiculoOcorrenciaId + "\\" ;
                if (!System.IO.Directory.Exists(path))
                    System.IO.Directory.CreateDirectory(path);

                Request.Files["Arquivo" ].SaveAs(path + veiculoOcorrenciaAnexoViewModel.NomeArquivo);
                _vOcorrenciaAnexoAppService.Adicionar(veiculoOcorrenciaAnexoViewModel);    
            }