Quote:
from DataRow _drloc in _dr.ItemArray
The
DataRow.ItemArray property[
^] returns an array containing the values from the current
DataRow
. None of those values will be other
DataRow
s, so that query will throw an
InvalidCastException
.
NB: When you're asking a question about code that throws an exception, you should
always include the full details of the exception!
Based on your sample data, your database query has "flattened" three separate tables - countries, locations, and sub-locations. You'll find it easier to process if you keep the tables separate, and load them into separate
DataTable
s:
DataSet data = new DataSet();
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ... FROM Countries; SELECT ... FROM Locations; SELECT ... FROM SubLocations;", connection))
{
var da = new SqlDataAdapter(command);
da.Load(data);
}
DataTable countries = data.Tables[0];
DataTable locations = data.Tables[1];
DataTable subLocations = data.Tables[2];
DataRelation fkCountryLocations = data.Relations.Add("CountryLocations", countries.Columns["Country_Id"], locations.Columns["Country_Id"]);
DataRelation fkLocationSubLocations = data.Relations.Add("LocationSubLocations", locations.Columns["Location_Id"], subLocations.Columns["Location_Id"]);
You can then navigate between the tables to build your JSON data:
_gloc.country = countries.Rows.Select(row => new Country
{
country_id = Convert.ToInt16(row["Country_Id"]),
country_name = row["Country_Name"].ToString(),
location = row.GetChildRows(fkCountryLocations).Select(locationRow => new Location
{
addedon = locationRow.IsNull("Location_Addedon") ? (DateTime?)null : Convert.ToDateTime(locationRow["Location_Addedon"]),
createdby = locationRow["Location_CreatedBy"].ToString(),
id = locationRow.IsNull("Location_ID") ? (Int16?)null : Convert.ToInt16(locationRow["Location_ID"]),
location_lat = locationRow["Location_Lat"].ToString(),
location_long = locationRow["Location_Long"].ToString(),
name = locationRow["Location_Name"].ToString(),
sub_location = locationRow.GetChildRows(fkLocationSubLocations).Select(subLocationRow => new Sub_Location
{
addedon = subLocationRow.IsNull("SubLocation_AddedOn") ? (DateTime?)null : Convert.ToDateTime(subLocationRow["SubLocation_AddedOn"]),
id = subLocationRow.IsNull("SubLocation_ID") ? (Int16?)null : Convert.ToInt16(subLocationRow["SubLocation_ID"]),
sublocation_name = subLocationRow["SubLocation_Name"].ToString()
}).ToList(),
}).ToList(),
}).ToList();
NB: When you're calling
Convert.ToSOMETHING(...)
, you don't need to call
.ToString()
on the value you're converting.