Exemplo n.º 1
0
        static void Main(string[] args)
        {
            // get an adapter
            SQAdapter adp = !string.IsNullOrEmpty(Settings.Default.SQLServerConn) ? (SQAdapter) new SQLServerAdapter(Settings.Default.SQLServerConn)
                : !string.IsNullOrEmpty(Settings.Default.MySQLConn) ? (SQAdapter) new MySQLAdapter(Settings.Default.MySQLConn)
                : null;

            if (adp == null)
            {
                return;
            }


            // Create Table 1
            // -------------------------------------------------------
            Console.WriteLine("Creating table:  FamousQuote");
            Console.WriteLine("ID               Int64");
            Console.WriteLine("FamousPersonID   Int64");
            Console.WriteLine("Quote            String(500)");
            SQTable quote = new SQTable()
            {
                Name    = "FamousQuote",
                Columns = new SQColumnList()
                {
                    new SQColumn()
                    {
                        Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
                    },
                    new SQColumn()
                    {
                        Name = "FamousPersonID", DataType = SQDataTypes.Int64, Nullable = false
                    },
                    new SQColumn()
                    {
                        Name = "Quote", DataType = SQDataTypes.String, Length = 500
                    }
                }
            };

            // delete table if it already exists
            if (adp.GetTable(quote.Name) != null)
            {
                Console.WriteLine("Table '" + quote.Name + "' exists. Deleting...");
                adp.RemoveTable(quote.Name);
            }

            adp.CreateTable(quote);
            Console.WriteLine("Table Created. ");



            // Create Table 2
            // -------------------------------------------------------
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Creating table:  FamousPerson");
            Console.WriteLine("ID               Int64");
            Console.WriteLine("FirstName        String(50)");
            Console.WriteLine("LastName         String(50)");


            SQTable person = new SQTable()
            {
                Name    = "FamousPerson",
                Columns = new SQColumnList()
                {
                    new SQColumn()
                    {
                        Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
                    },
                    new SQColumn()
                    {
                        Name = "FirstName", DataType = SQDataTypes.String, Length = 50
                    },
                    new SQColumn()
                    {
                        Name = "LastName", DataType = SQDataTypes.String, Length = 50
                    }
                }
            };

            // delete table if it already exists
            if (adp.GetTable(person.Name) != null)
            {
                Console.WriteLine("Table '" + person.Name + "' exists. Deleting...");
                adp.RemoveTable(person.Name);
            }

            adp.CreateTable(person);
            Console.WriteLine("Table Created. ");



            // add a foreign key
            // -------------------------------------------------------
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Adding a foreign key.");
            Console.WriteLine("FamousQuote.FamousPersonID -> FamousPerson.ID");


            adp.AddForeignKey(quote.GetColumnByName("FamousPersonID"), person.GetColumnByName("ID"));



            // make a couple of insert query objects
            // -------------------------------------------------------
            string        varFirstName = adp.CreateVariable("FirstName");
            string        varLastName  = adp.CreateVariable("LastName");
            SQInsertQuery personInsert = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(person.Name),
                ReturnID = true,
                SetPairs = new List <SQSetQueryPair>()
                {
                    new SQSetQueryPair("FirstName", varFirstName),
                    new SQSetQueryPair("LastName", varLastName),
                }
            };

            string        varPersonID = adp.CreateVariable("PersonID");
            string        varQuote    = adp.CreateVariable("Quote");
            SQInsertQuery quoteInsert = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(quote.Name),
                ReturnID = false,
                SetPairs = new List <SQSetQueryPair>()
                {
                    new SQSetQueryPair("FamousPersonID", varPersonID),
                    new SQSetQueryPair("Quote", varQuote),
                }
            };



            // add some data using insert objects
            // -------------------------------------------------------
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Inserting Data");
            Console.WriteLine("Inserting Person. ");


            personInsert.Parameters = new List <SQParameter>()
            {
                new SQParameter(varFirstName, "I"),
                new SQParameter(varLastName, "Asimov")
            };
            Int64 id = personInsert.ExecuteReturnID <Int64>(adp);

            Console.WriteLine("Inserting Quote. ");
            quoteInsert.Parameters = new List <SQParameter>()
            {
                new SQParameter(varPersonID, id),
                new SQParameter(varQuote, "Never let your sense of morals get in the way of doing what's right. ")
            };
            quoteInsert.Execute(adp);

            Console.WriteLine("Inserting Quote. ");
            quoteInsert.Parameters = new List <SQParameter>()
            {
                new SQParameter(varPersonID, id),
                new SQParameter(varQuote, "I do not fear computers. I fear the lack of them. ")
            };
            quoteInsert.Execute(adp);



            // update data
            // -------------------------------------------------------
            new SQUpdateQuery()
            {
                UpdateTable = new SQAliasableObject(person.Name),
                SetPairs    = new List <SQSetQueryPair>()
                {
                    new SQSetQueryPair("FirstName", varFirstName)
                },
                Condition  = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
                Parameters = new List <SQParameter>()
                {
                    new SQParameter(varFirstName, "Isaac"),
                    new SQParameter(varLastName, "Asimov")
                }
            }.Execute(adp);



            // Select data
            // -------------------------------------------------------
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Selecting quotes by Asimov. ");
            SQSelectQuery select = new SQSelectQuery()
            {
                From = new SQFromClause(new SQFromTable(quote.Name, "q")
                {
                    Join = new SQJoin(person.Name, "p")
                    {
                        JoinType  = SQJoinTypes.Inner,
                        Predicate = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
                    }
                }),
                Columns = new List <SQAliasableObject>
                {
                    new SQAliasableObject("q.Quote")
                },
                Condition  = new SQCondition("p.LastName", SQRelationOperators.Like, varLastName),
                Parameters = new List <SQParameter>
                {
                    new SQParameter(varLastName, "Asimov")
                }
            };

            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("SQL: ");
            Console.Write(select.Write(adp));
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Result: ");


            // get datatable instead of reader. this closes the reader
            // automatically
            foreach (DataRow row in select.Execute(adp).GetDataTable().Rows)
            {
                Console.WriteLine(row[0]);
            }



            // Select data joinless
            // -------------------------------------------------------
            Console.WriteLine();
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Selecting quotes by Asimov. Joinless. ");
            select = new SQSelectQuery()
            {
                From = new SQFromClause(
                    new SQFromTable(quote.Name, "q"),
                    new SQFromTable(person.Name, "p")
                    ),
                Columns = new List <SQAliasableObject>
                {
                    new SQAliasableObject("q.Quote")
                },
                Condition = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
                            .And("p.LastName", SQRelationOperators.Like, varLastName),
                Parameters = new List <SQParameter>
                {
                    new SQParameter(varLastName, "Asimov")
                }
            };
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("SQL: ");
            Console.Write(select.Write(adp));
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Result: ");


            SQSelectResult res = select.Execute(adp);

            while (res.Reader.Read())
            {
                Console.WriteLine(res.Reader.GetValue(0));
            }
            res.Close();



            // Delete data
            // -------------------------------------------------------
            new SQDeleteQuery()
            {
                DeleteTable = new SQAliasableObject(quote.Name),
                Join        = new SQJoin(person.Name)
                {
                    JoinType  = SQJoinTypes.Inner,
                    Predicate = new SQCondition("FamousPersonID", SQRelationOperators.Equal, "FamousPerson.ID")
                },
                Condition  = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
                Parameters = new List <SQParameter>
                {
                    new SQParameter(varLastName, "Asimov")
                }
            }.Execute(adp);



            // Remove created tables
            // -------------------------------------------------------
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Removing Table '" + quote.Name + "'");
            adp.RemoveTable(quote.Name);
            Console.WriteLine("Removing Table '" + person.Name + "'");
            adp.RemoveTable(person.Name);
            Console.WriteLine("-------------------------------------------------------");
            Console.WriteLine("Press any key to exit.");

            Console.ReadKey();
        }
Exemplo n.º 2
0
        static void TestAdapter(SQAdapter adp, List <string[]> data)
        {
            SQTable tMake, tModel, tBodyType, tTrans, tVehicle;

            #region Create Tables
            SQTable[] tables = new SQTable[]
            {
                tMake = new SQTable()
                {
                    Name    = "Make",
                    Columns = new SQColumnList()
                    {
                        new SQColumn()
                        {
                            Name = "MK_ID", DataType = SQDataTypes.Int64, IsPrimary = true
                        },
                        new SQColumn()
                        {
                            Name = "MK_Name", DataType = SQDataTypes.String, Length = 250
                        }
                    }
                },
                tModel = new SQTable()
                {
                    Name    = "Model",
                    Columns = new SQColumnList()
                    {
                        new SQColumn()
                        {
                            Name = "MD_ID", DataType = SQDataTypes.Int64, IsPrimary = true
                        },
                        new SQColumn()
                        {
                            Name = "MD_MKID", DataType = SQDataTypes.Int64
                        },
                        new SQColumn()
                        {
                            Name = "MD_Name", DataType = SQDataTypes.String, Length = 250
                        }
                    }
                },
                tBodyType = new SQTable()
                {
                    Name    = "BodyType",
                    Columns = new SQColumnList()
                    {
                        new SQColumn()
                        {
                            Name = "BT_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
                        },
                        new SQColumn()
                        {
                            Name = "BT_Name", DataType = SQDataTypes.String, Length = 250
                        },
                        new SQColumn()
                        {
                            Name = "BT_Dummy", DataType = SQDataTypes.String, Length = 36
                        }
                    }
                },
                tTrans = new SQTable()
                {
                    Name    = "TransmissionType",
                    Columns = new SQColumnList()
                    {
                        new SQColumn()
                        {
                            Name = "TR_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
                        },
                        new SQColumn()
                        {
                            Name = "TR_Name", DataType = SQDataTypes.String, Length = 250
                        }
                    }
                },
                tVehicle = new SQTable()
                {
                    Name    = "TestedVehicle",
                    Columns = new SQColumnList()
                    {
                        new SQColumn()
                        {
                            Name = "TV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
                        },
                        new SQColumn()
                        {
                            Name = "TV_VIN", DataType = SQDataTypes.String, Length = 25
                        },
                        new SQColumn()
                        {
                            Name = "TV_MDID", DataType = SQDataTypes.Int64
                        },
                        new SQColumn()
                        {
                            Name = "TV_BTID", DataType = SQDataTypes.String, Length = 20
                        },
                        new SQColumn()
                        {
                            Name = "TV_TRID", DataType = SQDataTypes.String, Length = 20
                        },
                        new SQColumn()
                        {
                            Name = "TV_Year", DataType = SQDataTypes.Int32
                        }
                    }
                }
            };

            Log.Info("------------------------------------------------------");
            Log.Info("Creating Tables");
            foreach (SQTable t in tables.Reverse <SQTable>())
            {
                if (adp.GetTable(t.Name) != null)
                {
                    Log.Info("Table '" + t.Name + "' exists. Deleting...");
                    Log.Info("SQL: " + adp.WriteRemoveTable(t.Name));
                    adp.RemoveTable(t.Name);
                }
                Log.Info("Creating table '" + t.Name + "'");
                Log.Info("SQL: " + adp.WriteCreateTable(t));
                adp.CreateTable(t);
            }
            #endregion


            #region Add Foreign Keys
            Log.Info("------------------------------------------------------");
            Log.Info("Adding Foreign Keys");
            Log.Info("Creating Foreign Key Model -> Make");
            adp.AddForeignKey(tModel.GetColumnByName("MD_MKID"), tMake.GetColumnByName("MK_ID"));
            Log.Info("Creating Foreign Key Vehicle -> Model");
            adp.AddForeignKey(tVehicle.GetColumnByName("TV_MDID"), tModel.GetColumnByName("MD_ID"));
            Log.Info("Creating Foreign Key Vehicle -> Body");
            adp.AddForeignKey(tVehicle.GetColumnByName("TV_BTID"), tBodyType.GetColumnByName("BT_ID"));
            Log.Info("Creating Foreign Key Vehicle -> Transmission");
            adp.AddForeignKey(tVehicle.GetColumnByName("TV_TRID"), tTrans.GetColumnByName("TR_ID"));
            #endregion


            #region Import Data
            Log.Info("------------------------------------------------------");
            Log.Info("Importing Data");
            List <string> makeIDs  = new List <string>();
            List <string> modelIDs = new List <string>();
            List <string> bodyIDs  = new List <string>();
            List <string> transIDs = new List <string>();

            string varMakeID  = adp.CreateVariable("MakeID");
            string varMake    = adp.CreateVariable("Make");
            string varModelID = adp.CreateVariable("ModelID");
            string varModel   = adp.CreateVariable("Model");
            string varTransID = adp.CreateVariable("TransID");
            string varTrans   = adp.CreateVariable("Trans");
            string varBodyID  = adp.CreateVariable("BodyID");
            string varBody    = adp.CreateVariable("Body");
            string varVIN     = adp.CreateVariable("VIN");
            string varYear    = adp.CreateVariable("Year");
            string varDummy   = adp.CreateVariable("Dummy");

            SQInsertQuery iMake = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(tMake.Name),
                SetPairs = new List <SQSetQueryPair>
                {
                    new SQSetQueryPair("MK_ID", varMakeID),
                    new SQSetQueryPair("MK_Name", varMake)
                },
                Parameters = new List <SQParameter> {
                    new SQParameter(varMakeID, ""), new SQParameter(varMake, "")
                }
            };

            SQInsertQuery iModel = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(tModel.Name),
                SetPairs = new List <SQSetQueryPair>
                {
                    new SQSetQueryPair("MD_ID", varModelID),
                    new SQSetQueryPair("MD_Name", varModel),
                    new SQSetQueryPair("MD_MKID", varMakeID)
                },
                Parameters = new List <SQParameter> {
                    new SQParameter(varModelID, ""), new SQParameter(varModel, ""), new SQParameter(varMakeID, "")
                }
            };

            SQInsertQuery iBodyType = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(tBodyType.Name),
                SetPairs = new List <SQSetQueryPair>
                {
                    new SQSetQueryPair("BT_ID", varBodyID),
                    new SQSetQueryPair("BT_Name", varBody),
                    new SQSetQueryPair("BT_Dummy", varDummy)
                },
                Parameters = new List <SQParameter> {
                    new SQParameter(varBodyID, ""), new SQParameter(varBody, ""), new SQParameter(varDummy, "")
                }
            };

            SQInsertQuery iTrans = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(tTrans.Name),
                SetPairs = new List <SQSetQueryPair>
                {
                    new SQSetQueryPair("TR_ID", varTransID),
                    new SQSetQueryPair("TR_Name", varTrans)
                },
                Parameters = new List <SQParameter> {
                    new SQParameter(varTransID, ""), new SQParameter(varTrans, "")
                }
            };

            SQInsertQuery iVehicle = new SQInsertQuery()
            {
                Table    = new SQAliasableObject(tVehicle.Name),
                SetPairs = new List <SQSetQueryPair>
                {
                    new SQSetQueryPair("TV_VIN", varVIN),
                    new SQSetQueryPair("TV_MDID", varModelID),
                    new SQSetQueryPair("TV_BTID", varBodyID),
                    new SQSetQueryPair("TV_TRID", varTransID),
                    new SQSetQueryPair("TV_Year", varYear)
                },
                Parameters = new List <SQParameter> {
                    new SQParameter(varVIN, ""),
                    new SQParameter(varModelID, ""),
                    new SQParameter(varBodyID, ""),
                    new SQParameter(varTransID, ""),
                    new SQParameter(varYear, "")
                }
            };



            Log.Info("------------------------------------------------------");
            Log.Info("Testing Inserts...");
            Log.Info("Make SQL: " + iMake.Write(adp));
            Log.Info("Model SQL: " + iModel.Write(adp));
            Log.Info("BodyType SQL: " + iBodyType.Write(adp));
            Log.Info("Transmission SQL: " + iTrans.Write(adp));
            Log.Info("TestedVehicle SQL: " + iVehicle.Write(adp));
            int           vehicleCount = 0;
            SQTransaction trn          = adp.OpenTransaction();
            foreach (string[] dataline in data)
            {
                string makeID = dataline[2];
                string make   = dataline[3];
                // model id's are unique with respect to the make
                string modelID = makeID + dataline[4];
                string model   = dataline[5];
                string transID = dataline[14];
                string trans   = dataline[15];
                string bodyID  = dataline[8];
                string body    = dataline[9];
                string VIN     = dataline[10];
                string year    = dataline[6];

                int numYear;
                // if there's no year, it's not a valid vehicle. skip this line
                if (!int.TryParse(year, out numYear) || numYear <= 0)
                {
                    continue;
                }

                long numMakeID  = Convert.ToInt64(makeID.TrimStart('0'));
                long numModelID = Convert.ToInt64(modelID.TrimStart('0'));

                if (!makeIDs.Contains(makeID))
                {
                    makeIDs.Add(makeID);
                    iMake.Parameters[0].Value = numMakeID;
                    iMake.Parameters[1].Value = make;
                    iMake.Execute(trn);
                }

                if (!modelIDs.Contains(modelID))
                {
                    modelIDs.Add(modelID);
                    iModel.Parameters[0].Value = numModelID;
                    iModel.Parameters[1].Value = model;
                    iModel.Parameters[2].Value = numMakeID;
                    iModel.Execute(trn);
                }

                if (!transIDs.Contains(transID))
                {
                    transIDs.Add(transID);
                    iTrans.Parameters[0].Value = transID;
                    iTrans.Parameters[1].Value = trans;
                    iTrans.Execute(trn);
                }

                if (!bodyIDs.Contains(bodyID))
                {
                    bodyIDs.Add(bodyID);
                    iBodyType.Parameters[0].Value = bodyID;
                    iBodyType.Parameters[1].Value = body;
                    iBodyType.Parameters[2].Value = Guid.NewGuid().ToString();
                    iBodyType.Execute(trn);
                }

                iVehicle.Parameters[0].Value = VIN;
                iVehicle.Parameters[1].Value = numModelID;
                iVehicle.Parameters[2].Value = bodyID;
                iVehicle.Parameters[3].Value = transID;
                iVehicle.Parameters[4].Value = numYear;
                long id = iVehicle.ExecuteReturnID <Int64>(trn);
                vehicleCount++;

                if (vehicleCount > 0 && vehicleCount % 500 == 0)
                {
                    Log.Info("Records Inserted: " + (vehicleCount + bodyIDs.Count + transIDs.Count + modelIDs.Count + makeIDs.Count));
                }
            }
            trn.Commit();
            Log.Info("------------------------------------------------------");
            Log.Info(string.Format(@"Insert Test Complete. 
    Makes: {0}; 
    Models: {1}; 
    BodyTypes: {2}; 
    TransmissionTypes: {3}; 
    TestedVehicle: {4}; ", makeIDs.Count, modelIDs.Count, bodyIDs.Count, transIDs.Count, vehicleCount));
            #endregion


            Log.Info("------------------------------------------------------");
            Log.Info("Select Test");

            SQSelectQuery q = new SQSelectQuery()
            {
                From = new SQFromClause(
                    new SQFromTable(tVehicle.Name),
                    new SQFromTable(tModel.Name),
                    new SQFromTable(tTrans.Name),
                    new SQFromTable(tBodyType.Name),
                    new SQFromTable(tMake.Name)
                    ),
                Columns = new List <SQAliasableObject> {
                    new SQAliasableObject("Model.*, make.*")
                },
                Condition = new SQConditionGroup(
                    new SQCondition("MD_ID", SQRelationOperators.Equal, "TV_MDID")
                    .And("BT_ID", SQRelationOperators.Equal, "TV_BTID")
                    .And("MD_MKID", SQRelationOperators.Equal, "MK_ID")
                    .And("TV_TRID", SQRelationOperators.Equal, "TR_ID"))
            };

            Log.Info("Selecting with joinless joins types");
            Log.Info("SQL: " + q.Write(adp));


            DataTable      dt  = new DataTable();
            SQSelectResult res = q.Execute(adp);

            int count = 0;
            while (res.Reader.Read())
            {
                count++;
            }
            res.Close();

            Log.Info("Rows Found: " + count);



            Log.Info("------------------------------------------------------");
            Log.Info("Rename Column");
            Log.Info("Rename BT_Dummy to BT_DummyOK");
            SQColumn dummyColumn = tBodyType.GetColumnByName("BT_Dummy");
            string   oldname     = dummyColumn.Name;
            dummyColumn.Name = "BT_DummyOK";
            Log.Info("SQL: " + adp.WriteRenameColumn(dummyColumn, oldname));
            adp.RenameColumn(dummyColumn, oldname);



            Log.Info("------------------------------------------------------");
            Log.Info("Remove Column");
            Log.Info("Removing column BT_DummyOK");
            Log.Info("SQL: " + adp.WriteRemoveColumn(dummyColumn));
            adp.RemoveColumn(dummyColumn);



            Log.Info("------------------------------------------------------");
            Log.Info("Insert From Test");
            SQTable tVehicleList = new SQTable()
            {
                Name    = "SimpleVehicle",
                Columns = new SQColumnList()
                {
                    new SQColumn()
                    {
                        Name = "SV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
                    },
                    new SQColumn()
                    {
                        Name = "SV_VIN", DataType = SQDataTypes.String, Length = 25
                    },
                    new SQColumn()
                    {
                        Name = "SV_Year", DataType = SQDataTypes.Int32
                    },
                    new SQColumn()
                    {
                        Name = "SV_Make", DataType = SQDataTypes.String, Length = 250
                    },
                    new SQColumn()
                    {
                        Name = "SV_Model", DataType = SQDataTypes.String, Length = 250
                    }
                }
            };
            Log.Info("Create new table");
            if (adp.GetTable(tVehicleList.Name) != null)
            {
                Log.Info("Table '" + tVehicleList.Name + "' exists. Deleting...");
                Log.Info("SQL: " + adp.WriteRemoveTable(tVehicleList.Name));
                adp.RemoveTable(tVehicleList.Name);
            }
            Log.Info("Creating table '" + tVehicleList.Name + "'");
            Log.Info("SQL: " + adp.WriteCreateTable(tVehicleList));
            adp.CreateTable(tVehicleList);

            SQInsertFromQuery insertFrom = new SQInsertFromQuery(new SQAliasableObject(tVehicleList.Name),
                                                                 new string[] { "SV_VIN", "SV_Year", "SV_Make", "SV_Model" },
                                                                 new string[] { "TV_VIN", "TV_Year", "MK_Name", "MD_Name" })
            {
                From = new SQFromClause(
                    new SQFromTable(tVehicle.Name)
                {
                    Join = new SQJoin(tModel.Name)
                    {
                        JoinType  = SQJoinTypes.Inner,
                        Predicate = new SQCondition("TV_MDID", SQRelationOperators.Equal, "MD_ID"),
                        Join      = new SQJoin(tMake.Name)
                        {
                            JoinType  = SQJoinTypes.Inner,
                            Predicate = new SQCondition("MD_MKID", SQRelationOperators.Equal, "MK_ID")
                        }
                    }
                }),
                Condition  = new SQCondition("TV_Year", SQRelationOperators.GreaterThanOrEqual, varYear),
                Parameters = new List <SQParameter> {
                    new SQParameter(varYear, 1990)
                }
            };

            Log.Info("Do Insert From");
            Log.Info("SQL: " + insertFrom.Write(adp));
            insertFrom.Execute(adp);

            q = new SQSelectQuery()
            {
                Columns = new List <SQAliasableObject> {
                    new SQAliasableObject(SQAggregates.COUNT.Create(adp, "*"), "RecordCount")
                },
                From = new SQFromClause(new SQFromTable(tVehicleList.Name))
            };
            Log.Info("Get record count for " + tVehicleList.Name);
            Log.Info("SQL: " + q.Write(adp));
            res = q.Execute(adp);

            if (res.Reader.Read())
            {
                Log.Info("Count: " + res.Reader.GetValue(0));
            }
            res.Close();



            Log.Info("------------------------------------------------------");
            Log.Info("Rollback Test");
            trn = adp.OpenTransaction();
            SQDeleteQuery delete = new SQDeleteQuery()
            {
                DeleteTable = new SQAliasableObject(tVehicleList.Name),
                Condition   = new SQCondition("SV_Year", SQRelationOperators.LessThan, varYear),
                Parameters  = new List <SQParameter> {
                    new SQParameter(varYear, 2000)
                }
            };
            Log.Info("Deleting data from " + tVehicleList.Name);
            Log.Info("SQL: " + delete.Write(adp));
            delete.Execute(trn);

            res = q.Execute(trn);
            if (res.Reader.Read())
            {
                Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
            }
            res.Close();

            Log.Info("Rolling Back");
            trn.RollBack();

            res = q.Execute(adp);
            if (res.Reader.Read())
            {
                Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
            }
            res.Close();
        }