I have a SQL CE 4.0 database in which I'm storing a (potentially) large signed XML string. Currently, I'm storing this string in a column of type ntext since there is a 4000 byte limit on nvarchar (see this post).
Inserting a Row
So to insert a row into this table it would look something like this:
string signedXmlObject = "....long string";
using (SqlCeCommand command = new SqlCeCommand("INSERT INTO SignedObjects (Key, SignedObject) VALUES(@key, @signedObject) ", connection))
{
command.CommandTimeout = 0;
command.CommandType = System.Data.CommandType.Text;
command.Parameters.AddWithValue("@key", theKey);
command.Parameters.AddWithValue("@signedObject", signedXmlObject);
rows = command.ExecuteNonQuery();
}
This works for "small" strings (less than 4000 bytes), but for strings larger than that it will fail silently. The command execution will not throw an exception, but an empty string will be in the SignedObject column.
Changing the Input Parameter
So, after some reading, I found that to make sure that a string is inserted correct as ntext and not interpreted as nvarchar, you must specify the parameter type like such:
SqlCeParameter param = command.Parameters.Add("@signedObject", SqlDbType.NText);
param.Value = signedXmlObject;
This allows me to insert the large strings correctly, so that problem is solved.
Signature Verification
So, my program will then read this string back from the database, populate an XML file with it, and attempt to verify the signature using the .NET SignedXml class. The problem is that now my signature will not verify correctly.
The Real Question
Why would this happen? My XML file is UTF-8, so this is the format that the string should be stored in. Will storing this in an ntext column just slightly modify the string if it stores as UTF-16? And why would SQL interpreting the input as nvarchar previously allow the signature verification to succeed? Is there a better way to store this data in the database that will not affect the encoding? Any help would be greatly appreciated!
