public static int InsertPasport(PasportDTO pasport, SqlConnection connection)
{
int ret = 0;
string query = "INSERT INTO [student3].[dbo].[PASPORT]([final_inspection], [building_id]) " +
"VALUES (@final_inspection, @id);";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.Add("@id", SqlDbType.VarChar).Value = pasport.ID;
cmd.Parameters.Add("@final_inspection", SqlDbType.Date).Value = DateTime.Parse(pasport.FinalInspection, CultureInfo.CreateSpecificCulture("cz-CZ"));
try
{
ret = cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException) { }
}
foreach (ElementDTO element in pasport.Elements)
{
InsertElement(element, pasport.ID, connection);
}
return(ret);
}
public static List <PasportDTO> GetByID(string id, SqlConnection connection)
{
List <PasportDTO> pasports = new List <PasportDTO>();
string query = "SELECT * FROM SVERENE_BUDOVY " +
"LEFT JOIN PASPORT ON SVERENE_BUDOVY.id = PASPORT.building_id " +
"WHERE id = @ID;";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ID", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) // there is only one row
{
PasportDTO pasport = new PasportDTO()
{
FinalInspection = reader["final_inspection"] == null ? null : Convert.ToString(reader["final_inspection"]),
ID = reader["building_id"] == null ? null : Convert.ToString(reader["building_id"])
};
BuildingDTO building = new BuildingDTO();
building.ID = id;
building.Type = Convert.ToString(reader["typ_budovy"]);
building.Use = Convert.ToString(reader["zpusob_vyuziti"]);
building.Municipality = Convert.ToString(reader["obec"]);
if (reader["cislo_domovni"] != DBNull.Value)
{
building.HouseNumber = Convert.ToInt32(reader["cislo_domovni"]);
}
building.Plat = reader["na_parcele"].ToString();
string temporaryStr = Convert.ToString(reader["docasna_stavba"]);
building.Temporary = temporaryStr.Equals("n") ? false : true;
if (reader["podil_citatel"] != DBNull.Value)
{
building.FractionDenominator = Convert.ToInt32(reader["podil_citatel"]);
}
if (reader["podil_jmenovatel"] != DBNull.Value)
{
building.FractionNominator = Convert.ToInt32(reader["podil_jmenovatel"]);
}
building.SubjectICO = Convert.ToString(reader["s_ico"]);
building.SubjectName = Convert.ToString(reader["s_nazev"]);
building.SubjectStreet = Convert.ToString(reader["s_ulice"]);
if (reader["s_cislo_domovni"] != DBNull.Value)
{
building.SubjectHouseNumber = Convert.ToInt32(reader["s_cislo_domovni"]);
}
if (reader["s_cislo_orientacni"] != DBNull.Value)
{
building.SubjectOrientationNumber = Convert.ToInt32(reader["s_cislo_orientacni"]);
}
building.SubjectMunicipality = Convert.ToString(reader["s_obec"]);
building.SubjectPostCode = Convert.ToString(reader["s_psc"]);
building.SubjectDistrict = Convert.ToString(reader["s_okres"]);
building.TelephoneNumber = Convert.ToString(reader["tel_id"]);
pasport.Building = building;
pasports.Add(pasport);
}
}
}
pasports.ForEach(pasport => pasport.Elements = GetPasportElementsByID(pasport.ID, connection));
return(pasports);
}
