public static List <T> QueryMultiBy <T>(IConnection pConn, string pCondition) where T : EntityBase, new()
{
List <T> dataObjectList = new List <T>();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
sql.BuildUpdate(SQLSyntax.Where, pCondition);
reader = pConn.ExecuteReader(sql.Query);
while (reader.Read())
{
T dataObject = new T();
ORMapping(dataObject, reader);
dataObjectList.Add(dataObject);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(dataObjectList);
}
public static string QueryBy <T>(IConnection pConn, string pField, string pCondition) where T : EntityBase, new()
{
T dataObject = new T();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
string value = string.Empty;
try
{
sql.BuildQuery(SQLSyntax.Select, pField);
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
sql.BuildUpdate(SQLSyntax.Where, pCondition);
reader = pConn.ExecuteReader(sql.Query);
if (reader.Read())
{
value = reader[0].ToString().Trim();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(value);
}
public static T QueryByKey <T>(IConnection conn, string keyValue) where T : EntityBase, new()
{
T dataObject = new T();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
sql.BuildUpdate(SQLSyntax.Where, (new T()).GetKeyCondition(keyValue));
reader = conn.ExecuteReader(sql.Query);
if (reader.Read())
{
ORMapping(dataObject, reader);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(dataObject);
}
public static T QueryByKey <T>(IConnection conn, ICriteria <T> criteria) where T : IDataObject, new()
{
T dataObject = new T();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Select, (new T()).Fields);
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildQuery(item.Key, item.Value);
}
reader = conn.ExecuteReader(sql.Query);
if (reader.Read())
{
ORMapping(dataObject, reader);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(dataObject);
}
public static List <string> Distinct <T>(IConnection conn, string fieldName, ICriteria <T> criteria) where T : IDataObject, new()
{
List <string> distinctList = new List <string>();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Select, string.Format(" DISTINCT {0} ", fieldName));
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildQuery(item.Key, item.Value);
}
reader = conn.ExecuteReader(sql.Query);
while (reader.Read())
{
distinctList.Add(reader.GetValue(reader.GetOrdinal(fieldName)).ToString().Trim());
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(distinctList);
}
public static int Count <T>(IConnection conn, ICriteria <T> criteria) where T : IDataObject, new()
{
int count = 0;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Select, " COUNT(*) ");
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildQuery(item.Key, item.Value);
}
count = Convert.ToInt32(conn.ExecuteScalar(sql.Query));
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
return(count);
}
public static List <T> QueryTop <T>(IConnection conn, ICriteria <T> criteria, int topCount = 1) where T : IDataObject, new()
{
List <T> dataObjectList = new List <T>();
IDataReader reader = null;
SQLBuilder sql = new TSQLBuilder();
try
{
sql.BuildQuery(SQLSyntax.Top, topCount.ToString(), (new T()).Fields);
sql.BuildQuery(SQLSyntax.From, (new T()).Mapping);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildQuery(item.Key, item.Value);
}
reader = conn.ExecuteReader(sql.Query);
while (reader.Read())
{
T dataObject = new T();
ORMapping(dataObject, reader);
dataObjectList.Add(dataObject);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if ((reader != null) || (!reader.IsClosed))
{
reader.Close();
}
}
return(dataObjectList);
}
