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
– sticky bit, Commented Jun 6, 2020 at 22:47
@stickybit how can i use them.. please if you could show me an example — Hamza DriouCh
– Hamza DriouCh, Commented Jun 6, 2020 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
– sticky bit, Commented Jun 6, 2020 at 22:57
Does this answer your question? Single quote handling in a SQL string — Dale K
– Dale K, Commented Jun 7, 2020 at 5:42

Dale K · Accepted Answer · 2020-06-07 06:44:25Z

1

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.

edited Jun 7, 2020 at 6:44

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Jun 6, 2020 at 22:58

Guru Stron

151k11 gold badges186 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related