public ICommand Parse(string sqlCommandText, string placeholderToken)
{
var commandText = sqlCommandText;
var commandBuilder = new CommandBuilder();
var options = new CommandBuilderOptions();
options.PlaceholderPrefix = placeholderToken;
var builder = commandBuilder.GetCommand(commandText, options);
return builder;
}
public void Experiment_With_Joins()
{
var logBuilder = new StringBuilder();
string joinType = "INNER";
var sql = string.Format("Select C.contactid, C.firstname, C.lastname From contact AS C {0} JOIN customeraddress AS A on C.contactid = A.contactid", joinType);
var commandText = sql;
var commandBuilder = new CommandBuilder();
var builder = commandBuilder.GetCommand(commandText) as SelectBuilder;
LogUtils.LogCommand(builder, logBuilder);
logBuilder.AppendLine();
var nestedJoinsSql =
string.Format(
"Select contactid, firstname, lastname From contact INNER JOIN customeraddress on contact.id = customeraddress.contactid INNER JOIN occupant on customeraddress.addressid = occupant.addressid ",
joinType);
builder = commandBuilder.GetCommand(nestedJoinsSql) as SelectBuilder;
LogUtils.LogCommand(builder, logBuilder);
logBuilder.AppendLine();
var anotherJoinSql =
string.Format(
"Select contactid, firstname, lastname From contact INNER JOIN customeraddress on contact.id = customeraddress.contactid INNER JOIN occupant on contact.contactid = occupant.contactid ",
joinType);
builder = commandBuilder.GetCommand(anotherJoinSql) as SelectBuilder;
LogUtils.LogCommand(builder, logBuilder);
logBuilder.AppendLine();
var moreSql =
string.Format(
"Select C.contactid, C.firstname, C.lastname, O.fullname From contact C INNER JOIN customeraddress A on c.id = A.contactid LEFT JOIN occupant O on C.contactid = O.contactid ",
joinType);
builder = commandBuilder.GetCommand(moreSql) as SelectBuilder;
LogUtils.LogCommand(builder, logBuilder);
logBuilder.AppendLine();
Console.Write(logBuilder.ToString());
}
private ICrmOperation GetOperationFromTextCommand(CrmDbCommand command, CommandBehavior behavior)
{
// We actually need to parse the SQL, and then build the appropriate organisation request.
var commandText = command.CommandText;
// Use SQLGeneration to parse the SQL command into a Visitable Builder.
var commandBuilder = new CommandBuilder();
var options = new CommandBuilderOptions();
options.PlaceholderPrefix = ParameterToken;
var sqlCommandBuilder = commandBuilder.GetCommand(commandText, options);
// Visit the builder with out custom visiter that will build the appropriate org request whilst visiting.
var visitor = GetVisitor(command);
if (visitor == null)
{
throw new InvalidOperationException("visitor was null");
}
sqlCommandBuilder.Accept(visitor);
// The visitor should now have vuild the OrgCommand that we need.
var orgCommand = visitor.CrmOperation;
if (orgCommand == null || orgCommand.Request == null)
{
throw new NotSupportedException("Could not translate the command into the appropriate Organization Service Request Message");
}
// Before returning the command, ensure some additional properties are set.
orgCommand.DbCommand = command;
orgCommand.CommandBehavior = behavior;
return orgCommand;
}
public void TestSelect_Newlines()
{
string commandText =
@"SELECT
*
FROM Table1
INNER JOIN Table2 ON Table1.Column = Table2.Column
WHERE Column3 = '123'";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Column = Table2.Column WHERE Column3 = '123'";
Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected.");
}
public void TestSelect_ExtraWhitespace()
{
string commandText = " SELECT * FROM Customer ";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT * FROM Customer";
Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected.");
}
public void TestSelect_ComplexCommand()
{
string commandText =
@"SELECT
r.RouteId,
r.RouteNumber,
o.CustomerId,
o.CustomerKey AS [Outlet#],
o.Name AS CustomerName,
vm.VendingMachineId,
vm.AssetNumber AS [Equipment#],
m.ModelType AS Model,
rc.FillFrequency,
rc.EffectiveDate AS SettlementDate,
p.ProductLookupId,
p.ProductSKU AS ProductCode,
rcvc.FillLevel AS ProductCapacity,
st.QuantityDelivered AS FillUnits
FROM Company b
INNER JOIN Route r ON b.CompanyId = r.CompanyId
INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId
INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId
INNER JOIN
(
SELECT
rc.RouteCardId,
rcvc.ProductLookupId,
SUM(rcvc.FillLevel) AS FillLevel
FROM RouteSchedule rs
INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId
INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId
WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate
GROUP BY rc.RouteCardId, rcvc.ProductLookupId
) as rcvc ON rc.RouteCardId = rcvc.RouteCardId
INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId
INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId
INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId
INNER JOIN Customer o ON vm.CustomerId = o.CustomerId
INNER JOIN ServiceTransaction svc ON
(rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate)
INNER JOIN SettlementTransactionSKU st ON
(svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId)
WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3)
ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
select.AddWhere(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1)));
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT"
+ " r.RouteId,"
+ " r.RouteNumber,"
+ " o.CustomerId,"
+ " o.CustomerKey AS [Outlet#],"
+ " o.Name AS CustomerName,"
+ " vm.VendingMachineId,"
+ " vm.AssetNumber AS [Equipment#],"
+ " m.ModelType AS Model,"
+ " rc.FillFrequency,"
+ " rc.EffectiveDate AS SettlementDate,"
+ " p.ProductLookupId,"
+ " p.ProductSKU AS ProductCode,"
+ " rcvc.FillLevel AS ProductCapacity,"
+ " st.QuantityDelivered AS FillUnits"
+ " FROM Company b"
+ " INNER JOIN Route r ON b.CompanyId = r.CompanyId"
+ " INNER JOIN RouteSchedule rs ON r.RouteId = rs.RouteId"
+ " INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId"
+ " INNER JOIN (SELECT rc.RouteCardId, rcvc.ProductLookupId, SUM(rcvc.FillLevel) AS FillLevel FROM RouteSchedule rs INNER JOIN RouteCard rc ON rs.RouteScheduleId = rc.RouteScheduleId INNER JOIN RouteCardVendColumn rcvc ON rc.RouteCardId = rcvc.RouteCardId WHERE rs.RouteId IN (1, 2, 3) AND rc.EffectiveDate BETWEEN @startDate AND @stopDate GROUP BY rc.RouteCardId, rcvc.ProductLookupId) rcvc ON rc.RouteCardId = rcvc.RouteCardId"
+ " INNER JOIN ProductLookup p ON rcvc.ProductLookupId = p.ProductLookupId"
+ " INNER JOIN VendingMachine vm ON rc.VendingMachineId = vm.VendingMachineId"
+ " INNER JOIN MachineTypeLookup m ON vm.MachineTypeLookupId = m.MachineTypeLookupId"
+ " INNER JOIN Customer o ON vm.CustomerId = o.CustomerId"
+ " INNER JOIN ServiceTransaction svc ON (rc.VendingMachineId = svc.VendingMachineId AND rc.EffectiveDate = svc.ServiceTransactionDate)"
+ " INNER JOIN SettlementTransactionSKU st ON (svc.ServiceTransactionId = st.ServiceTransactionId AND p.ProductLookupId = st.ProductLookupId)"
+ " WHERE rc.EffectiveDate BETWEEN @startDate AND @endDate AND r.RouteId IN (1, 2, 3) AND 1 = 1"
+ " ORDER BY b.CompanyId, r.RouteId, vm.VendingMachineId, p.ProductLookupId, rc.EffectiveDate DESC";
Assert.AreEqual(expected, actual, "The SELECT statement was not reproduced as expected.");
}
public void TestSelect_AddFilter()
{
const string commandText = "SELECT * FROM Customer";
CommandBuilder commandBuilder = new CommandBuilder();
SelectBuilder select = (SelectBuilder)commandBuilder.GetCommand(commandText);
Column customerId = select.Sources["Customer"].Column("CustomerId");
customerId.Qualify = false;
Placeholder parameter = new Placeholder("@customerId");
select.AddWhere(new EqualToFilter(customerId, parameter));
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(select);
string expected = "SELECT * FROM Customer WHERE CustomerId = @customerId";
Assert.AreEqual(expected, actual, "The SELECT statement was not updated as expected.");
}
private void assertCanReproduce(string commandText, CommandBuilderOptions options = null)
{
CommandBuilder builder = new CommandBuilder();
ICommand command = builder.GetCommand(commandText, options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(command);
Assert.AreEqual(commandText, actual, "The command builder did not generate the original command text.");
}