I am building a C# Web API and its purpose is to enable an external company to retrieve student data (they call them 'roles') via a GET call to the API I am building. Now, they have the choice of either retrieving all students via a call to https://localhost:XXXXX/custom-roles-api/campusCustomRoles or to retrieve a single student it'd be https://localhost:XXXXX/custom-roles-api/campusCustomRoles/123456 with '123456' being the student Id number. Some students have one row of data as they below to a single department, so the JSON return in the GET call shows them in a single element, however for students who belong to 2 or more departments, the return shows more than one element, for example;
{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Religions",
"Smith, John",
123456
]
]
}
In the above example, student John Smith belongs to 2 departments (Religions and Politics) and this is represented in two elements in the JSON return. What the external company have requested is for a single element which would look like this and this is where I am stuck;
{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Religions",
"Smith, John",
123456
]
]
}
I hasten to add that it was also one of the requirements to return only the Object values without the keys (or column names).
The Below is what my Roles Class looks like;
public class Roles
{
List<Roles> studentRoles = new List<Roles>();
public int UserName { get; set; }
public string PersonName { get; set; }
public string Profile { get; set; }
public string Level { get; set; }
public int Year { get; set; }
public string Department { get; set; }
}
public class readRoles : Roles
{
public readRoles(DataRow dataRow)
{
UserName = (int)dataRow["UserName"];
PersonName = (string)dataRow["PersonName"];
Profile = (string)dataRow["Profile"];
Level = (string)dataRow["Level"];
Year = Convert.ToInt32(dataRow["Year"]);
Department = (dataRow["Department"] == DBNull.Value) ? "No Department" : dataRow["Department"].ToString();
}
public int UserName { get; set; }
public string PersonName { get; set; }
public string Profile { get; set; }
public string Level { get; set; }
public int Year { get; set; }
public string Department { get; set; }
}
And below, I have this in my Controller;
public HttpResponseMessage Get(int id)
{
string connString;
SqlConnection con;
connString = @"XXXXXXXXXX";
DataSet _ds = new DataSet();
con = new SqlConnection(connString);
con.Open();
var sql = @"select distinct CONCAT(CONCAT(mytable.surname, ', '),dbo.initcap(mytable.forenames)) as 'PersonName'
,convert(int,mytable.studentID) as 'UserName'
,mytable.category as 'Profile'
,mytable.level as 'Level'
,mytable.year as 'Year'
,mytable.depat as 'Department'
from MYTABLE
WHERE convert(int,mytable.studentID) = @UserName
order by 2 desc ";
SqlParameter param = new SqlParameter();
param.ParameterName = "@UserName";
param.Value = id;
SqlCommand CMD2 = new SqlCommand(sql, con);
CMD2.Parameters.Add("@UserName", SqlDbType.Int).Value = id;
CMD2.Connection = con;
CMD2.CommandText = sql;
CMD2.CommandType = System.Data.CommandType.Text;
SqlDataReader dr = CMD2.ExecuteReader();
SqlDataAdapter _adapter = new SqlDataAdapter(sql, con);
_adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, con)
};
_adapter.SelectCommand.Parameters.AddWithValue("@UserName", id);
_adapter.Fill(_ds, "roles");
List<Roles> roles = new List<Roles>(_ds.Tables[0].Rows.Count);
if (_ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow studentrole in _ds.Tables[0].Rows)
{
roles.Add(new readRoles(studentrole));
}
}
foreach (DataRow drow in _ds.Tables[0].Rows)
{
var item = new List<object>();
item.Add((string)drow["Profile"]);
item.Add((string)drow["Level"]);
item.Add(Convert.ToInt32(drow["Year"]));
item.Add((drow["Department"] == DBNull.Value) ? "No Department" : drow["Department"].ToString());
item.Add((string)drow["PersonName"]);
item.Add((int)drow["UserName"]);
studentRoles.Add(item);
};
var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = studentRoles });
// The below part of the code is where the departments are merged but this solution only works if there are only 2 departments, but there are many cases where a student has more than 2 departments
if (roles.Count() > 1)
{
for (int i = 0; i < studentRoles[0].Count(); i++)
{
if (studentRoles[0][i].ToString() != studentRoles[1][i].ToString())
{
var arr = new JArray();
arr.Add(studentRoles[0][i]);
arr.Add(studentRoles[1][i]);
studentRoles[0][i] = arr;
}
}
studentRoles[1].Clear()
}
return response;
}
The problem I am having is that, my code works as expected but ONLY if a student belongs to only 2 departments, like our example above, John Smith. I need help tweaking the above last bit of code to accommodate students who will be returned with more than 2 elements as some belong to multiple departments. For example, if John Smith belonged to 3 departments;
{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Religions",
"Smith, John",
123456
],
[
"STUDENT",
"UG",
2,
"Languages",
"Smith, John",
123456
]
]
}
I would expect John Smith's return to look like this;
{
"roles": [
[
"STUDENT",
"UG",
2,
"Politics",
"Religions",
"Languages",
"Smith, John",
123456
]
]
}
Eventually, the external company's requirement would be for students' Modules to be included, and of course students will have multiple Modules, but I'll cross that bridge when I get there, ha! Any help would be appreciated.
add to your code my solution https://stackoverflow.com/a/75485207/11392290
var roles = JArray.From(studentRoles);
if (roles.Count() > 1)
{
.... use my code
}
var response = Request.CreateResponse(HttpStatusCode.OK, new { roles = roles });