//################################################################################################# //################################################################################################# //################################################################################################# public ActionModele insert(DataTable _dataTable, string _tableName, int _id_usr) { string test = ""; //------------------------------ ActionModele actionn = new ActionModele(); int actionId = Constantes.actionId_Create; GestLaboMach.labo.controle.Controle_Droits ctl_droits = new GestLaboMach.labo.controle.Controle_Droits(); actionn = ctl_droits.controler(_tableName, actionId); test = actionn.alert_msg; //----------------------------- if (actionn.isDroitsOk && _dataTable != null && _dataTable.Rows.Count > 0 && _dataTable.Columns.Count > 0) { // ouverture de la connexion try { con = SQLConnexion.getConnection(); con.Open(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex); } for (int ln = 0; ln < _dataTable.Rows.Count; ln++) { _dataTable.Rows[ln]["id_usr_cre"] = _id_usr; _dataTable.Rows[ln]["dt_cre"] = DateTime.Now; MySqlCommand cmd = new MySqlCommand(); string list_col = ""; string list_val = ""; for (int c = 0; c < _dataTable.Columns.Count; c++) { if (c == _dataTable.Columns.Count - 1 && !_dataTable.Columns[c].ColumnName.Trim().ToLower().Equals("id")) { list_col = list_col + " " + _dataTable.Columns[c].ColumnName + " "; list_val = list_val + "@" + _dataTable.Columns[c].ColumnName + "" + " "; } else if (!_dataTable.Columns[c].ColumnName.Trim().ToLower().Equals("id")) { list_col = list_col + " " + _dataTable.Columns[c].ColumnName + " , "; list_val = list_val + "@" + _dataTable.Columns[c].ColumnName + "" + " , "; } cmd.Parameters.AddWithValue("@" + _dataTable.Columns[c].ColumnName + "", _dataTable.Rows[ln][_dataTable.Columns[c].ColumnName + ""]); }// boucle sur les colonnes string req_insert_part_1 = @" INSERT INTO " + _tableName + " ( " + list_col + " ) "; string req_insert_part_2 = @" VALUES ( " + list_val + " ) "; string req_insert = req_insert_part_1 + " " + req_insert_part_2; try { System.Threading.Thread.Sleep(10); cmd.Connection = con; cmd.CommandText = req_insert; cmd.ExecuteNonQuery(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex, " DB.insert() :: " + req_insert); } }// boucle sur les lignes // fermeture de la connexion try { con.Close(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex); } } actionn.msg = test; return(actionn); }
//################################################################################################# //################################################################################################# //################################################################################################# /* * public string insertOrUpdate(long _ident, DataSet _daSet,string cmdSelect, string _tableName, int _id_usr) * { * string test = ""; * * * if (_daSet != null) * { * // ouverture de la connexion * try * { * con = SQLConnexion.getConnection(); * con.Open(); * * } * catch (Exception ex) * { * test = ex.Message; * labo.modele.SYS_LOG_Modele.tracerException(ex); * } * * try * { * * MySqlCommand cmd = new MySqlCommand(cmdSelect, con); * MySqlDataAdapter adapter1 = new MySqlDataAdapter(cmd); * MySqlCommandBuilder cmdbl = new MySqlCommandBuilder(adapter1); * adapter1.Update(_daSet, _tableName); * * } * catch (Exception ex) * { * * test = ex.Message; * * labo.modele.SYS_LOG_Modele.tracerException(ex ); * } * * // fermeture de la connexion * try * { * con.Close(); * } * catch (Exception ex) * { * test = ex.Message; * labo.modele.SYS_LOG_Modele.tracerException(ex); * } * * * } * * * return test; * } */ //################################################################################################# //################################################################################################# //################################################################################################# public ActionModele update(long _ident, DataTable _dataTable, string _tableName, int _id_usr) { string test = ""; ActionModele actionn = new ActionModele(); int actionId = Constantes.actionId_Update; GestLaboMach.labo.controle.Controle_Droits ctl_droits = new GestLaboMach.labo.controle.Controle_Droits(); actionn = ctl_droits.controler(_tableName, actionId); test = actionn.alert_msg; if (actionn.isDroitsOk && _dataTable != null && _dataTable.Rows.Count > 0 && _dataTable.Columns.Count > 0) { // ouverture de la connexion try { con = SQLConnexion.getConnection(); con.Open(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex); } for (int ln = 0; ln < _dataTable.Rows.Count; ln++) { _dataTable.Rows[ln]["id_usr_mod"] = _id_usr; _dataTable.Rows[ln]["dt_mod"] = DateTime.Now; MySqlCommand cmd = new MySqlCommand(); string list_val_update = ""; for (int c = 0; c < _dataTable.Columns.Count; c++) { string col = _dataTable.Columns[c].ColumnName + " "; string val = "@" + col + ""; if (c == _dataTable.Columns.Count - 1 && !_dataTable.Columns[c].ColumnName.Trim().ToLower().Equals("id")) { list_val_update = list_val_update + col + "=" + val + " "; } else if (!_dataTable.Columns[c].ColumnName.Trim().ToLower().Equals("id")) { list_val_update = list_val_update + col + "=" + val + " , "; } cmd.Parameters.AddWithValue("@" + _dataTable.Columns[c].ColumnName + "", _dataTable.Rows[ln][_dataTable.Columns[c].ColumnName + ""]); }// boucle sur les colonnes string req_update = " UPDATE " + _tableName + " SET " + list_val_update + " WHERE id=" + _ident + " "; try { System.Threading.Thread.Sleep(10); cmd.Connection = con; cmd.CommandText = req_update; //cmd.Parameters.AddWithValue("@ident", _ident); cmd.ExecuteNonQuery(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex); } }// boucle sur les lignes // fermeture de la connexion try { con.Close(); } catch (Exception ex) { test = ex.Message; labo.modele.SYS_LOG_Modele.tracerException(ex); } } actionn.msg = test; return(actionn); }