public override string buildDropStatement(QueryBuilder qbl)
{
string wSql = "drop ";
wSql += ((AField)qbl.getFieldList()[0]).value;
wSql += " " + ((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject + ";";
return wSql;
}
public bool Bind(QueryBuilder qbl)
{
this._queryObject = qbl;
this._dataReader = qbl.getDatabaseObject().getResult(this._queryObject);
ATable srcTable = qbl.getDatabaseObject().GetTableCache().getCachedTable(((ATable)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject).name);
/*
// remove redundant fields from the field list.
if (srcTable.getFieldList().Count != this._queryObject.getFieldList().Count)
{
object[] test = utils.arrayIntersect(this._queryObject.getFieldList().ToArray(), srcTable.getFieldList().ToArray());
this.setFieldList(new System.Collections.ArrayList(test));
}
else
this.setFieldList(srcTable.getFieldList());
* */
this.setFieldList(this._queryObject.getFieldList());
this.name = srcTable.name;
this.BindData(qbl.getFieldList());
return false;
}
public override string buildInsertStatement(QueryBuilder qbl)
{
string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject);
ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname);
if (tblObj == null)
throw (new Exception("Invalid table specified. Check query spelling and try again."));
string wSql = "insert into [" + tblname + "] ";
string fieldList = "";
string valueList = "";
ABSTRACTDATATYPES fieldType;
// If our chosen table object does not have an autoincrementing primary key/id field
// we can check for that here.
// Build up field list and value list.
foreach (AField field in qbl.getFieldList())
{
AField refField = qbl.getDatabaseObject().getTableObject(field.owner).getFieldByName(field.name);
if (tblObj.getFieldByName(field.name) == null)
{
this._dbObj.GetTableCache().rebuildAll();
throw(new Exception("Unable to build insert statement. An invalid field was included with the query."));
}
fieldList += "["+field.name+"]";
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ",";
fieldType = refField.type;
if (fieldType == ABSTRACTDATATYPES.ABool)
{
string boolRaw = Convert.ToString(field.value).ToLower();
bool relFieldValue = (boolRaw == "true" || boolRaw == "1" || boolRaw == "yes") ? true : false;
if (relFieldValue)
{
valueList += "1";
}
else
{
valueList += "0";
}
}
else if (fieldType == ABSTRACTDATATYPES.AForeignTable)
{
// This is tricky, we have to look up the value type from the
// referencing table's file.
}
else if ((fieldType == ABSTRACTDATATYPES.ASmallInteger) || (fieldType == ABSTRACTDATATYPES.ALargeInteger ))
{
valueList += field.value;
}
else if ((fieldType == ABSTRACTDATATYPES.AString) || (fieldType == ABSTRACTDATATYPES.AChar) || (fieldType == ABSTRACTDATATYPES.ADateTime))
{
// convert mysql/csql style quoting to mssql style quoting
//field.value = Convert.ToString(field.value).Replace("''", @"\'");
// if the value is longer than the allowed nvarchar, then do a CONVERT/cast to text instead.
valueList += "'" + field.value + "'";
}
else if (fieldType == ABSTRACTDATATYPES.AData)
{
valueList += field.value;
}
else
{
// If we get here, then the field type is possibly unsupported.
// try to figure out what type of formatting to use.
string sValue = field.value != null?Convert.ToString(field.value):"";
if (sValue == "")
{
valueList += "''";
}
else
{
if (sValue.Substring(0, 1) == "'")
{
// convert mysql/csql style quoting to mssql style quoting
//field.value = Convert.ToString(field.value).Replace("''", @"\'");
valueList += "'" + field.value + "'";
}
else
{
valueList += field.value;
}
}
}
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ",";
}
wSql += "(";
wSql += fieldList;
wSql += ") VALUES (";
wSql += valueList;
wSql += ");";
return wSql;
}
public override string buildUpdateStatement(QueryBuilder qbl)
{
bool skipped = false;
string tblname = ((object)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject).ToString();
ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname);
string wSql = "update " + tblname + " set ";
foreach(AField field in qbl.getFieldList()) {
skipped = false;
if (!field.hasModifier(ABSTRACTFIELDMODIFIERS.PrimaryKey))
// Skip the primary key as we cannot update it.
{
if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString)
{
wSql += "[" + field.name + "]" + "='" + field.value + "'";
}
else if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ABool)
{
if (Convert.ToString(field.value) != "" && field.value != null)
if(Convert.ToString(field.value).ToLower()=="true")
wSql += "[" + field.name + "]" + "=1";
else
wSql += "[" + field.name + "]" + "=0";
else
skipped = true;
}
else if ((tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ALargeInteger)||(tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ASmallInteger))
if (Convert.ToString(field.value) != "" && field.value != null)
wSql += "[" + field.name + "]" + "=" + field.value;
else
skipped = true;
else if ((tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.ADateTime))
if (Convert.ToString(field.value) != "" && field.value != null)
wSql += "[" + field.name + "]" + "=CONVERT(datetime, '" + field.value+"')";
else
skipped = true;
}
else skipped = true;
if ((Convert.ToString(field.value) != "" && field.value != null)||(tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString))
if(!skipped)
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wSql += ", ";
else
wSql += " ";
else
wSql += " ";
}
// sometimes the algorithm skips... fix it here
wSql = wSql.Trim();
if (wSql[wSql.Length - 1] == ',')
{
wSql = wSql.Substring(0, wSql.Length - 1);
}
if (wSql[wSql.Length - 1] != ' ') wSql += " ";
if(qbl.conditionalString != null)
if(qbl.conditionalString.Length > 2)
wSql += "where " + qbl.conditionalString;
return wSql;
}
public override string buildSelectStatement(QueryBuilder qbl)
{
// Fix for limit code.
if ((qbl.QueryLimit.lStart == 0) && (qbl.QueryLimit.lLimit == 0))
{
qbl.QueryLimit.lStart = -1;
qbl.QueryLimit.lLimit = -1;
}
string wSql = "select ";
if(qbl.QueryLimit.lStart != -1) {
wSql += " TOP " + ((int)(qbl.QueryLimit.lStart + qbl.QueryLimit.lLimit)) + " ";
}
// fields in selection.
if (qbl.distinct)
wSql += " DISTINCT ";
string wGrouping = "";
string wOrdering = "";
string totFieldList = "";
foreach (AField field in qbl.getFieldList())
{
string fieldName = field.name;
if (field.name[0] == '[') field.name = field.name.Substring(1);
if (field.name[field.name.Length - 1] == ']') field.name = field.name.Substring(0, field.name.Length - 1);
fieldName = "[" + field.name + "]";
//field.owner = ((ATable)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject).name;
string wFieldstr = null;
if ((field.owner == null) && (qbl.getSourceList().Count == 1))
{
wFieldstr = fieldName;
}
else
{
//if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).name != field.owner)
// wFieldstr = field.owner + "." + ""+field.name+"";
System.Collections.ArrayList al = qbl.getSourceList();
wFieldstr = "" + fieldName + "";
foreach (QueryBuilder.SOURCEBINDING s in al)
{
if (((ATable)s.sourceObject).alias == field.owner) //logic using table alias
{
ATable ownr = ((ATable)s.sourceObject);
if (ownr.alias != null)
{
wFieldstr = ownr.alias + "." + "" + fieldName + "";
break;
}
else
{
wFieldstr = field.owner + "." + "" + fieldName + "";
break;
}
}
}
//wFieldstr = field.owner + "." + "" + field.name + "";
}
if (field.function != 0)
{
if (field.function == ABSTRACTAGGREGATE.Count)
wFieldstr = "Count(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Avg)
wFieldstr = "Avg(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.DistinctCount)
wFieldstr = "Count(Distinct " + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Max)
wFieldstr = "Max(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Min)
wFieldstr = "Min(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Sum)
wFieldstr = "Sum(" + wFieldstr + ")";
}
wSql += wFieldstr;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wSql += ", ";
if (field.GroupBy == true)
{
string wFieldGroup = "";
if ((field.owner == null) && (qbl.getSourceList().Count == 1))
wFieldGroup = field.name;
else
if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).name != field.owner)
wFieldGroup = field.owner + "." + field.name;
else
wFieldGroup = field.name;
wGrouping += " " + wFieldGroup;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wGrouping += ", ";
}
if (field.OrderBy != ABSTRACTORDERTYPE.None)
{
string fStatMode = (field.OrderBy == ABSTRACTORDERTYPE.Ascending)?"asc":"desc";
string wFieldOrder = "";
if ((field.owner == null) && (qbl.getSourceList().Count == 1))
wFieldOrder = field.name;
else
if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).name != field.owner)
wFieldOrder = field.owner + "." + field.name;
else
wFieldOrder = field.name;
wOrdering += " " + wFieldOrder + " "+ fStatMode;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wOrdering += ", ";
}
totFieldList += wFieldstr;
}
wSql += " from ";
// sources
foreach (QueryBuilder.SOURCEBINDING src in qbl.getSourceList())
{
if (src.bindType == 0)
{
// This is our main table name
wSql += ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
wSql += " " + ((ATable)src.sourceObject).alias;
}
else if (src.bindType == ABSTRACTSOURCEBINDTYPES.INNERJOIN)
{
wSql += " INNER JOIN " + ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
{
wSql += " " + ((ATable)src.sourceObject).alias;
try
{
wSql += " on " + ((ATable)src.boundSource).alias + "." + ((String)src.srcMatchField) + "=" + ((ATable)src.sourceObject).alias + "." + ((String)src.dstMatchField) + " ";
}
catch
{
wSql += " on " + ((ATable)src.boundSource).alias + "." + ((AField)src.srcMatchField).name + "=" + ((ATable)src.sourceObject).alias + "." + ((AField)src.dstMatchField).name + " ";
}
}
else
{
wSql += " on " + ((ATable)src.boundSource).name + "." + ((AField)src.srcMatchField).name + "=" + ((ATable)src.sourceObject).name + "." + ((AField)src.dstMatchField).name + " ";
}
}
}
// conditional
if((qbl.conditionalString != null) && (qbl.conditionalString.Length > 0))
wSql += " where " + qbl.conditionalString + " ";
// group by
if (wGrouping.Trim() != "")
{
wGrouping = wGrouping.Trim();
if (wGrouping[wGrouping.Length-1].Equals(',') == true)
wGrouping = wGrouping.Substring(0, wGrouping.Length - 1);
wSql += " group by " + wGrouping;
}
// ordering by
if (wOrdering.Trim() != "")
{
wOrdering = wOrdering.Trim();
if (wOrdering[wOrdering.Length - 1].Equals(',') == true)
wOrdering = wOrdering.Substring(0, wOrdering.Length - 1);
}
// LIMIT CLAUSE WRAPPER
/*
if (qbl.QueryLimit.lStart > -1)
{
//string fStatMode = (field.OrderBy == ABSTRACTORDERTYPE.Ascending)?"asc":"desc";
string wFieldOrder = "";
if ((qbl.getField("id").owner == null) && (qbl.getSourceList().Count == 1))
wFieldOrder = qbl.getField("id").name;
else
if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).name != qbl.getField("id").owner)
wFieldOrder = qbl.getField("id").owner + "." + qbl.getField("id").name;
else
wFieldOrder = qbl.getField("id").name;
string fOrderingString = wFieldOrder;
string sqlInner1 = "";
string sqlInner2 = "";
if(wOrdering != "") {
sqlInner2 = String.Format("{0} order by {1} asc, {2}", wSql, fOrderingString, wOrdering);
}else {
sqlInner2 = String.Format("{0} order by {1} asc", wSql, fOrderingString); }
if(wOrdering != "") {
sqlInner1 = String.Format("select top {0} {1} from ({3}) as newtbl order by {2} desc, {3}", qbl.QueryLimit.lLimit, totFieldList, fOrderingString, sqlInner2, wOrdering);
}else
{sqlInner1 = String.Format("select top {0} {1} from ({3}) as newtbl order by {2} desc", qbl.QueryLimit.lLimit, totFieldList, fOrderingString, sqlInner2); }
if(wOrdering != "")
wSql = String.Format("select * from ({0}) as newtbl2 order by {1} asc, {2}", sqlInner1, fOrderingString, wOrdering);
else
wSql = String.Format("select * from ({0}) as newtbl2 order by {1} asc", sqlInner1, fOrderingString);
}
* */
if(wOrdering != "")
wSql += " order by " + wOrdering;
return wSql;
}
public override string buildUpdateStatement(QueryBuilder qbl)
{
string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject);
ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname);
string wSql = "update " + tblname + " set ";
foreach (AField field in qbl.getFieldList())
{
if (tblObj.getFieldByName(field.name).type == ABSTRACTDATATYPES.AString)
{
wSql += field.name + "='" + field.value + "'";
}
else
{
wSql += field.name + "=" + field.value;
}
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wSql += ", ";
else
wSql += " ";
}
wSql += "where " + qbl.conditionalString;
return wSql;
}
public override string buildSelectStatement(QueryBuilder qbl)
{
string wSql = "select ";
// fields in selection.
string wGrouping = "";
foreach (AField field in qbl.getFieldList())
{
string wFieldstr;
wFieldstr = field.owner + "." + field.name;
if (field.function != 0)
{
if (field.function == ABSTRACTAGGREGATE.Count)
wFieldstr = "Count(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Avg)
wFieldstr = "Avg(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.DistinctCount)
wFieldstr = "Count(Distinct " + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Max)
wFieldstr = "Max(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Min)
wFieldstr = "Min(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Sum)
wFieldstr = "Sum(" + wFieldstr + ")";
}
wSql += wFieldstr;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wSql += ", ";
if (field.GroupBy == true)
{
wGrouping += " " + field.owner + "." + field.name;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wGrouping += ", ";
}
}
wSql += " from ";
// sources
foreach (QueryBuilder.SOURCEBINDING src in qbl.getSourceList())
{
if (src.bindType == 0)
{
// This is our main table name
wSql += ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
wSql += " " + ((ATable)src.sourceObject).alias;
}
else if (src.bindType == ABSTRACTSOURCEBINDTYPES.INNERJOIN)
{
wSql += " INNER JOIN " + ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
{
wSql += " " + ((ATable)src.sourceObject).alias;
wSql += " on " + ((ATable)src.boundSource).alias + "." + src.srcMatchField + "=" + ((ATable)src.sourceObject).alias + "." + src.dstMatchField + " ";
}
else
{
wSql += " on " + ((ATable)src.boundSource).name + "." + src.srcMatchField + "=" + ((ATable)src.sourceObject).name + "." + src.dstMatchField + " ";
}
}
}
// conditional
if ((qbl.conditionalString != null) && (qbl.conditionalString.Length > 0))
wSql += " where " + qbl.conditionalString + " ";
// group by
if (wGrouping.Trim() != "")
{
wGrouping = wGrouping.Trim();
if (wGrouping[wGrouping.Length - 1].Equals(',') == true)
wGrouping = wGrouping.Substring(0, wGrouping.Length - 1);
wSql += " group by " + wGrouping;
}
return wSql;
}
public override string buildInsertStatement(QueryBuilder qbl)
{
string tblname = ((string)((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject);
ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname);
string wSql = "insert into " + tblname + " ";
string fieldList = "";
string valueList = "";
ABSTRACTDATATYPES fieldType;
// If our chosen table object does not have an autoincrementing primary key/id field
// we can check for that here.
// Build up field list and value list.
foreach (AField field in qbl.getFieldList())
{
fieldList += field.name;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ",";
fieldType = tblObj.getFieldByName(field.name).type;
if (fieldType == ABSTRACTDATATYPES.ABool)
{
if (((string)field.value == "true") || ((bool)field.value == true) || ((int)field.value == 1) || ((string)field.value == "1") || ((string)field.value == "yes"))
{
valueList += "1";
}
}
else if (fieldType == ABSTRACTDATATYPES.AForeignTable)
{
// This is tricky, we have to look up the value type from the
// referencing table's file.
}
else if (fieldType == ABSTRACTDATATYPES.ASmallInteger)
{
valueList += field.value;
}
else if (fieldType == ABSTRACTDATATYPES.AString)
{
valueList += "'" + field.value + "'";
}
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ",";
}
wSql += "(";
wSql += fieldList;
wSql += ") VALUES (";
wSql += valueList;
wSql += ");";
return wSql;
}
public override string buildSelectStatement(QueryBuilder qbl)
{
string wSql = "select ";
// fields in selection.
string wGrouping = "";
string wOrdering = "";
foreach (AField field in qbl.getFieldList())
{
string wFieldstr;
if((field.owner == null) && (qbl.getSourceList().Count == 1))
wFieldstr = field.name;
else
//if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).Name != field.owner)
wFieldstr = field.owner + "." + field.name;
//else
// wFieldstr = field.name;
if (field.function != 0)
{
if (field.function == ABSTRACTAGGREGATE.Count)
wFieldstr = "Count(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Avg)
wFieldstr = "Avg(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.DistinctCount)
wFieldstr = "Count(Distinct " + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Max)
wFieldstr = "Max(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Min)
wFieldstr = "Min(" + wFieldstr + ")";
else if (field.function == ABSTRACTAGGREGATE.Sum)
wFieldstr = "Sum(" + wFieldstr + ")";
}
wSql += wFieldstr;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wSql += ", ";
if (field.GroupBy == true)
{
wGrouping += " " + field.owner + "." + field.name;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wGrouping += ", ";
}
if (field.OrderBy != ABSTRACTORDERTYPE.None)
{
string fStatMode = (field.OrderBy == ABSTRACTORDERTYPE.Ascending) ? "asc" : "desc";
string wFieldOrder = "";
if ((field.owner == null) && (qbl.getSourceList().Count == 1))
wFieldOrder = field.name;
else
if (((ATable)(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject)).name != field.owner)
wFieldOrder = field.owner + "." + field.name;
else
wFieldOrder = field.name;
wOrdering += " " + wFieldOrder + " " + fStatMode;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1])
wOrdering += ", ";
}
}
wSql += " from ";
// sources
foreach (QueryBuilder.SOURCEBINDING src in qbl.getSourceList())
{
if (src.bindType == 0)
{
// This is our main table name
wSql += ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
wSql += " " + ((ATable)src.sourceObject).alias;
}
else if (src.bindType == ABSTRACTSOURCEBINDTYPES.INNERJOIN)
{
wSql += " INNER JOIN " + ((ATable)src.sourceObject).name;
if ((((ATable)src.sourceObject).alias != null) && (((ATable)src.sourceObject).alias.Length > 0))
{
wSql += " " + ((ATable)src.sourceObject).alias;
wSql += " on " + ((ATable)src.boundSource).alias + "." + src.srcMatchField + "=" + ((ATable)src.sourceObject).alias + "." + src.dstMatchField + " ";
}
else
{
wSql += " on " + ((ATable)src.boundSource).name + "." + src.srcMatchField + "=" + ((ATable)src.sourceObject).name + "." + src.dstMatchField + " ";
}
}
}
// conditional
if(qbl.conditionalString != null)
if (qbl.conditionalString.Replace(" ", "").ToLower().IndexOf("=null") > 0)
{
qbl.conditionalString = qbl.conditionalString.Replace("=", " IS ");
}
if((qbl.conditionalString != null) && (qbl.conditionalString.Length > 0))
wSql += " where " + qbl.conditionalString + " ";
// group by
if (wGrouping.Trim() != "")
{
wGrouping = wGrouping.Trim();
if (wGrouping[wGrouping.Length-1].Equals(',') == true)
wGrouping = wGrouping.Substring(0, wGrouping.Length - 1);
wSql += " group by " + wGrouping;
}
if (wOrdering.Trim() != "")
{
wOrdering = wOrdering.Trim();
if (wOrdering[wOrdering.Length - 1].Equals(',') == true)
wOrdering = wOrdering.Substring(0, wOrdering.Length - 1);
wSql += " order by " + wOrdering;
}
if (qbl.QueryLimit.lStart > -1)
{
wSql += " limit " + qbl.QueryLimit.lStart + "," + qbl.QueryLimit.lLimit;
}
return wSql;
}
public override string buildInsertStatement(QueryBuilder qbl)
{
string tblname = (Convert.ToString(((QueryBuilder.SOURCEBINDING)qbl.getSourceList()[0]).sourceObject));
ATable tblObj = qbl.getDatabaseObject().GetTableCache().getCachedTable(tblname);
string wSql = "insert into " + tblname + " ";
string fieldList = "";
string valueList = "";
ABSTRACTDATATYPES fieldType;
// If our chosen table object does not have an autoincrementing primary key/id field
// we can check for that here.
// Build up field list and value list.
foreach (AField field in qbl.getFieldList())
{
fieldList += field.name;
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) fieldList += ",";
fieldType = tblObj.getFieldByName(field.name).type;
if (fieldType == ABSTRACTDATATYPES.ABool)
{
if (((string)field.value == "true") || ((bool)field.value == true) || ((int)field.value == 1) || ((string)field.value == "1") || ((string)field.value == "yes"))
{
valueList += "1";
}
}
else if (fieldType == ABSTRACTDATATYPES.AForeignTable)
{
// This is tricky, we have to look up the value type from the
// referencing table's file.
}
else if (fieldType == ABSTRACTDATATYPES.ASmallInteger)
{
// Deal with blank values and default val...
if (field.defaultval == null)
{
if (field.hasModifier(ABSTRACTFIELDMODIFIERS.NotNull))
field.defaultval = 0;
else
field.defaultval = "NULL";
}
if (field.value == null || Convert.ToString(field.value) == "")
field.value = field.defaultval;
valueList += field.value;
}
else if (fieldType == ABSTRACTDATATYPES.AString)
{
valueList += "'" + field.value + "'";
}
else if (fieldType == ABSTRACTDATATYPES.ADateTime)
{
if (Convert.ToString(field.value).ToLower().Trim() == "now()")
{
valueList += field.value;
}
else
{
valueList += "'" + field.value + "'";
}
}
else
{
if (field.value == null || Convert.ToString(field.value)== "")
field.value = field.defaultval;
valueList += field.value;
}
if (field != qbl.getFieldList()[qbl.getFieldList().Count - 1]) valueList += ",";
}
wSql += "(";
wSql += fieldList;
wSql += ") VALUES (";
wSql += valueList;
wSql += ");";
return wSql;
}
