Insertion problems with single-quotes in MS-SQL and case insensitive comparison in .Net

Problem 1 :

Anybody working with Microsoft SQL Server as the back-end face this annoying issue while using insert statements, especially when they want to insert values containing single quotes within them. Now to clear the concept regarding this issue, the way Microsoft SQL (or say MS-SQL) works is that it treats anything between two single-quotes as a string and hence if the string itself has a single quote then it'll throw an SQL Exception.

1] Consider the following case wherein a simple string is inserted into a column :

INSERT into Student(name) VALUES(' Hardik ');

1 Row INSERTED successfully.

2] In the following case, a string containing a single quote is inserted which throws an SQL Exception :

INSERT into Student(name) VALUES(' Hardik's ');

System.Data.SqlClient.SqlException : Incorrect syntax near 's'. Unclosed quotation mark after the character string ')'.

Solution :

The problem as seen above is silly and so is its solution. If you want to include a single quote included in a string then use the single quotes twice. The single-quotes used twice is treated as a single-quote in MS-SQL. So the solution to the above SQL Query will be as follows :

INSERT into Student(name) VALUES(' Hardik''s ');

1 Row INSERTED successfully.

Now having done with the problem and the solution, a real challenge would be to implement the same in projects using our own programming skills. Now the challenge is to have the single-quotes twice when we have to insert a string containing a single-quote and not when we don't have a single-quote. An important method in .Net and Java which can be used here is the 'replace()' method. The replace method accepts two arguments where the first is the character which you want to replace and the second being the character which you want to replace with.

CODE Snippet in C# :

1.  
2. String s = "Guru says Lolz !!!";
3.  
4. s.Replace("!", ".");
5.  
6. Console.WriteLine(s); // OP : Guru says Lolz ...
7. 
   

If you felt that the above example still doesn't suffice then consider a real-time example in use with SQL.

CODE Snippet :

1.  
2. String s = " Hardik's ";
3.  
4. try
5. {
6.     SqlConnection con = new SqlConnection(conn); // where conn is the Connection String
7.     SqlCommand cmd = new SqlCommand("INSERT into Student(name) VALUES('" + s.Replace("'","''") + "')", con);
8.     con.Open();
9.     
10.     cmd.ExecuteNonQuery();
11.  
12.     con.Close();
13. }
14. catch(Exception ex)
15. {
16.     Console.WriteLine("Exception : " + ex);
17. }
18.  

The string [Hardik's] will be successfully inserted in the Column [name] of the Table [Student]. The case of having single-quotes in it is also dealt with appropriately.

---

Problem 2 :

Another very simple problem which people come across in .Net is when making case-insensitive comparisons between strings(or say its alias "Strings"). If the Itellisense feature in Visual Studio is used wisely then you are not away from the solution but still a lot of people are found searching for the solution over search engines. Also for people who are well versed with Java might recall the 'equalsIgnoreCase()' method but what about its equivalent in .Net?

CODE Snippet :

1.  
2. String s1 = "Guru";
3.  
4. if (s1.Equals("guru", StringComparison.OrdinalIgnoreCase) == true)
5. {
6.     Console.WriteLine("The String comparison was successful.");
7. }
8.  

Post a Comment