Save Byte[] in odbc with c#

Question

i need help with a little problem.

private void SaveButton_Click(object sender, EventArgs e)
{
    MemoryStream fingerprintData = new MemoryStream();
    Template.Serialize(fingerprintData);
    fingerprintData.Position = 0;
    BinaryReader br = new BinaryReader(fingerprintData);
    Byte[] bytes = br.ReadBytes((Int32)fingerprintData.Length);
    HL.RegistroHuella(ComboBx, LabelMs, bytes);
}

This is the save button...

public void RegistroHuella(ComboBox ComboBx, Label LabelMs, Byte[] bytes)
{
    try
    {
        string hola;
        ConexionHuella();
        hola = ComboBx.SelectedIndex.ToString();
        DbCommand = DbConnection.CreateCommand();
        DbCommand.CommandText = "SELECT * FROM HUELLAS WHERE ID = " + hola + "";
        DbReader = DbCommand.ExecuteReader();
        if (DbReader.Read())
        {
            LabelMs.Text = "El estudiante ya existe en la base de datos";
        }
        else
        {
            DbReader.Close();
            DbCommand.CommandText = "INSERT INTO HUELLAS VALUES('" + hola + "','" + bytes + "')";
            DbReader = DbCommand.ExecuteReader();
            if (DbReader.RecordsAffected > 0)
            {
                LabelMs.Text = "El estudiante ha sido registrado correctamente.";
            }
            else
            {
                LabelMs.Text = "Hubo un problema al momento de registrar a este usuario.";
            }
        }
        DbConnection.Close();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        //LabelMs.Text = ex.Message;
    }
}

The problem is the next... When i pressed the save button, the code generates a register in my odbc but in the field "Huella" didn't insert well.

The image: -> Problem

You inserted the value of byte[].ToString(), that happens when you use the + operator. Google "dbcommand insert bytes" for hits, several existing SO posts talk about it. — Hans Passant, Oct 12 '17 at 11:22
@JuanMolina - the .Net database commands for the various databases are very similar (by design). So while that solution is not a 100% fit, you might be able to adapt it (Use a `Db` prefix instead of `Sql`) — Hans Keﬆing, Oct 12 '17 at 12:51

Richard Deeming · Accepted Answer · 2017-10-12T12:47:11.713

2

Fixing the SQL Injection vulnerability in your code will also solve the problem with inserting the image data.

You should avoid storing connection and command objects in fields. Instead, they should be created as local variables, and wrapped in using statements to ensure that their resources are always cleaned up.

If you can't move the DbConnection field to a local variable yet, then you should call its Close method in a finally block.

You shouldn't call ExecuteReader on a command that isn't going to return any records - INSERT, UPDATE or DELETE commands. Instead, call ExecuteNonQuery, which returns the number of rows affected.

You also don't need to call ExecuteReader to test whether a record exists. Just select the ID of the first matching record, and use ExecuteScalar to return that value.

public void RegistroHuella(ComboBox ComboBx, Label LabelMs, Byte[] bytes)
{
    try
    {
        int hola = ComboBx.SelectedIndex;

        ConexionHuella();

        using (var command = DbConnection.CreateCommand())
        {
            command.CommandText = "SELECT TOP 1 ID FROM HUELLAS WHERE ID = ?";
            command.Parameters.AddWithValue("@hola", hola);

            if (command.ExecuteScalar() != null)
            {
                LabelMs.Text = "El estudiante ya existe en la base de datos";
                return;
            }
        }

        using (var command = DbConnection.CreateCommand())
        {
            command.CommandText = "INSERT INTO HUELLAS VALUES (?, ?)";
            command.Parameters.AddWithValue("@hola", hola);
            command.Parameters.AddWithValue("@bytes", bytes);

            int recordsAffected = command.ExecuteNonQuery();
            if (recordsAffected > 0)
            {
                LabelMs.Text = "El estudiante ha sido registrado correctamente.";
            }
            else
            {
                LabelMs.Text = "Hubo un problema al momento de registrar a este usuario.";
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        //LabelMs.Text = ex.Message;
    }
    finally
    {
        DbConnection.Close();
    }
}

NB: The OdbcCommand doesn't seem to work with named parameters. You have to use ? as the parameter placeholder, and ensure that the parameters are added to the collection in the same order as they appear in the query.

edited Oct 12 '17 at 12:47

answered Oct 12 '17 at 12:03

Richard Deeming

29,830
10
79
151

Thx, i tried your solution but i get errors in the parameters. https://imgur.com/a/PtVQ3 – Juan Molina Oct 12 '17 at 12:26
@JuanMolina: That error seems to be saying there are too few parameters being passed. Which line is it thrown from? *(Check the `ex.StackTrace`, or comment out the `catch` block and debug the code to see where it stops.)* – Richard Deeming Oct 12 '17 at 12:36
https://imgur.com/a/QgD94 – Juan Molina Oct 12 '17 at 12:42
@JuanMolina: Ah, OK; you're using ODBC, which [doesn't seem to like named parameters](https://stackoverflow.com/a/18082957/124386). You'll probably need to use `?` as the placeholder instead. I'll update my answer. – Richard Deeming Oct 12 '17 at 12:45
I need to use int because is an identification and i need an image. I tried but i get errors in the parameters – Juan Molina Oct 12 '17 at 12:52
@JuanMolina: What errors? – Richard Deeming Oct 12 '17 at 12:53
few parameters. – Juan Molina Oct 12 '17 at 12:56
@JuanMolina: On which line? – Richard Deeming Oct 12 '17 at 12:58
https://i.imgur.com/StWgaKs.png - https://imgur.com/a/8LeFy – Juan Molina Oct 12 '17 at 13:00
@JuanMolina: As I said previously, ODBC doesn't support named parameters. You need to use `?` as the parameter placeholder within the command text. See my updated answer. – Richard Deeming Oct 12 '17 at 13:02
oh, i undestand. I have another error different with the parameters https://imgur.com/a/yc16r – Juan Molina Oct 12 '17 at 13:08
@JuanMolina: Sounds like a mis-match between the column and parameter data types. Try specifying the `OdbcType` and size for each parameter. – Richard Deeming Oct 12 '17 at 13:12
Thx you, i solved my problem. :) – Juan Molina Oct 12 '17 at 13:29

apomene · Answer 2 · 2017-10-12T12:31:45.653

0

1st USE PARAMETERS, it is very important.

EDIT: (based on Richard Deeming comment)

DbCommand.CommandText = "INSERT INTO HUELLAS VALUES(@param1,@param2)";                
DbCommand.Parameters.Add("@param1",OdbcType.Text).Value = hola;
DbCommand.Parameters.Add("@param2",OdbcType.Image).Value = bytes;
int result = DbCommand.ExecuteNonQuery();
if (result> 0)
{
   LabelMs.Text = "El estudiante ha sido registrado correctamente ";
}
else
{
   LabelMs.Text = $"Hubo un problema al momento de registrar a este usuario";
}

edited Oct 12 '17 at 12:31

answered Oct 12 '17 at 11:16

apomene

14,282
9
46
72

I tried your solution but i have an error in parameters. https://imgur.com/a/IVudS – Juan Molina Oct 12 '17 at 11:40
@JuanMolina, see my edit – apomene Oct 12 '17 at 11:42
I doubt you want a separate `INSERT` for every byte in the array! A single `INSERT` will do. – Richard Deeming Oct 12 '17 at 11:48
i tried again but i have another error. https://i.imgur.com/G0wM9Uh.png Translate: error to convert the parameter value Byte to Byte[] – Juan Molina Oct 12 '17 at 12:19
@JuanMolina, use my edit, don't use a foreach loop – apomene Oct 12 '17 at 12:22
i tried but i get problems with the parameters because i use odbc, not sql. I change with... DbCommand.Parameters.Add("@param1", OdbcType.NVarChar).Value = hola; DbCommand.Parameters.Add("@param2", OdbcType.VarBinary).Value = bytes; And i get errors in the parameters – Juan Molina Oct 12 '17 at 12:28
@JuanMolina, Edited, See my edit – apomene Oct 12 '17 at 12:32
the same https://imgur.com/a/2saag – Juan Molina Oct 12 '17 at 12:39

Save Byte[] in odbc with c#

2 Answers2