How to read each data from json array and assign it to SQL Server IN clause

Question

Client application sends a json array to my .net application. I'm receiving it using name value collection. Now I want to read it's value and assign it to an IN clause of a SQL query.

HttpContext context = HttpContext.Current;
NameValueCollection nvc = context.Request.Form;

string co_json = nvc["co_json"];
// jsArray : [{"roll_number":"11","mark":"68"},{"roll_number":"22","mark":"70"}]";

// I want to get the two roll numbers from above json and need to pass it to SQL 
SqlDataAdapter da = new SqlDataAdapter("select * from  student_details where roll_number IN ( ???) ", con);

DataTable dt = new DataTable();
da.Fill(dt);

If you construct your IN clause by concatenating strings supplied by the user then be aware of opening yourself up to SQL injection attacks. — Mike Chamberlain
– Mike Chamberlain, Commented Apr 6, 2017 at 12:21

jomsk1e · Accepted Answer · 2017-04-06 12:28:09Z

You can convert the json to a custom c# object using Newtonsoft JSON.NET You can create a class like this:

public class RollMark 
{
    public string roll_number {get;set;}
    public string mark {get;set;}
}

now you can do this:

HttpContext context = HttpContext.Current;
NameValueCollection nvc = context.Request.Form;
string co_json = nvc["co_json"];// jsArray : [{"roll_number":"11","mark":"68"},{"roll_number":"22","mark":"70"}]";

//collect all rollMarks in json to c# list
var rollMarks = Newtonsoft.Json.JsonConvert.DeserializeObject<List<RollMark>>(co_json );

And you can now add parameters in your query like this:

//format your IN query
string[] paramArray = rollMarks.Select(x, i) => "@settings" + i).ToArray();
string query = string.Format("select * from  student_details where roll_number IN ({0})", string.Join(",", paramArray));
SqlDataAdapter da = new SqlDataAdapter(query, con);

DataTable dt = new DataTable();
for (int i = 0; i < rollMarks.Count(); ++i)
{
    da.SelectCommand.Parameters.AddWithValue("@settings", rollMarks[i].roll_number)
}
da.Fill(dt);

Hk_developer · Accepted Answer · 2017-04-06 12:18:31Z

0

First of all, split your json array to find each item of array. next, create a class that has each proprties of your item. finally deserialize your each json item to the class.

answered Apr 6, 2017 at 12:18

Hk_developer

1

Collectives™ on Stack Overflow

How to read each data from json array and assign it to SQL Server IN clause

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related