public ActionResult EditTable() { if (OrgTable == null) { OrgTable = new SchemaTable(); OrgTable.ID = Guid.NewGuid(); OrgTable.Name = @"Организация"; OrgTable.Description = "Справочник организаций и контрагентов"; SchemaTableColumn col; col = new SchemaTableColumn(); col.Table = OrgTable; OrgTable.Columns.Add(col); col.Name = "Номер"; col.Description = "Номер оганизации"; col.Position = 1; col = new SchemaTableColumn(); col.Table = OrgTable; OrgTable.Columns.Add(col); col.Name = "Название"; col.Description = "Название оганизации"; col.Position = 2; col = new SchemaTableColumn(); col.Table = OrgTable; OrgTable.Columns.Add(col); col.Name = "Город"; col.Description = "Родной город оганизации"; col.Position = 1010; } var model = new SchemaTableEditModel(); model.Controller = this; model.EditedObject = OrgTable; return View(model); }
private SchemaTable getSchemaTable(DbDataReader reader) { SchemaTable schemaTable = new SchemaTable(); DataTable resultSchema = reader.GetSchemaTable(); foreach (DataRow row in resultSchema.Rows) { SchemaColumn schemaColumn = new SchemaColumn(); schemaTable.Add(schemaColumn); schemaColumn.ColumnName = getDataRowColumn(row, "ColumnName") as string; schemaColumn.ColumnOrdinal = Convert.ToInt32(getDataRowColumn(row, "ColumnOrdinal")); schemaColumn.ColumnSize = Convert.ToInt32(getDataRowColumn(row, "ColumnSize")); schemaColumn.NumericPrecision = Convert.ToInt32(getDataRowColumn(row, "NumericPrecision")); schemaColumn.NumericScale = Convert.ToInt32(getDataRowColumn(row, "NumericScale")); schemaColumn.ProviderType = Convert.ToInt32(getDataRowColumn(row, "ProviderType")); schemaColumn.IsLong = (getDataRowColumn(row, "IsLong") as bool?).GetValueOrDefault(); schemaColumn.AllowDBNull = (getDataRowColumn(row, "AllowDBNull") as bool?).GetValueOrDefault(); schemaColumn.IsReadOnly = (getDataRowColumn(row, "IsReadOnly") as bool?).GetValueOrDefault(); schemaColumn.IsRowVersion = (getDataRowColumn(row, "IsRowVersion") as bool?).GetValueOrDefault(); schemaColumn.IsUnique = (getDataRowColumn(row, "IsUnique") as bool?).GetValueOrDefault(); schemaColumn.IsKey = (getDataRowColumn(row, "IsKey") as bool?).GetValueOrDefault(); schemaColumn.IsAutoIncrement = (getDataRowColumn(row, "IsAutoIncrement") as bool?).GetValueOrDefault(); schemaColumn.BaseTableName = getDataRowColumn(row, "BaseTableName") as string; schemaColumn.BaseColumnName = getDataRowColumn(row, "BaseColumnName") as string; /////////////////////////// object obj = getDataRowColumn(row, "DataType"); Type t = Type.GetType(obj.ToString()); XmlTypeMapping xmlMapping = getQualifiedNameForSystemType(t); schemaColumn.DataType = xmlMapping.XsdTypeName; /* * Console.WriteLine("-------------------"); * Console.WriteLine(xmlMapping.ElementName); * Console.WriteLine(xmlMapping.Namespace); * Console.WriteLine(xmlMapping.TypeFullName); * Console.WriteLine(xmlMapping.TypeName); * Console.WriteLine(xmlMapping.XsdElementName); * Console.WriteLine(xmlMapping.XsdTypeName); * Console.WriteLine(xmlMapping.XsdTypeNamespace); */ /* * XmlSerializer xs = new XmlSerializer(t); * var sb = new StringBuilder(); * using (var writer = new StringWriter(sb)) * { * xs.Serialize(writer, obj); * writer.Close(); * } * * //Xmltype * Console.WriteLine(sb.ToString()); */ } return(schemaTable); }
public virtual IProcedureBuilder DropDefaultProcedures(SchemaTable table) { DropProcedure(new SchemaProcedure($"Select{table.NameNormalized}", StoredProcedureType.Select) .ForTable(table)); DropProcedure( new SchemaProcedure( $"Select{GetSingularTableName(table.Name)}By{table.PrimaryKeyColumn.NameNormalized}", StoredProcedureType.SelectByKey) .ForTable(table) .WithParameter(table.PrimaryKeyColumn)); // delete from table where primaryKey = @primaryKey DropProcedure( new SchemaProcedure( $"Delete{GetSingularTableName(table.Name)}By{table.PrimaryKeyColumn.NameNormalized}", StoredProcedureType.DeleteByKey) .ForTable(table) .WithParameter(table.PrimaryKeyColumn)); // insert / update by primary key DropProcedure( new SchemaProcedure($"InsertUpdate{GetSingularTableName(table.Name)}", StoredProcedureType.InsertUpdate) .ForTable(table)); return(this); }
private bool SendToDb <T>(T data, EntityPropertyGetFunc <T> getFunc, EnttiyPostColumnFunc <T> postColumnFunc, bool synchronous) where T : ISqlEntity { if (Equals(data, null)) { return(false); } SchemaTable schemaTable = EntitySchemaSet.Get(data.GetType()); DbBaseProvider dbProvider = DbConnectionProvider.CreateDbProvider(_connectKey ?? schemaTable.ConnectKey); if (dbProvider == null) { return(false); } CommandStruct command = GenerateCommand(dbProvider, data, schemaTable, getFunc, postColumnFunc); if (command != null) { int result; if (synchronous) { //同时采集 ProfileManager.PostSqlOfMessageQueueTimes(command.TableName, 1); ProfileManager.ProcessSqlOfMessageQueueTimes(command.TableName); result = dbProvider.ExecuteQuery(CommandType.Text, command.Sql, command.Parameters); } else { //put into pool result = dbProvider.ExecuteNonQuery(data.GetMessageQueueId(), CommandType.Text, command.TableName, command.Sql, command.Parameters); } data.ResetState(); return(result > 0); } return(false); }
private static bool CovertDataValue(SchemaTable schemaTable, SchemaColumn schemaColumn, ref object value) { if (value is DateTime && value.ToDateTime() < MathUtils.SqlMinDate) { return(false); } //序列化Json if (schemaColumn.IsJson) { try { value = value ?? string.Empty; if (!string.IsNullOrEmpty(schemaColumn.JsonDateTimeFormat)) { value = JsonUtils.SerializeCustom(value); } else { value = JsonUtils.Serialize(value); } } catch (Exception ex) { TraceLog.WriteError("Table:{0} column:\"{0}\" json serialize error:\r\n:{1}", schemaTable.Name, schemaColumn.Name, ex); return(false); } } return(true); }
private static bool CovertDataValue(SchemaTable schemaTable, SchemaColumn schemaColumn, ref object value) { if (value is DateTime && value.ToDateTime() < MathUtils.SqlMinDate) { return(false); } //序列化Json if (schemaColumn.IsSerialized) { if (schemaColumn.DbType == ColumnDbType.LongBlob || schemaColumn.DbType == ColumnDbType.Blob) { value = SerializeBinaryObject(schemaTable, schemaColumn, value); } else { value = SerializeJson(schemaTable, schemaColumn, value); } if (value == null) { return(false); } } return(true); }
private static object DeserializeJsonObject(SchemaTable schemaTable, AbstractEntity entity, object value, SchemaColumn fieldAttr, string columnName) { try { if (fieldAttr.ColumnType.IsSubclassOf(typeof(Array))) { value = value.ToString().StartsWith("[") ? value : "[" + value + "]"; } string tempValue = value.ToNotNullString(); if (!string.IsNullOrEmpty(fieldAttr.JsonDateTimeFormat) && tempValue.IndexOf(@"\/Date(") == -1) { return(JsonUtils.DeserializeCustom(tempValue, fieldAttr.ColumnType, fieldAttr.JsonDateTimeFormat)); } else { return(JsonUtils.Deserialize(tempValue, fieldAttr.ColumnType)); } } catch (Exception ex) { TraceLog.WriteError("Table:{0} key:{1} column:{2} deserialize json error:{3} to {4}\r\nException:{5}", schemaTable.EntityName, entity.GetKeyCode(), columnName, value, fieldAttr.ColumnType.FullName, ex); } return(null); }
///<summary> ///</summary> ///<param name="redisKey"></param> ///<param name="schema"></param> ///<param name="capacity"></param> ///<param name="dbFilter"></param> public TransReceiveParam(string redisKey, SchemaTable schema, int capacity, DbDataFilter dbFilter) : this(redisKey) { Schema = schema; Capacity = capacity; DbFilter = dbFilter; }
private bool SendToDb <T>(T data, EntityPropertyGetFunc <T> getFunc, EnttiyPostColumnFunc <T> postColumnFunc, bool synchronous) where T : ISqlEntity { if (Equals(data, null)) { return(false); } SchemaTable schemaTable = EntitySchemaSet.Get(data.GetType()); DbBaseProvider dbProvider = DbConnectionProvider.CreateDbProvider(_connectKey ?? schemaTable.ConnectKey); if (dbProvider == null) { return(false); } CommandStruct command = GenerateCommand(dbProvider, data, schemaTable, getFunc, postColumnFunc); if (command != null) { bool result = (synchronous ? dbProvider.ExecuteQuery(CommandType.Text, command.Sql, command.Parameters) : dbProvider.ExecuteNonQuery(data.GetMessageQueueId(), CommandType.Text, command.Sql, command.Parameters)) > 0; data.ResetState(); return(result); } return(false); }
/// <summary> /// /// </summary> /// <param name="personalId"></param> /// <param name="dataList"></param> /// <returns></returns> protected bool TryLoadFromRedis(out List <T> dataList, string personalId = null) { string redisKey = string.IsNullOrEmpty(personalId) ? containerKey : string.Format("{0}_{1}", containerKey, personalId); SchemaTable schema = EntitySchemaSet.Get <T>(); return(RedisConnectionPool.TryGetEntity(redisKey, schema, out dataList)); }
protected override void TruncateTables(Sql.ManagementSession session, SchemaTable table) { var sql = new StringBuilder(); sql.Append("TRUNCATE TABLE " + table.StatementName); session.ExecuteSql(sql.ToString()); }
protected override void CreateTable(Sql.ManagementSession session, SchemaTable schemaTable) { var keys = new List <SchemaColumn>(); var sql = new StringBuilder(); sql.Append("CREATE TABLE " + schemaTable.StatementName); sql.Append("("); foreach (SchemaColumn column in schemaTable) { sql.Append(column.StatementName + " " + this.GetSqlType(column)); if (column.IsKey) { keys.Add(column); } sql.Append(",\n"); } sql.Append("CONSTRAINT " + schemaTable.Name + "_pk PRIMARY KEY ( "); for (int i = 0; i < keys.Count; i++) { if (i > 0) { sql.Append(", "); } sql.Append(keys[i].Name); } sql.Append(" )\n"); sql.Append(")"); session.ExecuteSql(sql.ToString()); }
private IList <string> GetPostColumns(ISqlEntity data, SchemaTable schemaTable, bool isChange) { List <string> columns = null; if (data is AbstractEntity) { var entity = data as AbstractEntity; //修正not change column if (!entity.IsNew && !entity.IsDelete && entity.HasChangePropertys && isChange && schemaTable.Columns.Keys.Count > schemaTable.Keys.Length) { columns = entity.DequeueChangePropertys().ToList(); } else { if (entity.HasChangePropertys) { entity.ResetChangePropertys(); } columns = schemaTable.GetColumnNames(); } } return(columns); }
/// <summary> /// /// </summary> /// <param name="schema"></param> /// <param name="key"></param> /// <returns></returns> public static IDataSender GetRedisSender(SchemaTable schema, string key) { return(new RedisDataSender(new TransSendParam(key) { Schema = schema })); }
/// <summary> /// add or update /// </summary> /// <param name="t"></param> /// <param name="period"></param> /// <returns></returns> public bool AddOrUpdate(T t, int period = 0) { string key = t.GetKeyCode(); SchemaTable schemaTable = SchemaTable(); int periodTime = schemaTable == null ? 0 : schemaTable.PeriodTime; return(AddOrUpdateEntity(key, t, periodTime)); }
protected override void DropTable(Sql.ManagementSession session, SchemaTable schemaTable) { var sql = new StringBuilder(); sql.Append( @"DROP TABLE IF EXISTS " + schemaTable + " CASCADE;"); session.ExecuteSql(sql.ToString()); }
public ICollection <string> v_1_0_1() { // Add indexes to ShellFeatures var shellFeatures = new SchemaTable() { Name = "ShellFeatures", Columns = new List <SchemaColumn>() { new SchemaColumn() { PrimaryKey = true, Name = "Id", DbType = DbType.Int32 }, new SchemaColumn() { Name = "ModuleId", DbType = DbType.String, Length = "255" }, new SchemaColumn() { Name = "[Version]", DbType = DbType.String, Length = "10" }, new SchemaColumn() { Name = "Settings", DbType = DbType.String, Length = "max" } } }; var output = new List <string>(); using (var builder = _schemaBuilder) { builder .Configure(options => { options.ModuleName = ModuleId; options.Version = "1.0.1"; options.DropProceduresBeforeCreate = true; }); // ------------------- // placeholder // ------------------- // Add builder results to output output.AddRange(builder.Statements); } return(output); }
public SchemaTableColumn(SchemaTable table, string name, string dataType, int?characterMaximumLength, int?numericPrecision, int?numericScale) { this.Table = table; this.Name = name; this.LowercaseName = name.ToLowerInvariant(); this.DataType = dataType; this.CharacterMaximumLength = characterMaximumLength; this.NumericPrecision = numericPrecision; this.NumericScale = numericScale; }
public SchemaTableColumn(SchemaTable table, string name, string dataType, int? characterMaximumLength, int? numericPrecision, int? numericScale) { this.table = table; this.name = name; this.lowercaseName = name.ToLowerInvariant(); this.dataType = dataType; this.characterMaximumLength = characterMaximumLength; this.numericPrecision = numericPrecision; this.numericScale = numericScale; }
/* Methods */ /// <inheritdoc /> public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { SchemaTable comparison = value as SchemaTable; if (comparison == null) { return(null); } return(new object[] { comparison.Columns }); }
/// <summary> /// /// </summary> /// <param name="schema"></param> /// <returns></returns> public static DbBaseProvider CreateDbProvider(SchemaTable schema) { if (!string.IsNullOrEmpty(schema.ConnectKey)) { return(CreateDbProvider(schema.ConnectKey)); } if (!string.IsNullOrEmpty(schema.ConnectionString)) { return(CreateDbProvider(schema.ConnectKey, schema.ConnectionProviderType, schema.ConnectionString)); } return(null); }
public void Dispose() { if (SchemaTable != null) { SchemaTable.Dispose(); } if (Maindataset != null) { Maindataset.Dispose(); } }
private string GetPropertyChainColumn(string chain, ORMSelect q) { EnsureChainJoins(chain, q); List <PropertyInfo> pis = new ORMPropertyChain(chain).GetChain(q.FocalType); PropertyInfo endProp = pis[pis.Count - 1]; SchemaTable t = _Schema.EnsureSchema(endProp.ReflectedType); string colName = t.GetColumnByName(endProp.Name).Column.Name; return(GetJoinAlias(chain, q) + "." + colName); }
/// <summary> /// Comprueba si una columna es una clave foránea /// </summary> private bool CheckIsForeignKey(SchemaTable objTable, SchemaColumn objColumn) { // Recorre las restricciones comprobando si es una clave foránea foreach (SchemaConstraint objConstraint in objTable.Constraints) { if (objConstraint.Type == SchemaConstraint.ConstraintType.ForeignKey && objColumn.Name.EqualsIgnoreCase(objConstraint.Column)) { return(true); } } // Si ha llegado hasta aquí es porque no es una clave foránea return(false); }
private void EnsureChainJoins(List <PropertyInfo> pis, ORMSelect q) { SQFromTable t = (SQFromTable)Query.From.GetByAlias(q.FocalType.Name); if (pis.Count > 1) { string previousAlias = q.FocalType.Name; SchemaTable previousTable = _Schema.GetSchemaForType(q.FocalType); string currChain = pis[0].Name; for (int i = 1; i < pis.Count; i++) { PropertyInfo prevPi = pis[i - 1]; PropertyInfo pi = pis[i]; // TODO: use foreign key hints somehow currChain += "." + pi.Name; string joinAlias = GetJoinAlias(currChain, q); SchemaTable nextTable = _Schema.GetSchemaForType(pi.ReflectedType); if (Query.From.GetByAlias(joinAlias) == null) { SQJoin newJoin = new SQJoin() { Table = new SQAliasableObject(nextTable.Table.Name, joinAlias), JoinType = SQJoinTypes.Left }; t.Append(newJoin); if (typeof(IEnumerable).IsAssignableFrom(prevPi.PropertyType) && pi.ReflectedType != prevPi.PropertyType) { // join to a child ORMChildJoinHint hint = prevPi.GetJoinHint(); if (hint != null) { newJoin.Predicate = new SQCondition(previousAlias + "." + previousTable.Table.GetPrimaryKey().Name, SQRelationOperators.Equal, joinAlias + "." + nextTable.GetColumnByName(nextTable.GetFKeyByName(hint.FKeyPropertyName).Storage).Column.Name); } } else { // join to a parent newJoin.Predicate = new SQCondition(previousAlias + "." + previousTable.GetColumnByName(previousTable.GetFKeyByName(prevPi.Name).Storage).Column.Name, SQRelationOperators.Equal, joinAlias + "." + nextTable.Table.GetPrimaryKey().Name); } } previousAlias = joinAlias; previousTable = nextTable; } } }
private void SetEntityValue(SchemaTable schemaTable, IDataReader reader, AbstractEntity entity) { var columns = schemaTable.GetColumnNames(); foreach (var columnName in columns) { SchemaColumn fieldAttr; if (!schemaTable.Columns.TryGetValue(columnName, out fieldAttr)) { continue; } object fieldValue = null; if (fieldAttr.IsSerialized) { var value = reader[columnName]; //指定序列化方式 if (fieldAttr.DbType == ColumnDbType.LongBlob || fieldAttr.DbType == ColumnDbType.Blob) { fieldValue = DeserializeBinaryObject(schemaTable, entity, value, fieldAttr, columnName); } else { fieldValue = DeserializeJsonObject(schemaTable, entity, value, fieldAttr, columnName); } if (fieldValue is EntityChangeEvent) { ((EntityChangeEvent)fieldValue).PropertyName = columnName; } } else { try { fieldValue = entity.ParseValueType(reader[columnName], fieldAttr.ColumnType); } catch (Exception ex) { TraceLog.WriteError("Table:{0} column:{1} parse value error:\r\n{0}", schemaTable.EntityName, columnName, ex); } } if (fieldAttr.CanWrite) { entity.SetPropertyValue(columnName, fieldValue); } else { entity.SetFieldValue(columnName, fieldValue); } } }
/// <summary> /// 从数据库恢复 /// </summary> /// <param name="chart">条件</param> /// <param name="value">值</param> public static void RecoverFromDb(string chart, int value = -2) { var cacheSet = new GameDataCacheSet <tb_User>(); if (cacheSet.Count == 0) { SchemaTable schema = EntitySchemaSet.Get <tb_User>(); DbBaseProvider provider = DbConnectionProvider.CreateDbProvider(schema); DbDataFilter filter = new DbDataFilter(0); filter.Condition = provider.FormatFilterParam("isRobot", chart); filter.Parameters.Add("isRobot", value); cacheSet.TryRecoverFromDb(filter);//从数据库中恢复数据 } }
private void OnSyncSql(object state) { try { if (Monitor.TryEnter(syncSqlObject, 1000)) { try { TraceLog.ReleaseWrite("Sql waiting to be written to the Redis"); while (true) { var list = CacheChangeManager.Current.PopSql(100); if (list.Count == 0) { break; } foreach (var key in list) { string redisKey = key; try { dynamic entity = CacheFactory.GetEntityFromRedis(redisKey, true); SchemaTable schemaTable = entity.GetSchema(); if (entity != null && schemaTable != null) { DbBaseProvider dbProvider = DbConnectionProvider.CreateDbProvider(schemaTable); if (dbProvider != null) { DataSyncManager.GetDataSender().Send(entity, false); } } } catch (Exception e) { TraceLog.WriteError("Sync to db {0} error:{1}", redisKey, e); } } } } finally { Monitor.Exit(syncSqlObject); } } } catch (Exception ex) { TraceLog.WriteError("Sql sync error:{0}", ex); } }
public void Delete(IDGBase vl, IDBExecutor exec) { SchemaTable t = Schema.EnsureSchema(vl.GetType()); if (t != null) { SQDeleteQuery dlt = new SQDeleteQuery() { DeleteTable = new SQAliasableObject(t.Table.Name), Condition = new SQCondition(t.Table.GetPrimaryKey().Name, SQRelationOperators.Equal, vl.ID.ToString()) }; dlt.Execute(exec); } }
/// <summary> /// Carga las restricciones de una tabla /// </summary> private void LoadConstraints(SQLServerProvider objConnection, SchemaTable objTable) { ParametersDBCollection objColParameters = new ParametersDBCollection(); string strSQL; // Añade los parámetros objColParameters.Add("@Table_Catalog", objTable.Catalog); objColParameters.Add("@Table_Schema", objTable.Schema); objColParameters.Add("@Table_Name", objTable.Name); // Crea la cadena SQL strSQL = @"SELECT TableConstraints.Table_Catalog, TableConstraints.Table_Schema, TableConstraints.Table_Name, ColumnConstraint.Column_Name, ColumnConstraint.Constraint_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position FROM Information_Schema.Table_Constraints AS TableConstraints INNER JOIN Information_Schema.Constraint_Column_Usage AS ColumnConstraint ON TableConstraints.Constraint_Catalog = ColumnConstraint.Constraint_Catalog AND TableConstraints.Constraint_Schema = ColumnConstraint.Constraint_Schema AND TableConstraints.Constraint_Name = ColumnConstraint.Constraint_Name INNER JOIN Information_Schema.Key_Column_Usage AS Key_Column ON ColumnConstraint.Constraint_Catalog = Key_Column.Constraint_Catalog AND ColumnConstraint.Constraint_Schema = Key_Column.Constraint_Schema AND ColumnConstraint.Constraint_Name = Key_Column.Constraint_Name AND ColumnConstraint.Column_Name = Key_Column.Column_Name WHERE TableConstraints.Table_Catalog = @Table_Catalog AND TableConstraints.Table_Schema = @Table_Schema AND TableConstraints.Table_Name = @Table_Name ORDER BY TableConstraints.Table_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position" ; // Carga los datos using (IDataReader rdoConstraint = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text)) { // Lee los datos while (rdoConstraint.Read()) { SchemaConstraint objConstraint = new SchemaConstraint(objTable.Parent); // Asigna los datos del registro objConstraint.Catalog = (string)rdoConstraint.IisNull("Table_Catalog"); objConstraint.Schema = (string)rdoConstraint.IisNull("Table_Schema"); objConstraint.Table = (string)rdoConstraint.IisNull("Table_Name"); objConstraint.Column = (string)rdoConstraint.IisNull("Column_Name"); objConstraint.Name = (string)rdoConstraint.IisNull("Constraint_Name"); objConstraint.Type = GetConstraintType((string)rdoConstraint.IisNull("Constraint_Type")); objConstraint.Position = (int)rdoConstraint.IisNull("Ordinal_Position"); // Añade la restricción a la colección objTable.Constraints.Add(objConstraint); } // Cierra el recordset rdoConstraint.Close(); } }
public void Update(IDGBase vl, IDBExecutor exec) { SchemaTable t = Schema.EnsureSchema(vl.GetType()); if (t != null) { SQUpdateQuery upd = new SQUpdateQuery() { UpdateTable = new SQAliasableObject(t.Table.Name), Condition = new SQCondition(t.Table.GetPrimaryKey().Name, SQRelationOperators.Equal, vl.ID.ToString()) }; PopulateSetQuery(upd, vl, t); upd.Execute(exec); } }
public override void DropIndex(Sql.ManagementSession session, SchemaTable table, SchemaColumn column) { var indexName = Sql.Schema.AllorsPrefix + table.Name + "_" + column.Name; try { var sql = new StringBuilder(); sql.Append("DROP INDEX IF EXISTS " + indexName); session.ExecuteSql(sql.ToString()); } catch (Exception e) { throw new Exception("Could not drop index " + indexName, e); } }