private string GetFilters(SqlCommand cmd)
{
var filters = string.Empty;
if (FilterOnOrderDateRangeBegin)
{
AddFilter(ref filters, "OrderDate >= @OrderDateRangeBegin");
cmd.Parameters.Add("@OrderDateRangeBegin", SqlDbType.DateTime2).Value = OrderDateRangeBegin;
}
if (FilterOnOrderDateRangeEnd)
{
AddFilter(ref filters, "OrderDate < @OrderDateRangeEnd");
cmd.Parameters.Add("@OrderDateRangeEnd", SqlDbType.DateTime2).Value = OrderDateRangeEnd;
}
if (true)
{
AddFilter(ref filters, "OnlineOrderFlag = @OnlineOrderFlag");
cmd.Parameters.Add("@OnlineOrderFlag", SqlDbType.Bit).Value = OnlineOrderFlag;
}
if (FilterOnCustomerId &&
int.TryParse(CustomerId, out var customerId))
{
AddFilter(ref filters, "CustomerID = @CustomerID");
cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
}
if (!string.IsNullOrWhiteSpace(LegacyTerritoryViewModel.GenerateSql()))
{
AddFilter(ref filters, LegacyTerritoryViewModel.GenerateSql());
}
return(filters);
}
protected override void Search()
{
using (var connection = new SqlConnection(MainWindowViewModel.GetSqlConnectionString()))
{
connection.Open();
var cmd = new SqlCommand
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "Sales.GetOrdersDynamic"
};
cmd.Parameters.Add("@OrderDateRangeBegin", SqlDbType.DateTime2, 7).Value = FilterOnOrderDateRangeBegin ? OrderDateRangeBegin : (object)DBNull.Value;
cmd.Parameters.Add("@OrderDateRangeEnd", SqlDbType.DateTime2, 7).Value = FilterOnOrderDateRangeEnd ? OrderDateRangeEnd : (object)DBNull.Value;
cmd.Parameters.Add("@OnlineOrderFlag", SqlDbType.Bit).Value = OnlineOrderFlag;
cmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = FilterOnCustomerId && int.TryParse(CustomerId, out var customerId) ? customerId : (object)DBNull.Value;
cmd.Parameters.Add("@WildWestSql", SqlDbType.NVarChar).Value = string.IsNullOrWhiteSpace(LegacyTerritoryViewModel.GenerateSql()) ? (object)DBNull.Value : LegacyTerritoryViewModel.GenerateSql();
var sda = new SqlDataAdapter(cmd);
var dt = new DataTable();
sda.Fill(dt);
Data = dt;
}
}