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 GetWhereSql(string memberName) { var value = GetValue(memberName); var columnName = IntrospectionManager.GetColumns(TableFullName)[memberName]; return(GetWhereSqlByInputValue(columnName, value)); }
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 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); }
/// <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()); }
internal string GetColumnName(string memberName) { return(IntrospectionManager.GetColumns(TableFullName)[memberName]); }