SQL's carriage return in C#

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

HI!!
I have some sql statements in my stored procedure which concatenates strings with carriage return wherever needed.I execute this stored procedure from C#,the procedure executes perfectly and the output is returned to a label.But the carriage return which I have added in my sql statements doesnt affects to C# labels.Why is this so?What is the alternate for this?
eg:-

SQL

declare @data varchar(500)
SET @data='HELLO,'+char(13)+char(10)+'Member'
print @data
--select @data

The above code is inside a stored procedure.I executed the stored procedure from SQL SERVER MANAGEMENT STUDIO and I get my desired output.The statement 'HELLO,'+char(13)+char(10)+'Member' will first print 'HELLO', and then the cursor comes to next line because of char(13)+char(10) and the it print 'Member'.
Below is the output:

VB

HELLO,
Member

But this is only possible if I use print command,but if I use print command,how can I output the value to C#?.
And I use select command then I can output the value to C# but the carriage return doesn't work.

Please help.

Posted 11-Nov-12 22:11pm

mathewtinu

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sanjay K. Gupta · Answer 1 · 2012-11-11T22:29:00

Handle "InfoMessage" event of SqlConnection object. It will fires when something you write print command in sql statement. For Example

C#

SqlConnection con = new SqlConnection(conString);
           con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessage);
           SqlCommand cmd = new SqlCommand("your_sp"), con);

           con.Open();
           cmd.ExecuteNonQuery();
           con.Close();

----------------------------------
Handle event as

C#

void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
       {
          //sb is your global StringBuilder variable
           sb.Append("\n" + e.Message);
       }

shahzads · Answer 2 · 2012-11-11T22:21:00

Solution 1

if possible useSET @data='HELLO,'+ '\n' +'Member' in stored procedure. It should work in .net

Posted 11-Nov-12 22:21pm

shahzads

Updated 11-Nov-12 22:28pm

v2

skokeh · Answer 3 · 2012-11-11T22:34:00

Solution 3

Dear Mathew,

You put Spaces in your Sql "Code +char(13)+char(10)+" it's can't be new Line in .net

in .net you have to mention the New line like this "\n" example "Hello" + "\n"
you Can put a word in your Stored Procedure, like this

SET @data='HELLO,'+'\n'+'\n'+'Member'

Posted 11-Nov-12 22:34pm

skokeh