Re: Save Images in SQl Server - C# Discussion Boards

14-Sep-13 17:13

Hello, I have a doubt, how to save an image in the bank. I have a class to convert the image to byte and then save. I do not know the correct way, ask for help.

class:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Drawing.Imaging;
using ImagensTeste.Dados;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace ImagensTeste.Negocio
{
class RegraNegocioFotos
{
private int id;

public int Id
{
get { return id; }
set { id = value; }
}
private string descricao;

public string Descricao
{
get { return descricao; }
set { descricao = value; }
}
private Image foto;

public Image Foto
{
get { return foto; }
set { foto = value; }
}
private Image foto1;

public Image Foto1
{
get { return foto1; }
set { foto1 = value; }
}

public byte[] ConvertImageToByteArray(Image image, ImageFormat imageFormat)
{
if (image == null)
return null;
MemoryStream ms = new MemoryStream();
image.Save(ms, imageFormat);
return ms.ToArray();
}

public Image ConvertByteArrayToImage(byte[] byteArray)
{
if (byteArray == null || byteArray.Length == 0)
{ return (null); }
return (Image.FromStream(new MemoryStream(byteArray)));
}

Banco dal = new Banco();

//Metodo inserir

public void InserirFoto()
{
try
{
dal.ComandoSQl("INSERT INTO tbFoto(descricao,foto, foto1) VALUES('" + Descricao + "','" + Foto + "','" + Foto1 + "')");
}
catch (Exception ex)
{
throw ex;
}
}
//Metodo atualizar

public void AtualizaFoto()
{
try
{
dal.ComandoSQl("UPDATE tbFoto set descricao='" + Descricao + "', foto='" + Foto + "', foto1='" + Foto1 + "'where id= " + Id + "");
}
catch (Exception ex)
{
throw ex;
}
}

//Metodo excluir

public void ExcluirFoto()
{
try
{
dal.ComandoSQl("DELETE FROM tbFoto where id= " + Id + "");
}
catch (Exception ex)
{
throw ex;
}
}

//Metodo preencher datagrid

public void PreencherDataGrid(DataGridView dgv)
{
dal.Consultar(dgv, "SELECT * FROM tbFoto");
}

//Metodo consultar codigo prefeitura

public void ConsultarId(DataGridView dgv)
{
dal.Consultar(dgv, "SELECT * FROM tbFoto WHERE id=" + Id + "");
}

//Metodo consultar razao social prefeitura

public void ConsultarNumero(DataGridView dgv)
{
dal.Consultar(dgv, "SELECT * FROM tbFoto WHERE descricao Like '%" + Descricao + "%'");
}
}
}

Form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using ImagensTeste.Negocio;

namespace ImagensTeste
{
public partial class frmFoto : Form
{
public frmFoto()
{
InitializeComponent();

//Alterar do app.config

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings["ImagensTeste.Properties.Settings.fotosConnectionString"].ConnectionString =
ConfigurationManager.ConnectionStrings["ImagensTeste.Properties.Settings.fotosConnectionString"].ConnectionString;
config.Save(ConfigurationSaveMode.Full);

// ler novamente a seção do HD
ConfigurationManager.RefreshSection("connectionStrings");
}

RegraNegocioFotos negocio = new RegraNegocioFotos();

private void habilitarControles()
{
txtDescricao.Enabled = true;
}

public void LimparCampo()
{
this.LimparCampo(this);
}

public void LimparCampo(Control parent)
{
foreach (Control cont in parent.Controls)
{
if (cont is TextBox)
(cont as TextBox).Text = "";
if (cont is PictureBox)
(cont as PictureBox).Text = "";
if (cont is ComboBox)
(cont as ComboBox).Text = "";
if (cont is MaskedTextBox)
(cont as MaskedTextBox).Text = "";
if (cont is RadioButton)
(cont as RadioButton).Checked = false;
if (cont is CheckBox)
(cont as CheckBox).Checked = false;
if (cont is RichTextBox)
(cont as RichTextBox).Text = "";
if (cont is CheckedListBox)
{
foreach (ListControl item in (cont as CheckedListBox).Items)
item.SelectedIndex = -1;
}
if (cont is ListView)
(cont as ListView).Items.Clear();

// varre os filhos...
this.LimparCampo(cont);
}
}

private void btnGravar_Click(object sender, EventArgs e)
{
try
{
// Campos obrigatorios
if ((txtDescricao.Text != "") && (txtDescricao.Text != ""))
{
{
RegraNegocioFotos cln = new RegraNegocioFotos();
cln.Descricao = this.txtDescricao.Text;

//???
cln.Foto = this.picBrasao.Image;

cln.InserirFoto();

{
txtDescricao.Focus();
}

LimparCampo();

MessageBox.Show("Documento cadastrada com sucesso", "Sucesso",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}

{
txtDescricao.Focus();

}
}
else
{
MessageBox.Show("Campos Obrigatórios: Numero e Descrição", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information);

}
}
catch (Exception ex)
{
//Caso haja uma exceção será tratada neste código
MessageBox.Show("Não foi possível salvar pelo seguinte motivo: " + ex.Message);
}
}

private void btnProcurarImagen1_Click(object sender, EventArgs e)
{
openBrasao.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";
DialogResult res = openBrasao.ShowDialog();

if (res == DialogResult.OK)
{
txtCaminho1.Text = openBrasao.FileName;
picBrasao.Image = Image.FromFile(openBrasao.FileName);
}
}

private void btnProcurarImagen2_Click(object sender, EventArgs e)
{
openLogo.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";
DialogResult res = openLogo.ShowDialog();

if (res == DialogResult.OK)
{
txtCaminho2.Text = openLogo.FileName;
picLogo.Image = Image.FromFile(openLogo.FileName);
}
}
}
}

thank you...

PIEBALDconsult14-Sep-13 18:12

14-Sep-13 18:12

I'm not going to try to read all that and try to figure out what you're doing wrong, but I'll point out two bad things that jump out at me:

dal.ComandoSQl("UPDATE tbFoto set descricao='" + Descricao + "', foto='" + Foto + "', foto1='" + Foto1 + "'where id= " + Id + "");

Don't use concatenation to build a query; use a parameterized query (this may actually be the whole problem).

catch (Exception ex)<br />
{<br />
throw ex;<br />
}

This is actually three problems in one.
Try to catch a specific Exception rather than all Exceptions.
Don't catch and rethrow without doing anything else.
If you rethrow (after doing something else), then just use throw, not throw ex.

In this case, you can remove the entire try/catch.

sigridslima15-Sep-13 14:57

15-Sep-13 14:57

Hello altered the way can save but not found the way to update and delete, please ask for a sample and how do I save if an image field null, because in this instance can not.

private void btnGravar_Click(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(txtCaminho1.Text))
{
if (new FileInfo(txtCaminho1.Text).Exists)
{
if (!String.IsNullOrEmpty(txtCaminho1.Text))
{
if (!String.IsNullOrEmpty(txtCaminho2.Text))
{
if (new FileInfo(txtCaminho2.Text).Exists)
{
Image Image1 = Image.FromFile(txtCaminho1.Text);
Image Image2 = Image.FromFile(txtCaminho2.Text);
using (SqlConnection Conexao = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\fotos.mdf;Integrated Security=True;Connect Timeout=30"))
{
Conexao.Open();
using (SqlCommand Comando = Conexao.CreateCommand())
{
Comando.CommandType = CommandType.Text;
Comando.CommandText = "INSERT INTO tbFoto (descricao,foto,foto1) values(@descricao,@foto,@foto1);";
Comando.Parameters.Add("@descricao", System.Data.SqlDbType.VarChar, 50).Value = this.txtDescricao.Text;
Comando.Parameters.Add("@foto", SqlDbType.Image).Value = ConvertImageToByteArray(Image1, ImageFormat.Jpeg);
Comando.Parameters.Add("@foto1", SqlDbType.Image).Value = ConvertImageToByteArray(Image2, ImageFormat.Jpeg);
if (Comando.ExecuteNonQuery() > 0)
{
MessageBox.Show("Imagem gravada com êxito", "Imagem: Ok", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
Conexao.Close();
getImagensSQLServer(conexaoSQLServer);
LimparCampo();
btnGravar.Visible = false;
btnGravaNegocio.Visible = true;
}
}
else
{
MessageBox.Show("Imagem inválida", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
}

public byte[] ConvertImageToByteArray(Image image, ImageFormat imageFormat)
{
if (image == null)
return null;
MemoryStream ms = new MemoryStream();
image.Save(ms, imageFormat);
return ms.ToArray();
}

public Image ConvertByteArrayToImage(byte[] byteArray)
{
if (byteArray == null || byteArray.Length == 0)
{ return (null); }
return (Image.FromStream(new MemoryStream(byteArray)));
}

PIEBALDconsult15-Sep-13 16:26

15-Sep-13 16:26

I still won't read it thoroughly, but it seems better. Thumbs Up | :thumbsup:

To store a NULL, set the value of the parameter to DBNull.Value.

sigridslima16-Sep-13 2:55

16-Sep-13 2:55

Put it more in error

Comando.Parameters.Add("@foto1", SqlDbType.Image)DBNull.Value = ConvertImageToByteArray(Image2, ImageFormat.Jpeg);

error:

Error 1 ; expected

Error 2 A static readonly field cannot be assigned to (except in a static constructor or a variable initializer)

Error 3 Cannot implicitly convert type 'byte[]' to 'System.DBNull'

PIEBALDconsult16-Sep-13 3:47

16-Sep-13 3:47

No .

Comando.Parameters.AddWithVAlue("@foto1", DBNull.Value );

Also, don't bother trying to set the data type; the framework will do that when you set the value.

sigridslima16-Sep-13 5:21

16-Sep-13 5:21

Presents no error in the description, but when I run the application the error in

Comando.Parameters.Add("@descricao", System.Data.SqlDbType.VarChar, 50).Value = this.txtDescricao.Text;
Comando.Parameters.AddWithValue("@foto", DBNull.Value).Value = ConvertImageToByteArray(Image1, ImageFormat.Jpeg);
Comando.Parameters.AddWithValue("@foto1", DBNull.Value).Value = ConvertImageToByteArray(Image2, ImageFormat.Jpeg);

error:

if (Comando.ExecuteNonQuery() > 0)

The parameterized query '(@descricao varchar(50),@foto nvarchar(4000),@foto1 nvarchar(400' expects the parameter '@foto', which was not supplied.

PIEBALDconsult16-Sep-13 13:37

16-Sep-13 13:37

Then I don't know; report the SQL statement.

And remove the Value = ConvertImageToByteArray(Image2, ImageFormat.Jpeg) part.

sigridslima16-Sep-13 14:35

16-Sep-13 14:35

It worked like this:

if ((picBrasao.Image != null)

Comando.Parameters.AddWithValue("@foto", DBNull.Value).Value = ConvertImageToByteArray(picBrasao.Image, ImageFormat.Jpeg);

Thank you brother

C# Application - SQL Syntax Problem

OriginalGriff14-Sep-13 22:17

OriginalGriff

14-Sep-13 22:17

As PIEBALDconsult says, you shouldn't even think about concatenating strings to form an SQL command - not only does it leave you vulnerable to SQL Injection attacks which could damage or destriy your database, it also causes significant problems with Images as well.

See here: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^] - it explains why it's a problem and how to save and reload the image properly.

This message is manufactured from fully recyclable noughts and ones. To recycle this message, please separate into two tidy piles, and take them to your nearest local recycling centre.
Please note that in some areas noughts are always replaced with zeros by law, and many facilities cannot recycle zeroes - in this case, please bury them in your back garden and water frequently.

Member 1026694314-Sep-13 16:34

Member 10266943

14-Sep-13 16:34

Hi All,

Im sure this is very simple but after googling for the past 4 hours i cannot seem to find the correct syntax.

I currenly have a SQL which amongst other tables has a DateTime Coloum in the folllowing Format:

2013-09-11 21:06:08:970

I am currently running the follow query from within my C# Application:
select * from table where errorcode = 448

What I would like to do is:
select * from table where errorcode = 448, where date = today

and also
select * from table where errorcode = 448, where date is between 2013-09-11 00:00:00:000 and 2013-09-11 23:59:59:999

I just cant figure out the correct syntax for the queries though.

Could anybody possible Help?

Many Thanks
SG

Re: C# Application - SQL Syntax Problem

PIEBALDconsult14-Sep-13 17:17