Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 595
Nosheen Avatar asked Jun 08 '14 06:06

Nosheen


People also ask

How use VARBINARY Max in SQL Server?

varbinary [ ( n | max ) ]n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.

How is VARBINARY stored?

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes. It contains no character set, and comparison and sorting are based on the numeric value of the bytes.

Is VARBINARY a string?

Store raw-byte data, such as IP addresses, up to 65000 bytes. The BINARY and BINARY VARYING (VARBINARY) data types are collectively referred to as binary string types and the values of binary string types are referred to as binary strings.

What is binary and VARBINARY in SQL?

The BINARY and VARBINARY types are similar to CHAR and VARCHAR , except that they store binary strings rather than nonbinary strings. That is, they store byte strings rather than character strings.


2 Answers

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"];
like image 44
Friyank Avatar answered Nov 09 '22 09:11

Friyank


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[];
    }
}
like image 189
Dimitar Dimitrov Avatar answered Nov 09 '22 08:11

Dimitar Dimitrov