private void DisplayBtn_Click(object sender, EventArgs e) { //https://devlights.hatenablog.com/entry/20070302/p2 this.MntGritView.DataSource = new BindingSource(); var dbConnect = new DbConect(); //設定 //奇数行を青色にする }
/** * Method: SalvarImagensURLs * Description: Salva no banco de dados as URLs de imagens/fotos dos locais * Author: Luiz F. Amaral * Data: 02/12/2020 * Website: https://luizamaral.dev.br * Contact: [email protected] */ public void SalvarImagensURLs(List <String> imagens, Int32 idLocal) { if (imagens == null) { return; } NpgsqlConnection pgsqlConnection = DbConect.GetConection(); //Abra a conexão com o PgSQL pgsqlConnection.Open(); try { foreach (var url in imagens) { String strSQL = " Insert into ImagemURL " + " ( idLocal, " + " url ) "+ " Values(@param1, @param2) "; NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection); cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal)); cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(url)); cmd.ExecuteNonQuery(); } } catch (NpgsqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { pgsqlConnection.Close(); } }
/** * Method: SalvarHorariosHistogramItem * Description: Salva no banco de dados um horario de funcionamento do local * Author: Luiz F. Amaral * Data: 02/12/2020 * Website: https://luizamaral.dev.br * Contact: [email protected] */ public void SalvarHorariosHistogramItem(Int32 idLocal, int diaSemana, int hora, int taxa) { NpgsqlConnection pgsqlConnection = DbConect.GetConection(); //Abra a conexão com o PgSQL pgsqlConnection.Open(); try { String strSQL = " Insert into HorariosHistoricoHistogram " + " ( idLocal, " + " idDiaSemana, "+ " hora, "+ " taxaOcupacao) "+ " Values(@param1, @param2, @param3, @param4) "; NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection); cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal)); cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(diaSemana)); cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(hora)); cmd.Parameters.AddWithValue("@param4", Utils.Utils.SafeDbObject(taxa)); cmd.ExecuteNonQuery(); } catch (NpgsqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { pgsqlConnection.Close(); } }
/** * Method: SalvarReviewsUsuarios * Description: Salva no banco de dados as revisões/comentarios dos usuários sobre o local * Author: Luiz F. Amaral * Data: 02/12/2020 * Website: https://luizamaral.dev.br * Contact: [email protected] */ public void SalvarReviewsUsuarios(List <Review> listaReview, Int32 idLocal) { if (listaReview == null) { return; } NpgsqlConnection pgsqlConnection = DbConect.GetConection(); //Abra a conexão com o PgSQL pgsqlConnection.Open(); try { foreach (var item in listaReview) { String strSQL = " Insert into reviewsusuarios " + " ( " + " idlocal, " + " idreview, " + " tempopublicacaodataimport, " + " dataimport, " + " url, " + " likes, " + " pontuacao, " + " nome, " + " mensagem, " + " reviewerid, " + " reviewerurl, " + " reviewernumberofreviews, " + " islocalguide " + " ) " + " Values(@param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, " + " @param9, @param10, @param11, @param12, @param13) "; NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection); cmd.Parameters.AddWithValue("@param1", Utils.Utils.SafeDbObject(idLocal)); cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(item.reviewId)); cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(item.publishAt)); cmd.Parameters.AddWithValue("@param4", DateTime.Now); cmd.Parameters.AddWithValue("@param5", Utils.Utils.SafeDbObject(item.reviewUrl)); cmd.Parameters.AddWithValue("@param6", Utils.Utils.SafeDbObject(item.likesCount)); cmd.Parameters.AddWithValue("@param7", Utils.Utils.SafeDbObject(item.stars)); cmd.Parameters.AddWithValue("@param8", Utils.Utils.SafeDbObject(item.name)); cmd.Parameters.AddWithValue("@param9", Utils.Utils.SafeDbObject(item.text)); cmd.Parameters.AddWithValue("@param10", Utils.Utils.SafeDbObject(item.reviewerId)); cmd.Parameters.AddWithValue("@param11", Utils.Utils.SafeDbObject(item.reviewerUrl)); cmd.Parameters.AddWithValue("@param12", Utils.Utils.SafeDbObject(item.reviewerNumberOfReviews)); cmd.Parameters.AddWithValue("@param13", Utils.Utils.SafeDbObject(item.isLocalGuide)); cmd.ExecuteNonQuery(); } } catch (NpgsqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { pgsqlConnection.Close(); } }
/** * Method: SalvarHorariosSemanais * Description: Salva no banco de dados as informações dos horarios semanais * Author: Luiz F. Amaral * Data: 02/12/2020 * Website: https://luizamaral.dev.br * Contact: [email protected] */ public void SalvarHorariosSemanais(List <OpeningHour> horarioFuncionamento, Int32 idLocal) { if (horarioFuncionamento == null) { return; } NpgsqlConnection pgsqlConnection = DbConect.GetConection(); //Abra a conexão com o PgSQL pgsqlConnection.Open(); try { foreach (var item in horarioFuncionamento) { int idDia = 0; if (item.day.Equals("Sunday,")) { idDia = ID_DOMINGO; } else if (item.day.Equals("Monday,")) { idDia = ID_SEGUNDA; } else if (item.day.Equals("Tuesday,")) { idDia = ID_TERCA; } else if (item.day.Equals("Wednesday,")) { idDia = ID_QUARTA; } else if (item.day.Equals("Thursday,")) { idDia = ID_QUINTA; } else if (item.day.Equals("Friday,")) { idDia = ID_SEXTA; } else if (item.day.Equals("Saturday,")) { idDia = ID_SABADO; } if (idDia == 0) { Console.WriteLine("Erro ao importar horários de funcionamento"); continue; } String strSQL = " Insert into HorariosFuncionamento " + " ( idLocal, " + " idDiaSemana, "+ " intervaloDesc ) "+ " Values(@param1, @param2, @param3) "; NpgsqlCommand cmd = new NpgsqlCommand(strSQL, pgsqlConnection); cmd.Parameters.AddWithValue("@param1", idLocal); cmd.Parameters.AddWithValue("@param2", idDia); cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(item.hours)); cmd.ExecuteNonQuery(); } } catch (NpgsqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { pgsqlConnection.Close(); } }
/** * Method: SalvarLocal * Description: Salva no banco de dados as informações dos Locais da pesquisa(Places) * Author: Luiz F. Amaral * Data: 02/12/2020 * Website: https://luizamaral.dev.br * Contact: [email protected] */ public void SalvarLocal(Place local, int count) { NpgsqlConnection pgsqlConnection = DbConect.GetConection(); //Abra a conexão com o PgSQL pgsqlConnection.Open(); String strSQL = ""; NpgsqlCommand cmd = null; try { strSQL = " Insert into Local " + " ( titulo, "+ " pontuacaoTotal, " + " categoriaNome, " + " endereco, " + " plusCode, " + " website, " + " telefone, " + " temporarioFechado, " + " fechado, " + " ranking, " + " placeId, " + " url, " + " latitude, " + " longitude, " + " searchString, " + " HorarioPopularesTxt, " + " contagemReviews) " + " Values(@param2, @param3, @param4, @param5, @param6, " + " @param7, @param8, @param9, @param10, @param11, @param12, " + " @param13, @param14, @param15, @param16, @param17, @param18) " + " RETURNING idLocal "; cmd = new NpgsqlCommand(strSQL, pgsqlConnection); cmd.Parameters.AddWithValue("@param2", Utils.Utils.SafeDbObject(local.title)); cmd.Parameters.AddWithValue("@param3", Utils.Utils.SafeDbObject(local.totalScore)); cmd.Parameters.AddWithValue("@param4", Utils.Utils.SafeDbObject(local.categoryName)); cmd.Parameters.AddWithValue("@param5", Utils.Utils.SafeDbObject(local.address)); cmd.Parameters.AddWithValue("@param6", Utils.Utils.SafeDbObject(local.plusCode)); cmd.Parameters.AddWithValue("@param7", Utils.Utils.SafeDbObject(local.website)); cmd.Parameters.AddWithValue("@param8", Utils.Utils.SafeDbObject(local.phone)); cmd.Parameters.AddWithValue("@param9", Utils.Utils.SafeDbObject(local.temporarilyClosed)); cmd.Parameters.AddWithValue("@param10", Utils.Utils.SafeDbObject(local.permanentlyClosed)); cmd.Parameters.AddWithValue("@param11", Utils.Utils.SafeDbObject(local.rank)); cmd.Parameters.AddWithValue("@param12", Utils.Utils.SafeDbObject(local.placeId)); cmd.Parameters.AddWithValue("@param13", Utils.Utils.SafeDbObject(local.url)); cmd.Parameters.AddWithValue("@param14", Utils.Utils.SafeDbObject(local.location.lat)); cmd.Parameters.AddWithValue("@param15", Utils.Utils.SafeDbObject(local.location.lng)); cmd.Parameters.AddWithValue("@param16", Utils.Utils.SafeDbObject(local.searchString)); cmd.Parameters.AddWithValue("@param17", Utils.Utils.SafeDbObject(local.popularTimesLivePercent)); cmd.Parameters.AddWithValue("@param18", Utils.Utils.SafeDbObject(local.reviewsCount)); Int32 idLocal = Convert.ToInt32(cmd.ExecuteScalar()); pgsqlConnection.Close(); SalvarHorariosSemanais(local.openingHours, idLocal); SalvarHorariosHistogram(local.popularTimesHistogram, idLocal); SalvarReviewsUsuarios(local.reviews, idLocal); SalvarImagensURLs(local.imageUrls, idLocal); } catch (NpgsqlException ex) { Console.WriteLine(count); throw ex; } catch (Exception ex) { throw ex; } }