How to Store and Retrieve a varbinary(max) Column in SQL Server

Question

I am developing an application in which I want to store the user's fingerprint into the database and then compare it with the one taken from the device. I've been having certain issues while converting a varbinary(max) column back to a byte[]. I have tried to use the GetSqlBinary function but it gives me indexoutofrangeException.

I am using the code below for storing the template into the database but found that the value is the same for all users. (e.g. 0x000000)

public int insernewVoter(NSubject thumb) 
{
    connectionOpen();
    byteArray = thumb.GetTemplateBuffer().ToArray();
    int insert = 0;

    cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "INSERT INTO VOTER (THUMB) VALUES(CONVERT(varbinary(max),'" + byteArray + "'))";
    int rowsupdated = cmd.ExecuteNonQuery();

    if (rowsupdated <= 0) {
        MessageBox.Show("Ho Gya");
    }
    else {
        MessageBox.Show("AP MAR KYN NAI JATA :D");
    }
    return 0;
    connectionClose();
}

Can anyone please show me how I can insert the byte[] into the varbinary(max) column and then retrieve it?

Yes it is but it inserting the same values for all users :?

Nosheen
– Nosheen

2014-06-08 06:57:18 +00:00
Commented Jun 8, 2014 at 6:57 — Nosheen
– Nosheen, Commented Jun 8, 2014 at 6:57

Dimitar Dimitrov · Accepted Answer · 2014-06-08 07:39:10Z

12

You should ALWAYS use parameters. Give this a shot:

using(var conn = new SqlConnection("YOUR CONNECTION STRING ..."))
using (var cmd = new SqlCommand("INSERT INTO VOTER (THUMB) VALUES(@THUMB)", conn)) {
    conn.Open();
    var param = new SqlParameter("@THUMB", SqlDbType.Binary) {
        // here goes your binary data (make sure it's correct)
        Value = thumb.GetTemplateBuffer().ToArray()
    };
    cmd.Parameters.Add(param);
    int rowsAffected = cmd.ExecuteNonQuery();

    // do your other magic ...
}

EDIT

Since you've asked how to retrieve it, you can do something like (not sure of your exact requirements, but it should give you the idea):

private byte[] GetThumbData(int userId) {
    using (var conn = new SqlConnection("YOUR CONNECTION STRING ..."))
    using (var cmd = new SqlCommand("SELECT THUMB FROM VOTER WHERE ID = @ID", conn)) {
        conn.Open();
        cmd.Parameters.AddWithValue("@ID", userId);
        return cmd.ExecuteScalar() as byte[];
    }
}

edited Jun 8, 2014 at 7:39

answered Jun 8, 2014 at 7:01

Dimitar Dimitrov

15.2k9 gold badges55 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nosheen Over a year ago

And how will I retrieve it after insertion?

Nosheen Over a year ago

Thank You so much you seriously solved my problem :) I was doing this fr round about 4 days and i thought that there is some kind of sdk issue

Friyank · Accepted Answer · 2014-06-08 06:59:27Z

1

if u have those finger prints in file format , thn u can use the following code , wch convert pdf into byte and byte again to pdf

  string filepath = Server.MapPath("~/pdf/" + file.FileName);
  byte[] bytes = System.IO.File.ReadAllBytes(filepath);

and pass this to Database field of varbinary now to retrive this content in to pdf u can use

 byte[] pdfcontent =  (byte[])DS.Tables[0].Rows[0]["PDFContent"];

answered Jun 8, 2014 at 6:59

Friyank

4893 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to Store and Retrieve a varbinary(max) Column in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related