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)
{
}
