public void TestUpdate() { ISqlCommand cmd = new CodingCat.MsSqlTeaser.SqlCommand() .AppendCommand("Select TOP 100 * From tblTest ") .AppendCommand("Where PK = @id "); cmd.AddParameter("@id", 5); IDbTable DataTable = null; using (IDbConnector DbConnector = new CodingCat.MsSqlTeaser.DbConnector( this.ConnectionString ).Read(cmd, out DataTable)) { if (DataTable != null && DataTable.Rows.Count > 0) { DataTable.FirstRow .AttachValue("TextField", "TestTextField2") .AttachValue("NVarcharField", "TestNVarcharField2") .AttachValue("DateTimeField", System.DateTime.Now); DbConnector.Update(DataTable); System.Diagnostics.Debug.WriteLine( "LatestID: {0}, Affected: {1}", DbConnector.LatestID, DbConnector.AffectedRowCount ); } } }
public DbQueryContext(IDbTable <TModel> dbTable) : this() { string connectionString = App.Configuration["connectionStrings/default"]; _queryTable = dbTable; _serviceProvider = _container.Resolve <IDbServiceProvider>(connectionString); }
private static IEnumerable <Column> GetUpdatableColumns(this Model model, IReadOnlyList <ColumnMapping> parentRelationship) { IDbTable dbTable = model.DataSource as IDbTable; bool isTempTable = dbTable == null ? false : dbTable.Kind == DataSourceKind.DbTempTable; bool isTable = dbTable == null ? false : dbTable.Kind == DataSourceKind.DbTable; var identity = dbTable == null ? null : model.GetIdentity(isTempTable); Column identityColumn = identity == null ? null : identity.Column; foreach (var column in model.Columns) { if (column == identityColumn) { continue; } if (isTable && column.IsDbComputed) { continue; } if (parentRelationship != null && parentRelationship.ContainsSource(column)) { continue; } yield return(column); } }
public Database() { ConfigTable = Globals.CreateInstance <IDbTable <IConfig> >(); FilesetTable = Globals.CreateInstance <IDbTable <IFileset> >(); CharacterTable = Globals.CreateInstance <IDbTable <ICharacter> >(); ModuleTable = Globals.CreateInstance <IDbTable <IModule> >(); RoomTable = Globals.CreateInstance <IDbTable <IRoom> >(); ArtifactTable = Globals.CreateInstance <IDbTable <IArtifact> >(); EffectTable = Globals.CreateInstance <IDbTable <IEffect> >(); MonsterTable = Globals.CreateInstance <IDbTable <IMonster> >(); HintTable = Globals.CreateInstance <IDbTable <IHint> >(); TriggerTable = Globals.CreateInstance <IDbTable <ITrigger> >(); ScriptTable = Globals.CreateInstance <IDbTable <IScript> >(); GameStateTable = Globals.CreateInstance <IDbTable <IGameState> >(); }
/// <summary> /// Gets insertable columns. /// </summary> /// <param name="model">The model.</param> /// <returns>The insertable columns.</returns> public static IEnumerable <Column> GetInsertableColumns(this Model model) { model.VerifyNotNull(nameof(model)); IDbTable dbTable = model.DataSource as IDbTable; bool isTempTable = dbTable == null ? false : dbTable.Kind == DataSourceKind.DbTempTable; bool isTable = dbTable == null ? false : dbTable.Kind == DataSourceKind.DbTable; var identity = dbTable == null ? null : model.GetIdentity(isTempTable); Column identityColumn = identity == null ? null : identity.Column; foreach (var column in model.Columns) { if (column == identityColumn && !model.IsIdentitySuspended) { continue; } if (isTable && column.IsDbComputed) { continue; } yield return(column); } }
public void RenderTableName(IDbTable table, StringBuilder output) { // Special characters must be enclosed in brackets. output.Append("["); output.Append(table.TableName); output.Append("]"); }
public void TestInsert() { ISqlCommand cmd = new CodingCat.MsSqlTeaser.SqlCommand().AppendCommand("Select TOP 100 * From tblTest "); IDbTable DataTable = null; using (IDbConnector DbConnector = new CodingCat.MsSqlTeaser.DbConnector( this.ConnectionString ).Read(cmd, out DataTable)) { if (DataTable != null) { DataTable.AppendRow( DataTable.GetEmptyRow() .AttachValue("TextField", "TestTextField") .AttachValue("NVarcharField", "TestNVarcharField") .AttachValue("DateTimeField", System.DateTime.Now) ); DbConnector.Insert(DataTable); System.Diagnostics.Debug.WriteLine( "LatestID: {0}, Affected: {1}", DbConnector.LatestID, DbConnector.AffectedRowCount ); } } }
internal DbSelectStatement BuildInsertScalarStatement <TSource>(DataSet <TSource> dataSet, int rowOrdinal, IReadOnlyList <ColumnMapping> columnMappings) where TSource : class, IEntity, new() { var sourceModel = dataSet._; var parentMappings = ShouldJoinParent(dataSet) ? this.Model.GetParentRelationship(columnMappings) : null; var paramManager = new ScalarParamManager(dataSet[rowOrdinal]); var select = GetScalarMapping(paramManager, columnMappings); IDbTable parentTable = null; if (parentMappings != null) { parentTable = (IDbTable)Model.ParentModel.DataSource; Debug.Assert(parentTable != null); var parentRowIdMapping = new ColumnMapping(Model.GetSysParentRowIdColumn(createIfNotExist: false), parentTable.Model.GetSysRowIdColumn(createIfNotExist: false)); select = select.Append(parentRowIdMapping); } DbFromClause from = GetScalarDataSource(paramManager, parentMappings); DbExpression where = null; if (from != null) { if (parentMappings != null) { from = new DbJoinClause(DbJoinKind.InnerJoin, from, parentTable.FromClause, parentMappings); } } return(new DbSelectStatement(Model, select, from, where, null, -1, -1)); }
public DbExecuteContext( IDbTable <TModel> dbTable, IDbServiceProvider runtimeProvider, IDbQueryStrategyProvider queryStrategyProvider) : this() { _queryTable = dbTable; _serviceProvider = runtimeProvider; _queryStrategyProvider = queryStrategyProvider; }
public virtual RetCode SaveRecords <T>(IDbTable <T> table, string fileName, bool printOutput = true) where T : class, IGameBase { RetCode rc; if (table == null || table.Records == null || string.IsNullOrWhiteSpace(fileName)) { rc = RetCode.InvalidArg; // PrintError goto Cleanup; } rc = RetCode.Success; if (printOutput) { gOut.Write("{0}Saving textfile [{1}] ... ", Environment.NewLine, fileName); } Globals.SharpSerializer.Serialize(table, fileName); if (printOutput) { gOut.Write("wrote {0} record{1}.", table.Records.Count, table.Records.Count != 1 ? "s" : ""); } Cleanup: return(rc); }
public void RenderAlias(IDbTable table, StringBuilder output) { // Special characters must be enclosed in quotes. output.Append("\""); output.Append(table.Alias); output.Append("\""); }
public ActionResult Add(PImageData model) { string fileName = Path.GetFileNameWithoutExtension(model.ImageFile.FileName); string extension = Path.GetExtension(model.ImageFile.FileName); fileName = fileName + DateTime.Now.ToString("yymmssfff") + extension; model.Image.ImagePath = "~/Images/Galerie/" + fileName; fileName = Path.Combine(Server.MapPath("~/Images/Galerie/"), fileName); model.ImageFile.SaveAs(fileName); IDbTable new_img = new IDbTable(); Mapper.Initialize(cfg => cfg.CreateMap <ImageData, Image>()); var image = Mapper.Map <Image>(model.Image); using (ImageContext db = new ImageContext()) { new_img.ImageID = image.ImageID; new_img.Title = image.Title; new_img.ImagePath = image.ImagePath; db.Images.Add(new_img); db.SaveChanges(); } return(RedirectToAction("Index", "Galerie")); }
private static string[] CreatePkColNames(IDbTable targetTable) { string[] pkColNames = new string[targetTable.PrimaryKey.Count]; for (int pkPart = 0; pkPart < pkColNames.Length; pkPart++) pkColNames[pkPart] = targetTable.PrimaryKey[pkPart].ColumnName; return pkColNames; }
private Func <CancellationToken, Task> GetAction(IDbTable dbTable) { if (_actions == null) { return(null); } return(_actions.TryGetValue(dbTable, out var result) ? result : null); }
private static void CreateSelectListItems(LevelQuerySelectList itemsToSelect, IDbTable targetLevelTable, SelectStatement selectFromTargetLevel) { if (itemsToSelect == LevelQuerySelectList.AllColumns) selectFromTargetLevel.SelectList.Add(targetLevelTable.Columns); else if (itemsToSelect == LevelQuerySelectList.Count) selectFromTargetLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount")); else if (itemsToSelect == LevelQuerySelectList.PrimaryKey) selectFromTargetLevel.SelectList.Add(targetLevelTable.PrimaryKey); }
public IDbQueryContext <TModel> Join <TJoinModel>( IDbTable <TJoinModel> table, Expression <Func <TModel, object> > innerSpecifierExpression, Expression <Func <TModel, object> > outerSpecifierExpression) where TJoinModel : class, new() { var queryContext = new DbQueryContext <TModel>(this, _serviceProvider); return(queryContext.Join(table, innerSpecifierExpression, outerSpecifierExpression)); }
/// <summary> /// Builds UPDATE query statement. /// </summary> /// <param name="target">The target DbTable.</param> /// <param name="source">The source DbTable.</param> /// <param name="columnMappings">Column mappings between source and target DbTables.</param> /// <param name="keyMappings">Key mappings between source and target DbTables.</param> /// <returns>The query statement</returns> public static DbSelectStatement BuildUpdateStatement(this IDbTable target, IDbTable source, IReadOnlyList <ColumnMapping> columnMappings, IReadOnlyList <ColumnMapping> keyMappings) { target.VerifyNotNull(nameof(target)); source.VerifyNotNull(nameof(source)); Verify(columnMappings, nameof(columnMappings), source.Model, target.Model); Verify(keyMappings, nameof(keyMappings), source.Model, target.Model); return(source.QueryStatement.BuildUpdateStatement(target.Model, columnMappings, keyMappings)); }
private static IDbColumn GetAutoIdField(IDbTable table) { foreach (IDbColumn field in table.Columns) { if (field.AutoIncrement) return field; } return null; }
private static bool HasAutoIdField(IDbTable table) { foreach (IDbColumn field in table.Columns) { if (field.AutoIncrement) return true; } return false; }
public void RenderAlias(IDbTable table, StringBuilder output) { // Special characters must be enclosed in quotes. bool hasSpecChars = TextUtil.HasSpecialChars(table.Alias); if (hasSpecChars) output.Append("\""); output.Append(table.Alias); if (hasSpecChars) output.Append("\""); }
private static void AppendFromFirstTableNameAndAlias(IDbTable firstTable, DbmsType dbms, StringBuilder from) { from.Append("FROM "); firstTable.RenderTableName(dbms, from); if (firstTable.Alias != firstTable.TableName) { // AS is not used because some DBMSs don't support that syntax. from.Append(" "); firstTable.RenderAlias(dbms, from); } }
public void TestRead() { ISqlCommand cmd = new CodingCat.MsSqlTeaser.SqlCommand().AppendCommand("Select TOP 100 * From tblTest "); IDbTable DataTable = null; using (new CodingCat.MsSqlTeaser.DbConnector(this.ConnectionString).Read(cmd, out DataTable)) if (DataTable != null) { System.Diagnostics.Debug.WriteLine("Rows: {0}", DataTable.Rows.Count); } }
public IDbConnector Update( IDbTable dbTable ) { this.ResetStateProperties(); this.Connection = this.OpenSqlConnection(this.Connection); this.AffectedRowCount = this.DataAdapter.Update(dbTable.GetSourceTable <System.Data.DataTable>()); this.GetAndAssignLatestID(this.Connection); this.Connection.Close(); return(this); }
private static void AppendTableNameAndAlias(DbmsType dbms, StringBuilder from, IDbTable currTable) { currTable.RenderTableName(dbms, from); from.Append(" "); if (currTable.Alias != currTable.TableName) { // AS is not used because some DBMSs don't support that syntax. from.Append(" "); currTable.RenderAlias(dbms, from); from.Append(" "); } }
private static string CreateAlias(IDbTable targetTable, bool superriors) { if (superriors) { bool shouldUseUniqueName = (targetTable.TableName.ToLowerInvariant() == defaultTableName) || (targetTable.Alias.ToLowerInvariant() == defaultTableName); return (shouldUseUniqueName) ? uniqueSuperriorsAlias : defaultSuperriorsAlias; } else { return null; } }
/// <summary> /// Gets the relation that connects the specified tables. <b>Null</b> if it doesn't exist. /// </summary> public DbRelation this[IDbTable t1, IDbTable t2] { get { foreach (DbRelation relation in this.list) { if (relation.Connects(t1, t2)) return relation; } return null; } }
internal FromClause(IDbTable firstTable, DbRelation[] orderedRelations, TableWithJoinMode[] orderedFromTablesIncludingFirstTable) { this.FromTable = firstTable; JoinClause[] joins = new JoinClause[orderedRelations.Length]; for (int relationIdx = 0; relationIdx < orderedRelations.Length; relationIdx++) { int joinedTableIdx = relationIdx + 1; joins[relationIdx] = new JoinClause(orderedRelations[relationIdx], orderedFromTablesIncludingFirstTable[joinedTableIdx].Table, orderedFromTablesIncludingFirstTable[joinedTableIdx].JoinAsOuter); } this.Joins = joins; this.JoinCount = joins.Length; }
private static IDbColumn GetAutoIdField(IDbTable table) { IDbColumn identityField = null; foreach (IDbColumn field in table.Columns) { if (field.AutoIncrement) { identityField = field; break; } } return identityField; }
public virtual void FreeRecordUid <T>(IDbTable <T> table, long uid) where T : class, IGameBase { if (table == null) { // PrintError goto Cleanup; } table.FreeRecordUid(uid); Cleanup: ; }
public UserService() { if (typeof(T) == typeof(StandardUser)) { _table = (IDbTable <T>)LocalDatabase.StandardUsers; } if (typeof(T) == typeof(PremiumUser)) { _table = (IDbTable <T>)LocalDatabase.PremiumUsers; } if (typeof(T) == typeof(TrainerUser)) { _table = (IDbTable <T>)LocalDatabase.TrainerUsers; } }
private static DbRelation JoinLevelsInSubTree(IDbTable prevLevel, IDbTable nextLevel, DbRelation recursiveRelation) { int keyLen = recursiveRelation.ChildForeignKey.Length; IDbColumn[] parentPkCols = new IDbColumn[keyLen]; IDbColumn[] childFkCols = new IDbColumn[keyLen]; for (int keyPart = 0; keyPart < keyLen; keyPart++) { string pkPartName = recursiveRelation.ParentPrimaryKey[keyPart].ColumnName; parentPkCols[keyPart] = nextLevel.Columns.GetByColumnName(pkPartName); string fkPartName = recursiveRelation.ChildForeignKey[keyPart].ColumnName; childFkCols[keyPart] = prevLevel.Columns.GetByColumnName(fkPartName); } DbRelation joinLevels = new DbRelation(nextLevel, parentPkCols, prevLevel, childFkCols, null); return joinLevels; }
public virtual RetCode LoadGameStates(string fileName, bool validate = true, bool printOutput = true) { RetCode rc; var table = GameStateTable; rc = LoadRecords <IGameState, IGameStateHelper>(ref table, fileName, validate, printOutput); if (gEngine.IsFailure(rc)) { Globals.Error.WriteLine("Error: LoadRecords function call failed"); } GameStateTable = table; return(rc); }
public virtual RetCode FreeRecords <T>(IDbTable <T> table, bool dispose = true) where T : class, IGameBase { RetCode rc; if (table == null) { rc = RetCode.InvalidArg; // PrintError goto Cleanup; } rc = table.FreeRecords(dispose); Cleanup: return(rc); }
public virtual long GetRecordsCount <T>(IDbTable <T> table) where T : class, IGameBase { long result; if (table == null) { result = -1; // PrintError goto Cleanup; } result = table.GetRecordsCount(); Cleanup: return(result); }
public virtual RetCode AddRecord <T>(IDbTable <T> table, T record, bool makeCopy = false) where T : class, IGameBase { RetCode rc; if (table == null) { rc = RetCode.InvalidArg; // PrintError goto Cleanup; } rc = table.AddRecord(record, makeCopy); Cleanup: return(rc); }
public virtual T RemoveRecord <T>(IDbTable <T> table, long uid) where T : class, IGameBase { T result; result = default(T); if (table == null) { // PrintError goto Cleanup; } result = table.RemoveRecord(uid); Cleanup: return(result); }
public virtual long GetRecordUid <T>(IDbTable <T> table, bool allocate = true) where T : class, IGameBase { long result; result = -1; if (table == null) { // PrintError goto Cleanup; } result = table.GetRecordUid(allocate); Cleanup: return(result); }
public virtual T RemoveRecord <T>(IDbTable <T> table, Type type, bool exactMatch = false) where T : class, IGameBase { T result; result = default(T); if (table == null || type == null) { // PrintError goto Cleanup; } result = table.RemoveRecord(type, exactMatch); Cleanup: return(result); }
private static string CreateLevelColumnName(IDbTable targetTable) { bool shouldUseUniqueName = false; foreach (IDbColumn col in targetTable.Columns) { if (col.ColumnName.ToLowerInvariant() == defaultLevelColumnName) { shouldUseUniqueName = true; break; } if (col.Alias.ToLowerInvariant() == defaultLevelColumnName) { shouldUseUniqueName = true; break; } } return (shouldUseUniqueName) ? uniqueLevelColumnName : defaultLevelColumnName; }
/// <summary> /// Checks all declared DbTable<> properties and assigns them the current context instance /// </summary> private void OnEntityRegister() { foreach (var property in GetType().GetProperties()) { if (typeof(IDbTable).IsAssignableFrom(property.PropertyType)) { /* * Get the details about the DbTable<T> type including what kind of type T is * Then use that to create an instance of DbTable with the correct type T */ Type propGenericType = property.PropertyType.GetGenericTypeDefinition(); Type[] typeArguments = property.PropertyType.GenericTypeArguments; var generic = propGenericType.MakeGenericType(typeArguments); IDbTable instance = (IDbTable)Activator.CreateInstance(generic); instance.SetAssignedContext(this); property.SetValue(this, instance); } } }
public IDbConnector Read( ISqlCommand sqlCommand, out IDbTable dbTable ) { this.ResetStateProperties(); this.Connection = this.OpenSqlConnection(); using (var cmd = sqlCommand.GenerateSqlCommandInstance <System.Data.SqlClient.SqlCommand>()) { cmd.Connection = this.Connection; this.DataAdapter = this.GetDataAdapter(cmd); this.CommandBuilder = this.GetCommandBuilder(this.DataAdapter); var DataTable = new System.Data.DataTable(); this.DataAdapter.Fill(DataTable); dbTable = new DbTable(DataTable); } this.Connection.Close(); return(this); }
public static Dictionary <string, string> GetTableNameAndColumns <T>(this IDbTable dbTable) { Type tType = typeof(T); Dictionary <string, string> columnNames; if (!CacheEntry.TypeCache.TryGetValue(tType, out columnNames)) { if (columnNames == null) { columnNames = new Dictionary <string, string>(); } PropertyInfo[] properties = tType.GetProperties(); var dbTableNameAttr = tType.GetCustomAttribute <DBTableNameAttribute>(); foreach (PropertyInfo property in properties) { object[] attributes = property.GetCustomAttributes(true); if (attributes.Length <= 0) { columnNames.Add(property.Name, property.Name); } foreach (object columnAttr in attributes) { DBColumnNameAttribute columnNameAttr = columnAttr as DBColumnNameAttribute; DBColumnIgnoreAttribute ignoreColumnAttr = columnAttr as DBColumnIgnoreAttribute; if (columnNameAttr != null) { if (ignoreColumnAttr == null || ignoreColumnAttr.Ignore == false) { columnNames.Add(property.Name, columnNameAttr != null ? columnNameAttr.Name : property.Name); } } } } if (columnNames != null && columnNames.Count >= 0) { CacheEntry.TypeCache.TryAdd(tType, columnNames); } } return(columnNames); }
private static DbColumnCollection CreateColumns(IDbTable targetTable) { int ordinal = 0; DbColumnCollection levelAndPk = new DbColumnCollection(); IDbColumnConfiguration config = new DbColumnConfiguration(CreateLevelColumnName(targetTable), DbType.Int32, typeof(int), false, ordinal, false, 0, 1, false, false, false, "Level", int.MinValue, int.MaxValue, false, null); levelAndPk.Add(new SealedDbColumn(config, targetTable)); ordinal = 1; foreach (IDbColumn pkPart in targetTable.PrimaryKey) { config = new DbColumnConfiguration(pkPart.ColumnName, pkPart.DbType, pkPart.DataType, pkPart.IsNullable, ordinal, pkPart.AutoIncrement, pkPart.DefaultValue, pkPart.MaxLengthIfText, pkPart.IsPrimaryKeyPart, pkPart.IsForeignKeyPart, pkPart.IsUniqueConstraintPart, pkPart.PropertyName, pkPart.MinValue, pkPart.MaxValue, pkPart.IsAutoGenerated, pkPart.SequenceName, pkPart.ExtendedProperties); levelAndPk.Add(new SealedDbColumn(config, targetTable)); ordinal++; } return levelAndPk; }
/// <summary> /// Constructor /// </summary> /// <param name="table"> /// IdbTable with defined structure /// </param> /// <param name="includeFields"> /// Which fields are included in data group /// </param> /// <param name="whereCause"> /// Where cause to select corresponding record /// </param> public DataGroup( IDbTable table, ISimpleDataAccess dataAccess, string[] includeFields, string whereCause) { Validation.RequireValid(table, "table"); Validation.RequireValid(dataAccess, "dataAccess"); Validation.RequireValid(includeFields, "includeFields"); Validation.RequireValidString(whereCause, "whereCause"); if (includeFields.Length < 1) { throw new LythumException("DataGroup: Must be at least one data group field!"); } this.Table = table; this.DataAccess = dataAccess; this.IncludeFields = includeFields; this.WhereCause = whereCause; this.IsDataLoaded = false; }
private static void RenderJoinTable(DbmsType dbms, StringBuilder from, DbRelation currRelation, IDbTable currTable, bool useLeftJoin) { AppendJoinMode(from, useLeftJoin); AppendTableNameAndAlias(dbms, from, currTable); RenderOnStatement(currRelation, dbms, from); }
public virtual RetCode LoadRecords <T, U>(ref IDbTable <T> table, string fileName, bool validate = true, bool printOutput = true) where T : class, IGameBase where U : class, IHelper <T> { IDbTable <T> table01; RetCode rc; if (table == null || table.Records == null || string.IsNullOrWhiteSpace(fileName)) { rc = RetCode.InvalidArg; // PrintError goto Cleanup; } rc = RetCode.Success; if (printOutput) { gOut.Write("{0}Loading textfile [{1}] ... ", Environment.NewLine, fileName); } try { Globals.UpgradeTextfile(fileName); table01 = Globals.SharpSerializer.Deserialize(fileName) as IDbTable <T>; } catch (FileNotFoundException) { table01 = null; } if (table01 == null || table01.Records == null) { table01 = Globals.CreateInstance <IDbTable <T> >(); Debug.Assert(table01 != null && table01.Records != null); } if (validate) { var helper = Globals.CreateInstance <U>(); long i = 1; foreach (var r in table01.Records) { helper.Record = r; if (helper.ValidateRecord() == false) { rc = RetCode.Failure; Globals.Error.WriteLine("{0}Error: expected valid [{1} value], found [{2}]", Environment.NewLine, helper.GetName(helper.ErrorFieldName), helper.GetValue(helper.ErrorFieldName) ?? "null"); Globals.Error.WriteLine("Error: Validate function call failed for record number {0}", i); goto Cleanup; } i++; } } foreach (var r in table01.Records) { r.SetParentReferences(); } rc = FreeRecords(table, false); if (gEngine.IsFailure(rc)) { // PrintError } table = table01; if (printOutput) { gOut.Write("read {0} record{1}.", table.Records.Count, table.Records.Count != 1 ? "s" : ""); } Cleanup: return(rc); }
/// <summary> /// Adds a relation to the bucket. /// </summary> /// <param name="parent">Parent/master table.</param> /// <param name="child">Child/data table.</param> /// <param name="childForeignKey">Child foreign key.</param> /// <remarks>Child table is connected as outer. Parent table is connected as outer if foreign /// key is made of exactly one nullable column. If parallel relations are used then tables must /// use aliases, otherwise an error will occur.</remarks> /// <exception cref="ArgumentException">is generated if the bucket already contains a relation /// which contains the same tables with the same aliases as the new relation.</exception> public void Add(IDbTable parent, IDbTable child, IDbColumn childForeignKey) { DbRelation relation = new DbRelation(parent, child, childForeignKey); Add(relation); }
/// <summary> /// Renders SQL FROM clause starting with the specified table. /// Automatically determines the correct order of JOIN clauses and modes (inner/left outer) for all tables. /// </summary> /// <param name="firstTable">1st table in the FROM clause.</param> /// <param name="dbms">DBMS. Required because table names with special characters are handled differently on different DBMSs.</param> /// <param name="output">StringBuilder to which SQL is appended.</param> /// <returns>FROM clause.</returns> public void RenderFromClause(IDbTable firstTable, DbmsType dbms, StringBuilder output) { // For each table. // Create empty ordered 'from tables' and relations list. // Lists are filled with all tables and relations in the matching order which enables the creation of FromClause. // // Add current table to the beginning of the 'from' list. // // (1) Get 1st relation wich connects one of the tables already in the 'from' list to a new table. // If such relation exists. // Add to ordered relation list. // Add new table to the from list. // If the new table needs to be connected with 'left outer join' then set its JoinAsOuter flag. // Continue at step (1) // Algorithm assumes that the first table is the most important one. // If the entity in that table is null then values from other tables will be null as well. // This means that right outer joins are never used. // Algorithm builds the from statement beginning with the specified table and all the other tables // are connected by inner or left outer joins. // Note: even though DbRelation objects contain all the required information to build // right outer joins that is never done. DbRelation[] orderedRelations; TableWithJoinMode[] orderedFromTables; SortRelationsAndJoinModes(this.relations, this.joinModes, firstTable, out orderedRelations, out orderedFromTables); ThrowExceptionIfNotAllTablesAreConnected(orderedFromTables); FromClause from = new FromClause(firstTable, orderedRelations, orderedFromTables); IFromClauseRenderer renderer = DbmsComponentFactory.GetComponent<IFromClauseRenderer>(dbms); renderer.Render(from, dbms, output); }
private static void SortRelationsAndJoinModes(DbRelationCollection allRelations, List<JoinMode> allJoinModes, IDbTable firstTable, out DbRelation[] orderedRelations, out TableWithJoinMode[] orderedFromTables) { // Sorts relations in a fashion so that only INNER and LEFT OUTER joins are used. RIGHT joins will never be required. orderedRelations = new DbRelation[allRelations.Count]; int relationIdx = 0; orderedFromTables = new TableWithJoinMode[allJoinModes.Count + 1]; int fromIdx = 0; // JoinAsOuter property doesn't doesn't affect 1st table. orderedFromTables[fromIdx] = new TableWithJoinMode(firstTable, false); fromIdx++; DbRelation nextRelation; TableWithJoinMode nextTable; while (GetNextRelationAndTable(allRelations, allJoinModes, orderedFromTables, out nextRelation, out nextTable)) { orderedRelations[relationIdx++] = nextRelation; orderedFromTables[fromIdx++] = nextTable; } }
/// <summary> /// Creates a new select statements that retrieves rows and columns from the given table. /// </summary> /// <param name="fromTable">Initial table in the FROM clause.</param> public SelectStatement(IDbTable fromTable) : base(fromTable) { }
public TableWithJoinMode(IDbTable table, bool joinAsOuter) { this.Table = table; this.JoinAsOuter = joinAsOuter; }
/// <summary> /// Initializes a new instance of the RelationBucket class and adds an initial relation to the bucket using <see cref="Add(DbRelation)"/> method. /// </summary> /// <param name="parent">Parent/master table.</param> /// <param name="child">Child/data table.</param> /// <param name="childForeignKey">Child foreign key.</param> public RelationBucket(IDbTable parent, IDbTable child, IDbColumn childForeignKey) { Add(parent, child, childForeignKey); }
/// <summary> /// Creates a new select statements that retrieves rows and columns from the given table. /// </summary> /// <param name="fromTable">Initial table in the FROM clause.</param> /// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param> public SelectStatement(IDbTable fromTable, Function selectList) : base(fromTable) { this.selectList.Add(selectList); }
internal SqlCommand GetInsertCommand(DbSelectStatement statement, IDbTable identityOutput = null) { return(SqlGenerator.Insert(this, statement, identityOutput).CreateCommand(Connection)); }
/// <summary> /// Initializes entity members with all data stored in the given entity. /// </summary> /// <param name="existing">Instance of IEntity. Must be compatibile with current entity; otherwise an exception is generated.</param> /// <remarks>Copies all class members. Parent entities are not copied by reference, but cloned (deep copy).</remarks> protected virtual void FromExistingEntity(IEntity existing) { if (_Table == null) _Table = existing.Table.Clone(null); // throw new ArgumentException("Given entity doesn't belong to same table as current entity. Expected " + _Table.TableName + ", but " + existing.Table.TableName + " was provided.", "existing"); if (_Table.TableName != existing.Table.TableName) throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Messages.EntityModelBase_EntityNotFromSameTableExpextedXButGotY, _Table.TableName, existing.Table.TableName), "existing"); // Clone meta. 'this.table' will be instantiated when _Table getter is accessed for the first time. this.tableAlias = existing.Table.Alias; this.dbTableClass = existing.Table.GetType(); this.table = null; // Members mapped to fields. object[] entityValues = CloneObjectArray(existing.ToObjectArray()); FromObjectArray(entityValues); // Parents. Temporary disable relation navigation to prevent possible data-access when cloning parents. bool relationNavigationStatusBeforeParentCloning = existing.RelationshipNavigationEnabled; existing.RelationshipNavigationEnabled = false; foreach (DbRelation fk in _Table.ForeignKeys) { IEntity parent = existing.GetParent(fk); // Only set if not null (it's already null) because when setting parent entity property to NULL // then FK field would also nulled, thus undoing the previous step (field cloning) and causing error. if (parent != null) this.SetParent(fk, parent.Clone() as IEntity); } // Restore original status of relationship navigation. existing.RelationshipNavigationEnabled = relationNavigationStatusBeforeParentCloning; // Behavior. Entity state, relation navigation. this.entityState = existing.EntityState; this.relationshipNavigationEnabled = existing.RelationshipNavigationEnabled; }
/// <summary> /// Creates a new UPDATE statement that updates rows in the given table. /// </summary> /// <param name="table">The table to update.</param> /// <param name="columnToUpdate">First columns in the <see cref="UpdateList"/>. Additional items may be specified in <see cref="UpdateList"/> collection.</param> /// <param name="newValue">New value of the specified field. May be <b>null</b>.</param> public UpdateStatement(IDbTable table, IDbColumn columnToUpdate, object newValue) : this(table) { this.UpdateList.Add(columnToUpdate, newValue); }
/// <summary> /// Initializes entity members with data stored in the given DataRow. /// </summary> /// <param name="row">DataRow with all or some of the columns defined in meta data.</param> /// <param name="fieldMetadata"><see cref="IDbTable"/> meta data object which links ADO.NET row columns to entity properties.</param> /// <remarks><p>This method, when used in combination with <see cref="IDbTable"/> objects that contain columns with /// prefixed aliases (see <see cref="IDbTable.SetPrefixedAliases()"/>), enables you to initialize multiple /// entities that belong to different tables with data from a single DataRow. This is particularly /// usefull when fetching an entity with one or more of its parent entities, since all required data can be /// retrieved in a single select statement.</p> /// <p>Generic implementation is not optimized, and should be overriden and optimized in derived classes. Latest version of FistCore generator generates /// optimized code. Generic implementation is only intended to be used for compatibility purposes, ie. if the code hasn't been regenerated.</p> /// </remarks> /// <example>The following example shows how to retrieve data required to initialize an EmployeesEntity /// and its EmployeesEntity.EmployeesSuperrior parent property. /// <code> /// public EmployeesEntity FetchEmployeesAndManager(int employeeId) /// { /// // Parent entity is from the same table. /// // Assign alises to tables and set prefixed aliases to all columns. /// // SELECT statement which is sent to the database looks like this: /// // SELECT [workers].[EmployeeID] AS [workers_EmployeeID], [workers].[LastName] AS [workers_LastName]... /// // This ensures that all items in the select list have unique aliases, /// // thus ensuring unique column names in the resulting ADO.NET DataRow. /// /// EmployeesMeta workers = new EmployeesMeta("workers", /*set column aliases*/ true); /// EmployeesMeta managers = new EmployeesMeta("managers", /*set column aliases*/ true); /// SelectStatement select = new SelectStatement(workers); /// select.SelectList.Add(workers.Columns); /// select.SelectList.Add(managers.Columns); /// select.Relations.Add(new DbRelation(managers, workers, workers.ReportsTo)); /// select.Where.Add(workers.EmployeeID, employeeId); /// /// DataTable data = select.Execute(); /// DataRow row = (data.Rows.Count > 0) ? data.Rows[0] : null; /// if (row == null) /// return null; /// /// EmployeesEntity emp = new EmployeesEntity(); /// emp.FromDataRow(row, workers); /// bool hasSuperrior = !emp.IsNull(emp.Table.ReportsTo); /// if (hasSuperrior) /// { /// EmployeesEntity reportsTo = new EmployeesEntity(); /// reportsTo.FromDataRow(row, managers); /// emp.EmployeesSuperior = reportsTo; /// } /// /// return emp; /// } /// </code> /// </example> public virtual void FromDataRow(DataRow row, IDbTable fieldMetadata) { IDbColumn currentColumn; object currentColumnValue; foreach (IDbColumn entityField in _Table.Columns) { currentColumn = fieldMetadata.Columns.GetByPropertyName(entityField.PropertyName); currentColumnValue = (currentColumn != null) ? GetColumnValue(row, currentColumn) : null; if (currentColumnValue != null) SetField(entityField, currentColumnValue); else ResetField(entityField); } }
/// <summary> /// Creates a new select statements that retrieves rows and columns from the given table. /// </summary> /// <param name="fromTable">Initial table in the FROM clause.</param> /// <param name="selectList">Item(s) to fetch. Additional items may be specified in <see cref="SelectList"/> collection.</param> public SelectStatement(IDbTable fromTable, params IDbColumn[] selectList) : base(fromTable) { this.selectList.Add(selectList); }
/// <summary> /// Creates a new UPDATE statement that updates rows in the given table. /// </summary> /// <param name="table">The table to update.</param> public UpdateStatement(IDbTable table) : base(table) { }
/// <summary> /// Creates a statement that inserts a single row. /// </summary> /// <param name="intoTable">The table that is to receive the data.</param> public InsertStatement(IDbTable intoTable) : base(intoTable) { }