public TableEntity(string table, DatabaseCore accessor)
{
if (string.IsNullOrWhiteSpace(table))
{
throw new Exception(string.Format(GeneralMessages.ERR_IS_NULL_OR_EMPTY, "table"));
}
//if (accessor == null) throw new Exception(string.Format(GeneralMessages.ERR_IS_NULL, "accessor"));
this.TableName = table.Trim();
this.accessor = accessor;
if (this.accessor != null && !this.accessor.TableExists(this.TableName))
{
throw new Exception(string.Format(GeneralMessages.ERR_TABLE_DOES_NOT_EXIST, this.TableName));
}
FreshTimeTicks = 0;
Columns = new Dictionary <string, DBColumn>();
Foreigns = new Dictionary <string, DBForeign>();
InitialColumns();
InitialForeigns();
SetEntityState();
}
private void PrimaryWhereSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
if (accessor == null)
{
return;
}
if (PrimaryList == null || PrimaryList.Count <= 0 || PrimaryList.Any(p => p.Value == null))
{
return;
}
var where = new StringBuilder();
var list = new List <DbParameter>();
PrimaryList.ForEach(p =>
{
if (where.Length > 0)
{
where.AppendLine(" AND ");
}
var parameter = accessor.CreateParameter(p);
where.Append(string.Format("{0} = {1}", p.ID, parameter.ParameterName));
list.Add(parameter);
});
sql = where.ToString();
parameters = list;
}
public static V MinValue <T, V>(DatabaseCore accessor, string column, Clause clause = null) where T : TableEntity, new()
{
if (accessor == null)
{
return(default(V));
}
var entity = CreateEntity <T>();
var col = entity.FindColumn(column);
if (col == null)
{
return(default(V));
}
var sql = new StringBuilder();
sql.AppendLine(string.Format("SELECT MIN({0}) AS MINVAL FROM {1}", col.ID, entity.TableName));
string where = null;
List <DbParameter> parameters = null;
if (clause != null)
{
clause.Export(accessor, out where, out parameters);
}
if (!string.IsNullOrEmpty(where))
{
sql.AppendLine("WHERE");
sql.AppendLine(where);
}
return(accessor.RetrieveValue <V>(accessor.CreateCommand(sql.ToString(), parameters), default(V)));
}
internal List <DbCommand> GetDeleteCommands(DatabaseCore accessor = null)
{
var commands = new List <DbCommand>();
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(commands);
}
if (EntityStatus < EntityState.FRESHED)
{
return(commands);
}
// Delete Foreigns
(new List <DBForeign>(Foreigns.Values)).ForEach(foreign =>
{
commands.AddRange(foreign.GetDeleteCommands(dba));
});
// Delete Self
string text = null;
List <DbParameter> list = null;
DeleteSQL(dba, out text, out list);
if (!string.IsNullOrWhiteSpace(text))
{
commands.Add(dba.CreateCommand(text, list));
}
return(commands);
}
internal List <DbCommand> GetSaveCommands(DatabaseCore accessor = null)
{
var commands = new List <DbCommand>();
if (this.EntityStatus > EntityState.RAW)
{
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(commands);
}
// Save Foreigns
(new List <DBForeign>(Foreigns.Values)).ForEach(foreign =>
{
commands.AddRange(foreign.GetSaveCommands(dba));
});
// Save Self
List <DbCommand> list = null;
if (this.EntityStatus == EntityState.ASSIGNED)
{
list = GetInsertCommands(accessor);
}
if (this.EntityStatus == EntityState.CHANGED)
{
list = GetUpdateCommands(accessor);
}
if (list != null && list.Count > 0)
{
commands.AddRange(list);
}
}
return(commands);
}
internal List <DbCommand> GetInsertCommands(DatabaseCore accessor = null)
{
var commands = new List <DbCommand>();
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(commands);
}
if (EntityStatus != EntityState.ASSIGNED)
{
return(commands);
}
string text = null;
List <DbParameter> list = null;
InsertSQL(dba, out text, out list);
if (!string.IsNullOrWhiteSpace(text))
{
commands.AddRange(GetDeleteCommands());
commands.Add(dba.CreateCommand(text, list));
}
return(commands);
}
internal void SetDBAccessor(DatabaseCore accessor)
{
if (accessor != null)
{
this.accessor = accessor;
}
}
public static long Count <T>(DatabaseCore accessor, Clause clause = null) where T : TableEntity, new()
{
if (accessor == null)
{
return(0);
}
var entity = CreateEntity <T>();
return(accessor.CountInTable(entity.TableName, clause));
}
protected virtual List <IADbCommand> GetSaveADbCommands(DatabaseCore accessor = null)
{
var commands = new List <IADbCommand>();
if (this.EntityStatus > EntityState.RAW)
{
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(commands);
}
//// Save Foreigns
//(new List<DBForeign>(Foreigns.Values)).ForEach(foreign =>
//{
// commands.AddRange(foreign.GetSaveCommands(dba));
//});
// Save Self
List <DbCommand> list = null;
Action <DbCommand, List <DbCommand> > action = null;
if (this.EntityStatus == EntityState.ASSIGNED)
{
list = GetInsertCommands(dba);
action = CallbackAction_INSERT;
}
if (this.EntityStatus == EntityState.CHANGED)
{
list = GetUpdateCommands(dba);
action = CallbackAction_UPDATE;
}
if (list != null && list.Count > 0)
{
foreach (var item in list)
{
commands.Add(new ADbCommand(item, action));
}
}
// Save Foreigns
(new List <DBForeign>(Foreigns.Values)).ForEach(foreign =>
{
commands.AddRange(foreign.GetSaveADbCommands(dba));
});
}
return(commands);
}
private void DeleteSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var pattern = "DELETE FROM [{0}] WHERE {1}";
string where = null;
List <DbParameter> list = null;
PrimaryWhereSQL(accessor, out where, out list);
if (string.IsNullOrWhiteSpace(where))
{
return;
}
sql = string.Format(pattern, this.TableName, where);
parameters = list;
}
protected T GetValue <T>(string column)
{
var value = default(T);
if (!string.IsNullOrWhiteSpace(column))
{
var id = column.Trim().ToUpper();
if (Columns.ContainsKey(id))
{
value = DatabaseCore.Convert2 <T>(Columns[id].Value);
}
else if (Foreigns.ContainsKey(id))
{
value = DatabaseCore.Convert2 <T>(Foreigns[id].Value);
}
}
return(value);
}
internal DbCommand GetDeleteCommand(DatabaseCore accessor = null)
{
DbCommand command = null;
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(null);
}
string text = null;
List <DbParameter> list = null;
DeleteSQL(dba, out text, out list);
if (!string.IsNullOrWhiteSpace(text))
{
command = dba.CreateCommand(text, list);
}
return(command);
}
private void FreshSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var select = new StringBuilder();
string where = null;
List <DbParameter> list = null;
PrimaryWhereSQL(accessor, out where, out list);
if (string.IsNullOrWhiteSpace(where))
{
return;
}
select.AppendLine(SQLTableSelect);
select.AppendLine("WHERE");
select.AppendLine(where);
sql = select.ToString();
parameters = list;
}
private void InsertSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var pattern = "INSERT INTO {0} ({1}) VALUES ({2})";
var list = new List <DbParameter>();
var fields = new List <string>();
var values = new List <string>();
var columns = new List <DBColumn>(Columns.Values);
columns.ForEach(col =>
{
var parameter = accessor.CreateParameter(col);
list.Add(parameter);
fields.Add(col.ID);
values.Add(parameter.ParameterName);
});
sql = string.Format(pattern, this.TableName, string.Join(",", fields), string.Join(",", values));
parameters = list;
}
private void UpdateSQL(DatabaseCore accessor, out string sql, out List <DbParameter> parameters)
{
sql = null; parameters = null;
var pattern = "UPDATE [{0}] SET {1} WHERE {2}";
var pattern1 = "{0} = {1}";
var list = new List <DbParameter>();
var items = new List <string>();
var columns = new List <DBColumn>(Columns.Values).FindAll(col => col.State == ColumnState.CHANGED);
var lst_items = new List <DbParameter>();
columns.ForEach(col =>
{
var parameter = accessor.CreateParameter(col);
lst_items.Add(parameter);
items.Add(string.Format(pattern1, col.ID, parameter.ParameterName));
});
if (items.Count <= 0)
{
return;
}
list.AddRange(lst_items);
string where = null;
List <DbParameter> lst_where = null;
PrimaryWhereSQL(accessor, out where, out lst_where);
if (string.IsNullOrWhiteSpace(where))
{
return;
}
list.AddRange(lst_where);
sql = string.Format(pattern, this.TableName, string.Join(",", items), where);
parameters = list;
}
protected virtual List <IADbCommand> GetUpdateADbCommands(DatabaseCore accessor = null)
{
var commands = new List <IADbCommand>();
var dba = (accessor != null) ? accessor : this.accessor;
if (dba == null)
{
return(commands);
}
if (EntityStatus != EntityState.CHANGED)
{
return(commands);
}
string text = null;
List <DbParameter> list = null;
UpdateSQL(dba, out text, out list);
if (!string.IsNullOrWhiteSpace(text))
{
commands.Add(new ADbCommand(dba.CreateCommand(text, list), CallbackAction_UPDATE));
}
return(commands);
}
internal List <IADbCommand> INTERNAL_GetDeleteADbCommands(DatabaseCore accessor = null)
{
return(GetDeleteADbCommands(accessor));
}
internal List <DbCommand> INTERNAL_GetSaveCommands(DatabaseCore accessor = null)
{
return(GetSaveCommands(accessor));
}