public int EntityCount(string _strFilter)
{
string strEntityName = this._activeEntity.TableName;
if (ListCache.IsExistCount(strEntityName, _strFilter))
{
return ListCache.GetCount(strEntityName, _strFilter);
}
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Select;
query.SqlTableName = strEntityName;
query.SqlContent = "COUNT(1)";
query.SqlWhereString = _strFilter;
int nCount = (int)HDBOperation.QueryScalar(query.ToString());
ListCache.SetCount(strEntityName, _strFilter, nCount);
return nCount;
}
public int EntityDelete()
{
bool IsEntity = this._activeEntity.ActiveObject is IEntity;
if (!IsEntity || string.IsNullOrEmpty(this._activeEntity.KeyName))
return -1;
int nKeyValue = TypeUtil.ParseInt(this._activeEntity.KeyValue + "", -1);
if (nKeyValue <= 0)
return -1;
string strEntityName = this._activeEntity.TableName;
string strUpdateKey = string.Format("[{0}]={1}", this._activeEntity.KeyName, this._activeEntity.KeyValue);
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Delete;
query.SqlTableName = strEntityName;
query.SqlWhereString = strUpdateKey;
HDBOperation.QueryNonQuery(query.ToString());
//清除表缓存
ListCache.Clear(strEntityName);
//清除实体缓存
EntityCache.Clear(strEntityName, nKeyValue);
return nKeyValue;
}
public int GetNextKey()
{
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Select;
query.SqlTableName = this._activeEntity.TableName;
query.SqlContent = string.Format("Max([{0}])", this._activeEntity.KeyName);
query.SqlWhereString = "";
object oValue = HDBOperation.QueryScalar(query.ToString());
return TypeUtil.ParseInt(oValue + "", 0) + 1;
}
public int EntitySave()
{
bool IsEntity = this._activeEntity.ActiveObject is IEntity;
string strKeyName = this._activeEntity.KeyName;
if (!IsEntity || string.IsNullOrEmpty(strKeyName))
return -1;
int nKeyValue = TypeUtil.ParseInt(this._activeEntity.KeyValue + "", -1);
int nCount = this._activeEntity.ObjectFields.Length;
if (nCount == 0)
return -1;
if (nKeyValue <= 0)
{
nKeyValue = GetNextKey();
List<string> ltInsertField = new List<string>();
List<string> ltInsertValue = new List<string>();
for (int i = 0; i < nCount; i++)
{
FieldInfo oF = this._activeEntity.ObjectFields[i];
string strFieldName = oF.Name;
if (strFieldName != strKeyName)
{
object oValue = oF.GetValue(this._activeEntity.ActiveObject);
ltInsertValue.Add(FormatSetValue(oValue, oF));
}
else
{
ltInsertValue.Add(nKeyValue.ToString());
oF.SetValue(this._activeEntity.ActiveObject, nKeyValue);
}
ltInsertField.Add(string.Format("[{0}]", strFieldName));
}
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Insert;
query.SqlTableName = this._activeEntity.TableName;
query.SqlContent = string.Join(",", ltInsertField.ToArray());
query.SqlWhereString = string.Join(",", ltInsertValue.ToArray());
try
{
HDBOperation.QueryNonQuery(query.ToString());
}
catch (Exception ee)
{
return -1;
}
}
else
{
List<string> ltUpdateValue = new List<string>();
for (int i = 0; i < nCount; i++)
{
FieldInfo oF = this._activeEntity.ObjectFields[i];
string strName = oF.Name;
if (strName == strKeyName)
continue;
object oValue = oF.GetValue(this._activeEntity.ActiveObject);
ltUpdateValue.Add(string.Format("[{0}]={1}", strName, FormatSetValue(oValue, oF)));
}
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Update;
query.SqlTableName = this._activeEntity.TableName;
query.SqlWhereString = string.Format("{0}={1}", strKeyName, nKeyValue);
query.SqlContent = string.Join(",", ltUpdateValue.ToArray());
try
{
HDBOperation.QueryNonQuery(query.ToString());
}
catch (Exception ee)
{
return -1;
}
}
//清除表缓存
ListCache.Clear(this._activeEntity.TableName);
//添加实体缓存
EntityCache.SetEntity(this._activeEntity.TableName, nKeyValue, this._activeEntity.ActiveObject);
//添加缓存(缓存的存储格式和EntityList的缓存格式相同，以防当出现"KeyName=KeyValue"类型的条件时两个方法可以存储相同的缓存)
//string strCacheKey = string.Format("[{0}]={1}", strKeyName ,nKeyValue);
//ArrayList alCaches = new ArrayList();
//alCaches.Add(this._activeEntity.ActiveObject);
//CacheBase.SetValue(strCacheKey, alCaches.ToArray(this._activeEntity.ObjectType));
return nKeyValue;
}
public object[] EntityList(string _strFilter, string _strSort, int _nPageIndex, int _nPageSize)
{
string strCacheFilter = string.Format("{0}-{1}-{2}", _strFilter, _nPageIndex, _nPageSize);
string strEntityName = this._activeEntity.TableName;
if (ListCache.IsExistList(strEntityName, strCacheFilter))
{
return (object[])ListCache.GetList(strEntityName, strCacheFilter);
}
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Select;
query.SqlTableName = strEntityName;
if (!string.IsNullOrEmpty(_strFilter))
_strFilter = string.Format("({0})", _strFilter);
if (_nPageIndex == 1)
{
#region 如果查询第一页的数据采用top方式，提高效率
query.SqlContent = string.Format("top {0} {1}", _nPageSize, this._activeEntity.SelectContent);
if (!string.IsNullOrEmpty(_strFilter))
query.SqlWhereString = _strFilter;
if (!string.IsNullOrEmpty(_strSort))
query.SqlWhereString += " order by " + _strSort;
#endregion
}
else if (_nPageIndex * _nPageSize <= 1000000)
{
#region 分页方案(not in),在百万条数据内效率高
query.SqlContent = string.Format("top {0} {1}", _nPageSize, this._activeEntity.SelectContent);
if (!string.IsNullOrEmpty(_strFilter))
query.SqlWhereString += _strFilter + " and ";
query.SqlWhereString += string.Format("{0} not in (select top (({1} - 1) * {2}) {0} from {3}{4} order by {0})", this._activeEntity.KeyName, _nPageIndex, _nPageSize, strEntityName, string.IsNullOrEmpty(_strFilter) ? "" : (" where " + _strFilter));
if (!string.IsNullOrEmpty(_strSort))
query.SqlWhereString += " order by " + _strSort;
#endregion
}
else
{
#region 分页方案(ROW_NUMBER()),在百万条数据以上效率高
query.SqlContent = string.Format("{0} from (select ROW_NUMBER() over(order by {1}) as nRow,{0}", this._activeEntity.SelectContent, this._activeEntity.KeyName);
if (!string.IsNullOrEmpty(_strFilter))
query.SqlWhereString += string.Format("where {0} ", _strFilter);
query.SqlWhereString += string.Format(") xTemp where nRow >= (({0} - 1) * {1} + 1) and nRow <= ({0}*{1})", _nPageIndex, _nPageSize);
if (!string.IsNullOrEmpty(_strSort))
query.SqlWhereString += " order by " + _strSort;
#endregion
}
DataSet ds = HDBOperation.QueryDataSet(query.ToString());
object[] oList = ChangeTableToEntitys(this, ds.Tables[0]);
ListCache.SetList(strEntityName, strCacheFilter, oList);
return oList;
}
public object[] EntityList(string _strFilter)
{
string strEntityName = this._activeEntity.TableName;
if (ListCache.IsExistList(strEntityName, _strFilter))
{
return (object[])ListCache.GetList(strEntityName, _strFilter);
}
HSqlFactory query = new HSqlFactory();
query.SqlType = HSqlType.Select;
query.SqlTableName = strEntityName;
query.SqlContent = this._activeEntity.SelectContent;
query.SqlWhereString = _strFilter;
DataSet ds = HDBOperation.QueryDataSet(query.ToString());
object[] oList = ChangeTableToEntitys(this, ds.Tables[0]);
ListCache.SetList(strEntityName, _strFilter, oList);
return oList;
}
