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());
}
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);
}
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");
}
public override void BeginDeclareStatement(DBParam param)
{
if (_scriptDeclareCount == 0)
{
this.WriteRawSQLString("DECLARE ");
}
this.ParameterExclusions.Add(param.Name);
this.WriteParameter(param, false);
_scriptDeclareCount++;
}
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");
}
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);
}
}
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");
}
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);
}
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");
}
/// <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);
}
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");
}
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);
}
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");
}
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());
}
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);
}
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");
}
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");
}
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);
}
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);
}
}
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);
}
