How do I compare byte file if exist in database using query?

Question

I want to check if file exist in database or not using asp.net. I searched about that but I didn't find comparing with byte file.

I used Visual Studio 2010, SQL Server 2008 and C# language .

So, I tried to write this code but display error:

Incorrect syntax near 'System.Byte[])'.

Also, is there anther solution about this problem ?

code

if (ext == ".doc" || ext == ".docx" || ext == ".pdf" || ext == ".txt")
{
   Stream fs = FileUpload1.PostedFile.InputStream;
   BinaryReader br = new BinaryReader(fs);
   Byte[] bytes = br.ReadBytes((Int32)fs.Length);

   //insert the file into database
   strQuery = "insert into [Text File](User_id, T_Title, T_Extension, T_Data, Course_code, Course_num, T_Description, T_Keyword,Date)" +
   " values (@User_id, @T_Title, @T_Extension, @T_Data, @Course_code, @Course_num, @T_Description, @T_Keyword, @Date)";

   SqlCommand cmd = new SqlCommand(strQuery);
   cmd.Parameters.Add("@User_id", (string)Session["ID"]);
   cmd.Parameters.Add("@T_Title", SqlDbType.VarChar).Value = filename;
   cmd.Parameters.Add("@T_Extension", SqlDbType.VarChar).Value = ext;
   cmd.Parameters.Add("@T_Data", SqlDbType.VarBinary).Value = bytes;

   strQueryCount = "select count(*) from [Text File] where T_Data.SequenceEqual ('" + bytes + ")'";

   cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now;
   cmd.Parameters.Add("@Course_code", Course_code.SelectedItem.Text);
   cmd.Parameters.Add("@Course_num", Course_num.SelectedItem.Text);
   cmd.Parameters.Add("@T_Description", Description.Text);
   cmd.Parameters.Add("@T_Keyword", keywords.Text);

   InsertUpdateData(cmd, bytes, strQueryCount);
}

private Boolean InsertUpdateData(SqlCommand cmd, Byte[] bytes, string strQueryCount)
{
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;

    try
    {
        con.Open();
        command = new SqlCommand(strQueryCount, con);

        int num = Convert.ToInt16(command.ExecuteScalar());
        Label2.Text = num.ToString();

        if (num == 0)
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        else
        {
            Label2.ForeColor = System.Drawing.Color.Red;
            Label2.Text = "error ";
            Description.Text = " ";
            keywords.Text = " ";
            Course_code.SelectedItem.Text = " ";
            Course_num.SelectedItem.Text = " ";
            return false;
        }
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
        return false;
    }
    finally
    {
        con.Close();
        con.Dispose();
    }
}

Thanks..

Files don't generally contain their filenames or even their extensions. What exactly are you trying to do? If you're trying to search for a binary file's type via its content.. you will have to get extremely familiar with file formats. — Simon Whitehead, Jul 23 '13 at 10:41

score 3 · Accepted Answer · edited Feb 21 '20 at 22:27

You cannot compare files in byte stream as we do on other data types. You can generate some unique values for a file like hash or check-sum and store it along with byte stream in DB, which can be used to check for whether the file exists or not. Normally these mechanisms are not used for this. This only works if file contents are exactly the same. Even the slightest of variation will be failed to identify the match.

OR alternatively, you can decide to store some alternate information like we normally do. Like file name or user-based validations to check whether the file exists.

EDIT:

You can find hash like

string hash;
using(SHA1CryptoServiceProvider sha1 = new SHA1CryptoServiceProvider())
{
    hash = Convert.ToBase64String(sha1.ComputeHash(byteArray));
}

see it here

How do I create hash file from byte file ? what is check-sum ? — Dalal, Jul 23 '13 at 11:06

score 1 · Answer 2 · answered Oct 19 '14 at 18:59

I agree, as suggested in above post, you can maintain hash to manage file comparing.

Since you asked how to compare using query, i am adding one more suggestion here.

Write a function in C# to get MD5 hash. Following is the code for function.

public static string GetMD5Hash(string input)
    {
        System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
        byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);

        bs = x.ComputeHash(bs);

        System.Text.StringBuilder s = new System.Text.StringBuilder();

        foreach (byte b in bs)
        {
            s.Append(b.ToString("x2").ToLower());
        }
        return s.ToString();
    }

So get MD5 hash of file, and then using HASHBYTES('MD5', VarbinaryColumn)), you can compare values . This will work,because you will have MD5 hash generated C# and used HASHBYTES in SQL server to compare.

You can also do other type hashing like SHA1 in SQL server as well as C# side.

More on hashbytes - http://codepieces.tumblr.com/post/31006268297/sql-server-hashbytes-function-and-net-hashing-md5

Again, this has same limitation, slight change in content implies mismatch between posted file and stored file in SQL .

How do I compare byte file if exist in database using query?

2 Answers2