Hello to you (phil.o nad OriginalGriff),
I ended up finding the ideal code against injections, well I think !
By cons I can not solve the problem of dates ...
I have to look in the database (SQLite database with C#) for dates in yyyy-MM-dd formats.
using (var conn = new SQLiteConnection(Program.Connex_Bdd))
{
Program.Connex_Bdd.Open();
using (DataTable dt_P4_Tble_cmd = new DataTable())
{
var command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT * FROM Tble_Commande INNER JOIN Tble_Demande ON (Tble_Demande.dmd_ID = Tble_Commande.cmd_ID) " +
" WHERE Tble_Commande.Annee = @Annee" +
" AND (Reference IS NULL OR Reference LIKE @Ref)" +
" AND (Designations IS NULL OR Designations LIKE @Article)" +
" AND (date(Date_prise_compte_cmds) IS NULL OR date(Date_prise_compte_cmds) = @Date)" +
" AND (Lieu_livraison IS NULL OR Lieu_livraison LIKE @Lieu)" +
" AND (Imputation IS NULL OR Imputation LIKE @EOTP)" +
" AND (Type_cmds IS NULL OR Type_cmds LIKE @TypeCmd)";
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command.CommandText, Program.Connex_Bdd))
{
adapter.SelectCommand = command;
SQLiteParameter param1 = command.Parameters.Add("@Annee", DbType.String, 4);
param1.Value = Cbo_Choix_Annee.Text;
SQLiteParameter param2 = command.Parameters.Add("@Ref", DbType.String, 20);
param2.Value = $"%{Txt_P4_recherche_Ref.Text}%";
SQLiteParameter param3 = command.Parameters.Add("@Article", DbType.String, 50);
param3.Value = $"%{Txt_P4_recherche_Article.Text}%";
SQLiteParameter param4 = command.Parameters.Add("@Date", DbType.Date, 10);
param4.Value = Txt_P4_recherche_Date.Text;
SQLiteParameter param5 = command.Parameters.Add("@Lieu", DbType.String, 10);
param5.Value = $"%{Cbo_P4_recherche_lieu.Text}%";
SQLiteParameter param6 = command.Parameters.Add("@EOTP", DbType.String, 10);
param6.Value = $"%{Cbo_P4_recherche_Imput.Text}%";
SQLiteParameter param7 = command.Parameters.Add("@TypeCmd", DbType.String, 10);
param7.Value = $"%{Cbo_P4_recherche_TypeCmds.Text}%";
adapter.Fill(dt_P4_Tble_cmd);
}
DGV_P4_Reporting.DataSource = dt_P4_Tble_cmd;
}
}
How to process the date search (yyyy-MM-dd) in my SQLite database?
Thanks for your help.
What I have tried:
AND (date(Date_prise_compte_cmds) IS NULL OR date(Date_prise_compte_cmds) = @Date)
SQLiteParameter param4 = command.Parameters.Add("@Date", DbType.Date, 10);
param4.Value = Txt_P4_recherche_Date.Text;
For my tests I even removed the TextBox and preferred an example of date like this:
SQLiteParameter param4 = command.Parameters.Add("@Date", DbType.Date, 10);
param4.Value = "2020-02-12";
But to no avail ...