C# OLEDB protect apostrophie in query

Question

I try to find a way to protect apostrophie in my query string. I have a value in string format that contain apostrophie and it throw me an error when I tried to insert

ex :

Insert into ["excelApp.worksheetsList.ElementAt(0).Name "$"] " + ([col1], [col2]) 
values values" + " ('" + val1 + "', '" + val2 + "');")

This is an exemple. here val1 contains "hereIsMy'value".

Thanks for helping me

Answer 1

You should use parametrized queries and you don't have to worry about single quotes in query

using(OleDbConnection cn = new OleDbConnection(GetConnectionString()))
{
    cn.Open();
    string cmdText = "Insert into [" + excelApp.worksheetsList.ElementAt(0).Name + "$] " +   
                     "([col1], [col2]) values (?, ?)";
    OleDbCommand cmd = new OleDbCommand(cmdText, cn)
    cmd.Parameters.AddWithValue("@p1", val1);
    cmd.Parameters.AddWithValue("@p2", val2);
    cmd.ExecuteNonQuery();
}

In this example your command text consist of a single string with placeholders for the parameters value. Then a command object is declared to have that string and two parameters are added at its collection of parameters. They are created according to the variable type passed as value. So, if val1 and val2 are strings and a single quote (apostrophe) is present it is automatically formatted for the insert/delete/update or select operation requested