1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
| /// <summary>
/// Recherche multi-critère dans la BDD Access en fonction des valeurs du formulaire
/// </summary>
private void Search(ScriptsInterface c2d)
{
//SpiLog.LogMessage(SpiMessages.InfoScript, "DEBUT Search()" , "Script_externe C#", "SpiScript.Main()"); //AVIRER
DataControl lw = c2d.GetDataControl("LW_Result");
lw.DeleteRows(); //RAZ
//Acquisition des critères de recherche à partir des champs du formullaire
String sNom = c2d.ControlGetValue("Nom" );
String sPrenom = c2d.ControlGetValue("PRENOM" );
String sNumSS = c2d.ControlGetValue("NUMSS" );
String sMatricule = c2d.ControlGetValue("Matricule" );
//String sCivilite = c2d.ControlGetValue("CIVILITE" );
MessageBox.Show("Here Search Acquisition");
//SpiLog.LogMessage(SpiMessages.InfoScript, "Search() Nom"+sNom+", Prenom="+sPrenom+", NumSS="+sNumSS+", Matricule="+sMatricule+", "Script_externe C#", "SpiScript.Main()"); //AVIRER
//Recherche dans la Feuille ACCESS
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +PATH_EXCEL_DATABASE;
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
int iNbr = 0;
using (DbConnection connection = factory.CreateConnection())
{
connection.connectionString = connectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT Numero_SS, Matricule, Civilite, Nom_marital, Prenom, Nom_patronymique FROM [BG-SG-COMPLET-Avec matricule$]";
MessageBox.Show("Here Search Selection");
string sWhere = "";
if( !String.IsNullOrEmpty(sNom) )
{
if( !String.IsNullOrEmpty(sWhere) ) sWhere += " AND ";
sWhere += " ( [BG-SG-COMPLET-Avec matricule$].Nom_patronymique = '" + sNom + "' OR [BG-SG-COMPLET-Avec matricule$].Nom_marital = '" + sNom + "' )";
MessageBox.Show("Here sWhereNom"+sWhere);
}
if( !String.IsNullOrEmpty(sPrenom) )
{
if( !String.IsNullOrEmpty(sWhere) ) sWhere += " AND ";
sWhere += " [BG-SG-COMPLET-Avec matricule$].Prenom LIKE '%"+sPrenom+"%' ";
MessageBox.Show("Here sWherePre"+sWhere);
}
if( !String.IsNullOrEmpty(sNumSS) )
{
if( !String.IsNullOrEmpty(sWhere) ) sWhere += " AND ";
sWhere += " [BG-SG-COMPLET-Avec matricule$].Numero_SS LIKE '"+sNumSS+"%' ";
MessageBox.Show("Here sWhereNum"+sWhere);
}
if( !String.IsNullOrEmpty(sMatricule) )
{
if( !String.IsNullOrEmpty(sWhere) ) sWhere += " AND ";
sWhere += " [BG-SG-COMPLET-Avec matricule$].Matricule LIKE '%"+sMatricule+"' ";
MessageBox.Show("Here sWhereMat"+sWhere);
}
//ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$] WHERE [Feuil1$].NOM LIKE '%DUPONT%' ")
if( !String.IsNullOrEmpty(sWhere) )
command.CommandText += " WHERE " + sWhere ;
c2d.ShowInfoMessage(String.Format("REQUETE" + sNom + ","+ sPrenom + ","+ sNumSS + ","+ sMatricule));
connection.Open();
MessageBox.Show("Here Search Connection");
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{ //Ajoute les éléments trouvés dans la ListView
string[] arRows = { dr["Civilite"].ToString(), dr["Nom_patronymique"].ToString(), dr["Prenom"].ToString(), FormatNumSS( dr["Numero_SS"].ToString() ), dr["Matricule"].ToString() } ;
lw.AddRow( dr["Matricule"].ToString(), arRows );
iNbr++;
MessageBox.Show("Here Search Ajout");
}
}
}
}
if( iNbr < 1 )
{ //Rien trouvé
SpiMisc.PlaySound(@"C:\windows\media\Ding.wav");
}
}//Search() |
Partager