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 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); }
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); }
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> /// 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(); } }