How to Avoid Single Quote Exception in SQL

Avoid Single Quote Issue

Most of Beginner facing problem in executing SQL query that contains varchar type fields..
for example : inserting string value to varchar type field in Sql query.. will raise "Unclosed quotation mark......." error.
-------Code ------------------------
string frstName="c-sharp's';
string lastName="corner";
//Here frstName contains single quote character.
string sqlQuery= "insert into TestTable(Firstname,lastname) value(frstName,lastName)";
//Excute Sql Query..

While Executing the above query will raise "Unclosed quotation mark after the character string" exception. To avoid this type of error replace ' with ''.

string sqlQuery= "insert into TestTable(Firstname,lastname) value(frstName.Replace("'", "''"),lastNameReplace("'", "''"))";

We can generalize the code to single Extension Method. Here is the Extension Method for replacing ' with ''.

-----Code----------

 /// <summary>
/// Class use for replace single qoutes ' to ''
/// </summary>
public static class SqlString
{
    /// <summary>
    /// This Extension method will replace the single qoutes ' to ''..
    /// So Sql single qoutes exception will never raise
    /// </summary>
    /// <param name="fieldvalue"></param>
    /// <returns></returns>
    public static string GetSqlString(this string fieldvalue)
    {
        if (fieldvalue != null)
            return fieldvalue.Replace("'", "''");
        return fieldvalue;
    }
}

Code Implementation:

string sqlQuery= "insert into TestTable(Firstname.GetSqlString(),lastname.GetSqlString()) value(frstName,lastName)";