SqlParameter.Value not saving correctly

OH

It is what I want as I need to store the raw data from a piece of hardware and this is what its sends

Is there a way around this?

Basically what I said before.  Modify the raw data by changing a \0 to a \\0 before saving it.  (Or change the field type in the database to a binary data if you can do so).

ps.  Try it first by hard coding - just in case that isn't the problem.

*NO POINTS*

As a proof of concept consider the following -

SQL Table Schema -

CREATE TABLE [dbo].[Literals]
(
	[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Literal] NVARCHAR(500) NOT NULL
)

Select allOpen in new window

Program.cs -

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace EE_Q28969683
{
	class Program
	{
		static readonly List<string> literals = new List<string>() 
		{
			/* string retrieved from device - unaltered */
			"$$\0j?yefuI?U062956.000,A,0120.0672,S,03642.4399,E,0.00,120.41,140916|0.0|0.0|0000|0000,0000|140028000?\r\n", 
			/* string retrieved from device - as a string literal */
			@"$$\0j?yefuI?U062956.000,A,0120.0672,S,03642.4399,E,0.00,120.41,140916|0.0|0.0|0000|0000,0000|140028000?\r\n", 
			/* string retrieved from device - with escape characters */
			"$$\\0j?yefuI?U062956.000,A,0120.0672,S,03642.4399,E,0.00,120.41,140916|0.0|0.0|0000|0000,0000|140028000?\\r\\n" 
		};

		static readonly List<string> data = new List<string>();

		static void Main(string[] args)
		{
			try
			{
				Globals.Connection.Open();
				using (DataSet set = new DataSet())
				{
					set.Tables.Add(new DataTable("Literals"));
					Globals.Adapter.Fill(set.Tables["Literals"]);
					foreach (DataRow row in set.Tables["Literals"].Rows)
						data.Add(row["Literal"].ToString());

					foreach (var literal in literals)
					{
						if (!data.Contains(literal, StringComparer.OrdinalIgnoreCase))
						{
							Globals.Adapter.InsertCommand.Parameters["@LITERAL"].Value = literal;
							Console.Write("Will insert this value; {0} - into database... ", Globals.Adapter.InsertCommand.Parameters["@LITERAL"].Value);
							Console.WriteLine("Inserted at id = {0}", Globals.Adapter.InsertCommand.ExecuteScalar());
						}
					}
				}
			}
			finally
			{
				Globals.Connection.Close();
			}
			Console.ReadLine();
		}
	}

	static class Globals
	{
		private static SqlDataAdapter _adapter = default(SqlDataAdapter);
		private static SqlConnection _connection = default(SqlConnection);

		public static SqlDataAdapter Adapter 
		{
			get
			{
				if (_adapter == default(SqlDataAdapter))
				{
					_adapter = new SqlDataAdapter("SELECT * FROM LITERALS", Connection);
					_adapter.InsertCommand = new SqlCommand("INSERT INTO LITERALS (LITERAL) OUTPUT INSERTED.IDENTITYCOL VALUES (@LITERAL)", Connection);

					// Insert Command
					_adapter.InsertCommand.Parameters.Add("@LITERAL", SqlDbType.NVarChar);
				}
				return _adapter;
			}
		}

		public static SqlConnection Connection
		{
			get
			{
				if (_connection == default(SqlConnection))
					_connection = new SqlConnection("Data Source=.;Initial Catalog=EE_Q28969683;Integrated Security=True");
				return _connection;
			}
		}
	}
}

Select allOpen in new window

Produces the following output -

Straight Run -Table Data -
In order to correct as instructed by Andy you only need to convert it to a string literal, unfortunately a simple replace function is inadequate as we are dealing with escape sequences (\0, \r, \n, etc) which, for all intents and purposes, are single characters.  This means that we have to find the sequences and convert them to literals which can be accomplished from an extension method; e.g. -

static class Extensions
{
	public static string ToLiteral(this string source)
	{
		using (var writer = new StringWriter())
		{
			using (var provider = CodeDomProvider.CreateProvider("CSharp"))
			{
				provider.GenerateCodeFromExpression(new CodePrimitiveExpression(source), writer, new CodeGeneratorOptions {IndentString = "\t"});
				var literal = writer.ToString();
				literal = literal.Replace(string.Format("\" +{0}\t\"", Environment.NewLine), "").Replace("\"", "");
				return literal;
			}
		}
	}
}

Select allOpen in new window

Your usage would then become:

param[39].Value = this.raw_data.ToLiteral();

Select allOpen in new window

Which then gives you the escape character on the escape sequence and produces the intended output into the field value.  Full implementation:

using System;
using System.CodeDom;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace EE_Q28969683
{
	class Program
	{
		static readonly List<string> literals = new List<string>() 
		{
			"$$\0j?yefuI?U062956.000,A,0120.0672,S,03642.4399,E,0.00,120.41,140916|0.0|0.0|0000|0000,0000|140028000?\r\n", 
		};

		static readonly List<string> data = new List<string>();

		static void Main(string[] args)
		{
			try
			{
				Globals.Connection.Open();
				using (DataSet set = new DataSet())
				{
					set.Tables.Add(new DataTable("Literals"));
					Globals.Adapter.Fill(set.Tables["Literals"]);
					foreach (DataRow row in set.Tables["Literals"].Rows)
						data.Add(row["Literal"].ToString());

					foreach (var literal in literals)
					{
						if (!data.Contains(literal, StringComparer.OrdinalIgnoreCase))
						{
							string temp = literal.ToLiteral();
							Globals.Adapter.InsertCommand.Parameters["@LITERAL"].Value = literal.ToLiteral();
							Console.Write("Will insert this value; {0} - into database... ", Globals.Adapter.InsertCommand.Parameters["@LITERAL"].Value);
							Console.WriteLine("Inserted at id = {0}", Globals.Adapter.InsertCommand.ExecuteScalar());
						}
					}
				}
			}
			finally
			{
				Globals.Connection.Close();
			}
			Console.ReadLine();
		}
	}

	static class Extensions
	{
		public static string ToLiteral(this string source)
		{
			using (var writer = new StringWriter())
			{
				using (var provider = CodeDomProvider.CreateProvider("CSharp"))
				{
					provider.GenerateCodeFromExpression(new CodePrimitiveExpression(source), writer, new CodeGeneratorOptions {IndentString = "\t"});
					var literal = writer.ToString();
					literal = literal.Replace(string.Format("\" +{0}\t\"", Environment.NewLine), "").Replace("\"", "");
					return literal;
				}
			}
		}
	}

	static class Globals
	{
		private static SqlDataAdapter _adapter = default(SqlDataAdapter);
		private static SqlConnection _connection = default(SqlConnection);

		public static SqlDataAdapter Adapter 
		{
			get
			{
				if (_adapter == default(SqlDataAdapter))
				{
					_adapter = new SqlDataAdapter("SELECT * FROM LITERALS", Connection);
					_adapter.InsertCommand = new SqlCommand("INSERT INTO LITERALS (LITERAL) OUTPUT INSERTED.IDENTITYCOL VALUES (@LITERAL)", Connection);

					// Insert Command
					_adapter.InsertCommand.Parameters.Add("@LITERAL", SqlDbType.NVarChar);
				}
				return _adapter;
			}
		}

		public static SqlConnection Connection
		{
			get
			{
				if (_connection == default(SqlConnection))
					_connection = new SqlConnection("Data Source=.;Initial Catalog=EE_Q28969683;Integrated Security=True");
				return _connection;
			}
		}
	}
}

Select allOpen in new window

Step through snippets -
Inside the extension method -What will be stored in the parameter value -What the SqlParameter shows -What the field contains in the database -
-saige-

@Andy: I also was asked to resolve the question and came to a different decision. Don't think that it is worth to object, but as it has cost some time to reason my decision I will submit it as a comment:

The question is about the contents of a buffer that comes from an unknown device with unknown encoding and where the Author told us that they want it to store into a NVARCHAR field into the SQLServer database.

They posted the buffer contents as a string literal. if we assume that the literal was defined in C# (the given TA), we know that string literals are wide characters (UTF-16). As we don't know whether the original buffer contents sent by the device also is UTF-16 (more likely is that the device sent a byte buffer), we don't know whether the Author has typed out the C# literal from some output or whether it really is the orignal buffer contents, The second is not very likely as the there are some escaped characters included: \0, \r, \n.

Escaped character pairs are used in literals to be able to write those non-printable binary characters. The \0, \r, \n  are binary characters  with hex codes 0x0 , 0xD, 0xA (ZERO, CR, LF). In C# those characters would be 16 bit. The point is now, that those binary characters never would be displayed as '\0', '\r', '\n' in normal string output. On the best a binary zero was ignored in output. CRLF might cause a new line in case of a multi-line output.

The Author posted a screenshot of a Debug Window where we can see the the same character sequences as in the string literal. We also can see that for the 'SQLValue' we only have [$$] as a visualization of ist contents what seems to indicate that the visualisation stopped outputting when it recognizes a binary zero character. The Author told us that in the database also only $$ was stored.

Unfortunately we have no chance to solve this puzzle. We see from the screenshot that the sequence was existing in some cells and therefore it is likely that the full sequence also was stored as 'raw data' in the database. Therefore it seems well possible that the data existed and that it is only an output issue if a query shows only "$$".

Beside of this, NVARCHAR is a string format and isn't the right type to store binary data.

It makes little sense to find a solution to the original question if there are so many unknowns. It is also impossible for me to say whether any of the comments given was a solution or at least helpful. Therefore I recommend to delete the question.

Sara

@Sara
I think the quick watch is showing the first part because of the string termination character.  We don't actually see what is actually saved to the DB but the asker does state only the first part (before the \0) is being saved.  
I understand your reasons but I still think the truncation is because of saving to a 'string' type of DB field rather than binary data field.  (AngelIII seems to be of the same opinion at least with the comment made.)  The question is asked about the truncation, not really about how the DB should have been designed.


ps.  The point splitting was what the auto close procedure came back with.

I am not objecting, just adding what my own testing produced.

Andy's (and Guy's, for that matter) analysis is spot on.  The reason we see the [$$] as the visual representation of the entered data is because C# saved the variable as data separated by new-line constants.  That data stored into the database field was therefore a multi-line entry as opposed to a single line entry.

The author clearly states that they wish to retain the new-line constant as entry data not actionable data:

It is what I want as I need to store the raw data from a piece of hardware and this is what its sends - Source

In response to Guy's asertion of:

I don't think that you want that one in there? - Source

Therefore, I agree with Andy's recommendation.

-saige-

As said \0 is a string terminator