0

I have a C# script meant to calculate a MD5 checksum of an online article as such:

using System;
public class Script
{
    public static string TransformContent(ContentTransformationArguments args)
    {
        // CONVERT CAPTURED FIELD INTO BYTE ARRAY
        var buffer = System.Text.Encoding.UTF8.GetBytes(args.Content);
        var md5Hash = string.Empty;
        using (System.Security.Cryptography.MD5 md5 = System.Security.Cryptography.MD5.Create()) {
        // COMPUTE MD5 HASH AND CONVERT TO STRING
          md5Hash = BitConverter.ToString(md5.ComputeHash(buffer)).Replace("-", String.Empty);
        }
        return md5Hash;
    }
}

The above generates a data element called "ChecksumElement" - then I have an export script which serves to write the datum to a field in our SQL Server called Checksum2, of data type varbinary(16):

using (var sqlConnection = new SqlConnection(sqlString.ConnectionString))
            {
                sqlConnection.Open();
                using (IExportReader dataReader = args.Data.GetTable()) 
                {
                    while (dataReader.Read()) // Loop through Export data rows
                    {   
                        var checksumBytes = System.Text.Encoding.UTF8.GetBytes(dataReader.GetStringValue("ChecksumElement"));
                        using (var sqlCommand = new SqlCommand())
                        {
                            sqlCommand.Connection = sqlConnection;
                            sqlCommand.CommandText = @"INSERT INTO [dbo].[Data1] (
                            Checksum2)
                             VALUES (,
                            @ChecksumElement)";
                            sqlCommand.Parameters.AddWithValue("@ChecksumElement", checksumBytes);
                            sqlCommand.ExecuteNonQuery();
                            }
                        }
                    }
                }

The issue is that running the export script throws the following error:

"System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated.
    The statement has been terminated.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Script.ExportData(DataExportArguments args)
    ClientConnectionId:492cda62-285f-4a74-903d-55c7b7acee30
    Error Number:8152,State:11,Class:16"

It would appear that it's having trouble writing the checksum in its current format to the varbinary(16) field. Does anyone know how I can tweak this code to, perhaps convert the checksum to the proper format before it hits the database table? Please note, that per a suggestion of a colleague, I changed "UTF8" to "ASCII" in the two places in the above code, but I get the same error. Changing the data type of the database field is unfortunately not an option.

Stpete111
  • 3,109
  • 4
  • 34
  • 74
  • Why do you have your data in a string format if you are just going to go back to a byte array. Drop the BitConverter and return the array. – Scott Chamberlain Jun 28 '17 at 21:36
  • @ScottChamberlain in your example, it would seem I'd also need to change the "var md5Hash = string.Empty;" to something else as well? At least I assume this to be the case as simply dropping the BitConverter still gives the error "Cannot implicitly convert type byte[] to string." – Stpete111 Jun 28 '17 at 21:41
  • You can get rid of the variable and just return the byte array. It will require other changes too. – Scott Chamberlain Jun 28 '17 at 22:02

0 Answers0