C# with INSERT Stored Procedure \r\n problem

Question

Basically i've got a very simple insert statement which

INSERT INTO [dbo].[ORDER]
    (ORDER_DATE
    ,ORDER_TYPE_ID
    ,PAYMENT_STATUS_ID
    ,TOTAL_COST
    ,SENDER_NAME
    ,SENDER_EMAIL
    ,SENDER_MESSAGE
    ,RECIPIENT_NAME
    ,RECIPIENT_ADDRESS)
VALUES
    (@ORDER_DATE
    ,@ORDER_TYPE_ID
    ,@PAYMENT_STATUS_ID
    ,@TOTAL_COST
    ,@SENDER_NAME
    ,@SENDER_EMAIL
    ,@SENDER_MESSAGE
    ,@RECIPIENT_NAME
    ,@RECIPIENT_ADDRESS)

The address input in the C# code is:

Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetStoredProcCommand("sp_ORDER_INSERT");

db.AddInParameter(cmd, "@ORDER_DATE", DbType.DateTime, this._orderDate);
db.AddInParameter(cmd, "@ORDER_TYPE_ID", DbType.Int32, this._typeOfOrder.OrderTypeId);
db.AddInParameter(cmd, "@PAYMENT_STATUS_ID", DbType.Int32, this._statusOfPayment.PaymentStatusId);
db.AddInParameter(cmd, "@TOTAL_COST", DbType.Decimal, this._totalCost);
db.AddInParameter(cmd, "@SENDER_NAME", DbType.String, this._senderName);
db.AddInParameter(cmd, "@SENDER_EMAIL", DbType.String, this.SenderEmail);
db.AddInParameter(cmd, "@SENDER_MESSAGE", DbType.String, this._senderMessage);
db.AddInParameter(cmd, "@RECIPIENT_NAME", DbType.String, this._recipientName);
db.AddInParameter(cmd, "@RECIPIENT_ADDRESS", DbType.String, this._recipientAddress);

this._orderId = Convert.ToInt32(db.ExecuteScalar(cmd));            
cmd.Dispose();

The value in this._recipientAddress is a string of "address line 1 \r\naddress line2\r\n\r\naddressline3"

Anyway when I insert this into SQL it removed the '\r\n' and I don't want it to as this will get displayed later and I want the line breaks.

The RECIPIENT_ADDRESS database column is nText

Anyone got any ideas?

Answer 1

I do not believe there is a way to do what you are looking for. The line breaks aren't stored in the database. Probably a better idea for you would be to store address in 3 columns address1, adress2 and address3 and then you can format it while displaying in the UI.

Answer 2

"address line 1 \r\naddress line2\r\n\r\naddressline3"

In SQL would be:

SELECT 'address line 1 ' + CHAR(13) + CHAR(10) 
          + 'address line2'  + CHAR(13) + CHAR(10) 
          + CHAR(13) + CHAR(10) 
          + 'addressline3'

Answer 3

Basically it worked but doesn't show in SQL Management Studio when the result pane is set to display in Grid, need to set it to Display in Text.

When I do a select in C# then view the data the \r\n are still in the data.

The CHAR(13) + CHAR(10) are auto added to the database but they are not visable in the Grid view, just as if you did 'SELECT CHAR(13) + CHAR(10)' you get nothing.

Cheers guys

Answer 4

You can simply replace those '\n' and '\r' in the stored procedure

INSERT INTO [dbo].[ORDER] (ORDER_DATE ,ORDER_TYPE_ID ,PAYMENT_STATUS_ID ,TOTAL_COST ,SENDER_NAME ,SENDER_EMAIL ,SENDER_MESSAGE ,RECIPIENT_NAME ,RECIPIENT_ADDRESS) VALUES (@ORDER_DATE ,@ORDER_TYPE_ID ,@PAYMENT_STATUS_ID ,@TOTAL_COST ,@SENDER_NAME ,@SENDER_EMAIL ,@SENDER_MESSAGE ,@RECIPIENT_NAME ,REPLACE(REPLACE(@RECIPIENT_ADDRESS, '\r', CHAR(13), '\n', CHAR(10))