How can I prevent undesirable conversions done by ADO.NET?

[TomTom]

Hi,

I have a simple command.ExecuteNonQuery() to insert some data into the SQL
DB. The data can be inserted, but the characters like '\n' are somehow
stripped during the process. I want to keep it.

My code looks like below.

SqlCommand insertCommand = new SqlCommand();
SqlCommand command = new SqlCommand();
command.CommandText = "up_parm_insert_data_into_sql"; // This stored
procedure includes a very simple INSERT statement.
command.CommandType = CommandType.StoredProcedure;
SqlParameter [] sqlParams = new SqlParameter[1];

sqlParams[0] =
command.Parameters.Add("@myParameterText",SqlDbType.NVarChar);
sqlParams[0].Direction = ParameterDirection.Input;

// dataRow["myDataColumn"].ToString() incluedes the following text. Please
note that the text includes '\n'.
// Merry Cristmas. \n Wish you happy programming in 2005."
sqlParams[0].Value = dataRow["myDataColumn"].ToString();
command.Connection = sqlConnection;
command.ExecuteNonQuery();


Does someone know how I can preserve \n in the SQL DB? During the insert
process it appears to be lost.

TomTom

 

[Sylvain Lafontaine]

How can you tell that the \n have been striped? For example, you print the
length of the string and it is shorter by two caracters?

S. L.

 

[TomTom]

Thanks for your reply. I see the \n is stripped when I view the row in Query
Analyzer. Instead of \n, a white space is inserted. The white space appears
to be a real white space (not tab character or \n or anything else).

How can you tell that the \n have been striped? For example, you print
the length of the string and it is shorter by two caracters?

S. L.

Hi,

I have a simple command.ExecuteNonQuery() to insert some data into the
SQL
DB. The data can be inserted, but the characters like '\n' are somehow
stripped during the process. I want to keep it.

My code looks like below.

SqlCommand insertCommand = new SqlCommand();
SqlCommand command = new SqlCommand();
command.CommandText = "up_parm_insert_data_into_sql"; // This stored
procedure includes a very simple INSERT statement.
command.CommandType = CommandType.StoredProcedure;
SqlParameter [] sqlParams = new SqlParameter[1];

sqlParams[0] =
command.Parameters.Add("@myParameterText",SqlDbType.NVarChar);
sqlParams[0].Direction = ParameterDirection.Input;

// dataRow["myDataColumn"].ToString() incluedes the following text.
Please
note that the text includes '\n'.
// Merry Cristmas. \n Wish you happy programming in 2005."
sqlParams[0].Value = dataRow["myDataColumn"].ToString();
command.Connection = sqlConnection;
command.ExecuteNonQuery();


Does someone know how I can preserve \n in the SQL DB? During the insert
process it appears to be lost.

TomTom

 

[TomTom]

A little update.
textInserted.Replace(Environment.NewLine,"\n");

This did not insert \n in the table either. In my case, it's important to
represent the line break as \n. The data is mainly consumed by SQL Query
Analyzer. The people who query the text needs to see the line break (or \n).

Thanks for your reply. I see the \n is stripped when I view the row in
Query Analyzer. Instead of \n, a white space is inserted. The white space
appears to be a real white space (not tab character or \n or anything
else).

How can you tell that the \n have been striped? For example, you print
the length of the string and it is shorter by two caracters?

S. L.

Hi,

I have a simple command.ExecuteNonQuery() to insert some data into the
SQL
DB. The data can be inserted, but the characters like '\n' are somehow
stripped during the process. I want to keep it.

My code looks like below.

SqlCommand insertCommand = new SqlCommand();
SqlCommand command = new SqlCommand();
command.CommandText = "up_parm_insert_data_into_sql"; // This stored
procedure includes a very simple INSERT statement.
command.CommandType = CommandType.StoredProcedure;
SqlParameter [] sqlParams = new SqlParameter[1];

sqlParams[0] =
command.Parameters.Add("@myParameterText",SqlDbType.NVarChar);
sqlParams[0].Direction = ParameterDirection.Input;

// dataRow["myDataColumn"].ToString() incluedes the following text.
Please
note that the text includes '\n'.
// Merry Cristmas. \n Wish you happy programming in 2005."
sqlParams[0].Value = dataRow["myDataColumn"].ToString();
command.Connection = sqlConnection;
command.ExecuteNonQuery();


Does someone know how I can preserve \n in the SQL DB? During the insert
process it appears to be lost.

TomTom

 

[Jon Skeet [C# MVP]]

A little update.
textInserted.Replace(Environment.NewLine,"\n");

This did not insert \n in the table either. In my case, it's important to
represent the line break as \n. The data is mainly consumed by SQL Query
Analyzer. The people who query the text needs to see the line break (or \n).

Did you just call it as above? If so, you need to understand that
strings are immutable - the call to Replace doesn't change the string
it's called on, it returns a new string with the replacements made.

 

[TomTom]

Yes, I understand how to use the Replacement method. I put the modified text
in the variable. The earlier posting was misleading. Sorry!

 

[TomTom]

I tried it again and now it work! I had to use the escaping character
correctly. Thank you for your help!

Yes, I understand how to use the Replacement method. I put the modified
text in the variable. The earlier posting was misleading. Sorry!

 

[Sylvain Lafontaine]

Do you mean by that writing \\n instead of \n ?

S. L.

I tried it again and now it work! I had to use the escaping character
correctly. Thank you for your help!

 

[TomTom]

Yes, I tried \\n and @"n".

Do you mean by that writing \\n instead of \n ?

S. L.