/// <summary> /// Compare table to table /// </summary> private static TableDB ValidationByTable(TableDB tbl) { string tblName = tbl.TableName; // validate header field try { int bashSize = int.Parse(ConfigurationManager.AppSettings["BashSize"]); for (int i = 0; i < Math.Ceiling((decimal)tbl.RecordCount / bashSize); i++) { var dataTableMySql = DBMySQLUtils.DataTableInMySql(tblName, i + 1, bashSize); var dataTableSqlServer = DBSqlServerUtils.DataTableInSqlServer(tblName, i + 1, bashSize); CompareTableMySqlAndSqlServer(dataTableMySql, dataTableSqlServer, tbl); } } catch (SqlException e) { if (e.Number == 208) { tbl.Problems.Clear(); tbl.Problems.Add("Table is not found in Sql Server"); } } catch (Exception e) { //tbl.Problems.Add(e.Message); } return(tbl); }
private void add(String prenom, String nom, String telephone, String status, String informations, DateTime dateInscription, DateTime dateFinInscription) { ////SQL STMT MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", 3306, "ram", "root", "root"); conn.Open(); string sql = "call `creer_enfant`(@prenom , @nom, @telephone, @status, @informations, @dateinscription, @datefininscription );"; MySqlCommand cmd = new MySqlCommand(sql, conn); ////ADD PAramETERS cmd.Parameters.AddWithValue("@prenom", prenom); cmd.Parameters.AddWithValue("@nom", nom); cmd.Parameters.AddWithValue("@telephone", telephone); cmd.Parameters.AddWithValue("@status", status); cmd.Parameters.AddWithValue("@informations", informations); cmd.Parameters.AddWithValue("@dateInscription", dateInscription); cmd.Parameters.AddWithValue("@dateFinInscription", dateFinInscription); ////OPEN CON AND EXEC insert try { if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("Successfully Inserted"); } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } }
private void delete(int pId) { int id = pId; //SQL STMT MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", "ram", "root", "root"); conn.Open(); Console.WriteLine("ok2"); String sql = "CALL `supprimer_Enfant`(@id);"; MySqlCommand cmd = new MySqlCommand(sql, conn); //ADD PARAMETERS cmd.Parameters.AddWithValue("@id", id); //OPEN CON AND EXEC insert try { if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("Successfully Inserted"); } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } }
public static MySqlConnection GetDBConnection() { string host = "tarangok.ru"; int port = 3306; string database = "tarangok_db"; string username = "******"; string password = "******"; return(DBMySQLUtils.GetDBConnection(host, port, database, username, password)); }
public static MySqlConnection GetDBConnection() { string host = "172.29.102.22"; int port = 3306; string database = "ram"; string username = "******"; string password = "******"; return(DBMySQLUtils.GetDBConnection(host, port, database, username, password)); }
private void Form1_Load(object sender, EventArgs e) { dataGridViewListeEnfant.Rows.Clear(); MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", , "ram", "root", "root"); DataTable dt = new DataTable(); conn.Open(); AfficherEnfants(); conn.Close(); }
private void dateTimePickerJourConcerne_ValueChanged(object sender, EventArgs e) { dataGridViewListeEnfant.Rows.Clear(); MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", 3306, "ram", "root", "root"); DataTable dt = new DataTable(); conn.Open(); DateTime pDate = dateTimePickerJourConcerne.Value; AfficherEnfantsDate(pDate); }
private void listAtelier_SelectedIndexChanged(object sender, EventArgs e) { try { Atelier ate = new Atelier(); ate = (Atelier)listAtelier.SelectedItem; int ateId = ate.GetId(); Console.WriteLine(ateId); MySqlConnection conn = null; conn = DBMySQLUtils.GetDBConnection("172.29.102.22", "ram", "root", "root"); String recupEnfant = null; recupEnfant = "CALL `recup_Enfant_Atelier2`(@pateId);"; MySqlCommand afficherEnfant = null; afficherEnfant = conn.CreateCommand(); afficherEnfant.CommandText = recupEnfant; afficherEnfant.Parameters.AddWithValue("@pateId", ateId); conn.Open(); DataTable dt = new DataTable(); MySqlDataAdapter adapter = null; adapter = new MySqlDataAdapter(afficherEnfant); adapter.Fill(dt); dataGridViewListeEnfant.DataSource = null; dataGridViewListeEnfant.Rows.Clear(); dataGridViewListeEnfant.Refresh(); foreach (DataRow row in dt.Rows) { populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString()); } // Terminez la connexion. conn.Close(); // Disposez un objet, libérez des ressources. conn.Dispose(); } catch (Exception e5) { Console.WriteLine("Error: " + e5); Console.WriteLine(e5.StackTrace); } Console.Read(); }
private void btnAjoutEnfantAtelier_Click(object sender, EventArgs e) { Console.WriteLine(listEnfant.SelectedItem); Console.WriteLine(listAtelier.SelectedItem); Atelier ate = new Atelier(); ate = (Atelier)listAtelier.SelectedItem; Console.WriteLine(ate); int ateId = ate.GetId(); Console.WriteLine(ateId); Enfant enf = new Enfant(); enf = (Enfant)listEnfant.SelectedItem; int enfId = enf.GetId(); Console.WriteLine(enfId); MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", 3306, "ram", "root", "root"); string sql = "CALL `creer_Lien`(@pEnfId , @pAteId);"; // Créez un objet Command. MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.Parameters.AddWithValue("@pEnfId", enfId); cmd.Parameters.AddWithValue("@pAteId", ateId); conn.Open(); try { if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("Enfant mis à jour"); } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } }
private void EditionAtelier_Load(object sender, EventArgs e) { { List <Atelier> lstAtelier = new List <Atelier>(); MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", 3306, "ram", "root", "root"); string sql = "CALL `recup_Atelier`();"; // Créez un objet Command. MySqlCommand cmd = new MySqlCommand(sql, conn); string sql2 = "CALL `recup_Enfant`();"; // Créez un objet Command. MySqlCommand cmd2 = new MySqlCommand(sql2, conn); conn.Open(); using (MySqlDataReader objReader = cmd.ExecuteReader()) { if (objReader.HasRows) { while (objReader.Read()) { //I would also check for DB.Null here before reading the value. int ateId = objReader.GetInt32(objReader.GetOrdinal("ateId")); String ateNom = objReader.GetString(objReader.GetOrdinal("ateNom")); String ateDescription = objReader.GetString(objReader.GetOrdinal("ateDescription")); String ateHoraire = objReader.GetString(objReader.GetOrdinal("ateHoraire")); DateTime ateJour = objReader.GetDateTime(objReader.GetOrdinal("ateJour")); int ateNbrPlace = objReader.GetInt32(objReader.GetOrdinal("ateNbrPlace")); String atePublicConcerne = objReader.GetString(objReader.GetOrdinal("atePublicConcerne")); Atelier atelier = new Atelier(ateId, ateNom, ateDescription, ateHoraire, ateJour, ateNbrPlace, atePublicConcerne); listAtelier.Items.Add(atelier); } } } conn.Close(); } }
private void AfficherEnfantsDate(DateTime pDate) { try { MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", "ram", "root", "root"); conn.Open(); // DateTime date = DateTime.ParseExact(pDate.ToString(), "MM/dd/yyyy hh:mm:ss", CultureInfo.InvariantCulture); String americanFormat = pDate.Date.ToString("yyyy-MM-dd"); //Console.WriteLine(pDate); Console.WriteLine(americanFormat); String recupEnfant = "CALL `recup_Enfant_Date`(@pdate);"; MySqlCommand afficherEnfant = conn.CreateCommand(); afficherEnfant.Parameters.AddWithValue("@pdate", americanFormat); afficherEnfant.CommandText = recupEnfant; DataTable dt = new DataTable(); MySqlDataAdapter adapter = new MySqlDataAdapter(afficherEnfant); adapter.Fill(dt); dataGridViewListeEnfant.Rows.Clear(); dataGridViewListeEnfant.Refresh(); foreach (DataRow row in dt.Rows) { populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString()); } // Terminez la connexion. conn.Close(); // Disposez un objet, libérez des ressources. conn.Dispose(); } catch (Exception e5) { Console.WriteLine("Error: " + e5); Console.WriteLine(e5.StackTrace); } Console.Read(); }
private void AfficherEnfants() { try { MySqlConnection conn = null; conn = DBMySQLUtils.GetDBConnection("172.29.102.22", "ram", "root", "root"); conn.Open(); String recupEnfant = null; recupEnfant = "CALL `recup_Enfant`();"; MySqlCommand afficherEnfant = null; afficherEnfant = conn.CreateCommand(); afficherEnfant.CommandText = recupEnfant; DataTable dt = new DataTable(); MySqlDataAdapter adapter = null; adapter = new MySqlDataAdapter(afficherEnfant); adapter.Fill(dt); dataGridViewListeEnfant.DataSource = null; dataGridViewListeEnfant.Rows.Clear(); dataGridViewListeEnfant.Refresh(); foreach (DataRow row in dt.Rows) { populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString()); } // Terminez la connexion. conn.Close(); // Disposez un objet, libérez des ressources. conn.Dispose(); } catch (Exception e5) { Console.WriteLine("Error: " + e5); Console.WriteLine(e5.StackTrace); } Console.Read(); }
private void update(int id, String prenom, String nom, String telephone, String status, String informations, DateTime dateInscription, DateTime dateFinInscription) { MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", 3306, "ram", "root", "root"); DataTable dt = new DataTable(); conn.Open(); //SQL STMT String sql = "call `update_Enfant`(@prenom , @nom, @telephone, @status, @informations, @dateInscription, @dateFinInscription, @id );"; MySqlCommand cmd = new MySqlCommand(sql, conn); //ADD PARAMETERS cmd.Parameters.AddWithValue("@prenom", prenom); cmd.Parameters.AddWithValue("@nom", nom); cmd.Parameters.AddWithValue("@telephone", telephone); cmd.Parameters.AddWithValue("@status", status); cmd.Parameters.AddWithValue("@informations", informations); cmd.Parameters.AddWithValue("@dateInscription", dateInscription); cmd.Parameters.AddWithValue("@dateFinInscription", dateFinInscription); cmd.Parameters.AddWithValue("@id", id); //OPEN CON,UPDATE,RETRIEVE DGVIEW try { if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("Successfully Inserted"); AfficherEnfants(); } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } }
private void btnAjouter_Click(object sender, EventArgs e) { string nom = txtBoxNomAtelier.Text; string description = txtBoxDescriptionAtelier.Text; string horaire = lstBoxHoraire.Text; string nbPlace = txtBoxNbPlace.Text; string persoConcerne = listBoxPerso.Text; DateTime jour = dateTimePickerJourAtelier.Value.Date; MySqlConnection conn = DBMySQLUtils.GetDBConnection("172.29.102.22", "ram", "root", "root"); conn.Open(); try { string sql = "CALL `creer_Atelier`(@pnom, @pdescription, @phoraire, @pjour, @pnbPlace, @ppersoConcerne);"; // Créez un objet Command. MySqlCommand cmd = new MySqlCommand(); // Établissez la connexion de la commande. cmd.Connection = conn; cmd.CommandText = sql; cmd.Parameters.AddWithValue("@pnom", nom); cmd.Parameters.AddWithValue("@pdescription", description); cmd.Parameters.AddWithValue("@phoraire", horaire); cmd.Parameters.AddWithValue("@pjour", jour); cmd.Parameters.AddWithValue("@pnbPlace", nbPlace); cmd.Parameters.AddWithValue("@ppersoConcerne", persoConcerne); try { if (cmd.ExecuteNonQuery() > 0) { MessageBox.Show("Successfully Inserted"); } conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } // using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader()) // { // if (reader.HasRows) // { // while (reader.Read()) // { // //string sql = "Select ateId, ateNom, ateDescription, ateHoraire, ateJour, ateNbrPlace, atePublicConcerne from ATELIER"; // // Récupérez l'indexe (index) de colonne Emp_ID dans l'instruction de requête SQL. // //string ateId = reader.GetString(0); // 0 // string ateNom = reader.GetString(1); // string ateDescription = reader.GetString(2);// 2 // string ateHoraire = reader.GetString(3); // string ateJour = reader.GetString(4); // int ateNbrPlace = reader.GetInt32(5); // string atePublicConcerne = reader.GetString(6); // // Index (index) de la colonne Mng_Id dans l'instructions de requête SQL. // String nom_fr_frIndex = reader.GetString(3); // String nom_fr_fr = null; // // Si une colonne est nullable, vérifiez toujours DBNull ... // //if (!reader.IsDBNull(nom_fr_frIndex)) // //{ // // //nom_fr_fr = Convert.ToInt64(reader.GetValue(nom_fr_frIndex)); // //} // //this.dataGridViewPlanning.Rows.Add(ateNom, ateDescription, ateHoraire, ateJour, ateNbrPlace, atePublicConcerne); // int iLEL = 0; // while (iLEL < 19) // { // iLEL++; // Atelier atelier = new Atelier(ateNom, ateDescription, ateHoraire, ateNbrPlace, atePublicConcerne); // if (atelier != null) // { // Console.WriteLine(atelier.ToString()); // } // if (iLEL >= 10) // { // break; // } // } // } // } // } //add(prenom, nom, telephone, status, informations, dateInscription, dateFinInscription); } catch (Exception ex) { MessageBox.Show(ex.Message); conn.Close(); } }
public Queries() { _conn = null; _conn = DBMySQLUtils.GetDBConnection(new DAL.DBSettings()); }
/// <summary> /// Main menu /// </summary> public static void MainMenu() { bool isCustomInput = false; bool hideMenu = false; int subMenu = 0; string selected = "m"; while (!hideMenu) { try { isCustomInput = false; Console.Clear(); if (selected == "q") { break; } if (selected == "m") { subMenu = 0; Console.WriteLine("1) Lists tables in MySql database"); Console.WriteLine("2) Lists tables in Sql Server database"); Console.WriteLine("3) Validate all tables in database - using query"); Console.WriteLine("4) Validate selection tables in database - using query"); Console.WriteLine("5) Export Mysql database to csv file"); Console.WriteLine("6) Validate all tables in database - using csv file"); } else if (subMenu == 0 && selected == "1") { subMenu = 1; var listTbl = DBMySQLUtils.GetListTable(); PrintLine(); PrintRow("Lists tables in MySql database"); PrintLine(); PrintRow("Table name", "Number of records"); PrintLine(); foreach (var tbl in listTbl) { PrintRow(tbl.Key, tbl.Value.ToString()); } } else if (subMenu == 0 && selected == "2") { subMenu = 2; var listTblSqlServer = DBSqlServerUtils.GetListTable(); PrintLine(); PrintRow("Lists tables in Sql Server database"); PrintLine(); PrintRow("Table name", "Number of records"); PrintLine(); foreach (var tbl in listTblSqlServer) { PrintRow(tbl.Key, tbl.Value.ToString()); } } else if (subMenu == 0 && selected == "3") { subMenu = 3; var listTblMySql = DBMySQLUtils.GetListTable() .Select(item => new TableDB { TableName = item.Key, RecordCount = item.Value }).ToList(); var listTblSqlServer = DBSqlServerUtils.GetListTable(); PrintLine(); PrintRow("Validation Summary Report"); PrintLine(); PrintRow("", "MySql", "Sql Server"); PrintLine(); foreach (var tbl in listTblMySql) { ValidationByTable(tbl); } PrintRow("Number of tables Checked", listTblMySql.Count().ToString(), listTblSqlServer.Count().ToString()); PrintLine(); var listTblPass = listTblMySql.Where(tbl => tbl.Problems.Count() == 0); var listTblFail = listTblMySql.Where(tbl => !listTblPass.Any(item => item.TableName == tbl.TableName)); PrintRow("Table Passed", listTblPass.Count().ToString(), ""); PrintLine(); PrintRow("Table Failed", listTblFail.Count().ToString(), ""); foreach (var tblFailed in listTblFail.ToList()) { for (int index = 0; index < tblFailed.Problems.Count(); index++) { var problem = tblFailed.Problems[index]; if (index == 0) { PrintRow("", tblFailed.TableName, "- " + problem); } else { PrintRowRight("", "", "- " + problem); } } } PrintLine(); } else if (subMenu == 0 && selected == "4") { subMenu = 4; var listTbl = DBMySQLUtils.GetListTable(); PrintRow("Validate table theo lựa chọn"); PrintLine(); PrintRow("Danh sách table trong Mysql"); PrintLine(); PrintRow("#", "table", "Number of Records"); PrintLine(); for (int index = 0; index < listTbl.Count(); index++) { var tbl = listTbl.ElementAt(index); PrintRow(index.ToString(), tbl.Key, tbl.Value.ToString()); } Console.WriteLine("Có 3 cách nhập: "); Console.WriteLine(" - Chọn tất cả table: a hoặc A hoặc All"); Console.WriteLine(" - Chọn khoảng table: 1-10 hoặc 1-5 5-10"); Console.WriteLine(" - Chọn từng table: 1,2,3 hoặc 1 2 3"); } else if (subMenu == 4) { var listTbl = DBMySQLUtils.GetListTable() .Select((item, index) => new TableDB { TableName = item.Key, RecordCount = item.Value, Index = index }).ToList(); if (new Regex("(\\d-\\d \\d-\\d)|(\\d-\\d)").IsMatch(selected)) { var listSelected = selected.Split(' ').ToList(); var selectedTbl = new List <TableDB>(); foreach (var item in listSelected) { var selectedTables = item.Split('-').Select(int.Parse).ToList(); if (selectedTables[0] < 0 || selectedTables[0] < selectedTables[1] || selectedTables[1] > listTbl.Count()) { selected = "4"; } selectedTbl.AddRange(listTbl.Where(e => e.Index >= selectedTables[0] && e.Index <= selectedTables[1]).ToList()); } listTbl = selectedTbl.Distinct().ToList(); } else if (new Regex("(\\d)|(\\d \\d)|(\\d,\\d)").IsMatch(selected)) { var selectedTables = selected.Split(' ', ',').Select(int.Parse).Distinct().ToList(); if (selectedTables.Any(e => e < 0) || selectedTables.Any(e => e > listTbl.Count())) { selected = "4"; } listTbl = listTbl.Where(item => selectedTables.Contains(item.Index)).ToList(); } else if (new string[] { "a", "all" }.Contains(selected.ToLower())) { } else { subMenu = 0; selected = "4"; continue; } PrintLine(); PrintRow("Validation summary report for selection tables"); PrintLine(); PrintRow("Table", "Checked Records", "Status", "Error Message"); PrintLine(); foreach (var tbl in listTbl) { ValidationByTable(tbl); if (tbl.Problems.Count() > 0) { for (int index = 0; index < tbl.Problems.Count(); index++) { var problem = tbl.Problems[index]; if (index == 0) { PrintRow(tbl.TableName, tbl.RecordCount.ToString(), "Failed", problem); } else { PrintRowRight("", "", "", problem); } } } else { PrintRow(tbl.TableName, tbl.RecordCount.ToString(), "Passed", ""); } PrintLine(); } } else if (subMenu == 0 && selected == "5") { PrintRow("Export Mysql database to csv file"); PrintLine(); subMenu = 5; var listTbl = DBMySQLUtils.GetListTable() .Select((item, index) => new TableDB { TableName = item.Key, RecordCount = item.Value, Index = index }).ToList(); int exportSuccessCount = 0; foreach (var tbl in listTbl) { try { DBMySQLUtils.ExportTableMetadataCsv(tbl.TableName); DBMySQLUtils.ExportTableDataCsv(tbl.TableName); PrintRow($"{tbl.TableName}", "Done"); exportSuccessCount++; } catch (Exception e) { PrintRow(tbl.TableName, e.Message); } PrintLine(); } if (exportSuccessCount > 0) { Console.WriteLine($"{exportSuccessCount} tables exported"); } else { Console.WriteLine($"Export failed."); } Console.WriteLine($"File path (MySql Server's absolute path): {DBMySQLUtils.GetExportFolder()}"); } else if (subMenu == 6) { var listTblMySql = DBMySQLUtils.GetListTable() .Select((item, index) => new TableDB { TableName = item.Key, RecordCount = item.Value, Index = index }).ToList(); var listTblSqlServer = DBSqlServerUtils.GetListTable(); PrintRow("Validation Summary Report"); PrintLine(); PrintRow("", "MySql", "Sql Server"); PrintLine(); foreach (var tbl in listTblMySql) { try { var tableDataCsvPath = Path.Combine(selected, $"{tbl.TableName}.csv"); var tableMetadataCsvPath = Path.Combine(selected, $"{tbl.TableName}_metadata.csv"); ValidDataFromCsvAndSqlServer(tableDataCsvPath, tableMetadataCsvPath, tbl); } catch (Exception e) { if (e is SqlException) { if ((e as SqlException).Number == 208) { tbl.Problems.Add("Table not exist in Sql Server"); } else if ((e as SqlException).Number == 207) { tbl.Problems.Add(e.Message); } } if (e is FileNotFoundException) { tbl.Problems.Add("Cant find csv file"); } } } PrintRow("Number of tables Checked", listTblMySql.Count().ToString() + "", listTblMySql.Count().ToString() + ""); PrintLine(); var listTblPass = listTblMySql.Where(tbl => tbl.Problems.Count() == 0); var listTblFail = listTblMySql.Where(tbl => !listTblPass.Any(item => item.TableName == tbl.TableName)); PrintRow("Table Passed", listTblPass.Count().ToString(), ""); PrintLine(); PrintRow("Table Failed", listTblFail.Count().ToString(), ""); foreach (var tblFailed in listTblFail.ToList()) { for (int index = 0; index < tblFailed.Problems.Count(); index++) { var problem = tblFailed.Problems[index]; if (index == 0) { PrintRow("", tblFailed.TableName, "- " + problem); } else { PrintRowRight("", "", "- " + problem); } } } PrintLine(); } else if (subMenu == 0 && selected == "6") { isCustomInput = true; string path = ""; Console.WriteLine("m) Menu"); Console.WriteLine("q) Quit"); while (true) { Console.Write("Enter exported csv path (Example: C:/mysql/excel): "); var input = Console.ReadLine().Trim().ToLower(); if (input == "m" || input == "q") { selected = input; subMenu = 0; break; } if (Directory.Exists(input)) { selected = input; subMenu = 6; break; } else { Console.WriteLine("Path is not found"); } } } else { subMenu = 0; selected = "m"; continue; } Console.WriteLine("m) Menu"); Console.WriteLine("q) Quit"); if (!isCustomInput) { Console.Write("\r\nEnter: "); selected = Console.ReadLine().Trim().ToLower(); } } catch (Exception) { Console.WriteLine("Input again!"); } } }