//#################################################################################################
//#################################################################################################
//#################################################################################################
public ActionModele insert(DataTable _dataTable, string _tableName)
{
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"] = GestLaboGlobal.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)
{
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"] = GestLaboGlobal.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);
}
