Solved

SqlParameter.Value not saving correctly

Posted on 2016-09-14
10
46 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 34

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
 
LVL 34

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 34

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 34

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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