如何在SQL Server中存储和检索varbinary(max)列

问题描述

我正在开发一个应用程序，我想在其中将用户的指纹存储到数据库中，然后将其与从设备中获取的指纹进行比较.将 varbinary(max)列转换回byte []时遇到了某些问题.我试图使用 GetSqlBinary 函数，但是它给了我 indexoutofrangeException .

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.

我正在使用下面的代码将模板存储到数据库中，但是发现所有用户的值都相同.(例如0x000000)

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();
}

有人可以告诉我如何将byte []插入varbinary(max)列然后进行检索吗?

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

推荐答案

您应该始终使用参数.试一试:

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 ...
}

编辑

由于您已经询问过如何检索它，因此您可以执行以下操作(不确定您的确切要求，但是应该可以给您一个想法):

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[];
    }
}

这篇关于如何在SQL Server中存储和检索varbinary(max)列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！