Correct String Escaping for T-SQL string literals

Question

I want to use a query as following, I am looking for exact information/link to escape strings

BookTitle is NVARCHAR(200)

SELECT * FROM Books WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow \r\n')

Question: Does only "'" needs to be escaped or even \r\n needs to be escaped as well? MySql .Net Provider exposes a method to escape string values, is there any such function in Sql Server .Net Provider?

I probably need C# equivalent escaping for string values.

I am aware of Parameterized Command, but in order to minimize my server to client communication, and my values in IN clause are in number from 20 to 50, it becomes too much network expensive to run SELECTs for each value of BookTitle in one call. Rather running single query and returning all results cascaded helps saving network resources.

Answer 1

SQL Server won't recognise the \r\n sequence, whether it's escaped or not.

You'll need to do something like this instead if you want to match the \r\n in BookTitle:

-- \r = CHAR(13)
-- \n = CHAR(10)
SELECT *
FROM Books
WHERE BookTitle IN ('Mars and Venus', 'Stack''s Overflow ' + CHAR(13) + CHAR(10))