// GET api/ItemsShop
public string Get([FromQuery] RequestLoadFiltrUslugs req)
{
string json = "";
try
{
ResponseLoadFiltrUslug resp = new ResponseLoadFiltrUslug();
resp.GetLoadFiltr(Constants.connectDB, req);
json = JsonConvert.SerializeObject(resp);
}
catch (Exception ex)
{
json += " err " + ex.Message;
}
return(json);
}
public void GetLoadFiltr(string connectionString, RequestLoadFiltrUslugs req)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
Uslugs = new List <USLUG>();
Metros = new List <Metro>();
MetroLines = new List <Metro>();
string sqlExpression = "";
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
//SqlDataReader reader;
#region get USLUG
sqlExpression = @"
SELECT
ID_USLUG,
NAME_USLUG,
NOTE_USLUG,
IMG_URL
FROM SPAVREMONT.USLUG
";
command.CommandText = sqlExpression;
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int ID_USLUG_Index = reader.GetOrdinal("ID_USLUG");
int NAME_USLUG_Index = reader.GetOrdinal("NAME_USLUG");
int NOTE_USLUG_Index = reader.GetOrdinal("NOTE_USLUG");
int IMG_URL_Index = reader.GetOrdinal("IMG_URL");
while (reader.Read()) // построчно считываем данные
{
USLUG item = new USLUG
{
ID_USLUG = reader.GetString(ID_USLUG_Index),
NAME_USLUG = reader.GetString(NAME_USLUG_Index),
NOTE_USLUG = reader.GetString(NOTE_USLUG_Index),
IMG_URL = reader.GetString(IMG_URL_Index)
};
Uslugs.Add(item);
}
}
reader.Close();
#endregion
#region get metro
sqlExpression = @"
SELECT
m.ID_metro,
m.Name_line,
m.Station,
m.ID_Geo,
m.Color_Hex,
m.ID_City
FROM SPAVREMONT.METRO m
JOIN SPAVREMONT.SHOP sh ON m.ID_metro=sh.ID_metro
WHERE m.ID_City='" + req.ID_City + @"'
AND sh.id_type_shop = '360eb5f2-0ffd-411b-9cf2-318a60b22604'
GROUP BY
m.ID_metro,
m.Name_line,
m.Station,
m.ID_Geo,
m.Color_Hex,
m.ID_City
ORDER BY m.Station ASC
";
command.CommandText = sqlExpression;
reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
//int genreIDIndex = reader.GetOrdinal("GenreID");
//...
//while...
//GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex)
int ID_metro_Index = reader.GetOrdinal("ID_metro");
int Name_line_Index = reader.GetOrdinal("Name_line");
int Station_Index = reader.GetOrdinal("Station");
// int ID_Geo_Index = reader.GetOrdinal("ID_Geo");
int Color_Hex_Index = reader.GetOrdinal("Color_Hex");
// int ID_city_Index = reader.GetOrdinal("ID_City");
while (reader.Read()) // построчно считываем данные
{
Metro item = new Metro
{
ID_metro = reader.GetString(ID_metro_Index),
Name_line = reader.GetString(Name_line_Index),
Station = reader.GetString(Station_Index),
Color_Hex = reader.GetString(Color_Hex_Index)
};
Metros.Add(item);
}
}
reader.Close();
#endregion
#region get metroLine
sqlExpression = @"
SELECT
m.Name_line,
m.Color_Hex
FROM SPAVREMONT.METRO m
JOIN SPAVREMONT.SHOP sh ON m.ID_metro=sh.ID_metro
WHERE m.ID_City='" + req.ID_City + @"'
AND sh.id_type_shop = '360eb5f2-0ffd-411b-9cf2-318a60b22604'
GROUP BY
m.Name_line,
m.Color_Hex
ORDER BY m.Name_line ASC
";
command.CommandText = sqlExpression;
reader = command.ExecuteReader();
if (reader.HasRows) // если есть данные
{
int Name_line_Index = reader.GetOrdinal("Name_line");
int Color_Hex_Index = reader.GetOrdinal("Color_Hex");
int tmp_id = 0;
while (reader.Read()) // построчно считываем данные
{
tmp_id++;
Metro item = new Metro
{
ID_metro = tmp_id.ToString(),
Station = reader.GetString(Name_line_Index),
Color_Hex = reader.GetString(Color_Hex_Index)
};
MetroLines.Add(item);
}
}
// reader.Close();
#endregion
}//END using
Cities = City.GetCities();
}
