public override void WriteParameter(DBParam param, bool writetype, bool includeDirection)
 {
     if (!writetype)
     {
         base.WriteParameter(param, writetype, includeDirection);
     }
 }
        public void _07_DropTableIfExists()
        {
            DBDatabase db = ConnectDb();

            //Check for existance and drop
            DBQuery drop = DBQuery.Drop.Table(TableName).IfExists();

            TestContext.WriteLine(drop.ToSQLString(db));
            db.ExecuteNonQuery(drop);
            TestContext.WriteLine("Dropped table {0} if it existed\r\n", TableName);

            //Now create the table - if it still exists an error will be thrown
            CreateCustomTable(db);


            //Add one row
            InsertARowIntoCustomTable(db, "First", "Second");
            TestContext.WriteLine("Added 1 row to an existing table\r\n");

            //Get the number of rows (should be 1)
            DBQuery countSql = DBQuery.SelectCount().From(TableName);
            int     count    = Convert.ToInt32(db.ExecuteScalar(countSql));

            Assert.AreEqual(1, count);
            TestContext.WriteLine("Got the row count of {0} for table {1}\r\n", count, TableName);


            //Insert another row based on the table existing (which it should)
            DBParam p1 = DBParam.ParamWithValue(DbType.AnsiString, "Existing");
            DBParam p2 = DBParam.ParamWithValue(DbType.String, "Description");

            DBQuery insert = DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2);

            TestContext.WriteLine(insert.ToSQLString(db));
            db.ExecuteNonQuery(insert);

            //Re-Check the count and make sure one has been added
            int newcount = Convert.ToInt32(db.ExecuteScalar(countSql));

            Assert.AreEqual(count + 1, newcount);
            TestContext.WriteLine("Added 1 row to an existing table\r\n");

            //drop the table - checking for it first even though we know it exists
            db.ExecuteNonQuery(drop);
            TestContext.WriteLine("Checked existance and dropped " + TableName + " table\r\n");

            try
            {
                db.ExecuteNonQuery(DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2));
                throw new InvalidOperationException("The insert succeeded on a table that should have been dropped");
            }
            catch (InvalidOperationException)
            {
                throw;
            }
            catch (Exception)
            {
                TestContext.WriteLine("Successfully caught exception for row insert");
            }
        }
        public override UpdateAction Where(TableFilter filter)
        {
            if (this.UpdateColumns.Count == 0)
            {
                throw new Exception("no update columns");
            }

            #region update columns
            string columnStr = string.Empty;
            foreach (var col in this.UpdateColumns)
            {
                var p = new DBParam(col.Key, col.Value);
                ParamCollection.Add(p);
                columnStr += string.Format(" {0} ={1},", col.Key, p.ParamName);
            }
            columnStr = columnStr.TrimEnd(',');
            #endregion

            #region where
            string filterCondition = string.Empty, where = string.Empty;
            if (filter != null)
            {
                filterCondition = filter.Build(base.ParamCollection);
                if (!string.IsNullOrWhiteSpace(filterCondition))
                {
                    where = string.Format(" where {0} ", filterCondition);
                }
            }
            #endregion

            base.MainSql = string.Format("update {0} set {1} {2}", base.TableCode, columnStr, where);
            return(this);
        }
        private void prepareInsertStatement(string[] state, string tableName)
        {
            List <DBClause> cs   = new List <DBClause>();
            CSVData         data = CSVData.ParseString(state, true);

            all = data.Items;
            List <string> c = new List <string>();

            foreach (TableField tf in DataSchema.Fields)
            {
                cs.Add(DBParam.ParamWithDelegate(delegate {
                    if (tf.Type == FieldType._datetime)
                    {
                        if (item[data.GetOffset(tf.Field)] != "")
                        {
                            return(Convert.ToDateTime(item[data.GetOffset(tf.Field)]).ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else
                        {
                            return(null);
                        }
                    }
                    else if (tf.Type == FieldType._bool)
                    {
                        if (item[data.GetOffset(tf.Field)] != "")
                        {
                            if (item[data.GetOffset(tf.Field)] == "true" || item[data.GetOffset(tf.Field)] == "True" || item[data.GetOffset(tf.Field)] == "1")
                            {
                                return(true);
                            }
                            else
                            {
                                return(false);
                            }
                            //    return Convert.ToDateTime(item[data.GetOffset(tf.Field)]).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        else
                        {
                            return(false);
                        }
                    }
                    else
                    {
                        if (item[data.GetOffset(tf.Field)] != "")
                        {
                            return(item[data.GetOffset(tf.Field)].Trim());
                        }
                        else
                        {
                            return(null);
                        }
                    }
                }));
                c.Add(tf.Field);
            }
            insert = DBQuery.InsertInto(tableName)
                     .Fields(c.ToArray())
                     .Values(cs.ToArray());
        }
Exemple #5
0
            private static String SerializeValue(DBCommand cmd, Object value)
            {
                var name  = "@Param" + cmd.Parameters.Count;
                var param = new DBParam(name, value ?? DBNull.Value);

                cmd.Parameters.Add(param);

                return(name);
            }
Exemple #6
0
        public void _12_InsertSimpleXmlTest()
        {
            DBParam pname    = DBParam.ParamWithDelegate(delegate { return("My Customer Name"); });
            DBParam paddress = DBParam.ParamWithDelegate(delegate { return("My Customer Address"); });

            DBQuery q = DBQuery.InsertInto("CUSTOMERS").Fields("customer_name", "customer_address")
                        .Values(pname, paddress);

            q = SerializeAndDeserialzeQueryToMatch(q, "Simple Insert");
        }
Exemple #7
0
 public override void BeginDeclareStatement(DBParam param)
 {
     if (_scriptDeclareCount == 0)
     {
         this.WriteRawSQLString("DECLARE ");
     }
     this.ParameterExclusions.Add(param.Name);
     this.WriteParameter(param, false);
     _scriptDeclareCount++;
 }
Exemple #8
0
        public void _13_InsertWithSubSelectXmlTest()
        {
            DBParam pduplicate = DBParam.ParamWithDelegate("dupid", System.Data.DbType.Int32, delegate { return(10); });

            DBQuery q = DBQuery.InsertInto("CUSTOMERS").Fields("customer_name", "customer_address")
                        .Select(DBQuery.Select().Fields("customer_name", "customer_address")
                                .From("CUSTOMERS").As("DUP")
                                .WhereFieldEquals("customer_id", pduplicate));

            q = SerializeAndDeserialzeQueryToMatch(q, "Sub Select Insert");
        }
Exemple #9
0
        public bool Save(ref DataTable dtFields)
        {
            DataRow item = null;
            //dtFields.Columns.Add(new DataColumn("fieldid"));
            //for (int i = 0; i < dtFields.Rows.Count; i++)
            //{
            //    dtFields.Rows[i]["fieldid"] = Shared.generateID();
            //}
            DBParam dbfieldid   = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["fieldid"] == null ? Shared.generateID() : item["fieldid"]); });
            DBParam dbTableid   = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["tableid"] == null ? "" : item["tableid"]); });
            DBParam dbfieldName = DBParam.ParamWithDelegate(DbType.String, delegate { return(item["fieldname"] == null ? "" : item["fieldname"]); });
            DBParam dbfieldtype = DBParam.ParamWithDelegate(DbType.Int32, delegate { return(item["fieldtype"] == null ? 1 : item["fieldtype"]); });
            DBParam dbisnull    = DBParam.ParamWithDelegate(DbType.Boolean, delegate { return(item["isnull"] == null ? false : item["isnull"]); });
            DBParam dbisprimary = DBParam.ParamWithDelegate(DbType.Boolean, delegate { return(item["isprimary"] == null?false: item["isprimary"]); });
            DBParam dblength    = DBParam.ParamWithDelegate(DbType.Int32, delegate { return(item["length"] == null? 0: item["length"]); });


            DBQuery insert = DBQuery.InsertInto(TzAccount.Field.Table).Fields(
                TzAccount.Field.TableID.Name,
                TzAccount.Field.FieldID.Name,
                TzAccount.Field.FieldName.Name,
                TzAccount.Field.FieldType.Name,
                TzAccount.Field.Length.Name,
                TzAccount.Field.IsNullable.Name,
                TzAccount.Field.ISPrimaryKey.Name
                ).Values(
                dbTableid,
                dbfieldid,
                dbfieldName,
                dbfieldtype,
                dblength,
                dbisnull,
                dbisprimary
                );
            int sum = 0;

            using (DbTransaction trans = db.BeginTransaction())
            {
                for (int i = 0; i < dtFields.Rows.Count; i++)
                {
                    item = dtFields.Rows[i];
                    sum += db.ExecuteNonQuery(trans, insert);
                }
                trans.Commit();
            }
            if (sum == dtFields.Rows.Count)
            {
                return(true);
            }
            else
            {
                return(false);
            }
        }
Exemple #10
0
        public void _09_DeleteParameterXmlTest()
        {
            DBParam p1 = DBParam.ParamWithValue("first", 1);
            DBParam p2 = DBParam.ParamWithValue("second", 2);
            DBParam p3 = DBParam.ParamWithValue("third", 3);

            DBQuery q = DBQuery.DeleteFrom("Customers")
                        .WhereField("customer_id", Compare.In, DBValueGroup.All(p1, p2, p3))
                        .OrWhereField("picture", Compare.Is, DBConst.Null());

            q = SerializeAndDeserialzeQueryToMatch(q, "Parameterized Delete");
        }
Exemple #11
0
        public void _08_CreateTableIfNotExists()
        {
            DBDatabase db = ConnectDb();

            //Make sure the table does not exist first
            DBQuery dropit = DBQuery.Drop.Table(TableName).IfExists();

            db.ExecuteNonQuery(dropit);

            DBParam p1     = DBParam.ParamWithValue(DbType.AnsiString, "Existing");
            DBParam p2     = DBParam.ParamWithValue(DbType.String, "Description");
            DBQuery insert = DBQuery.InsertInto(TableName).Fields("ColB", "ColC").Values(p1, p2);

            try
            {
                db.ExecuteNonQuery(insert);
                throw new InvalidOperationException("The insert succeeded on a table that should have been dropped");
            }
            catch (InvalidOperationException)
            {
                throw;
            }
            catch (Exception)
            {
                TestContext.WriteLine("Successfully caught exception for row insert. We know the table does not exist");
            }

            //This should now not exist and be created
            DBQuery ifnotexistsCreate = GetCustomTableQuery().IfNotExists();

            TestContext.WriteLine(ifnotexistsCreate.ToSQLString(db));
            db.ExecuteNonQuery(ifnotexistsCreate);

            //Now we should be able to insert a row
            db.ExecuteNonQuery(insert);

            int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));

            //Check that the rows are there
            Assert.AreEqual(1, count);

            //Check that it does not try and create again
            db.ExecuteNonQuery(ifnotexistsCreate);

            //Validate that it has not just succeeded and re-created an empty table
            count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
            //Check that the rows are there
            Assert.AreEqual(1, count);

            //Finally just drop the table
            db.ExecuteNonQuery(dropit);
        }
Exemple #12
0
        public void _14_ScriptTest()
        {
            DBParam pname    = DBParam.ParamWithDelegate(delegate { return("My Customer Name"); });
            DBParam paddress = DBParam.ParamWithDelegate(delegate { return("My Customer Address"); });

            DBQuery q = DBQuery.Begin(
                DBQuery.InsertInto("CUSOMERS").Fields("customer_name", "customer_address")
                .Values(pname, paddress),
                DBQuery.Select(DBFunction.LastID()))
                        .End();

            q = SerializeAndDeserialzeQueryToMatch(q, "Simple Script");
        }
Exemple #13
0
        /// <summary>
        /// Returns a statement that will insert a row into the custom table - must have been created before
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        private DBQuery InsertARowIntoCustomTable(DBDatabase db, object first, object second)
        {
            DBParam p1 = DBParam.ParamWithValue(DbType.AnsiString, first);
            DBParam p2 = DBParam.ParamWithValue(DbType.String, second);

            DBQuery ins = DBQuery.InsertInto(TableName)
                          .Fields(TblCol2, TblCol3)
                          .Values(p1, p2);

            int count = db.ExecuteNonQuery(ins);

            return(ins);
        }
Exemple #14
0
        public void Test_01_SelectOneCategoryTest()
        {
            int id = 1;

            DBSelectQuery select = DBQuery.SelectAll()
                                   .From("Categories").As("C")
                                   .WhereField("CategoryID", Compare.Equals, DBParam.ParamWithDelegate(DbType.Int32, delegate { return(id); }))
                                   .GroupBy("CategoryName")
                                   .OrderBy("ProductCount", Order.Descending);

            this.OutputSql(select, " SELECT ONE CATEGORY");
            this.OutputXML(select, " SELECT ONE CATEGORY");
        }
Exemple #15
0
        public void _11_UpdateSubSelectAndParameterXmlTest()
        {
            DBParam cid = DBParam.ParamWithValue("cid", 10);

            DBQuery thirtyDaysOrders = DBQuery.Select()
                                       .Sum(DBField.Field("order_value")).From("ORDER").As("O")
                                       .WhereField("order_date", Compare.GreaterThan, DBConst.DateTime(DateTime.Today.AddDays(-30)))
                                       .AndWhere("customer_id", Compare.Equals, cid);

            DBQuery q = DBQuery.Update("CUSTOMERS").Set("customer_name", DBConst.String("new name"))
                        .AndSet("customer_address", DBConst.String("new address"))
                        .AndSet("order_value", thirtyDaysOrders)
                        .WhereField("customer_id", Compare.Equals, cid);

            q = SerializeAndDeserialzeQueryToMatch(q, "Sub Select Update");
        }
        /// <summary>
        /// We don't declare variables, but SET them
        /// </summary>
        /// <param name="param"></param>
        public override void BeginDeclareStatement(DBParam param)
        {
            this.ParameterExclusions.Add(param.Name);
            this.WriteRawSQLString("SET ");
            bool writeType      = false;
            bool writeDirection = false;

            this.WriteParameter(param, writeType, writeDirection);

            string value = GetDefaultValueForType(param.DbType);

            this.WriteOperator(Operator.Equals);
            this.WriteSpace();
            //Should be safe as we return this value internally
            this.WriteRawSQLString(value);
        }
Exemple #17
0
        public void Test_10_CreateView()
        {
            DBParam startswith = DBParam.Param("startswith", DbType.AnsiString, 50);

            DBQuery create = DBQuery.Create.View("dbo", "CategoriesByName")
                             .As(
                DBQuery.Select()
                .Field("mc_id").As("id")
                .Field("mc_name").As("name")
                .Field("mc_desc").As("desc")
                .From("dbo", "MoreCategories")
                .Where("mc_name", Compare.Like, startswith)
                .OrderBy("mc_name")
                );

            this.OutputAndValidate(create, " Create a view with a select statement");
        }
Exemple #18
0
        private void prepareInsertStatement(string state, string tb, string pFields)
        {
            List <DBClause> cs   = new List <DBClause>();
            CSVData         data = CSVData.ParseString(string.Join(Environment.NewLine, state), true);

            all = data.Items;
            List <string> c = new List <string>();

            string[] flds;
            flds = pFields.Split(System.Convert.ToChar(","));
            foreach (string s in flds)
            {
                cs.Add(DBParam.ParamWithDelegate(() => itm[data.GetOffset(s)]));
                c.Add(s);
            }
            insert = DBQuery.InsertInto(tb).Fields(c.ToArray()).Values(cs.ToArray());
        }
Exemple #19
0
        public static OracleDbType GetDBType(DBParam dbParam)
        {
            var oracleDbType = OracleDbType.NVarchar2;

            switch (dbParam.DbType)
            {
            case CommonDbType.Integer:
                oracleDbType = OracleDbType.Int32;
                break;

            case CommonDbType.DateTime:
                oracleDbType = OracleDbType.Date;
                break;

            case CommonDbType.Boolean:
                oracleDbType = OracleDbType.Char;
                break;

            case CommonDbType.StringAscii:
                oracleDbType = OracleDbType.Varchar2;
                break;

            case CommonDbType.StringUnicode:
                oracleDbType = OracleDbType.NVarchar2;
                break;

            case CommonDbType.Guid:
                oracleDbType = OracleDbType.NVarchar2;
                break;

            case CommonDbType.TextAscii:
                oracleDbType = OracleDbType.NVarchar2;
                break;

            case CommonDbType.TextUnicode:
                oracleDbType = OracleDbType.NVarchar2;
                break;

            case CommonDbType.Money:
                oracleDbType = OracleDbType.Decimal;
                break;
            }
            return(oracleDbType);
        }
Exemple #20
0
        public void Test_04_InsertScript()
        {
            byte[] imgdata = this.GetLocalImage("bomb.gif");

            DBParam cName = DBParam.ParamWithValue("name", DbType.String, (object)"newType_1");
            DBParam cDesc = DBParam.ParamWithValue("desc", DbType.String, (object)"newDescrption_1");
            DBParam cPic  = DBParam.ParamWithValue("pic", DbType.Binary, (object)imgdata);

            DBInsertQuery ins = DBQuery.InsertInto("Categories")
                                .Fields("CategoryName", "Description", "Picture")
                                .Values(cName, cDesc, cPic);
            DBSelectQuery sel = DBQuery.Select(DBFunction.LastID());

            DBScript script = DBQuery.Begin(ins)
                              .Then(sel)
                              .End();

            this.OutputSql(script, "Insert and Return last ID script");
            this.OutputXML(script, "Insert and Return last ID script");
        }
Exemple #21
0
        public void Test_11_CreateProcedure()
        {
            DBParam custid  = DBParam.Param("cust", DbType.Int32);
            DBParam orderid = DBParam.Param("id", DbType.Int32, ParameterDirection.Input);
            DBParam name    = DBParam.Param("name", DbType.String, 50, ParameterDirection.Input);

            DBQuery create = DBQuery.Create.StoredProcedure("dbo", "ChangeOrderItemCustomer")
                             .WithParams(orderid, name)
                             .As(
                DBQuery.Declare(custid),

                DBQuery.Select().TopN(1)
                .Field(DBAssign.Set(custid, DBField.Field("customerid")))
                .From("Orders")
                .WhereFieldEquals("order_id", orderid),

                DBQuery.Update("OrderItems")
                .Set("customerid", custid)
                .Set("customername", name)
                .WhereFieldEquals("orderid", orderid)
                );

            this.OutputAndValidate(create, " Create Procedure ChangeOrderItemCustomer");
        }
Exemple #22
0
        public AddAction Add()
        {
            string sql   = string.Empty;
            var    collP = DbContext.GetNewParamCollection();

            string values = string.Empty, columns = string.Empty;
            var    entityType = insertEntity.GetType();

            foreach (var col in insertEntity.ChangeField)
            {
                var param = new DBParam(col, entityType.GetProperty(col).GetValue(insertEntity, null));
                collP.Add(param);
                values  += param.ParamName + ",";
                columns += col + ",";
            }
            values  = values.TrimEnd(',');
            columns = columns.TrimEnd(',');

            sql = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", insertEntity._TableCode, columns, values);

            base.MainSql         = sql;
            base.ParamCollection = collP;
            return(this);
        }
Exemple #23
0
        public void _12_CreateSProc()
        {
            DBDatabase db = ConnectDb();

            this.CreateCustomTable(db);
            try
            {
                DBParam name = DBParam.Param("name", DbType.String, 50);
                DBParam desc = DBParam.Param("desc", DbType.String, 150);

                DBQuery ins = DBQuery.InsertInto(TableName).Fields(TblCol2, TblCol3).Values(name, desc);
                char    one = 'A';
                char    two = 'a';

                TestContext.WriteLine(ins.ToSQLString(db));

                for (int i = 0; i < 26; i++)
                {
                    int c = (int)one;
                    c += i;
                    string offset = ((char)c).ToString() + two.ToString();
                    name.Value = offset;
                    desc.Value = "Description of " + offset;
                    db.ExecuteNonQuery(ins);
                }
                int count = Convert.ToInt32(db.ExecuteScalar(DBQuery.SelectCount().From(TableName)));
                Assert.AreEqual(26, count);

                DBQuery q = DBQuery.Create.StoredProcedure("finditemsincol2")
                            .WithParam("p1", DbType.String, 50, ParameterDirection.Input)
                            .As(
                    DBQuery.SelectAll().From(TableName)
                    .WhereField(TblCol2, Compare.Like, DBParam.Param("p1"))

                    );
                TestContext.WriteLine("Execute Procedure: " + q.ToSQLString(db));
                db.ExecuteNonQuery(q);
                TestContext.WriteLine("Created the new stored procedure");


                DBQuery exec = DBQuery.Exec("finditemsincol2").WithParamValue("p1", DbType.String, 50, "A%");
                count = 0;
                TestContext.WriteLine(exec.ToSQLString(db));
                db.ExecuteRead(exec, reader =>
                {
                    while (reader.Read())
                    {
                        count++;
                        Assert.IsTrue(reader[TblCol2].ToString().StartsWith("A"));
                    }
                    TestContext.WriteLine("Executed the stored procedure and read '" + count.ToString() + "' rows");
                });

                Assert.AreEqual(1, count);
            }
            finally
            {
                try
                {
                    DBQuery drop = DBQuery.Drop.StoredProcedure("finditemsincol2");
                    db.ExecuteNonQuery(drop);
                    TestContext.WriteLine("Sucessfully dropped the stored procedure");
                }
                catch
                {
                    TestContext.WriteLine("DROP PROCEDURE failed");
                }
                this.DropCustomTable(db);
            }
        }
Exemple #24
0
        public void _10_CreateTableWithForeignKeys()
        {
            DBDatabase db = ConnectDb();

            //Create the persons table
            DBQuery createPersons = DBQuery.Create.Table("DSQL_Persons")
                                    .Add("Person_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
                                    .Add("Person_Name", DbType.String, 50);

            TestContext.WriteLine(createPersons.ToSQLString(db));
            db.ExecuteNonQuery(createPersons);

            //Create the orders table
            DBQuery createOrders = DBQuery.Create.Table("DSQL_Orders")
                                   .Add("Order_ID", DbType.Int32, DBColumnFlags.AutoAssign | DBColumnFlags.PrimaryKey)
                                   .Add("Ordered_By", DbType.Int32)
                                   .Add("Ordered_Date", DbType.DateTime)
                                   .Add("Signed_By", DbType.Int32, DBColumnFlags.Nullable)
                                   .Constraints(
                DBConstraint.ForeignKey().Column("Ordered_By")                                         //unnamed foreign key first
                .References("DSQL_Persons").Column("Person_ID"),

                DBConstraint.ForeignKey("Orders_Signed_By_2_Persons_PersonID").Column("Signed_By")
                .References("DSQL_Persons").Column("Person_ID")
                .OnDelete(DBFKAction.Cascade)
                .OnUpdate(DBFKAction.Cascade)
                );

            //Execute the Create Table statements
            TestContext.WriteLine(createOrders.ToSQLString(db));
            db.ExecuteNonQuery(createOrders);

            try
            {
                bool scripts = db.GetProperties().CheckSupports(DBSchemaTypes.CommandScripts);

                DBParam pname = DBParam.Param("name", DbType.String);

                DBScript insertperson = DBQuery.Script(
                    DBQuery.InsertInto("DSQL_Persons").Field("Person_Name").Value(pname),
                    DBQuery.Select(DBFunction.LastID())
                    );

                //Insert one row into the persons table
                pname.Value = "First Person";
                TestContext.WriteLine(insertperson.ToSQLString(db));
                int firstpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));

                //And another row
                pname.Value = "Second Person";
                int secondpid = Convert.ToInt32(this.ExecuteScalarScript(db, insertperson, scripts));


                //Create an order with orderedby = firstpid and signedby = secondpid
                DBParam  orderedby   = DBParam.ParamWithValue(DbType.Int32, firstpid);
                DBParam  signedby    = DBParam.ParamWithValue(DbType.Int32, secondpid);
                DBScript insertorder = DBQuery.Script(
                    DBQuery.InsertInto("DSQL_Orders")
                    .Field("Ordered_By").Value(orderedby)
                    .Field("Ordered_Date").Value(DBFunction.GetDate())
                    .Field("Signed_By").Value(signedby),
                    DBQuery.Select(DBFunction.LastID())
                    );
                TestContext.WriteLine(insertorder.ToSQLString(db));
                int orderid = Convert.ToInt32(this.ExecuteScalarScript(db, insertorder, scripts));

                //Now try to create an order that breaks referential integrity
                orderedby.Value = -100;
                try
                {
                    orderid = Convert.ToInt32(db.ExecuteScalar(insertorder));
                    throw new InvalidOperationException("We should not be able to insert these rows. FAILED test");
                }
                catch (InvalidOperationException)
                {
                    throw;
                }
                catch (Exception ex)
                {
                    TestContext.WriteLine("Sucessfully caught an exception that breaks referential integrity");
                }


                //Finally check the cascading deletes
                //check the Orders table count
                //delete a person row
                //ensure that the corresponding row for Signed By FK was deleted

                DBQuery getcount   = DBQuery.SelectCount().From("DSQL_ORDERS");
                int     ordercount = Convert.ToInt32(
                    db.ExecuteScalar(getcount));

                DBQuery del = DBQuery.DeleteFrom("DSQL_Persons")
                              .WhereField("Person_ID", Compare.Equals, DBParam.ParamWithValue(secondpid));
                int removed = db.ExecuteNonQuery(del);

                Assert.AreEqual(1, removed);
                TestContext.WriteLine("Removed a single row from the persons table");

                int newordercount = Convert.ToInt32(db.ExecuteScalar(getcount));
                //Make sure the orders row has been deleted
                Assert.AreEqual(newordercount, ordercount - 1);
                TestContext.WriteLine("Validated that the corresponding row in the orders table has been removed too");
            }
            finally
            {
                //Clean up tables in order
                db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Orders"));
                db.ExecuteNonQuery(DBQuery.Drop.Table("DSQL_Persons"));
                TestContext.WriteLine("Database has been cleaned up");
            }
        }
 public override void BeginDeclareStatement(DBParam param)
 {
     //base.BeginDeclareStatement(param);
 }