Please help in escaping this SQL string to C# string

Question

'<ad name="'+ am.name+ '" tag="C" cityid="'+cast(am.city_id as varchar) +'" 
 stateid="'+convert(varchar(11),cast(cm.state_id as varchar)) +'" capital="N"> 
 </ad>' as chkdata

I have above statement in SQL query.

I want to add this to C# string

Unsucessful attempt

string sqlqry = "\'<ad name=""\'+ am.name_id + \'"" tag=""C"" 
cityid=""\'+cast(am.city_id as varchar) +\'"" 
stateid=""\'+convert(varchar(11),cast(cm.state_id as varchar)) +\'"" capital=""N"">
</ad>\' as chkdata ,";

Later I am doing this . I am building a query in string.

sqlcountcmd = new SqlCommand(finalsqlqry, sqlconn);

+1 to all guaranteed

it would be far preferable to simply select the columns you want, and let your application layer worry about xml — Marc Gravell
– Marc Gravell, Commented May 20, 2013 at 8:09

Marc Gravell · Accepted Answer · 2013-05-20 08:13:03Z

2

presumably that entire thing is to be treated as sql - not a composite of sql and C#. Thus if we use a verbatim literal we only need to worry about the "s:

string s = @"'<ad name=""'+ am.name+ '"" tag=""C"" cityid=""'+cast(am.city_id as varchar) +'"" 
stateid=""'+convert(varchar(11),cast(cm.state_id as varchar)) +'"" capital=""N""> 
</ad>' as chkdata ,";

However! I strongly suggest not doing this. You should just return the columns from SQL, and let your app tier worry about xml/html etc.

answered May 20, 2013 at 8:13

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

1 Comment

Mudassir Hasan Over a year ago

Yes entire thing is to be treated as sql - not a composite of sql and C#

RemarkLima · Accepted Answer · 2013-05-20 08:13:14Z

1

So, if I've understood correctly, you'll only need to escape the double quotes " and not single quotes '

string sqlqry = "'<ad name=\"'+ am.name_id + '\" tag=\"C\" " +
"sectordetailid=\"'+cast(am.sector_detail_id as varchar) +'\" " +
"stateid=\"'+convert(varchar(11),cast(cm.state_id as varchar)) +'\" capital=\"N\">" +
"</ad>' as chkdata";

You can use the verbatim character @ I'm sure, but I've not got Visual Studio in front of me to check.

answered May 20, 2013 at 8:13

RemarkLima

12.1k8 gold badges41 silver badges57 bronze badges

Collectives™ on Stack Overflow

Please help in escaping this SQL string to C# string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related