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);
}
public override DeleteAction Where(TableFilter filter)
{
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);
}
}
base.MainSql = string.Format("delete from {0} {1}", base.TableCode, where);
return(this);
}
//public QueryAction LeftJoin(string table)
//{
// this.joinTables.Add(new JoinTable(table, EJoinType.Left));
// return this;
//}
//public QueryAction RightJoin(string table)
//{
// this.joinTables.Add(new JoinTable(table, EJoinType.Right));
// return this;
//}
//public QueryAction InnerJoin(string table)
//{
// this.joinTables.Add(new JoinTable(table, EJoinType.Inner));
// return this;
//}
public override QueryAction Where(TableFilter filter)
{
if (filter != null)
{
filter.MainTable = base.TableCode;
}
string joinCondition = string.Empty, filterCondition = string.Empty, tmpField = string.Empty, where = string.Empty;
if (string.IsNullOrWhiteSpace(this.fixField))
{
throw new Exception("no field");
}
#region tables
joinCondition = base.TableCode;
foreach (var table in joinTables)
{
var tmpJoin = string.Empty;
foreach (var item in table.Conditions)
{
tmpJoin = string.Format("{0}={1}", item.Key.IndexOf('.') == -1 ? (base.TableCode + "." + item.Key) : item.Key,
item.Value.IndexOf('.') == -1 ? (table.Table + "." + item.Value) : item.Value);
}
joinCondition += string.Format(" {0} join {1} on {2} ", table.JoinType.ToString(), table.Table, tmpJoin);
}
#endregion
#region field
foreach (var col in this.fixField.Split(','))
{
if (col.IndexOf(".") == -1)
{
tmpField += string.Format("{0}.{1},", base.TableCode, col);
}
else if (col.IndexOf(".") > -1 && col.IndexOf("*") > -1)
{
tmpField += string.Format("{0},", col);
}
else
{
var tmpArray = col.Split('.');
if (string.Compare(tmpArray[0], base.TableCode, true) == 0)
{
tmpField += string.Format("{0}.{1} as {1},", tmpArray[0], tmpArray[1]);
}
else
{
tmpField += string.Format("{0}.{1} as {0}{1},", tmpArray[0], tmpArray[1]);
}
}
}
#endregion
#region where
if (filter != null)
{
filterCondition = filter.Build(base.ParamCollection);
where = string.Format(" {0} ", filterCondition);
}
else
{
where = " 1=1 ";
}
#endregion
string sql = string.Empty;
#region paging
if (this.pageIndex > 0 && this.pageSize > 0)
{
if (string.IsNullOrWhiteSpace(this.sortField))
{
this.sortField = string.Format("{0}.{1} asc", base.TableCode, this.defaultField);
}
var start = (this.pageIndex - 1) * this.pageSize + 1;
var end = this.pageSize * this.pageIndex;
sql = base.DbContext.GetPagingFormat(new QueryModel()
{
Fields = tmpField.TrimEnd(','),
Tables = joinCondition,
Where = where,
SortField = this.sortField,
Start = start,
End = end
});
base.SecondarySql = string.Format("select count(1) from {0} where {1}", joinCondition, where);
}
else
{
sql = string.Format("select {0} from {1} where {2} {3}", tmpField.TrimEnd(','), joinCondition, where,
!string.IsNullOrWhiteSpace(this.sortField) ? ("order by " + this.sortField) : string.Empty);
}
#endregion
base.MainSql = sql;
return(this);
}