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; } }