private string getOrderBy(IBaseDataSpecial cfg)
{
if (_sort == null)
{
return(string.Empty);
}
string ret = string.Empty;
EM_SortKeyWord s;
if (_sort.Keys.Count == 0)
{
return(string.Empty);
}
foreach (string key in _sort.Keys)
{
s = _sort[key];
if (s == EM_SortKeyWord.asc)
{
ret += string.Format(",{0} desc", cfg.FieldEncode(key));
}
else
{
ret += string.Format(",{0} asc", cfg.FieldEncode(key));
}
}
return(ret.Substring(1));
}
public string ToString(IBaseDataSpecial cfg)
{
StringBuilder sql = new StringBuilder();
IUpatedataInfo _row = this.FilterRow;
foreach (string f in _row.GetUpateFields())
{
object v = _row.GetValueByName(f);
if (v == null || v == DBNull.Value)
{
switch (cfg.FieldMask)
{
case EM_Safe_Field_MASK.MySQLMode:
sql.AppendFormat(" and isNull(`{0}`)=1", f); continue;
case EM_Safe_Field_MASK.MsSQLMode:
sql.AppendFormat(" and [{0}] is null", f); continue;
default:
throw new Exception("未知的编码方式:" + cfg.FieldMask.ToString());
}
}
if (v is int)
{
sql.AppendFormat(" and {0}={1}", cfg.FieldEncode(f), v);
}
else
{
sql.AppendFormat(" and {0}='{1}'", cfg.FieldEncode(f), cfg.SqlEncode(v.ToString()));
}
}
if (_andFilter != null && _andFilter.Count != 0)
{
sql.AppendFormat(" and {0}", DataFilter.FiltersToString(_andFilter, "and", cfg));
}
if (_orFilter != null && _orFilter.Count != 0)
{
sql.AppendFormat("\n or {0}", DataFilter.FiltersToString(_orFilter, "or", cfg));
}
if (sql.Length > 4)
{
string h = sql.ToString().Substring(0, 4);
if (h == " and" || h == "\n or")
{
sql.Remove(0, 4);
}
}
return(sql.ToString());
}
public override string ToString(IBaseDataSpecial cfg)
{
string sql;
if (_max > 0 && cfg.PageMode == List.EM_PAGE_Mode.ByTop)
{
sql = string.Format("select top {2} {0} from {1}",
cfg.FieldEncode(_idField), cfg.FieldEncode(_tableName), _max);
}
else
{
sql = string.Format("select {0} from {1}",
cfg.FieldEncode(_idField), cfg.FieldEncode(_tableName));
}
if (_tableFilters != null && _tableFilters.HasFilter)
{
sql += " where " + _tableFilters.ToString(cfg);
}
if (_max > 0 && _sort != null && _sort.Count > 0)
{
sql += " order by " + getOrderBy(cfg);
}
string ret;
if (Operator == EM_DataFiler_Operator.Not_In)
{
ret = "{0} not in ({1})";
}
else if (Operator == EM_DataFiler_Operator.Equal)
{
ret = "{0} = ({1})";
}
else
{
ret = "{0} in ({1})";
}
ret = string.Format(ret, cfg.FieldEncode(this.FieldName), sql);
if (this.AndFilters.Count != 0)
{
ret += "\n and " + FiltersToString(this.AndFilters, "and", cfg);
}
if (this.OrFilters.Count != 0)
{
ret = "(" + ret + "\n or " + FiltersToString(this.OrFilters, "or", cfg) + ")";
}
return(ret);
}
public virtual string ToString(IBaseDataSpecial cfg)
{
StringBuilder ret = new StringBuilder();
string value;
if (_value is int || _value is long || _value is short || _value is byte)
{
value = _value.ToString();
}
else if (_value is bool)
{
value = (bool)_value ? "1" : "0";
}
else if (_value is Enum)
{
value = ((Enum)_value).ToString("D");
}
else if (_value is DateTime)
{
value = "'" + _value.ToString() + "'";
}
else if (_value is IEnumerable <int> ||
_value is IEnumerable <string> )
{
StringBuilder t = new StringBuilder();
int arrType = 0;
if (_value is IEnumerable <Enum> )
{
arrType = 2;
}
else if (_value is IEnumerable <int> )
{
arrType = 1;
}
else if (_value is string[])
{
arrType = 3;
}
foreach (object s in (System.Collections.IEnumerable)_value)
{
switch (arrType)
{
case 0: t.AppendFormat(",'{0}'", s); break; //unkonw
case 1: t.AppendFormat(",{0}", s); break; //digi
case 2: t.AppendFormat(",{0}", (int)s); break; //enum
case 3: t.AppendFormat(",'{0}'", cfg.SqlEncode((string)s)); break; //string
default: throw new Exception("未知数据类别");
}
}
if (t.Length == 0)
{
value = string.Empty;
}
else
{
t.Remove(0, 1);
value = t.ToString();
}
}
else
{
if (_value == null)
{
value = "''";
}
else
{
value = "'" + cfg.SqlEncode(_value.ToString()) + "'";
}
}
string op;
switch (_operator)
{
case EM_DataFiler_Operator.Equal: op = "="; break;
case EM_DataFiler_Operator.In: op = "in"; break;
case EM_DataFiler_Operator.Less: op = "<"; break;
case EM_DataFiler_Operator.LessOrEqual: op = "<="; break;
case EM_DataFiler_Operator.Like: op = "like"; break;
case EM_DataFiler_Operator.More: op = ">"; break;
case EM_DataFiler_Operator.MoreOrEqual: op = ">="; break;
case EM_DataFiler_Operator.Not_In: op = "not in"; break;
case EM_DataFiler_Operator.Not_Equal: op = "<>"; break;
default:
throw new Exception("未知操作符！");
}
if (NullToValue != null)
{
if (_operator == EM_DataFiler_Operator.In || _operator == EM_DataFiler_Operator.Not_In)
{
ret.AppendFormat("{0} {1} ({2})", cfg.funcNullToValue(_field, NullToValue), op, value);
}
else
{
ret.AppendFormat("{0} {1} {2}", cfg.funcNullToValue(_field, NullToValue), op, value);
}
}
else
{
if (_operator == EM_DataFiler_Operator.In || _operator == EM_DataFiler_Operator.Not_In)
{
ret.AppendFormat("{0} {1} ({2})", cfg.FieldEncode(_field), op, value);
}
else
{
ret.AppendFormat("{0} {1} {2}", cfg.FieldEncode(_field), op, value);
}
}
if (_andFilter != null && _andFilter.Count != 0)
{
ret.Append("\n and ");
ret.Append(FiltersToString(_andFilter, "and", cfg));
}
if (_orFilter != null && _orFilter.Count != 0)
{
ret.Append("\n or ");
ret.Append(FiltersToString(_orFilter, "or", cfg));
ret.Insert(0, "(");
ret.Append(")");
}
return(ret.ToString());
}
public virtual T GetDataList()
{
if (dBase == null)
{
throw new NullReferenceException("数据库对像不能空");
}
string sql = string.Empty;
string sqlWhere;
int start = 0, SelCount = 0;
//正序排法
string sqlOrder = string.Empty;
//反序排法
string sqlOrderRev = string.Empty;
sqlWhere = getWhereString();
string safeTableName = dbCfg.FieldEncode(this.TableName);
if (CurrentPage <= 1)
{//调首页
if (this.PageSize == int.MaxValue)
{
sql = string.Format("select {0} from {1} {2} {3}",
getFieldsString(), safeTableName, sqlWhere, getOrderBy(false));
}
else
{
switch (dbCfg.PageMode)
{
case EM_PAGE_Mode.ByLimit:
sql = string.Format("select {0} from {1} {2} {3} limit {4}",
getFieldsString(), safeTableName, sqlWhere, getOrderBy(false), this.PageSize);
break;
case EM_PAGE_Mode.ByTop:
sql = string.Format("select top {4} {0} from {1} {2} {3}",
getFieldsString(), safeTableName, sqlWhere, getOrderBy(false), this.PageSize);
break;
default:
throw new Exception("未知的分页方式:" + dbCfg.PageMode.ToString());
}
}
}
else
{//分页调取(初始化）
start = (CurrentPage - 1) * PageSize;
SelCount = TotalCount - start;
if (SelCount <= 0)
{
sql = string.Format("select {0} from {1} where 1=0 ", getFieldsString(), safeTableName);
}
if (SelCount > PageSize)
{
SelCount = PageSize;
}
sqlWhere = this.getWhereString();
sqlOrder = this.getOrderBy(false);
sqlOrderRev = this.getOrderBy(true);
}
if (string.IsNullOrEmpty(sql))
{//分面调取（实现）
switch (dbCfg.PageMode)
{
case EM_PAGE_Mode.ByLimit:
sql = string.Format("select {0} from {1} {2} {3} limit {4},{5}",
getFieldsString(), safeTableName, sqlWhere, getOrderBy(false), start, this.PageSize);
break;
case EM_PAGE_Mode.ByTop:
sql = "select " + getFieldsString() + " from " + safeTableName + " where [" + this.IdentityField + "] in (\n" +
"select top " + SelCount.ToString() + " " + dbCfg.FieldEncode(IdentityField) + " from (\n" +
"select top " + (start + SelCount).ToString() + " " + this.getOrderField() +
" from " + dbCfg.FieldEncode(this.TableName) + " \n" + sqlWhere + "\n " + sqlOrder + " ) as t \n" +
sqlOrderRev + ") \n" + sqlOrder;
break;
}
}
T dt = new T();
if (dt.Columns.Count != 0 && _fields != null && _fields.Length != 0)
{
ClearEmptyColume(dt);
}
_lastSql = sql;
_dBase.TableFill(sql, dt);
return(dt);
}