/*ReadGeoData * Takes nothing * Returns List of all GeoData in DB */ public static List <GeoDataModel> ReadGeoData() { List <GeoDataModel> GeoDataList = new List <GeoDataModel>(); using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("SELECT * FROM dbo.GeoTable", connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { GeoDataModel data = new GeoDataModel { ID = reader.GetInt32(0), PostalCode = reader.GetInt32(1), City = reader.GetString(2), Population = reader.GetInt32(3), Temperature = reader.GetDouble(4) }; GeoDataList.Add(data); } } } return(GeoDataList); }
/*DeleteGeoData * Takes in GeoDataModel object to remove * Returns 1 if successful * Returns 0 if not */ public static int DeleteGeoData(GeoDataModel RemovedGeoData) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("DELETE FROM dbo.GeoTable WHERE ID = @ID", connection)) { cmd.Parameters.AddWithValue("@ID", RemovedGeoData.ID); connection.Open(); return(cmd.ExecuteNonQuery()); } } }
/*GeoData Functions * This is all the functions needed for the app regarding GeoData * FULL CRUD */ #region CreateGeoData /*CreateGeoData * Takes in GeoDataModel object to add * Returns 1 if successful * Returns 0 if not */ public static int CreateGeoData(GeoDataModel NewGeoData) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("INSERT INTO dbo.GeoTable VALUES (@PostalCode, @City, @Population, @Temperature)", connection)) { cmd.Parameters.AddWithValue("@PostalCode", NewGeoData.PostalCode); cmd.Parameters.AddWithValue("@City", NewGeoData.City); cmd.Parameters.AddWithValue("@Population", NewGeoData.Population); cmd.Parameters.AddWithValue("@Temperature", NewGeoData.Temperature); connection.Open(); return(cmd.ExecuteNonQuery()); } } }
/*UpdateGeoData * Takes in GeoDataModel object to update * Returns 1 if successful * Returns 0 if not */ public static int UpdateGeoData(GeoDataModel UpdatedGeoData) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("UPDATE dbo.GeoTable SET PostalCode = @PostalCode, City = @City, Population = @Population, Temperature = @Temperature WHERE ID = @ID", connection)) { cmd.Parameters.AddWithValue("@PostalCode", UpdatedGeoData.PostalCode); cmd.Parameters.AddWithValue("@City", UpdatedGeoData.City); cmd.Parameters.AddWithValue("@Population", UpdatedGeoData.Population); cmd.Parameters.AddWithValue("@Temperature", UpdatedGeoData.Temperature); cmd.Parameters.AddWithValue("@ID", UpdatedGeoData.ID); connection.Open(); return(cmd.ExecuteNonQuery()); } } }
/*ReturnIDGeo * Takes GeoDataModel object to add id to * returns GeoDataModel object * used to fix issue with running code having no id for current GeoDataModel item */ public static GeoDataModel ReturnIDGeo(GeoDataModel geoData) { using (SqlConnection connection = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("SELECT ID FROM dbo.GeoTable WHERE PostalCode=@PostalCode AND City=@City", connection)) { cmd.Parameters.AddWithValue("@PostalCode", geoData.PostalCode); cmd.Parameters.AddWithValue("@City", geoData.City); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { geoData.ID = reader.GetInt32(0); } } } return(geoData); }