public List <T> Load <T>(T parm)
{
var sqlTxt = SqlConstructor.GetSelectSqlByObj(parm);
var tLst = new List <T>();
DbConnection dbConn = null;
try
{
var dr = DbHelper.ExecuteReader(sqlTxt, ref dbConn);
while (dr.Read())
{
var t = MakeMapToObject <T>(dr);
if (t != null)
{
tLst.Add(t);
}
}
}
catch (Exception e)
{
}
finally
{
if (dbConn != null)
{
dbConn.Close();
dbConn.Dispose();
}
}
return(tLst);
}
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 Delete <T>(T t)
{
var delSql = SqlConstructor.GetDeleteSqlByParmDic(t);
var result = DbHelper.ExecuteNonQuery(delSql);
return(result);
}
public List <T> Load <T>(CommonSqlKey sqlKey, IDictionary <string, object> parmObj)
{
var parmDic = SqlConstructor.MakeParms(parmObj);
//parmDic = SqlConstructor.FilterParmsWithList(parmDic, CommSqlText.SqlParms[sqlKey]);
return(GetFromDictionary <T>(sqlKey, parmDic));
}
private int GetCountFromDictionary <T>(CommonSqlKey sqlKey, IDictionary <string, object> parmDic)
{
DbDataReader dr = null;
int tLst = 0;
var logStep = 0;
try
{
//如果为空sql可以，则根据类型和传入参数自动构造select语句，同时where条件为传入参数
var sqlTxt = sqlKey == CommonSqlKey.Null ? SqlConstructor.GetSelectSqlByParmDic <T>(parmDic) : CommSqlText.Instance[sqlKey];
logStep++;
var sqlParameters = sqlKey == CommonSqlKey.Null ? null : parmDic; // SqlConstructor.MakeParms(parmDic, CommSqlText.SqlParms[sqlKey]);
sqlTxt = SqlConstructor.FilterSQLWithInsteadValue(sqlTxt, sqlParameters);
logStep++;
sqlParameters = sqlKey == CommonSqlKey.Null ? null : SqlConstructor.FilterParmsWithList(sqlParameters, CommSqlText.SqlParms[sqlKey]);
logStep++;
tLst = DbHelper.ExecuteScalar(sqlTxt, sqlParameters) == null
? 0 : int.Parse(DbHelper.ExecuteScalar(sqlTxt, sqlParameters).ToString());
}
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();
}
}
return(tLst);
}
public DataTable LoadDataTable(CommonSqlKey sqlKey, IDictionary <string, object> parmDic)
{
var logStep = 0;
try
{
if (sqlKey == CommonSqlKey.Null)
{
return(null);
}
var sqlTxt = CommSqlText.Instance[sqlKey];
logStep++;
sqlTxt = SqlConstructor.FilterSQLWithInsteadValue(sqlTxt, 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 List <T> Load <T>(CommonSqlKey sqlKey, T parmObj)
{
var parmDic = SqlConstructor.MakeParms(parmObj);
var resultLst = GetFromDictionary <T>(sqlKey, parmDic);
return(resultLst);
}
private List <T> GetFromDictionary <T>(CommonSqlKey sqlKey, IDictionary <string, object> parmDic)
{
DbConnection dbConn = null;
DbDataReader dr = null;
var tLst = new List <T>();
var logStep = 0;
try
{
//如果为空sql可以，则根据类型和传入参数自动构造select语句，同时where条件为传入参数
var sqlTxt = sqlKey == CommonSqlKey.Null ? SqlConstructor.GetSelectSqlByParmDic <T>(parmDic) : CommSqlText.Instance[sqlKey];
logStep++;
var sqlParameters = sqlKey == CommonSqlKey.Null ? null : parmDic; // SqlConstructor.MakeParms(parmDic, CommSqlText.SqlParms[sqlKey]);
sqlTxt = SqlConstructor.FilterSQLWithInsteadValue(sqlTxt, sqlParameters);
logStep++;
sqlParameters = sqlKey == CommonSqlKey.Null ? null : SqlConstructor.FilterParmsWithList(sqlParameters, CommSqlText.SqlParms[sqlKey]);
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);
}
public object Execute <T>(CommonSqlKey sqlKey, T t)
{
var sqlContent = CommSqlText.Instance[sqlKey];
var sqlParameters = SqlConstructor.MakeParms(t);
sqlParameters = SqlConstructor.FilterParmsWithList(sqlParameters, CommSqlText.SqlParms[sqlKey]);
var result = DbHelper.ExecuteScalar(sqlContent, sqlParameters);
return(result);
}
public int Delete <T>(object kv)
{
if (!ReflectionHandler.IsValueType(kv))
{
return(Delete((T)kv));
}
var delSql = SqlConstructor.GetDeleteSqlByParmDic <T>(kv);
var result = DbHelper.ExecuteNonQuery(delSql);
return(result);
}
public int Delete <T>(CommonSqlKey sqlKey, T t)
{
var sqlContent = CommSqlText.Instance[sqlKey];
var sqlParameters = SqlConstructor.MakeParms(t);
sqlContent = SqlConstructor.FilterSQLWithInsteadValue(sqlContent, sqlParameters);
sqlParameters = SqlConstructor.FilterParmsWithList(sqlParameters, CommSqlText.SqlParms[sqlKey]);
var result = DbHelper.ExecuteNonQuery(sqlContent, sqlParameters);
return(result);
}
private int GetSequenceId <T>()
{
var autoIdSql = SqlConstructor.GetSequeceIdSql <T>();
if (String.IsNullOrEmpty(autoIdSql))
{
return(0);
}
var seqId = int.Parse(DbHelper.ExecuteScalar(autoIdSql).ToString());
return(seqId);
}
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);
}
public List <T> LoadByConditions <T>(CommonSqlKey sqlKey, IList <Condition> parmObj)
{
var parmDic = SqlConstructor.MakeParms(parmObj);
return(GetFromDictionaryByConditions <T>(sqlKey, parmDic, parmObj));
}
public List <T> LoadByConditionsWithOrder <T>(CommonSqlKey sqlKey, IList <Condition> parmObj, string orderField = "", string orderType = "")
{
var parmDic = SqlConstructor.MakeParms(parmObj);
return(GetFromDictionaryByConditionsWithOrder <T>(sqlKey, parmDic, parmObj, orderField, orderType));
}
private int CreateBatch <T>(List <T> ts)
{
var sql = SqlConstructor.GetCreateSQL(ts);
return(DbHelper.ExecuteNonQuery(sql));
}
public int CountByDictionary <T>(CommonSqlKey sqlKey, IDictionary <string, object> parmObj)
{
var parmDic = SqlConstructor.MakeParms(parmObj);
return(GetCountFromDictionary <T>(sqlKey, parmDic));
}
public object Single <T>(CommonSqlKey sqlKey, T parmObj)
{
var parmDic = SqlConstructor.MakeParms(parmObj);
return(GetSingleFromDicDictionary(sqlKey, parmDic));
}