How to pass dates from datepickers as parameters and retrieve records between dates properly?

Question

Hello everyone,
I'm using two datepickers to pass first date value and second date value in order to find records between dates, in SQLite database.

XAML

 <DatePicker  
                        x:Name="FirstDate"  
                        SelectedDate="{Binding SelectedDate_1, UpdateSourceTrigger=PropertyChanged}"  
                        SelectedDateFormat="Long" />  
     <DatePicker  
                        x:Name="SecondDate"  
                        SelectedDate="{Binding SelectedDate_2, UpdateSourceTrigger=PropertyChanged}"  
                        SelectedDateFormat="Long" />  
      
      

VM

// "SelectedDate_1" property  
public DateTime? SelectedDate_1  
{  
    get { return _selectedDate_1; }  
    set { Set(ref _selectedDate_1, value); }  
}  
private DateTime? _selectedDate_1;  
 
 // "SelectedDate_2" property  
    public DateTime? SelectedDate_2  
    {  
        get { return _selectedDate_2; }  
        set { Set(ref _selectedDate_2, value); }  
    }  
    private DateTime? _selectedDate_2;  

 //Getting only date        
 if (SelectedDate_1 != null && SelectedDate_2 != null)  
        {  
            Time1 = SelectedDate_1.Value.Date.ToString("dd.MM.yyyy");  
            Time2 = SelectedDate_2.Value.Date.ToString("dd.MM.yyyy");  
        }  
        else return;  

 // an attempt to get sum between dates from particular column  
 cmd.CommandText = "SELECT SUM([" + ColumnName.Replace("]", "]]") + "]) FROM Components WHERE Date BETWEEN @Date1 AND @Date2 ";  
                cmd.Parameters.AddWithValue("@ColumnName", ColumnName);               
                cmd.Parameters.AddWithValue("@Date1", Time1);  
                cmd.Parameters.AddWithValue("@Date2", Time2);   

Generaly speaking, how to do this properly?

DB mapping looks as per picture below.

Answer 1

This is an SQL Server tag, and not a XAML or .NET tag, so don't expect the best answers from that perspective.

But we SQL Server people frown upon .AddWithValue, because it causes performance issues on the SQL Server side. Also the way, you use it, you will get a headache to date and time formats.

When you work with date and time, you very rarely use things like ToString("dd.MM.yyyy"). Let the platform interpret and format the values according to the user's regional settings. And the format you use is not good at all. Is 02.12.2020 a date in December or in February? (Yes, there are people who interpret dates that way. I know it seems funny.)

The code should presumably be:

cmd.Parameters.Add("@date1", SqlDbType.Date).Value = SelectedDate_1.Value;

I say presumably, because being an SQL Server guy I don't know that much about date pickers. I know about writing data-access code, though.

Answer 2

@Erland Sommarskog ,

seems I'm not half bad with WPF,MVVM, however I don't know exactly what do I need to pass towards DB to get reliable search between dates, for example.
The code above mostly performs search normally, however sometimes it returns nothing. Still can't catch a reason.
I relate it either with faulty passed date format or faulty DB mapping or both.

I will test your suggestions. Thanks!

Update:
I have same fail.