Use method Replace() on table dynamic JSON using C#

Question

I want to use method Replace() to change double quote " by single quote ' in my code C# for many reasons.

For example in SQL Server I can't insert in column nvarchar a text contains a single quote.

INSERT INTO table_ VALUES('can't use');

So I figured out I should use

SET QUOTED_IDENTIFIER OFF;
INSERT INTO table_ VALUES("can't use");
SET QUOTED_IDENTIFIER ON;

But the problem in my JSON tables contains single and double quotes that I can't insert in my database. That why I want to use method Replace(). But it throw error

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'No overload for method 'Replace' takes '2' arguments'

My code:

public SqlConnection con;
public SqlCommand cmd;
public SqlDataAdapter da;
public DataSet ds;
public DataTable dt;
public SqlDataReader rdr;
public SqlParameter pr;
protected void Page_Load(object sender, EventArgs e)
{
    con = new SqlConnection("data source = (local);initial catalog = DB; integrated security = true;");
    con.Open();
    cmd = new SqlCommand();
    cmd.Connection = con;

    WebClient api = new WebClient();
    string JSON = api.DownloadString("http://newsapi.org/v2/top-headlines?country=ma&apiKey=4fff1c0a541c43d99e4a6f319d191fca");
    dynamic myObj = JsonConvert.DeserializeObject<dynamic>(JSON);
    var qt = '"';

    for (int i = 0; i < myObj.articles.Count; i++)
    { 
        cmd.CommandText = "" +
            " SET QUOTED_IDENTIFIER OFF;" +
            " INSERT INTO NEWS VALUES(" +
            "" + qt + myObj.articles[i].source.id.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].name.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].author.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].title.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].description.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].url.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].urlToImage.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].publishedAt.Replace("'", qt) + qt + "," +
            "" + qt + myObj.articles[i].content.Replace("'", qt) + qt + ")" +
            " SET QUOTED_IDENTIFIER ON;";
        cmd.ExecuteNonQuery();
    }
}

My SQL table

create table news
(
    id int primary key identity(1,1),
    source_id nvarchar(1000)null,
    source_name nvarchar(1000)null,
    author nvarchar(100)null,
    title nvarchar(1000)null,
    description_ nvarchar(4000)null,
    url  nvarchar(1000)null,
    urlToImage nvarchar(1000)null,
    publishedAt date null,
    content nvarchar(4000) null
)

Double quotes won't help you. They enclose identifiers in SQL server, not string literals. And what are you gonna do, if you have a string with single and double quotes? As @pinkfloydx33 already pointed out, you're on a completely wrong way here. Use parameterized queries. That will fix your current problem (and also some more). — sticky bit, Jun 06 '20 at 22:47
@stickybit how can i use them.. please if you could show me an example — Hamza DriouCh, Jun 06 '20 at 22:55
Use your favorite web search engine of the search of this site. There's plenty of explanations and example out there. — sticky bit, Jun 06 '20 at 22:57
Does this answer your question? [Single quote handling in a SQL string](https://stackoverflow.com/questions/4644703/single-quote-handling-in-a-sql-string) — Dale K, Jun 07 '20 at 05:42

score 1 · Accepted Answer · edited Jun 07 '20 at 06:44

Looking at your table scheme I would assume that JSON you are fetching has number for myObj.articles[i].source.id which will be parsed to int which in turn does not have Replace method:

var json = "{'id':1}";
var obj = JsonConvert.DeserializeObject<dynamic>(json);
obj.id.Replace("1", ""); // throws RuntimeBinderException: No overload for method 'Replace' takes 2 arguments

So just remove Replace call for it.

As for parameterized queries there is a lot of information on this topic, for example you can look at this answer or this.

Also I would recommend to introduce some class which will represent your JSON scheme and deserialize to it, not dynamic.

Use method Replace() on table dynamic JSON using C#

1 Answers1