public PagedTableList(Type type,int? PageSize,SelectParameter[] SelectParams,bool transationSafe)
{
if (!type.IsSubclassOf(typeof(Table)))
throw new Exception("cannot produce a Paged Table List from a class that does not inherit Table");
_tableType=type;
_pageSize = PageSize;
_pars = SelectParams;
_transationSafe=transationSafe;
_conn = ConnectionPoolManager.GetConnection(type);
_count = (int)_conn.SelectCount(type,_pars);
if (!_pageSize.HasValue)
_pageSize=20;
_data = new List<Table>();
LoadToIndex(_pageSize.Value);
}
internal override string SelectPaged(System.Type type, SelectParameter[] parameters, out List<IDbDataParameter> queryParameters, ulong? start, ulong? recordCount, string[] OrderByFields)
{
if (!start.HasValue)
start = 0;
if (!recordCount.HasValue)
recordCount = 0;
string baseQuery = Select(type, parameters, out queryParameters, OrderByFields);
queryParameters.Add(pool.CreateParameter(CreateParameterName("startIndex"), (long)start.Value));
queryParameters.Add(pool.CreateParameter(CreateParameterName("rowCount"), (long)recordCount.Value));
string primarys = "";
if ((OrderByFields == null) || (OrderByFields.Length == 0))
{
foreach (string str in pool.Mapping[type].PrimaryKeyFields)
primarys += "," + str;
}
else if (baseQuery.Contains("ORDER BY"))
primarys = baseQuery.Substring(baseQuery.IndexOf("ORDER BY") + "ORDER BY".Length);
else
{
foreach (string str in OrderByFields)
{
if (str.EndsWith(" ASC") || str.EndsWith(" DESC"))
{
string[] tmp = str.Split(new char[] { ' ' });
foreach (sTableField fld in pool.Mapping[type][tmp[0]])
primarys += "," + fld.Name+" "+tmp[1];
}
else
{
foreach (sTableField fld in pool.Mapping[type][str])
primarys += "," + fld.Name;
}
}
}
if (primarys.StartsWith(","))
primarys = primarys.Substring(1);
return String.Format(SelectWithPagingIncludeOffset, (baseQuery.Contains("ORDER BY") ? baseQuery.Substring(0, baseQuery.IndexOf("ORDER BY")) : baseQuery), CreateParameterName("startIndex"), CreateParameterName("rowCount"), primarys);
}
public object SelectMin(string fieldName, System.Type type, SelectParameter[] parameters)
{
pool.Updater.InitType(type, this);
object ret = null;
List<IDbDataParameter> pars = new List<IDbDataParameter>();
string query = queryBuilder.SelectMin(type, fieldName, parameters, out pars);
ExecuteQuery(query, pars);
if (this.Read())
ret = this[0];
Close();
if (ret is DBNull)
ret = null;
return ret;
}
public PagedTableList(Type type,SelectParameter[] SelectParams,bool transactionSafe) : this(type,null,SelectParams,transactionSafe)
{}
internal virtual string SelectPaged(System.Type type, SelectParameter[] parameters, out List<IDbDataParameter> queryParameters, ulong? start, ulong? recordCount, string[] OrderByFields)
{
string query = Select(type, parameters, out queryParameters, OrderByFields);
if (queryParameters == null)
queryParameters = new List<IDbDataParameter>();
if (!start.HasValue)
start = 0;
if (!recordCount.HasValue)
recordCount = 0;
queryParameters.Add(pool.CreateParameter(CreateParameterName("startIndex"), (long)start.Value));
queryParameters.Add(pool.CreateParameter(CreateParameterName("rowCount"), (long)recordCount.Value));
return String.Format(SelectWithPagingIncludeOffset, query, CreateParameterName("startIndex"), CreateParameterName("rowCount"));
}
public NotParameter(SelectParameter negatedParameter)
{
_negatedParameter = negatedParameter;
}
public void Delete(Type tableType, SelectParameter[] pars){
if (_readonly)
throw new Exception("Unable to delete to a readonly database.");
if (!Pool.Mapping.IsMappableType(tableType))
throw new Exception("Unable to delete type " + tableType.FullName + " no matching Table Map found for the connection pool "+Pool.ConnectionName+".");
pool.Updater.InitType(tableType, this);
pars = (pars == null ? new SelectParameter[0] : pars);
List<IDbDataParameter> parameters = new List<IDbDataParameter>();
string del = queryBuilder.Delete(tableType, pars, out parameters);
if (del != null)
{
bool abort = false;
ConnectionPoolManager.RunTriggers(this,tableType, pars, ConnectionPoolManager.TriggerTypes.PRE_DELETE,out abort);
if (!abort)
{
ExecuteNonQuery(del, parameters);
ConnectionPoolManager.RunTriggers(this, tableType, pars, ConnectionPoolManager.TriggerTypes.POST_DELETE, out abort);
}
}
else
throw new Exception("An error occured attempting to build the delete query.");
}
internal string Select(System.Type type, SelectParameter[] parameters, out List<IDbDataParameter> queryParameters, string[] OrderByFields)
{
sTable tbl = _pool.Mapping[type];
string fields="";
string tables="";
string joins="";
string where="";
bool startAnd=false;
queryParameters=new List<IDbDataParameter>();
string order = "";
if ((OrderByFields != null) && (OrderByFields.Length > 0))
{
foreach (string str in OrderByFields)
{
if (str.EndsWith(" ASC") || str.EndsWith(" DESC"))
{
string[] tmp = str.Split(new char[] { ' ' });
foreach (sTableField stf in tbl[tmp[0]])
order += stf.Name+" "+tmp[1] + ",";
}
else
{
foreach (sTableField stf in tbl[str])
order += stf.Name + ",";
}
}
if (order.Length>0)
order = order.Substring(0, order.Length - 1);
}
if (ObtainFieldTableWhereList(out fields,out tables, out joins,out where,type))
{
if ((parameters!=null)&&(parameters.Length>0))
{
startAnd=(where.Length>0);
string appended="";
int parCount=0;
foreach (SelectParameter par in parameters)
{
AppendJoinsForParameter(par.Fields, ref joins, type);
appended+="("+par.ConstructString(type,pool,this,ref queryParameters,ref parCount)+") AND ";
}
appended=appended.Substring(0,appended.Length-4);
if (!startAnd)
where = "("+appended+")";
else
where+=" AND ("+appended+")";
}
if (order.Length > 0)
{
if (where.Length > 0)
return String.Format(OrderBy,String.Format(SelectWithConditions, fields, joins + tables, where),order);
else
return String.Format(OrderBy,String.Format(SelectWithoutConditions, fields, joins + tables),order);
}
else
{
if (where.Length > 0)
return String.Format(SelectWithConditions, fields, joins + tables, where);
else
return String.Format(SelectWithoutConditions, fields, joins + tables);
}
}else
return null;
}
public List<Table> SelectPaged(System.Type type, SelectParameter[] parameters, ulong? StartIndex, ulong? RowCount)
{
return SelectPaged(type, parameters, StartIndex, RowCount, null);
}
public List<Table> SelectPaged(System.Type type, SelectParameter[] parameters, ulong? StartIndex, ulong? RowCount, string[] OrderByFields)
{
if (!type.IsSubclassOf(typeof(Table)))
{
throw new Exception("Unable to perform select on Type object without object inheriting Org.Reddragonit.DbPro.Structure.Table");
}
if (((Table)type.GetConstructor(System.Type.EmptyTypes).Invoke(new object[0])).ConnectionName!=ConnectionName)
{
throw new Exception("Cannot select from a table from the database connection that it was not specified for.");
}
pool.Updater.InitType(type, this);
List<Table> ret = new List<Table>();
List<IDbDataParameter> pars = new List<IDbDataParameter>();
string query = queryBuilder.SelectPaged(type,parameters,out pars,StartIndex,RowCount,OrderByFields);
ExecuteQuery(query,pars);
while (Read())
{
Table t = (Table)LazyProxy.Instance(type.GetConstructor(System.Type.EmptyTypes).Invoke(new object[0]));
t.SetValues(this);
t.LoadStatus=LoadStatus.Complete;
ret.Add(t);
}
Close();
ret = AddArrayedTablesToSelect(ret, type);
return ret;
}
public long SelectCount(System.Type type,SelectParameter[] parameters)
{
if (!type.IsSubclassOf(typeof(Table)))
{
throw new Exception("Unable to perform select on Type object without object inheriting Org.Reddragonit.DbPro.Structure.Table");
}
if (((Table)type.GetConstructor(System.Type.EmptyTypes).Invoke(new object[0])).ConnectionName!=ConnectionName)
{
throw new Exception("Cannot select from a table from the database connection that it was not specified for.");
}
pool.Updater.InitType(type, this);
long ret=0;
List<IDbDataParameter> pars = new List<IDbDataParameter>();
string query = queryBuilder.SelectCount(type,parameters,out pars);
ExecuteQuery(query,pars);
if (Read())
ret=long.Parse(this[0].ToString());
Close();
return ret;
}
public List<Table> Select(System.Type type,SelectParameter[] parameters,string[] OrderByFields)
{
if (!type.IsSubclassOf(typeof(Table)))
{
throw new Exception("Unable to perform select on Type object without object inheriting Org.Reddragonit.DbPro.Structure.Table");
}
if (((Table)type.GetConstructor(System.Type.EmptyTypes).Invoke(new object[0])).ConnectionName!=ConnectionName)
{
throw new Exception("Cannot select from a table from the database connection that it was not specified for.");
}
pool.Updater.InitType(type, this);
List<Table> ret = new List<Table>();
List<IDbDataParameter> pars = new List<IDbDataParameter>();
string query = queryBuilder.Select(type,parameters,out pars,OrderByFields);
ExecuteQuery(query,pars);
Logger.LogLine("Query executed, beginning to read results");
while (Read())
{
Logger.LogLine("Creating a lazy proxy instance for the table type " + type.FullName);
Table t = (Table)LazyProxy.Instance(type.GetConstructor(System.Type.EmptyTypes).Invoke(new object[0]));
Logger.LogLine("Reading result and loading table object " + type.FullName + " from query");
t.SetValues(this);
Logger.LogLine("Setting load status for " + type.FullName + " as completed and adding to results");
t.LoadStatus=LoadStatus.Complete;
ret.Add(t);
}
Close();
ret = AddArrayedTablesToSelect(ret, type);
return ret;
}
public List<Table> Select(System.Type type, SelectParameter[] parameters)
{
return Select(type, parameters, null);
}
internal string Update(Type tableType, Dictionary<string, object> updateFields, SelectParameter[] parameters, out List<IDbDataParameter> queryParameters)
{
queryParameters = new List<IDbDataParameter>();
sTable table = _pool.Mapping[tableType];
try
{
string fields = "";
string conditions = "";
bool addedAutogenCorrection = false;
List<string> pkeys = new List<string>(table.PrimaryKeyFields);
foreach (string prop in updateFields.Keys)
{
sTableField[] flds = table[prop];
if (flds.Length > 0)
{
if (pkeys.Contains(flds[0].Name) && !Utility.StringsEqual(table.AutoGenProperty, prop) && !addedAutogenCorrection && pkeys.Count > 0)
{
if (table.AutoGenProperty != null)
{
fields += table.AutoGenProperty + " = (SELECT (CASE WHEN MAX(" + table.AutoGenProperty + ") IS NULL THEN 0 ELSE MAX(" + table.AutoGenProperty + ") END)+1 FROM " + table.Name + " WHERE ";
foreach (sTableField fld in table.Fields)
{
if (pkeys.Contains(fld.Name) && !Utility.StringsEqual(table.AutoGenProperty, fld.Name))
{
object val = updateFields[fld.ClassProperty];
if (val == null)
fields += fld.Name + " IS NULL AND ";
else
{
fields += fld.Name + " = " + CreateParameterName(fld.Name);
queryParameters.Add(pool.CreateParameter(CreateParameterName(fld.Name), val));
}
}
}
fields = fields.Substring(0, fields.Length - 4);
fields += "), ";
addedAutogenCorrection = true;
}
}
if (flds[0].ExternalField != null&&flds[0].Type!=FieldType.ENUM)
{
PropertyInfo pi = tableType.GetProperty(flds[0].ClassProperty, Utility._BINDING_FLAGS_WITH_INHERITANCE);
sTable relTable = _pool.Mapping[(pi.PropertyType.IsArray ? pi.PropertyType.GetElementType() : pi.PropertyType)];
if (updateFields[flds[0].ClassProperty] == null)
{
foreach (sTableField fld in flds)
{
fields += fld.Name + " = " + CreateParameterName(fld.Name) + ", ";
queryParameters.Add(pool.CreateParameter(CreateParameterName(fld.Name), null, fld.Type, fld.Length));
}
}
else
{
Table relatedTable = (Table)updateFields[flds[0].ClassProperty];
foreach (sTableField fld in flds)
{
foreach (sTableField f in relTable.Fields)
{
if (fld.ExternalField == f.Name)
{
object val = LocateFieldValue(relatedTable, f, pool);
fields += fld.Name + " = " + CreateParameterName(fld.Name) + ", ";
if (val == null)
queryParameters.Add(pool.CreateParameter(CreateParameterName(fld.Name), null, fld.Type, fld.Length));
else
queryParameters.Add(pool.CreateParameter(CreateParameterName(fld.Name), val));
break;
}
}
}
}
}
else if (flds[0].ComputedCode==null)
{
fields += flds[0].Name + " = " + CreateParameterName(flds[0].Name) + ", ";
if (updateFields[flds[0].ClassProperty] == null)
queryParameters.Add(pool.CreateParameter(CreateParameterName(flds[0].Name), null,flds[0].Type,flds[0].Length));
else
{
if (flds[0].Type==FieldType.ENUM)
queryParameters.Add(pool.CreateParameter(CreateParameterName(flds[0].Name), pool.GetEnumID(updateFields[flds[0].ClassProperty].GetType(), updateFields[flds[0].ClassProperty].ToString())));
else
queryParameters.Add(pool.CreateParameter(CreateParameterName(flds[0].Name), updateFields[flds[0].ClassProperty], flds[0].Type, flds[0].Length));
}
}
}
}
int parCount = 0;
foreach (SelectParameter eq in parameters)
{
conditions += eq.ConstructString(tableType, pool, this, ref queryParameters, ref parCount) + " AND ";
}
if (fields.Length == 0)
return "";
fields = fields.Substring(0, fields.Length - 2);
if (conditions.Length > 0)
{
return String.Format(UpdateWithConditions, table.Name, fields, conditions.Substring(0, conditions.Length - 4).Replace("main_table.", ""));
}
else
return String.Format(UpdateWithoutConditions, table.Name, fields);
}
catch (Exception e)
{
Logger.LogLine(e.Message);
}
return null;
}
public void Update(Type tableType, Dictionary<string, object> updateFields, SelectParameter[] parameters)
{
if (_readonly)
throw new Exception("Unable to update to a readonly database.");
if (!Pool.Mapping.IsMappableType(tableType))
throw new Exception("Unable to update type " + tableType.FullName + " no matching Table Map found for the connection pool " + Pool.ConnectionName + ".");
pool.Updater.InitType(tableType, this);
List<IDbDataParameter> pars = new List<IDbDataParameter>();
string query = queryBuilder.Update(tableType, updateFields, parameters, out pars);
if (query != null)
{
bool abort = false;
ConnectionPoolManager.RunTriggers(this, tableType, updateFields, parameters, ConnectionPoolManager.TriggerTypes.PRE_UPDATE, out abort);
if (!abort)
{
ExecuteNonQuery(query, pars.ToArray());
ConnectionPoolManager.RunTriggers(this, tableType, updateFields, parameters, ConnectionPoolManager.TriggerTypes.POST_UPDATE, out abort);
}
}
}
internal string SelectMin(System.Type type, string maxField, SelectParameter[] parameters, out List<IDbDataParameter> queryParameters)
{
sTable map = _pool.Mapping[type];
string fields = "";
string tables = "";
string joins = "";
string where = "";
bool startAnd = false;
queryParameters = new List<IDbDataParameter>();
if (ObtainFieldTableWhereList(out fields, out tables, out joins, out where, type))
{
AppendJoinsForParameter(new List<string>(new string[] { maxField }), ref joins, type);
string alias = "main_table";
if (maxField.Contains("."))
{
sTable curMap = map;
Type curType = type;
while (maxField.Contains("."))
{
PropertyInfo pi = curType.GetProperty(maxField.Substring(0, maxField.IndexOf(".")), Utility._BINDING_FLAGS);
curType = (pi.PropertyType.IsArray ? pi.PropertyType.GetElementType() : pi.PropertyType);
curMap = _pool.Mapping[curType];
alias += "_" + maxField.Substring(0, maxField.IndexOf("."));
maxField = maxField.Substring(maxField.IndexOf(".") + 1);
}
fields = alias + "." + curMap[maxField][0].Name;
}
else
{
sTableField[] flds = map[maxField];
if (flds.Length > 0)
fields = alias + "." + flds[0].Name;
else
fields = maxField;
}
if ((parameters != null) && (parameters.Length > 0))
{
startAnd = (where.Length > 0);
string appended = "";
int parCount = 0;
foreach (SelectParameter par in parameters)
{
AppendJoinsForParameter(par.Fields, ref joins, type);
appended += "(" + par.ConstructString(type, pool, this, ref queryParameters, ref parCount) + ") AND ";
}
appended = appended.Substring(0, appended.Length - 4);
if (!startAnd)
where = "(" + appended + ")";
else
where += " AND (" + appended + ")";
}
if (where.Length > 0)
return String.Format(SelectMinWithConditions, fields, joins + tables, where);
else
return String.Format(SelectMinWithoutConditions, fields, joins + tables);
}
else
return null;
}
public List<IClassView> SelectClassView(System.Type type,SelectParameter[] pars)
{
return SelectClassView(type, pars, null);
}
internal string SelectCount(System.Type type,SelectParameter[] parameters,out List<IDbDataParameter> queryParameters)
{
string query=Select(type,parameters,out queryParameters,null);
return String.Format(SelectCountString,query);
}
public virtual List<IClassView> SelectClassView(System.Type type,SelectParameter[] pars,string[] OrderByFields)
{
ClassViewAttribute cva = Pool[type];
if (cva==null || !new List<Type>(type.GetInterfaces()).Contains(typeof(IClassView)))
throw new Exception("Unable to execute a Class View Query from a class that does not have a ClassViewAttributes attached to it as well as has the interface IClassView.");
pool.Updater.InitType(type, this);
List<IClassView> ret = new List<IClassView>();
string viewName = Pool.Translator.GetViewName(type);
int parCount = 0;
List<IDbDataParameter> queryParameters = new List<IDbDataParameter>();
string parString = "";
string orderByString = "";
if (pars != null)
{
foreach (SelectParameter par in pars)
{
foreach (string str in par.Fields)
{
if (cva.Query.GetOrdinal(str)==-1)
throw new Exception("Unable to execute a Class View Query with parameters that are not fields in the Class View");
}
parString += " AND ( " + par.ConstructClassViewString(cva,Pool,queryBuilder , ref queryParameters, ref parCount) + " ) ";
}
}
if (OrderByFields != null)
{
foreach (string str in OrderByFields)
{
if (str.EndsWith(" ASC") || str.EndsWith(" DESC"))
{
if (cva.Query.GetOrdinal(str.Split(new char[]{' '})[0]) == -1)
throw new Exception("Unable to execute a Class View Query with Order By Fields that are not fields in the Class View");
}
else
{
if (cva.Query.GetOrdinal(str) == -1)
throw new Exception("Unable to execute a Class View Query with Order By Fields that are not fields in the Class View");
}
orderByString += ","+str;
}
}
this.ExecuteQuery("SELECT * FROM " + viewName + (parString == "" ? "" : " WHERE "+parString.Substring(4))+(orderByString == "" ? "" : " ORDER BY "+orderByString.Substring(1)),queryParameters.ToArray());
ViewResultRow vrr = new ViewResultRow(this,cva.Query);
while (Read())
{
IClassView icv = (IClassView)type.GetConstructor(Type.EmptyTypes).Invoke(new object[0]);
icv.LoadFromRow(vrr);
ret.Add(icv);
}
Close();
return ret;
}
internal string Delete(Type tableType, SelectParameter[] pars, out List<IDbDataParameter> parameters)
{
parameters = new List<IDbDataParameter>();
try
{
string conditions = "";
sTable tbl = _pool.Mapping[tableType];
int parCount = 0;
foreach (SelectParameter eq in pars)
{
conditions += eq.ConstructString(tableType, pool, this, ref parameters, ref parCount) + " AND ";
}
if (conditions.Length > 0)
conditions = conditions.Substring(0, conditions.Length - 4).Replace("main_table.", "");
if (conditions.Length>0)
return string.Format(DeleteWithConditions, tbl.Name, conditions);
return string.Format(DeleteWithoutConditions, tbl.Name);
}
catch (Exception e)
{
Logger.LogLine(e.Message);
return null;
}
}
public object SelectMaxClassView(string fieldName, System.Type type, SelectParameter[] pars)
{
object ret = null;
ClassViewAttribute cva = Pool[type];
if (cva==null || !new List<Type>(type.GetInterfaces()).Contains(typeof(IClassView)))
throw new Exception("Unable to execute a Class View Query from a class that does not have a ClassViewAttribute attached to it and does not inherit IClassView.");
pool.Updater.InitType(type, this);
string viewName = Pool.Translator.GetViewName(type);
if (cva.Query.GetOrdinal(fieldName) == -1)
throw new Exception("Unable to execute a Max Class View Query without specificying a Field in the Class View");
int parCount = 0;
List<IDbDataParameter> queryParameters = new List<IDbDataParameter>();
string parString = "";
if (pars != null)
{
foreach (SelectParameter par in pars)
{
foreach (string str in par.Fields)
{
if (cva.Query.GetOrdinal(str)==-1)
throw new Exception("Unable to execute a Class View Query with parameters that are not fields in the Class View");
}
parString += " AND ( " + par.ConstructClassViewString(cva, Pool, queryBuilder, ref queryParameters, ref parCount) + " ) ";
}
}
this.ExecuteQuery("SELECT MAX("+fieldName+") FROM " + viewName + (parString == "" ? "" : " WHERE " + parString.Substring(4)), queryParameters.ToArray());
if (Read())
ret = this[0];
Close();
return ret;
}
public PagedTableList(Type type,SelectParameter[] SelectParams) : this(type,null,SelectParams,true)
{}
internal static void RunTriggers(Connection conn, Type tableType, Dictionary<string, object> updateFields, SelectParameter[] parameters, TriggerTypes type, out bool abort)
{
abort = false;
ITrigger[] tmp = new ITrigger[0];
Monitor.Enter(_triggers);
if (_triggers.ContainsKey(tableType))
{
tmp = new ITrigger[_triggers[tableType].Count];
_triggers[tableType].CopyTo(tmp);
}
Monitor.Exit(_triggers);
foreach (ITrigger tr in tmp)
{
switch (type)
{
case TriggerTypes.PRE_UPDATE:
tr.PreUpdate(conn,tableType, updateFields, parameters,out abort);
break;
case TriggerTypes.POST_UPDATE:
tr.PostUpdate(conn,tableType, updateFields, parameters);
break;
}
}
}
public PagedTableList(Type type,int? PageSize,SelectParameter[] SelectParams) : this(type,PageSize,SelectParams,true)
{}
public JoinParameter(SelectParameter[] parameters)
{
_parameters = parameters;
}
public AndParameter(SelectParameter[] parameters)
: base(parameters)
{
}
public List<IClassView> SelectPagedClassView(System.Type type, SelectParameter[] parameters, ulong? StartIndex, ulong? RowCount, string[] OrderByFields)
{
return this.SelectPagedClassView(type,(parameters==null ? null : new List<SelectParameter>(parameters)),StartIndex,RowCount,OrderByFields);
}