如何计算在C＃中等同于SQL Server（HASHBYTES（“SHA1”，[的ColumnName]））？ [英] How do I calculate the equivalent to SQL Server (hashbytes('SHA1',[ColumnName])) in C#?

问题描述

在我的数据库我有一个包含一个名为URLString列其中包含的URL的SHA1哈希（如http://xxxx.com/index.html）计算列。

In my database I have a computed column that contains a SHA1 hash of a column called URLString which holds URLs (e.g. "http://xxxx.com/index.html").

我经常需要查询表了解基础上，URLString列特定的URL。
表包含100K的和这些查询需要几秒钟（使用SQL Azure的）。
由于的URL可能会很长，我不能创建此列的索引（超过450字节）。

I often need to query the table to find a specific URL based on the URLString column. The table contains 100K's and these queries take several seconds (using SQL Azure). Since URLs can be quite long, I cannot create an index on this column (above 450 bytes).

要加快速度了，我要计算的等价基于此值从C＃和查询的SQL Server HASHBYTES（SHA1，[URLString]）。

To speed things up I want to calculate the equivalent of SQL Server hashbytes('SHA1',[URLString]) from C# and query based on this value.

我试过下面的代码，但我得到的价值是不同的，比由数据库计算的一个

I tried the below code, but the value I get is different than the one calculated by the database.

var urlString = Encoding.ASCII.GetBytes(url.URLString); //UTF8 also fails
var sha1 = new SHA1CryptoServiceProvider();
byte[] hash = sha1.ComputeHash(urlString);

我失去了一些小事在这里吗？结果
我接受其他的想法。可以解决相同的问题（只要它们是由SQL天青支持）

Am I missing something trivial here?
I'm open to other ideas that can solve the same problem (as long as they are supported by SQL Azure).

实施例：在数据库中的URL的 http://www.whatismyip.org/ 是0xAE66CA69A157186A511ED462153D7CA65F0C1BF7。

Example: in the database the automatically calculated SHA1 value of URL http://www.whatismyip.org/ is 0xAE66CA69A157186A511ED462153D7CA65F0C1BF7.

推荐答案

你很可能得到由字符编码差异咬伤：

You're likely getting bitten by character encoding differences:

的 http://weblogs.sqlteam.com/mladenp/archive/2009 /04/28/Comparing-SQL-Server-HASHBYTES-function-and-.Net-hashing.aspx

您可以尝试通过<得到字节code> Encoding.ASCII.GetBytes（URL）或 Encoding.Unicode.GetBytes（URL），看看哪一个你的数据库是使用

You could try getting the bytes via Encoding.ASCII.GetBytes(url) or Encoding.Unicode.GetBytes(url) and see which one your db is using.

这篇关于如何计算在C＃中等同于SQL Server（HASHBYTES（“SHA1”，[的ColumnName]））？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！