/// <summary>
/// Execute an SQL Command (SELECT) using the _IDBClass properties in the query, only when properties are not null
/// SQLSelect can be used to return only what was specified, ex. SQLSelect = "name, phone"
/// SQLWhere can be used to restrict, ex. SQLWhere = "age > 23"
/// SQLOrderBy can be used to sort the result, ex. SQLOrderBy = "date_birth desc, name asc"
/// </summary>
/// <param name="_IDBClass"></param>
/// <returns>The Select Results (BPA.Data.Hashtables)</returns>
public static Hashtables Select(IDBClass _IDBClass)
{
if (_IDBClass.SQLSelect.IsNullOrEmpty())
_IDBClass.SQLSelect = "*";
StringBuilder SQLQuery = new StringBuilder(String.Format(" SELECT {0} FROM {1} ", _IDBClass.SQLSelect, GETDBName(_IDBClass)));
var props = (from t in _IDBClass.GetType().GetProperties()
where t.GetValue(_IDBClass, null) != null
&& t.Name != "PrimaryKey"
&& t.Name != "SQLSelect"
&& t.Name != "SQLWhere"
&& t.Name != "SQLOrderBy"
select new
{
Name = t.Name,
Value = t.GetValue(_IDBClass, null),
DbType = GETDBType((t.GetValue(_IDBClass, null)).GetType().Name)
});
if (props.Count() != 0)
SQLQuery.Append(" WHERE ");
Parameters p = new Parameters();
foreach (var prop in props)
{
p.Add(new Parameter(String.Format("#{0}", prop.Name), prop.Value, prop.DbType));
SQLQuery.Append(String.Format(" AND {0} = #{1} ", prop.Name, prop.Name));
}
if (p.Count != 0)
SQLQuery = SQLQuery.Replace(" WHERE AND ", " WHERE ");
if (!_IDBClass.SQLWhere.IsNull())
{
if (!SQLQuery.ToString().Contains("WHERE"))
SQLQuery.Append(String.Format(" WHERE {0} ", _IDBClass.SQLWhere));
else
SQLQuery.Append(String.Format(" AND {0} ", _IDBClass.SQLWhere));
}
if (!_IDBClass.SQLOrderBy.IsNullOrEmpty())
SQLQuery.Append(String.Format(" ORDER BY {0} ", _IDBClass.SQLOrderBy));
return DBExecuteReader(SQLQuery.ToString(), p);
}
private static Int64? DBExecuteNonQuery(IDBClass _IDBClass, DBActionType _DBActionType)
{
if (_DBActionType != DBActionType.Insert)
throw new BPAExtensionException("DBActionType Not Found!");
DbCommand DbComm = GETDBCommand(DBCore.SQLDBType);
String PrimaryKey = GETDBPrimaryKey(_IDBClass);
String Table = GETDBName(_IDBClass);
Int64? ID = null;
if (!DBCore.UseAutoIncrement)
ID = GETDBLastID(PrimaryKey, Table);
StringBuilder sbName = new StringBuilder();
StringBuilder sbValue = new StringBuilder();
Parameters ps = new Parameters();
var props = (from t in _IDBClass.GetType().GetProperties()
where t.GetValue(_IDBClass, null) != null
&& t.Name != "PrimaryKey"
&& t.Name != "SQLSelect"
&& t.Name != "SQLWhere"
&& t.Name != "SQLOrderBy"
select new
{
Name = t.Name,
Value = t.GetValue(_IDBClass, null),
DbType = GETDBType((t.GetValue(_IDBClass, null)).GetType().Name)
});
foreach (var prop in props)
{
sbName.Append(String.Format("{0}, ", prop.Name));
sbValue.Append(String.Format("#{0}, ", prop.Name));
ps.Add(new Parameter(String.Format("#{0}", prop.Name), prop.Value, prop.DbType));
}
if (!DBCore.UseAutoIncrement)
{
sbName.Append(String.Format("{0}, ", PrimaryKey));
sbValue.Append(String.Format("#{0}, ", PrimaryKey));
ps.Add(new Parameter(String.Format("#{0}", PrimaryKey), ID, DbType.Int32));
}
DbComm.CommandText = String.Format(
"INSERT INTO {0} ({1}) VALUES ({2})",
Table,
new StringBuilder(sbName.Remove(sbName.ToString().LastIndexOf(','), 2).ToString()),
new StringBuilder(sbValue.Remove(sbValue.ToString().LastIndexOf(','), 2).ToString())
);
GETDBParams(DbComm, ps);
Int64? _ID = DBSync(DbComm, DBActionType.Insert);
if (DBCore.UseAutoIncrement)
ID = _ID;
return ID;
}
private static void ExecuteNonQuery(IDBClass _IDBClass, DBActionType _DBActionType)
{
DbCommand DbComm = GETDBCommand(DBCore.SQLDBType);
String SQLQuery = String.Empty;
String Table = GETDBName(_IDBClass);
String PrimaryKey = GETDBPrimaryKey(_IDBClass);
String PrimaryKeyValue = String.Empty;
DbType PrimaryKeyType = DbType.Object;
Parameters ps = new Parameters();
switch (_DBActionType)
{
case DBActionType.Update:
StringBuilder sb = new StringBuilder();
var propsUpdate = (from t in _IDBClass.GetType().GetProperties()
where t.GetValue(_IDBClass, null) != null
&& t.Name != "PrimaryKey"
&& t.Name != "SQLSelect"
&& t.Name != "SQLWhere"
&& t.Name != "SQLOrderBy"
select new
{
Name = t.Name,
Value = t.GetValue(_IDBClass, null),
DbType = GETDBType((t.GetValue(_IDBClass, null)).GetType().Name)
});
foreach (var prop in propsUpdate)
{
if (prop.Name != PrimaryKey)
{
sb.Append(String.Format("{0} = #{1}, ", prop.Name, prop.Name));
ps.Add(new Parameter(String.Format("#{0}", prop.Name), prop.Value, prop.DbType));
}
else
{
PrimaryKeyType = prop.DbType;
PrimaryKeyValue = prop.Value.ToString();
}
}
sb = new StringBuilder(sb.Remove(sb.ToString().LastIndexOf(','), 2).ToString());
SQLQuery = String.Format("UPDATE {0} SET {1} WHERE {2} = #{3}", Table, sb, PrimaryKey, PrimaryKey);
ps.Add(new Parameter(String.Format("#{0}", PrimaryKey), PrimaryKeyValue, DbType.Int32));
break;
case DBActionType.Delete:
var propsDelete = (from t in _IDBClass.GetType().GetProperties()
where t.Name == GETDBPrimaryKey(_IDBClass)
select new
{
Name = t.Name,
Value = t.GetValue(_IDBClass, null),
DbType = GETDBType((t.GetValue(_IDBClass, null)).GetType().Name)
});
foreach (var prop in propsDelete)
{
if (prop.Name == PrimaryKey)
{
PrimaryKeyType = prop.DbType;
PrimaryKeyValue = prop.Value.ToString();
}
}
SQLQuery = String.Format("DELETE FROM {0} WHERE {1} = #{2}", Table, PrimaryKey, PrimaryKey);
ps.Add(new Parameter(String.Format("#{0}", PrimaryKey), PrimaryKeyValue, DbType.Int32));
break;
case DBActionType.Select:
case DBActionType.Insert:
default:
throw new BPAExtensionException("DBActionType Not Found!");
}
DbComm.CommandText = SQLQuery.ToString();
GETDBParams(DbComm, ps);
DBSync(DbComm, null);
}
private static String GETDBName(IDBClass _IDBClass)
{
return GETDBName(_IDBClass.GetType().Name.ToString());
}