public int DeleteData()
{
int ret = 0;
if (deleteCondSqlc != null)
{
deleteCondSql = new SqlStr();
deleteCondSql.sql = deleteCondSqlc;
}
if (deleteCondObj != null)
{
ret = DeleteData(deleteCondObj);
}
else
{
ret = DeleteData(deleteCondSql);
}
deleteCondSqlc = null;
deleteCondSql = null;
deleteCondObj = default(T);
return(ret);
}
public DataTable QueryData()
{
DataTable dt;
if (condSqlc != null)
{
condSql = new SqlStr();
condSql.sql = condSqlc;
}
if (joinSqlc != null)
{
joinSql = new SqlStr();
joinSql.sql = joinSqlc;
}
if (condObj != null)
{
dt = QueryData(searchType, fieldSql, condObj, excludeField, condSql, joinSql, startIdx, endIdx, sortFieldSql);
}
else
{
dt = QueryData(fieldSql, condSql, joinSql, startIdx, endIdx, sortFieldSql);
}
ResetSqlParams();
return(dt);
}
public int UpdateData(T updateObj, SqlStr condSql)
{
SqlStr[] condSqls = null;
if (condSql != null)
{
condSqls = new SqlStr[] { condSql }
}
;
return(UpdateData(updateObj, condSqls));
}
public int DeleteData(SqlStr condSql)
{
SqlStr[] sqls = null;
if (condSql != null)
{
sqls = new SqlStr[] { condSql }
}
;
return(DeleteData(sqls));
}
private void ResetSqlParams()
{
searchType = PreciseOrFuzzyType.Fuzzy;
condObj = default(T);
excludeField = null;
fieldSql = null;
condSql = null;
condSqlc = null;
joinSql = null;
joinSqlc = null;
startIdx = -1;
endIdx = -1;
sortFieldSql = null;
}
/// <summary>
/// 查询语句，支持分页，连接，条件查询
/// </summary>
/// <param name="fieldSqls">字段语句(ex1: column_name1,NAME,SEX // ex2: COUNT(column_name) )</param>
/// <param name="condSql">条件语句(ex: ID = 'xxxx' and NAME = 'n')</param>
/// <param name="joinSql">连接语句（ex: LEFT JOIN TABLE2 ON TABLE1.ID=TABLE2.ID ）</param>
/// <param name="startIdx">条目起始位置</param>
/// <param name="endIdx">条目结束位置</param>
/// <param name="sortFieldSql">所有条目根据指定字段排序(ex: ID asc)</param>
/// <returns></returns>
public DataTable QueryData(
string fieldSql,
SqlStr condSql = null,
SqlStr joinSql = null,
int startIdx = -1, int endIdx = -1, string sortFieldSql = null)
{
SqlStr[] sqls = null;
if (condSql != null)
{
sqls = new SqlStr[] { condSql }
}
;
return(QueryData(fieldSql, sqls, joinSql, startIdx, endIdx, sortFieldSql));
}
private DataTable QueryData(
string fieldSql,
SqlStr[] condSql = null,
SqlStr joinSql = null,
int startIdx = -1, int endIdx = -1, string sortFieldSql = null)
{
string cond = "";
string join = "";
List <object> condParams = new List <object>();
List <object> joinParams = new List <object>();
if (condSql != null)
{
for (int i = 0; i < condSql.Length; i++)
{
if (!string.IsNullOrWhiteSpace(cond))
{
cond += " " + condSql[i].logicalRelation + " ";
}
cond += "(" + condSql[i].sql + ")";
if (condSql[i].sqlParams != null)
{
for (int j = 0; j < condSql[i].sqlParams.Length; j++)
{
condParams.Add(condSql[i].sqlParams[j]);
}
}
}
}
if (joinSql != null)
{
join = joinSql.sql;
if (joinSql.sqlParams != null)
{
for (int j = 0; j < joinSql.sqlParams.Length; j++)
{
joinParams.Add(joinSql.sqlParams[j]);
}
}
}
return(QueryData(fieldSql, cond, condParams.ToArray(), join, joinParams.ToArray(), startIdx, endIdx, sortFieldSql));
}
/// <summary>
/// 查询语句，支持分页，连接，条件查询
/// </summary>
/// <param name="type">查询类型，模糊或精确查询</param>
/// <param name="fieldSqls">字段语句(ex1: column_name1,NAME,SEX // ex2: COUNT(column_name) )</param>
/// <param name="condObj">对应表字段的数据模型条件</param>
/// <param name="excludeField">在条件模型中排除一些字段，不按照指定的查询类型查询</param>
/// <param name="condSql">条件语句(ex: ID = 'xxxx' and NAME = 'n')</param>
/// <param name="joinSql">连接语句（ex: LEFT JOIN TABLE2 ON TABLE1.ID=TABLE2.ID ）</param>
/// <param name="startIdx">条目起始位置</param>
/// <param name="endIdx">条目结束位置</param>
/// <param name="sortFieldSql">所有条目根据指定字段排序(ex: ID asc)</param>
/// <returns></returns>
/// <returns></returns>
public DataTable QueryData(
PreciseOrFuzzyType type,
string fieldSql,
T condObj, string excludeField,
SqlStr condSql = null,
SqlStr joinSql = null,
int startIdx = -1, int endIdx = -1, string sortFieldSql = null)
{
SqlStr[] sqls = null;
if (condSql != null)
{
sqls = new SqlStr[] { condSql }
}
;
return(QueryData(type, fieldSql, condObj, excludeField, sqls, joinSql, startIdx, endIdx, sortFieldSql));
}
public int UpdateData()
{
int ret = 0;
if (updateCondSqlc != null)
{
updateCondSql = new SqlStr();
updateCondSql.sql = updateCondSqlc;
}
if (updateObj != null)
{
ret = UpdateData(updateObj, updateCondSql);
}
ResetUpdateSqlParams();
return(ret);
}
public DataTable QueryData(
PreciseOrFuzzyType type,
string fieldSql,
T condObj, string excludeField,
SqlStr[] condSqls = null,
SqlStr joinSql = null,
int startIdx = -1, int endIdx = -1, string sortFieldSql = null)
{
List <SqlStr> condSqlList = new List <SqlStr>();
SqlStr[] newCondSqls = CreateCondObjectToSql(condObj, excludeField, type);
if (newCondSqls != null && newCondSqls.Length > 0)
{
foreach (SqlStr condSql in newCondSqls)
{
if (condSql != null)
{
condSqlList.Add(condSql);
}
}
}
if (condSqls != null && condSqls.Length > 0)
{
foreach (SqlStr condSql in condSqls)
{
if (condSql != null)
{
condSqlList.Add(condSql);
}
}
}
return(QueryData(fieldSql, condSqlList.ToArray(), joinSql, startIdx, endIdx, sortFieldSql));
}
/// <summary>
/// 生成精确或模糊SQL条件语句
/// </summary>
/// <param name="condObj"></param>
/// <param name="excludeFields">个别字段相反的查询模式</param>
/// <param name="preciseOrFuzzy">生成精确或模糊SQL条件语句 0：精确， 1：模糊</param>
/// <returns></returns>
private SqlStr[] CreateCondObjectToSql(T condObj, string excludeField, PreciseOrFuzzyType preciseOrFuzzy)
{
if (condObj == null)
{
return(null);
}
string field;
Type type = condObj.GetType(); //获取类型
FieldInfo fieldInfo;
object propValue;
SqlStr additionalSql;
List <SqlStr> additionalSqlList = new List <SqlStr>();
bool isExclude = false;
string[] excludeFields = null;
if (excludeField != null)
{
excludeFields = excludeField.Split(',');
}
for (int i = 0; i < fields.Length; i++)
{
field = fields[i];
fieldInfo = type.GetField(field); //获取指定名称的属性
propValue = fieldInfo.GetValue(condObj);
//判断当前查询字段是否在模糊字段列表中
if (excludeFields != null && excludeFields.Length > 0)
{
for (int j = 0; j < excludeField.Length; j++)
{
if (field.Equals(excludeField[j]))
{
isExclude = true;
break;
}
}
}
if (isExclude) //被排除在查询模式之外
{
if (preciseOrFuzzy == PreciseOrFuzzyType.Precise) //精确查询类型
{
string cond = IsUsedFuzzy(fieldInfo, propValue, field);
if (cond != null)
{
additionalSql = new SqlStr();
additionalSql.sql = cond;
additionalSqlList.Add(additionalSql);
cond = null;
}
}
else //模糊查询类型
{
if (IsUsedPrecise(fieldInfo, propValue, field))
{
additionalSql = new SqlStr();
additionalSql.sql = field + " =@" + field;
additionalSql.sqlParams = new object[1] {
propValue
};
additionalSqlList.Add(additionalSql);
}
}
isExclude = false;
}
else //未被排除
{
if (preciseOrFuzzy == PreciseOrFuzzyType.Precise) //精确查询类型
{
if (IsUsedPrecise(fieldInfo, propValue, field))
{
additionalSql = new SqlStr();
additionalSql.sql = field + " =@" + field;
additionalSql.sqlParams = new object[1] {
propValue
};
additionalSqlList.Add(additionalSql);
}
}
else
{
string cond = IsUsedFuzzy(fieldInfo, propValue, field);
if (cond != null)
{
additionalSql = new SqlStr();
additionalSql.sql = cond;
additionalSqlList.Add(additionalSql);
cond = null;
}
}
}
}
return(additionalSqlList.ToArray());
}
private void ResetUpdateSqlParams()
{
updateObj = default(T);
updateCondSql = null;
updateCondSqlc = null;
}