public static int Delete <T>(IConnection conn, ICriteria <T> criteria) where T : EntityBase, new()
{
SQLBuilder sql = new TSQLBuilder();
string sqlStatement = string.Empty;
try
{
sql.BuildDelete(SQLSyntax.Delete, (new T()).Mapping);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildDelete(item.Key, item.Value);
}
sqlStatement = sql.Delete;
return(conn.ExecuteNonQuery(sqlStatement));
}
catch (Exception ex)
{
throw new Exception(ex.Message + "{" + sqlStatement + "}");
}
finally
{
}
}
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 int Update <T>(IConnection conn, T entity, ICriteria <T> criteria) where T : EntityBase
{
SQLBuilder sql = new TSQLBuilder();
string sqlStatement = string.Empty;
try
{
sql.BuildUpdate(SQLSyntax.Update, entity.Mapping);
sql.BuildUpdate(SQLSyntax.Set, entity.UpdateContent);
Dictionary <SQLSyntax, string> sqlSyntaxDictionary = TranslateCriteria <T>(criteria);
foreach (var item in sqlSyntaxDictionary)
{
sql.BuildUpdate(item.Key, item.Value);
}
//if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Update);
sqlStatement = sql.Update;
return(conn.ExecuteNonQuery(sqlStatement));
}
catch (Exception ex)
{
throw new Exception(ex.Message + "{" + sqlStatement + "}");
}
finally
{
}
}
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 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 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);
}
// Parameter with Transaction or NOT
public static int Update <T>(IConnection conn, T entity, bool transact) where T : EntityBase
{
SQLBuilder sql = new TSQLBuilder();
string sqlStatement = string.Empty;
try
{
sql.BuildUpdate(SQLSyntax.Update, entity.Mapping);
sql.BuildUpdate(SQLSyntax.Set, entity.UpdateContent);
sql.BuildUpdate(SQLSyntax.Where, entity.KeyCondition);
sqlStatement = sql.Update;
if (transact)
{
conn.BeginTransaction();
int result = conn.ExecuteNonQuery(sqlStatement);
if (result == 0)
{
conn.Rollback();
}
else
{
conn.Commit();
}
return(result);
}
else
{
return(conn.ExecuteNonQuery(sqlStatement));
}
}
catch (Exception ex)
{
throw new Exception(ex.Message + "{" + sqlStatement + "}");
}
finally
{
}
}
public static int Delete <T>(IConnection conn, T entity) where T : EntityBase
{
SQLBuilder sql = new TSQLBuilder();
string sqlStatement = string.Empty;
try
{
sql.BuildDelete(SQLSyntax.Delete, entity.Mapping);
sql.BuildDelete(SQLSyntax.Where, entity.KeyCondition);
//if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Delete);
sqlStatement = sql.Delete;
return(conn.ExecuteNonQuery(sqlStatement));
}
catch (Exception ex)
{
throw new Exception(ex.Message + "{" + sqlStatement + "}");
}
finally
{
}
}
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);
}
// private static void Audit<T>(IConnection conn, T entity, SQLSyntax sqlsyntax) where T : EntityBase
// {
// string auditSql = @"INSERT INTO [AUDIT] (AUD_SERNO, FUT_GROUP, FUT_NAME, BRN_NAME, ACC_NAME,
//AUD_DTTM, AUD_OLDCOLUMN, AUD_NEWCOLUMN, AUD_COMMENT) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}',
//'{5}', '{6}', '{7}', '{8}')";
// auditSql = string.Format(auditSql,
// My.GenSerNo(),
// entity.Description,
// entity.Description,
// "",
// "",
// My.DatetimeToDateTimeStr(DateTime.Now),
// "",
// "",
// sqlsyntax.ToString()
// );
// conn.ExecuteNonQuery(auditSql);
// }
#endregion
#region CRUD - Create
public static bool Add <T>(IConnection conn, T entity) where T : EntityBase
{
SQLBuilder sql = new TSQLBuilder();
string sqlStatement = string.Empty;
try
{
sql.BuildInsert(SQLSyntax.Insert, entity.Mapping);
sql.BuildInsert(SQLSyntax.Fields, entity.Fields);
sql.BuildInsert(SQLSyntax.Values, entity.InsertValues);
//if (entity.NeedAudit) Audit<T>(conn, entity, SQLSyntax.Insert);
sqlStatement = sql.Insert;
return(conn.ExecuteNonQuery(sqlStatement) > 0);
}
catch (Exception ex)
{
throw new Exception(ex.Message + "{" + sqlStatement + "}");
}
finally
{
}
}