none
C# Environment.NewLine and SqL Server RRS feed

  • Question

  • Hi

    I am inserting a string which contains Environment.NewLine in the middle of the string, onto a SQL server field of VarChar type. I am using Entity Framework 6 to do the SQL transactions.

    Using SQL server management studio SQL query, I try to find the position of Carriage Return (CR) character (Char 13) in the above string I inserted. To my surprise, the query returns CR position as 0, indicating it did not find CR. But if I change the query to search Line Feed character (Char 10) in the above string, query returns the correct position of LF.

    But when I cut and pasted the string from SQL field onto a notepad++, I do see CR and LF. But if I cut and pasted the string from SQL field onto a notepad, I do not see CR. So I am confused.

    C# Environment.NewLine is CR+LF. Then why can't I see the carriage return character when I do a query? Any feedback or thoughts are appreciated.

    Thanks,

    KK

    • Moved by CoolDadTx Tuesday, May 26, 2015 4:59 PM EF related
    Tuesday, May 26, 2015 1:48 PM

Answers

  • Hello KKailasamoni,

    According to your description, I created a small test to insert a value including a C# Environment.NewLine to a varchar type column with below code by using Entity Framework 6:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    Order order = new Order() { OrderID = 6, OrderName = "Line1" + Environment.NewLine + "Line2" };
    
    
                    db.Order.Add(order);
    
    
                    db.SaveChanges();
    
                }
    

    However, on my side, by using you provided query to fetch the CR and LF as:

    SELECT  OrderName,CHARINDEX(CHAR(13), OrderName) as CR_Location, CHARINDEX(CHAR(10), OrderName) as LF_Location
    
    from [Order]
    

    It could get the correct position:

    OrderName        CR_Location       LF_Location
    
    Line1  Line2         6                 7
    

    This sounds like confused since both you and me use the SQL Server database, .NET 4.5 and Entity Framework 6, while getting different results. I am wondering if the table schema and code used to insert data would be the reason, if possible, you could share your table and code with us, or you could try with mine and the Order is as below:

    CREATE TABLE [dbo].[Order] (
    
        [OrderID]   INT            NOT NULL,
    
        [OrderName] VARCHAR(50) NULL,
    
        CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
    
    );
    

    >>Any idea how to prevent EF from converting CRLF to LF and write CRLF?

    If my above demo does not work, you could try to use raw sql or store produce to check if you could get the expected result.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 27, 2015 2:52 AM
    Moderator
  • Thank you Fred for the details and taking the time to post the code and SQL. In fact, it helped me to identify where the issue was and we got it solved!

    We are using XML to insert the data into SQL server, which is what parsing the character string CRLF and removed the LF.

    Thanks once again truly appreciated!

    KK

    Wednesday, May 27, 2015 6:06 PM

All replies

  • What query?

    DECLARE @Memo VARCHAR(MAX);
    
    SET @Memo = 'Line1' + CHAR(13) + CHAR(10) + 'Line2';
    
    SELECT  @Memo ,
            CHARINDEX(CHAR(13), @Memo);

    Tuesday, May 26, 2015 1:51 PM
  • Following is the query:

    SELECT  NOTES,

    CHARINDEX(CHAR(13), NOTES) as CR_Location, CHARINDEX(CHAR(10), NOTES) as LF_Location

    FROM  NotesTable

    Here NOTES field has the string that was inserted by C# application, .NET framework 4.5

    Tuesday, May 26, 2015 2:02 PM
  • Well, I don't have EF running, but I guess it just converts CRLF to LF when storing it.
    Tuesday, May 26, 2015 4:08 PM
  • Any idea how to prevent EF from converting CRLF to LF and write CRLF?
    Tuesday, May 26, 2015 4:43 PM
  • Hello KKailasamoni,

    According to your description, I created a small test to insert a value including a C# Environment.NewLine to a varchar type column with below code by using Entity Framework 6:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    Order order = new Order() { OrderID = 6, OrderName = "Line1" + Environment.NewLine + "Line2" };
    
    
                    db.Order.Add(order);
    
    
                    db.SaveChanges();
    
                }
    

    However, on my side, by using you provided query to fetch the CR and LF as:

    SELECT  OrderName,CHARINDEX(CHAR(13), OrderName) as CR_Location, CHARINDEX(CHAR(10), OrderName) as LF_Location
    
    from [Order]
    

    It could get the correct position:

    OrderName        CR_Location       LF_Location
    
    Line1  Line2         6                 7
    

    This sounds like confused since both you and me use the SQL Server database, .NET 4.5 and Entity Framework 6, while getting different results. I am wondering if the table schema and code used to insert data would be the reason, if possible, you could share your table and code with us, or you could try with mine and the Order is as below:

    CREATE TABLE [dbo].[Order] (
    
        [OrderID]   INT            NOT NULL,
    
        [OrderName] VARCHAR(50) NULL,
    
        CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
    
    );
    

    >>Any idea how to prevent EF from converting CRLF to LF and write CRLF?

    If my above demo does not work, you could try to use raw sql or store produce to check if you could get the expected result.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 27, 2015 2:52 AM
    Moderator
  • Thank you Fred for the details and taking the time to post the code and SQL. In fact, it helped me to identify where the issue was and we got it solved!

    We are using XML to insert the data into SQL server, which is what parsing the character string CRLF and removed the LF.

    Thanks once again truly appreciated!

    KK

    Wednesday, May 27, 2015 6:06 PM