예제 #1
0
 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;
 }
예제 #2
0
        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;
        }
예제 #4
0
        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.");
        }
예제 #5
0
        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.");
        }
예제 #6
0
        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.");
        }
예제 #7
0
        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.");
        }
예제 #8
0
 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.");
 }