internal string GetWhereSql(string memberName)
{
var value = GetValue(memberName);
var columnName = IntrospectionManager.GetColumns(TableFullName)[memberName];
return(GetWhereSqlByInputValue(columnName, value));
}
internal string GetSaveSql(bool isIdAutoGrow)
{
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
var infos = IntrospectionManager.GetColumns(TableFullName);
string valueSql = "";
string columnSql = "";
if (isIdAutoGrow)
{
var filedsArr = infos.Keys.Where(x => !pk.Contains(x)).Select(GetSqlValue);
valueSql = string.Join(",", filedsArr);
var columnsArr = infos.Keys.Where(x => !pk.Contains(x)).Select(x => Escape(GetColumnName(x)));
columnSql = string.Join(",", columnsArr);
}
else
{
var filedsArr = infos.Keys.Select(GetSqlValue);
valueSql = string.Join(" , ", filedsArr);
var columnsArr = infos.Keys.Select(x => Escape(GetColumnName(x)));
columnSql = string.Join(",", columnsArr);
}
string sql = string.Format("insert into {0}({1}) values ({2});", Escape(TableName), columnSql, valueSql);
return(sql);
}
internal string GetSetSql(string memberName)
{
var value = GetValue(memberName);
var columnName = IntrospectionManager.GetColumns(TableFullName)[memberName];
if (value == null)
{
return(Escape(columnName) + "=null");
}
else if (value.GetType().IsDigital())
{
return(Escape(columnName) + "=" + value);
}
else if (value.GetType().IsByteArrary())
{
return(Escape(columnName) + "='" + Encoding.Default.GetString((byte[])value) + "'");
}
else if (value is DateTime && (DateTime)value == DateTime.MinValue)
{
return(Escape(columnName) + "=null");
}
else if (value is Guid && (Guid)value == Guid.Empty)
{
return(Escape(columnName) + "=null");
}
else
{
return(Escape(columnName) + "='" + value + "'");
}
}
internal string GetDelSql()
{
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
var sql = string.Format("delete from {0} where {1};", Escape(TableName), GetWhereSql(pk));
return(sql);
}
/// <summary>
/// Delete record from database
/// </summary>
/// <param name="condition">Condition</param>
/// <param name="tran">Transaction</param>
public virtual void Del(Condition condition, DbTransaction tran = null)
{
_entityStatus = "deleted";
string whereSql = "";
if (condition != null)
{
var filedsArr = condition.Fields.Select(GetWhereSql);
whereSql = string.Join(" and ", filedsArr);
}
else
{
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
whereSql = GetWhereSql(pk);
}
string sql = string.Format("delete from {0} where {1}", Escape(TableName), whereSql);
if (tran == null)
{
_sqlHelper.ExecuteNonQuery(sql);
}
else
{
_sqlHelper.ExecuteNonQuery(tran, sql);
}
}
internal string GetUpdateSql()
{
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
string setSql = GetUpdateSetSql();
var sql = string.Format("update {0} set {1} where {2};", Escape(TableName), setSql, GetWhereSql(pk));
return(sql);
}
/*static EntityPool()
* {
* var entityPoolType = typeof(TSelfReferenceType);
* var attributes = entityPoolType.GetCustomAttributes(false).Where(x => x is DataBaseAttribute);
* if (attributes.Any())
* {
* var dBAttribute = (DataBaseAttribute)attributes.FirstOrDefault();
* NativeSql = IntrospectionManager.GetSqlHelperByKey(dBAttribute.Key);
* }
* else
* {
* throw new ClassNotHaveDataBaseAttributeException(entityPoolType.Name);
* }
* } */
public EntityPool()
{
Key = IntrospectionManager.GetDbKey(GetType().FullName);
if (string.IsNullOrWhiteSpace(Key) && Configure.SetKey != null)
{
Key = Configure.SetKey.Invoke(this);
}
}
internal string GetUpdateSetSql()
{
var infos = IntrospectionManager.GetColumns(TableFullName);
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
var filedsArr = infos.Keys.Where(x => !pk.Contains(x) && Resources.Keys.Contains(x)).Select(GetSetSql);
var setSql = string.Join(",", filedsArr);
return(setSql);
}
protected Entity()
{
TableFullName = GetType().FullName;
Key = IntrospectionManager.GetTableKey(TableFullName);
if (string.IsNullOrWhiteSpace(Key) && Configure.SetKey != null)
{
Key = Configure.SetKey.Invoke(this);
}
TableName = IntrospectionManager.GetTableName(TableFullName);
Resources = new Dictionary <string, object>();
}
public static SqlHelper Sql(string key)
{
var sqlHelper = IntrospectionManager.GetSqlHelperByKey(key);
if (sqlHelper == null)
{
throw new SqlHelperCanNotExists(key);
}
else
{
return(sqlHelper);
}
}
public virtual void Update(Partly partly, Condition condition, DbTransaction tran = null)
{
_entityStatus = "updated";
string setSql = "";
string whereSql = "";
if (partly != null)
{
var filedsArr = partly.Fields.Where(x => Resources.Keys.Contains(x)).Select(GetSetSql);
setSql = partly.Fields.Any() ? string.Join(",", filedsArr) : "*";
}
else
{
setSql = GetUpdateSetSql();
}
if (condition != null)
{
var filedsArr = condition.Fields.Select(GetWhereSql);
whereSql = string.Join(" and ", filedsArr);
}
else
{
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
whereSql = GetWhereSql(pk);
}
string sql = string.Format("update {0} set {1} where {2}", Escape(TableName), setSql, whereSql);
if (tran == null)
{
_sqlHelper.ExecuteNonQuery(sql);
}
else
{
_sqlHelper.ExecuteNonQuery(tran, sql);
}
}
/// <summary>
/// Betch insert
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="list">Entity list</param>
/// <param name="isIdAutoGrow">Is id auto grow?</param>
/// <param name="trans">Transaction</param>
public virtual void Save <T>(List <T> list, bool isIdAutoGrow = true, DbTransaction trans = null) where T : Entity
{
var sql = new StringBuilder();
var typeName = typeof(T).FullName;
var pk = IntrospectionManager.GetPrimaryMemberName(typeName);
var setValue = IntrospectionManager.GetSetDelegate(typeName);
var isNeedCommit = false;
var isSupportBatch = ProviderManager.GetProvider(ProviderName).IsSupportBatch;
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql.Append(ProviderManager.GetProvider(ProviderName).BatchBegin);
}
else if (trans == null)
{
var conn = NativeSql.GetConn();
conn.Open();
trans = conn.BeginTransaction();
isNeedCommit = true;
}
foreach (var item in list)
{
item.Key = Key;
var sqlT = item.GetSaveSql(isIdAutoGrow);
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql.Append(sqlT);
if (isIdAutoGrow)
{
sql.Append(item.GetLastInsertIdSql());
}
}
else
{
if (ProviderName == DataBaseNames.Oracle)
{
sqlT = sqlT.Remove(sqlT.LastIndexOf(";"), 1);
}
NativeSql.ExecuteNonQuery(trans, sqlT);
if (isIdAutoGrow)
{
object idValue = NativeSql.ExecuteScalar(trans, item.GetLastInsertIdSql());
if (idValue != null)
{
var pkType = IntrospectionManager.GetPrimaryKeyType(typeName);
setValue(item, pk, idValue.ChangeType(pkType));
}
}
}
}
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql.Append(ProviderManager.GetProvider(ProviderName).BatchEnd);
DataSet ds;
if (trans == null)
{
ds = NativeSql.ExecuteDataSet(sql.ToString());
}
else
{
ds = NativeSql.ExecuteDataSet(trans, sql.ToString());
}
if (isIdAutoGrow)
{
var pkType = IntrospectionManager.GetPrimaryKeyType(typeName);
for (int i = 0; i < list.Count; i++)
{
object idValue = ds.Tables[i].Rows[0][0];
setValue(list[i], pk, idValue.ChangeType(pkType));
}
}
}
if (!isSupportBatch && isNeedCommit)
{
var conn = trans.Connection;
trans.Commit();
trans.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// Load data list
/// </summary>
/// <typeparam name="T">Entity</typeparam>
/// <param name="by">Sql,All,Top,Page,Between</param>
/// <returns></returns>
public List <T> List <T>(By by) where T : new()
{
#region BySql
if (by is BySql)
{
var bySql = by as BySql;
var trans = bySql.Tran;
var cmdText = bySql.CmdText;
var cmdType = bySql.CmdType;
var cmdParms = bySql.CmdParms;
var list = new List <T>();
Type type = typeof(T);
var typeName = type.FullName;
Dictionary <string, string> infos = null;
if (IntrospectionManager.IsExistsColumnName(typeName))
{
infos = IntrospectionManager.GetColumns(typeName);
}
else
{
infos = new Dictionary <string, string>();
var fields = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty | BindingFlags.GetProperty);
foreach (var fieldInfo in fields)
{
infos.Add(fieldInfo.Name, fieldInfo.Name);
}
IntrospectionManager.SetColumns(typeName, infos);
IntrospectionManager.SetDelegate(type);
}
DbDataReader dr = null;
if (trans == null)
{
dr = NativeSql.ExecuteReader(cmdText, cmdType, cmdParms);
}
else
{
dr = NativeSql.ExecuteReader(trans, cmdText, cmdType, cmdParms);
}
while (dr.Read())
{
T entity = new T();
foreach (var propertyName in infos.Keys)
{
var fieldName = infos[propertyName];
if (dr.GetSchemaTable().Select("columnname='" + fieldName + "'").Length > 0)
{
object columnValue = dr[fieldName];
if (columnValue is System.DBNull)
{
columnValue = null;
}
IntrospectionManager.GetSetDelegate(typeName)(entity, propertyName, columnValue);
}
/* object columnValue = dr[fieldName];
* if (columnValue is DBNull)
* {
* columnValue = null;
* }
* IntrospectionManager.GetSetDelegate(typeName)(entity, propertyName, columnValue);*/
}
list.Add(entity);
}
dr.Close();
dr.Dispose();
return(list);
}
#endregion
#region ByAll
else if (by is ByAll)
{
var byAll = by as ByAll;
var asc = byAll.Asc;
var trans = byAll.Trans;
string _left = ProviderManager.GetProvider(ProviderName).GetLeftEscape();
string _right = ProviderManager.GetProvider(ProviderName).GetRightEscape();
string sql;
var typeName = typeof(T).FullName;
var tableName = IntrospectionManager.GetTableName(typeName);
var pk = IntrospectionManager.GetPrimaryColumnName(typeName);
if (asc)
{
sql = string.Format("select * from {0}", _left + tableName + _right);
}
else
{
sql = string.Format("select * from {0} order by {1} desc", _left + tableName + _right, _left + pk + _right);
}
return(List <T>(By.Sql(sql, trans)));
}
#endregion
#region ByTop
else if (by is ByTop)
{
var byTop = by as ByTop;
var top = byTop.Top;
var asc = byTop.Asc;
var trans = byTop.Trans;
var typeName = typeof(T).FullName;
var pk = IntrospectionManager.GetPrimaryColumnName(typeName);
var tableName = IntrospectionManager.GetTableName(typeName);
string sql = ProviderManager.GetProvider(ProviderName).GetTopSql(top, asc, tableName, pk);
return(List <T>(By.Sql(sql, trans)));
}
#endregion
#region ByPage
else if (by is ByPage)
{
var byPage = by as ByPage;
var page = byPage.Page;
var pageSize = byPage.PageSize;
var asc = byPage.Asc;
var trans = byPage.Trans;
var typeName = typeof(T).FullName;
var pk = IntrospectionManager.GetPrimaryColumnName(typeName);
var tableName = IntrospectionManager.GetTableName(typeName);
string sql = ProviderManager.GetProvider(ProviderName).GetPageSql(page, pageSize, asc, tableName, pk);
return(List <T>(By.Sql(sql, trans)));
}
#endregion
#region ByBetween
else if (by is ByBetween)
{
var byBetween = by as ByBetween;
var from = byBetween.From;
var to = byBetween.To;
var asc = byBetween.Asc;
var trans = byBetween.Trans;
string _left = ProviderManager.GetProvider(ProviderName).GetLeftEscape();
string _right = ProviderManager.GetProvider(ProviderName).GetRightEscape();
var typeName = typeof(T).FullName;
var pk = IntrospectionManager.GetPrimaryColumnName(typeName);
var tableName = IntrospectionManager.GetTableName(typeName);
string sql;
if (asc)
{
sql = string.Format("select * from {0} where {1} between {2} and {3}", _left + tableName + _right,
_left + pk + _right, from, to);
}
else
{
sql = string.Format("select * from {0} where {1} between {2} and {3} order by {1}", _left + tableName + _right,
_left + pk + _right, from, to);
}
return(List <T>(By.Sql(sql, trans)));
}
#endregion
else
{
throw new NotSupportThisByException(by == null ? "null" : by.ToString());
}
}
/// <summary>
/// Load data for this entity
/// </summary>
public virtual void Load(By by)
{
if (by is BySql)
{
#region BySql
var tmp = by as BySql;
var trans = tmp.Tran;
var cmdText = tmp.CmdText;
var cmdType = tmp.CmdType;
var cmdParms = tmp.CmdParms;
_entityStatus = "loaded";
var columns = IntrospectionManager.GetColumns(TableFullName);
DbDataReader dataReader;
if (trans == null)
{
dataReader = _sqlHelper.ExecuteReader(cmdText, cmdType, cmdParms);
}
else
{
dataReader = _sqlHelper.ExecuteReader(trans, cmdText, cmdType, cmdParms);
}
if (dataReader.Read())
{
foreach (var propertyName in columns.Keys)
{
var fieldName = columns[propertyName];
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable != null && schemaTable.Select("columnname='" + fieldName + "'").Length > 0)
{
object columnValue = dataReader[fieldName];
SetValue(propertyName, columnValue.ChangeType(columnValue.GetType()));
}
//if not exists this filed
else
{
SetValue(propertyName, null);
}
}
}
else
{
foreach (var propertyName in columns.Keys)
{
SetValue(propertyName, null);
}
}
dataReader.Close();
dataReader.Dispose();
#endregion
return;
}
else if (by is ById)
{
var tmp = by as ById;
var idValue = tmp.IdValue;
var tran = tmp.Tran;
var pk = IntrospectionManager.GetPrimaryColumnName(TableFullName);
string sql = string.Format("select * from {0} where {1}", Escape(TableName), GetWhereSqlByInputValue(pk, idValue));
Load(By.Sql(sql, tran));
return;
}
else if (by is ByAttach)
{
var pk = IntrospectionManager.GetPrimaryColumnName(TableFullName);
if (!pk.Any())
{
throw new TableNotHavePrimaryKeyException(TableFullName);
}
var tmp = by as ByAttach;
var idValue = tmp.IdValue;
var filedsArr = tmp.Fields.Select(Escape);
var fields = tmp.Fields.Any() ? string.Join(",", filedsArr) : "*";
string sql = string.Format("select {2} from {0} where {1}", Escape(TableName), GetWhereSqlByInputValue(pk, idValue), fields);
Load(By.Sql(sql));
return;
}
throw new NotSupportThisByException(by == null ? "null" : by.ToString());
}
public void SetValue(string memberName, object value)
{
InvokeSetProperty(memberName, value);
IntrospectionManager.GetSetDelegate(TableFullName)(this, memberName, value);
}
public object GetValue(string memberName)
{
return(IntrospectionManager.GetGetDelegate(TableFullName)(this, memberName));
}
/// <summary>
/// Insert entity to database
/// </summary>
/// <param name="isIdAutoGrow">Id is or isn't auto grow </param>
/// <param name="transaction">Transaction</param>
public virtual void Save(bool isIdAutoGrow = true, DbTransaction transaction = null)
{
_entityStatus = "saved";
var isNeedCommit = false;
var isSupportBatch = ProviderManager.GetProvider(ProviderName).IsSupportBatch;
var pk = IntrospectionManager.GetPrimaryMemberName(TableFullName);
var sql = "";
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql += ProviderManager.GetProvider(ProviderName).BatchBegin;
}
else if (transaction == null)
{
DbConnection conn = _sqlHelper.GetConn();
conn.Open();
transaction = conn.BeginTransaction();
isNeedCommit = true;
}
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql += GetSaveSql(isIdAutoGrow);
if (isIdAutoGrow)
{
sql += GetLastInsertIdSql();
}
}
else
{
var sqlT = GetSaveSql(isIdAutoGrow);
if (ProviderName == DataBaseNames.Oracle)
{
sqlT = sqlT.Remove(sqlT.LastIndexOf(";"), 1);
}
_sqlHelper.ExecuteNonQuery(transaction, sqlT);
if (isIdAutoGrow)
{
var idValue = _sqlHelper.ExecuteScalar(transaction, GetLastInsertIdSql());
if (idValue != null)
{
var pkType = IntrospectionManager.GetPrimaryKeyType(TableFullName);
SetValue(pk, idValue.ChangeType(pkType));
}
}
}
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
sql += ProviderManager.GetProvider(ProviderName).BatchEnd;
}
if (isSupportBatch && ProviderName != DataBaseNames.Oracle)
{
object idValue;
if (transaction == null)
{
idValue = _sqlHelper.ExecuteScalar(sql);
}
else
{
idValue = _sqlHelper.ExecuteScalar(transaction, sql);
}
if (isIdAutoGrow && idValue != null)
{
var pkType = IntrospectionManager.GetPrimaryKeyType(TableFullName);
SetValue(pk, idValue.ChangeType(pkType));
}
}
if (!isSupportBatch && isNeedCommit)
{
var conn = transaction.Connection;
transaction.Commit();
transaction.Dispose();
conn.Close();
conn.Dispose();
}
}
internal string GetColumnName(string memberName)
{
return(IntrospectionManager.GetColumns(TableFullName)[memberName]);
}