public DataTable LoadDataTableByConditions(CommonSqlKey sqlKey, IList <Condition> parmObj)
{
var logStep = 0;
try
{
if (sqlKey == CommonSqlKey.Null)
{
return(null);
}
var sqlTxt = CommSqlText.Instance[sqlKey];
logStep++;
var parmDic = SqlConstructor.MakeParms(parmObj);
List <DbParameter> parameter;
var whereSql = ConditionHandler.GetWhereSql(parmObj.ToList(), out parameter, sqlKey);
sqlTxt = SqlConstructor.FilterSQLWithInsteadValue(sqlTxt + whereSql, parmDic);
logStep++;
parmDic = SqlConstructor.FilterParmsWithList(parmDic, CommSqlText.SqlParms[sqlKey]);
logStep++;
return(DbHelper.ExecuteDataTable(sqlTxt, parmDic));
}
catch (MySqlException ee)
{
Logger.LogInfo(String.Format("GetSingleFromDicDictionary ERROR STEP:{0}, EXCEPTION:{1}", logStep, ee.Message), 0, LogType.ERROR);
throw ee;
}
}
public int CountByConditions(CommonSqlKey sqlKey, IList <Condition> parmObj)
{
var result = 0;
var parmDic = SqlConstructor.MakeParms(parmObj);
var sqlTxt = CommSqlText.Instance[sqlKey];
var sqlParameters = parmDic;
//解析where 后查询条件
List <DbParameter> parameter;
var whereSql = ConditionHandler.GetWhereSql(parmObj.ToList(), out parameter, sqlKey);
sqlTxt = sqlTxt + whereSql;
result = DbHelper.ExecuteScalar(sqlTxt, sqlParameters) == null
? 0
: int.Parse(DbHelper.ExecuteScalar(sqlTxt, sqlParameters).ToString());
return(result);
}
private List <T> GetFromDictionaryByConditionsWithOrder <T>(CommonSqlKey sqlKey, IDictionary <string, object> parmDic, IList <Condition> conditions, string orderField = "", string orderType = "")
{
DbDataReader dr = null;
DbConnection dbConn = null;
var tLst = new List <T>();
var logStep = 0;
try
{
var sqlTxt = CommSqlText.Instance[sqlKey];
logStep++;
var sqlParameters = parmDic;
//解析where 后查询条件
List <DbParameter> parameter;
var whereSql = ConditionHandler.GetWhereSql(conditions.ToList(), out parameter, sqlKey, orderField, orderType);
sqlTxt = sqlTxt + whereSql;
logStep++;
logStep++;
dr = DbHelper.ExecuteReader(sqlTxt, sqlParameters, ref dbConn);
logStep = 100;
if (dr == null)
{
return(tLst);
}
var hasCount = dr.Read();
while (hasCount)
{
logStep += 10;
var t = MakeMapToObject <T>(dr);
if (t != null)
{
tLst.Add(t);
}
logStep += 1;
hasCount = dr.Read();
}
}
catch (MySqlException ee)
{
Logger.LogInfo(String.Format("GetFromDictionary ERROR STEP:{0}, EXCEPTION:{1}", logStep, ee.Message), 0, LogType.ERROR);
throw ee;
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
}
if (dbConn != null)
{
dbConn.Close();
dbConn.Dispose();
}
}
return(tLst);
}