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); }