Unable to obtain public key for StrongNameKeyPair

You are using Visual Studio 2008 (SP1 or not -- doesn't matter).
You have test project that is signed with strong key and that strong key has a password on it.
You are trying to create a unit test for private method and are trying to create accessor.
You have an error like that:
"Creation of the private accessor for Xyz failed"

You have that error because you excluded "Test References" folder and YourProject.accessor file from your test project.

The reason why you excluded YourProject.accessor file from your project is that you were getting a compilation error:
"Unable to obtain public key for StrongNameKeyPair".

The reason why you are getting that error is that VS 2008 SP1 and even VS 2010 Beta 1 have a bug.

Workaround for that bug is to turn off strong key signing on your test project.

Read more here:
Unable to obtain public key for StrongNameKeyPair

SQL_Latin1_General_Cp850_BIN

While working on Auto-Moderator for PostJobFree.com I encountered the following problem: my C# code considered ‘3’ and ‘３’ as different words, but SQL Server considered them the same.
That expressed itself in the following error:

Cannot insert duplicate key row in object 'dbo.Word' with unique index 'IX_Word'.

That was a little bit surprising, considering that I defined Word as Unicode column (nvarchar).

While searching for the solution, my first thought was to make 'IX_Word' index not unique. That worked, but would have introduced other problems with spam filtering business logic down the road.

The solution should have been about making SQL Server to compare strings exactly the same way C# code does.

I started to look into SQL Server Collations, and finally found the solution: use SQL_Latin1_General_Cp850_BIN collation.

Basically the solution is about to declaring 'Word' column with SQL_Latin1_General_Cp850_BIN collation:

Create Table Word(
 WordId bigint identity(1,1) not null,
 Word nvarchar(50) COLLATE SQL_Latin1_General_Cp850_BIN not null,
 JobPostCount int not null DEFAULT 0,
 JobLogSpamCount int not null DEFAULT 0,
 CreateDate datetime not null,
 UpdateDate datetime not null,
 CONSTRAINT PK_Word Primary Key Clustered
 (
  WordId ASC
 )
)
GO

Create Unique Index IX_Word ON Word
(
 Word
)
GO

Possible drawbacks of the solution: using SQL_Latin1_General_Cp850_BIN collation may cause weird sorting in SQL queries, but sorting collation can be easily redefined like this:

select * from Word
order by Word COLLATE SQL_Latin1_General_CP1_CI_AS

Moreover, the sorting it provided by binary collation (SQL_Latin1_General_Cp850_BIN) looks quite reasonable.

You may also use SQL_Latin1_General_Cp850_BIN2 collation for better sorting.


Here’s SQL sample to you to play with:

--drop table t;
select N'３' as Word
into t;

insert into t
select '3' as Word;

select * from t
where Word = N'3';

select * from t
where Word = N'３' 

select * from t
where Word = N'3' collate SQL_Latin1_General_Cp850_BIN;

select * from t
where Word = N'３' collate SQL_Latin1_General_Cp850_BIN

Enjoy!

web.config in IIS 7.0

IIS 7.0 treats Web Application differently from how IIS 6.0 treats Virtual Directory connected to Application pool.
The difference is in how web.config properties are inherited from parent application.
If Virtual Directory in IIS 6.0 is physically located outside of parent web application, then web.config properties are not inherited. Even if logically Virtual Directory is inside parent web application.
So, even if from end user URL looks like this:
http://www.parentwebapp.com/virtualdirectory -- virtualdirectory web app does not inherit web.config properties from parentwebapp.com

IIS 7.0 is smarter than IIS 6.0.
Even if parentwebapp.com and virtualdirectory from above example are mapped to different physical folders like this:
http://www.parentwebapp.com/ -- c:\parentwebapp
http://www.parentwebapp.com/virtualdirectory/ -- c:\virtualdirectory

IIS 7.0 still recognizes that http://www.parentwebapp.com/virtualdirectory/ inherits all web.config settings from http://www.parentwebapp.com/

Such inheritance sometimes causes undesired effects.
For example, if you added HttpModules to parent web app code, but didn't add them to child virtual directory, you may end up with "Could not load type ..." run-time error.

In order to get rid of this error you need from inherited settings.
You can do it like this:

 <system.web>
  <httpModules>
   <remove name="DenyIpAddressModule"/>
  </httpModules>
 </system.web>
 <system.webServer>
  <pages theme="">
  <modules>
   <remove name="DenyIpAddressModule" />
  </modules>
 </system.webServer>

Note, that in this example I remove "DenyIpAddressModule" twice.
The reason for that is that I added "DenyIpAddressModule" module to parent web.config twice: once as regular httpmodule, and another time as integrated pipeline module.

Google Maps API team joke

Google Maps API rolled out unpleasant joke on April 1st day.
Google Maps javascript stopped working today.

There is a quick fix available: switching back to older, more carefully tested version.
(from "v=2.x" to "v=2").

There is a good lesson here: don't use the latest "bleeding edge" version of the code in production environment. Even if it comes from Google.

Technical details are here:
http://groups.google.com/group/Google-Maps-API/browse_thread/thread/193fbebd8066f19f/7fecbfed5908d29e

Serialize and Deserialize objects in .NET

I'm not sure why XML standard doesn't allow certain characters to be encoded into XML... and it causes problems.

This C# code:

            XmlSerializer xs = new XmlSerializer(typeof(T));
            using (MemoryStream memoryStream = new MemoryStream(StringToUTF8ByteArray(objString)))
            {
                obj = xs.Deserialize(memoryStream);
            }

crashes with exception:

System.InvalidOperationException: There is an error in XML document (1, 50). ---> System.Xml.XmlException: '', hexadecimal value 0x0C, is an invalid character. Line 1, position 50.

Here's the fix and the fully working version (note that XmlTextReader is used in between MemoryStream and XmlSerializer:

        [TestMethod()]
        public void SerializeDeserializeObjectTest()
        {
            SerializeDeserializeObjectTest("test");
            SerializeDeserializeObjectTest("\f");
        }

        private void SerializeDeserializeObjectTest(string input)
        {
            string serialized = Serializer.SerializeObject(input);
            string deserialized = Serializer.DeserializeObject<string>(serialized);
            Assert.AreEqual(input, deserialized, input);
        }


    public static class Serializer
    {
        public static string SerializeObject(Object obj)
        {
            MemoryStream memoryStream = new MemoryStream();
            XmlSerializer xs = new XmlSerializer(obj.GetType());
            XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
            xs.Serialize(xmlTextWriter, obj);
            memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
            return UTF8ByteArrayToString(memoryStream.ToArray());
        }

        public static T DeserializeObject<T>(string objString)
        {
            Object obj = null;
            XmlSerializer xs = new XmlSerializer(typeof(T));
            using (MemoryStream memoryStream = new MemoryStream(StringToUTF8ByteArray(objString)))
            {
                XmlTextReader xtr = new XmlTextReader(memoryStream);
                obj = xs.Deserialize(xtr);
            }
            return (T)obj;
        }

        private static string UTF8ByteArrayToString(byte[] characters)
        {
            UTF8Encoding encoding = new UTF8Encoding();
            return encoding.GetString(characters);
        }

        private static byte[] StringToUTF8ByteArray(string xmlString)
        {
            UTF8Encoding encoding = new UTF8Encoding();
            return encoding.GetBytes(xmlString);
        }

    }

Thanks to Tom Goff for XML Serialization Sorrows article.

Thanks to Andrew Gunn for XML Serialization in C# article.

Troubleshooting SqlCacheDependency in SQL Server 2008 and SQL Server 2005

"Getting immediate notification from SQL Server when data changes" is a very attractive feature, but unfortunately it's not easy to implement.
(It took me full day to identify and fix all issues...).

SQL Server Query Notification framework is quite fragile and may not work for multiple reasons.
If you get error messages -- consider youself lucky. Sometimes there will be no error messages, but notifications simply would refuse to work.

There are two major steps in troubleshooting SqlCacheDependency notifications:
Step 1: Make SqlCacheDependency clean up ASP.NET Cache item.
Step 2: Prevent SqlCacheDependency from cleaning up ASP.NET Cache item when it's inapropriate.

Both steps are hard, but Step 1 is the hardest.

I strongly recommend iterative approach: implement the easiest possible solution first, and then make it more advanced one small step at a time. Test every little step.

Business context

In this example I use SqlCacheDependency in order to get list of blocked IP addresses on my web site PostJobFree.com.
From time to time I delete bad users and write their IP addresses into BlackListIpAddress table.

I can retrieve the list of recently blocked IP addresses like that:

CREATE Procedure spGetBlockedIpList
@cutDate datetime
AS

set nocount on
select IpAddress
from BlackListIpAddress with (nolock)
where (DecisionDate > @cutDate)
group by IpAddress
having count(1) > 1
GO

When anybody opens web page -- I check if current web page request came from that list of blocked IP addresses.
I created C# function that does that check:

public static bool IsBlackListed(string ipAddress)
{
bool cached;
if (GetBlockedIpAddresses(out cached).Contains(ipAddress)) return true;
return false;
}

Because I run IsBlackListed() on every page, I don't want to run spGetBlockedIpList without need.
So, I keep database results in ASP.NET Cache object and use SqlCacheDependency to clean up Cache object as soon as new IP address is blacklisted in BlackListIpAddress table.

Implementation

public static List GetBlockedIpAddresses(out bool cached)
{
HttpContext context = HttpContext.Current;
List blockedIPs = (List)context.Cache["BlockedIPAddresses"];
if (blockedIPs == null)
{
SqlCommand cmdDependency = new SqlCommand(@"select IpAddress from dbo.BlackListIpAddress where DecisionDate > @cutDate",
SqlUtilities.GetSqlConnection("PostJobFreeConnectionString"));
SqlUtilities.AddInputParameter(cmdDependency, "@cutDate", DateTime.UtcNow.AddMinutes(-1), SqlDbType.DateTime);
SqlCacheDependency dependency = new SqlCacheDependency(cmdDependency);
SqlUtilities.ExecuteNonQuery(cmdDependency, "PostJobFreeConnectionString");
blockedIPs = LoadBlockedIPsFromDatabase();
// Cache retrieved blockedIPs in ASP.NET Cache object:
context.Cache.Insert("BlockedIPAddresses", blockedIPs, dependency);
cached = false;
}
else
{
cached = true;
}
return blockedIPs;
}

Note, that almost always blockedIPs will be retrieved from ASP.NET Cache.
But if Cache["BlockedIPAddresses"] is empty -- I execute two SQL queries instead of one query.

I run simple query "select IpAddress from dbo.BlackListIpAddress where DecisionDate > @cutDate" in order to make SqlCacheDependency work.
I then run more complex query LoadBlockedIPsFromDatabase() (it runs spGetBlockedIpList) in order to get data I need.
spGetBlockedIpList is too complex to work with SqlCacheDependency.

Simple query is not smart enough to give me the data I need.

When you debug your own code -- dump more complex query and use only simple one.
Remember -- first step is to make SqlCacheDependency clean up ASP.NET Cache item.
If SqlCacheDependency cleans up your ASP.NET Cache -- you are about 70% done.

You may even start with even simpler SQL query. For example: "select IpAddress from dbo.BlackListIpAddress". You would polish it later.

Preparations

1) Make sure that when your Web Application start, you run SqlDependency.Start().
I do it this way:

public sealed class DenyIpAddressModule : IHttpModule
{
void IHttpModule.Init(HttpApplication application)
{
string connectionString = WebConfigurationManager.
ConnectionStrings["PostJobFreeConnectionString"].ConnectionString;
SqlDependency.Start(connectionString);
}
}

If you forget to do that, you would get "When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance." error message.

2) Enable Service Brocker on your database.
I do it like this in SQL Server Management Studio (SSMS):

use PostJobFree;
alter database PostJobFree set ENABLE_BROKER;

The trick here is to kill all existing connections prior to altering your database.
Use these SQL commands in SSMS:

sp_Who2
kill 52 -- or whatever SPID is

You may check your Service Broker setting like this:

use PostJobFree;
select is_broker_enabled from sys.databases where database_id=db_id()

1 - Enabled; 0 - Disabled (default).

3) Make sure that permissions in your database are not out of whack. SQL Server 2005 and SQL Server 2008 have unpleasant bug that [almost] silently kills Queue Notifications:
"You cannot run a statement or a module that includes the EXECUTE AS clause after you restore a database in SQL Server 2005" http://support.microsoft.com/kb/913423

I fixed it by running this command:

use PostJobFree
GO
sp_changedbowner [MyServerName\dennis]

You may check current database settings by this command:

sp_helpdb PostJobFree

Troubleshooting

1) If you are still unable to make SqlCacheDependency to invalidate your ASP.NET Cache, I recommend you great article "Using and Monitoring SQL 2005 Query Notification"
http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/
Sanchan explains how to use SQL Profiler to see what's going on with query notifications.
2) Using Profiler helped me to find the following errors in SQL Profiler:
- An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'PostJobFree'. You should correct this situation by resetting the owner of database 'PostJobFree' using the ALTER AUTHORIZATION statement.
- Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.
That gave me an idea to run:

sp_changedbowner [MyServerName\dennis]

3) These couple of queries would let you take a look at what active Query Notification Subscriptions you currently have:

select * from sys.dm_qn_subscriptions
select * from sys.transmission_queue

4) I didn't need that step, but while troubleshooting I did it anyway.
Grant these permissions to the user account that runs your web application (it's usually either "aspnet" or "NT AUTHORITY\NETWORK SERVICE").

use PostJobFree
GRANT CREATE PROCEDURE TO [MyServerName\aspnet]
GRANT CREATE QUEUE TO [MyServerName\aspnet]
GRANT CREATE SERVICE TO [MyServerName\aspnet]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [MyServerName\aspnet]
GRANT SELECT ON OBJECT::dbo.BlackListIpAddress TO [MyServerName\aspnet]
GRANT SELECT ON OBJECT::dbo.T TO [MyServerName\aspnet]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [MyServerName\aspnet]
ALTER DATABASE PostJobFree SET TRUSTWORTHY ON

Clean up Cache only when needed

I assume that at this point you are done with the hardest part ("Make SqlCacheDependency object clean up Cache").
There is still some work ahead.
For example I noticed that my SqlCacheDependency code when I was playing with it -- always invalidated ASP.NET Cache. It didn't matter if I updated underlying BlackListIpAddress table or not.
By using trial & error approach I found that the problem was caused by using inapropriate version of SQL query.
I found that:
- "group by" doesn't work no matter what.
- "top 10" doesn't work.
- "with (nolock)" hint doesn't work.
- passing @cutDate parameter to the query _does_ work.

See documentation on SELECT statements that are supported by Query Notification: http://msdn.microsoft.com/en-us/library/ms181122(SQL.90).aspx

Other useful resources

1) More tips about SQL Server Query Notification:
http://rusanu.com/2006/06/17/the-mysterious-notification/

2) Troubleshooting Query Notifications
http://msdn.microsoft.com/en-us/library/ms177469.aspx

3) If you are lucky and expect everything to go smooth - use this articles:
SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005
SQL 2005 and SQL2008 Enabling Notifications. SQL Chache Dependancy Part-I

Keywords:
ASP.NET 2.0, ASP.NET 3.5, C#