Solved

SqlParameter.Value not saving correctly

Posted on 2016-09-14
10
33 Views
Last Modified: 2016-10-03
I have string of data i'm trying to save into an  Nvarchar(500) column:

"$$\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"

However, its only saving as
"$$"

When i put a breakpoint on the .net code i see this:
quickwatch12

I'm using this code to save
param[39] = new SqlParameter("@raw_data", SqlDbType.NVarChar, 500);
param[39].Value = this.raw_data;

Then if i look at the assigned .Value, its as it should be:

quickwatch121

but if you look at the first image, it shows SqlValue as $$, and this is what is being saved
the SP inserting this data and the column are both Nvarchar 500

stumped!
any ideas?
0
Comment
Question by:websss
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 250 total points (awarded by participants)
ID: 41797421
Try this instead
"$$\\0j?ye  
at the start - note the double backslash.

I guess the system is treating the \0 as an end of string, the \\0 should instruct it to treat it as two chars, a '\' followed by a '0'
1
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points (awarded by participants)
ID: 41797423
well, I see \0 which is the "string null termination character" ...
I don't think that you want that one in there?
1
 

Author Comment

by:websss
ID: 41797435
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?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41797448
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.
1
 
LVL 32

Assisted Solution

by:sarabande
sarabande earned 125 total points (awarded by participants)
ID: 41797595
I need to store the raw data from a piece of hardware and this is what its sends
if \0 is part of a buffer Contents sent by a device, it is NOT a binary zero character but two characters: a backslash \ and a digit 0. the sequence '\0' only translates to a binary zero if it is a in a c, c++, or c# source character or string literal.

so, you are safe if you would copy the contents of the buffer sent by the device to a char array, terminate the char array with a zero byte and assign it to a String before storing it to the NVARCHAR column.

if you want to define the sequence as a literal in c# you have to escape the backslash as shown by Andy.

Sara
1
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 32

Expert Comment

by:it_saige
ID: 41798227
*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
)

Open 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;
			}
		}
	}
}

Open in new window

Produces the following output -

Straight Run -Capture.JPGTable Data -Capture.JPG
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;
			}
		}
	}
}

Open in new window

Your usage would then become:
param[39].Value = this.raw_data.ToLiteral();

Open 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;
			}
		}
	}
}

Open in new window

Step through snippets -
Inside the extension method -Capture.JPGWhat will be stored in the parameter value -Capture.JPGWhat the SqlParameter shows -Capture.JPGWhat the field contains in the database -Capture.JPG
-saige-
1
 
LVL 32

Expert Comment

by:sarabande
ID: 41820354
@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
1
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41820384
@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.
1
 
LVL 32

Expert Comment

by:it_saige
ID: 41820459
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-
1
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 41826019
As said \0 is a string terminator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now