How to Store and Retrieve a varbinary(max) Column in SQL Server
Solution 1
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[];
}
}
Solution 2
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"];
Nosheen
Updated on April 07, 2020Comments
-
Nosheen about 4 years
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 theGetSqlBinary
function but it gives meindexoutofrangeException
.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?