public string GetSelect(ITableMap tableMap, SqlBuilderColumns columns, string whereStatement)
{
var sql = string.Format("select {0} from {1}", GetColumns(tableMap), GetSchemaAndTable(tableMap));
return string.IsNullOrWhiteSpace(whereStatement)
? sql
: sql + " where " + whereStatement;
}
public string GetDelete(ITableMap tableMap)
{
var propertyMaps = tableMap.Columns;
var keys = propertyMaps.Where(x => x.IsPrimaryKey);
return string.Format("delete {0} where {1}",
GetSchemaAndTable(tableMap),
GetColumnEqualsParameter(" and ", keys));
}
public string GetInsert(ITableMap tableMap)
{
var propertyMaps = tableMap.Columns;
return string.Format("insert {0} ({1}) values ({2})",
GetSchemaAndTable(tableMap),
GetColumns(tableMap),
string.Join(", ", propertyMaps.Select(GetParameter)));
}
public string GetUpdate(ITableMap tableMap)
{
var propertyMaps = tableMap.Columns;
var keys = propertyMaps.Where(x => x.IsPrimaryKey);
var values = propertyMaps.Where(x => !x.IsPrimaryKey);
return string.Format("update {0} set {1} where {2}",
GetSchemaAndTable(tableMap),
GetColumnEqualsParameter(", ", values),
GetColumnEqualsParameter(" and ", keys));
}
public string Insert <T>(ITableMap <T> tableMap)
{
var insertColumns = tableMap.InsertColumns;
var insert = $"insert into {BuildTableIdentifier(tableMap)}({string.Join(", ", insertColumns.Select(BuildColumnIdentifier))})";
var values = $"values({string.Join(", ", insertColumns.Select(ic => BuildColumnParameter(ic)))});";
var generatedColumns = tableMap.GeneratedColumns;
if (generatedColumns.Any())
{
return(string.Join(Environment.NewLine,
"declare @outputResult table(",
string.Join($",{Environment.NewLine}", generatedColumns.Select(c => $" {BuildColumnIdentifier(c)} {c.GetSqlType()}")),
");",
insert,
$"output {string.Join(", ", generatedColumns.Select(c => $"inserted.{BuildColumnIdentifier(c)}"))} into @outputResult({string.Join(", ", generatedColumns.Select(c => BuildColumnIdentifier(c)))})",
values,
"select * from @outputResult;"));
}
else
{
return(string.Join(Environment.NewLine, insert, values));
}
}
public Table(Database database)
{
this.database = database;
this.tableMap = database.MapTable <TEntity>();
}
public ArrayList GetPropertyMapsForTable(ITableMap tableMap)
{
throw new Exception("The method or operation is not implemented.");
}
public void SetTableMap(ITableMap value)
{
throw new Exception("The method or operation is not implemented.");
}
public SqlAllColumnsInTableSelectListItem AddSqlAllColumnsInTableSelectListItem(ITableMap tableMap)
{
SqlTableAlias sqlTableAlias = this.SqlStatement.GetSqlTableAlias(tableMap);
return AddSqlAllColumnsInTableSelectListItem(sqlTableAlias) ;
}
public virtual void SetTableMap(ITableMap value)
{
m_Table = value.Name;
}
public static bool TryGetTableMap(Type type, out ITableMap tableMap) => GetTableMaps().TryGetValue(type, out tableMap);
public virtual void TouchTable(ITableMap tableMap, int exceptionLimit)
{
;
}
public virtual string GetTouchStatement(ITableMap tableMap, int exceptionLimit)
{
SqlSelectStatement select = new SqlSelectStatement(tableMap.SourceMap);
SqlTableAlias table = select.GetSqlTableAlias(tableMap);
select.SqlSelectClause.AddSqlAllColumnsSelectListItem();
select.Top = 1;
select.SqlFromClause.AddSqlAliasTableSource(table);
string sql = this.GenerateSql(select);
return sql;
}
public override void Map <TRecord>(ITableMap <TRecord> tableMap)
{
tableMap.Map(x => x.CreatedByUserId).Ignore(SqlOperation.Update);
tableMap.Map(x => x.CreatedDateUtc).Generated();
}
public void SetTableMap(ITableMap value)
{
this.TableMap = value;
}
public SqlAllColumnsInTableSelectListItem AddSqlAllColumnsInTableSelectListItem(ITableMap tableMap)
{
SqlTableAlias sqlTableAlias = this.SqlStatement.GetSqlTableAlias(tableMap);
return(AddSqlAllColumnsInTableSelectListItem(sqlTableAlias));
}
public void SetTableMap(ITableMap value)
{
throw new Exception("The method or operation is not implemented.");
}
public static string BuildTableIdentifier(ITableMap table) => BuildIdentifier(table.TableName);
public void Add <T>(ITableMap tb)
{
Add(typeof(T), tb);
}
public SqlAliasTableSource AddSqlAliasTableSource(ITableMap tableMap)
{
SqlTableAlias sqlTableAlias = this.SqlStatement.GetSqlTableAlias(tableMap);
return AddSqlAliasTableSource(sqlTableAlias) ;
}
protected virtual void DeserializeColumnMap(ITableMap tableMap, XmlNode xmlCol)
{
IColumnMap columnMap = new ColumnMap();
columnMap.TableMap = tableMap;
if (!(xmlCol.Attributes["name"] == null))
{
columnMap.Name = xmlCol.Attributes["name"].Value;
}
if (!(xmlCol.Attributes["type"] == null))
{
columnMap.DataType = (DbType) Enum.Parse(typeof (DbType), xmlCol.Attributes["type"].Value);
}
if (!(xmlCol.Attributes["format"] == null))
{
columnMap.Format = xmlCol.Attributes["format"].Value;
}
if (!(xmlCol.Attributes["default"] == null))
{
columnMap.DefaultValue = xmlCol.Attributes["default"].Value;
}
if (!(xmlCol.Attributes["foreign-key"] == null))
{
columnMap.ForeignKeyName = xmlCol.Attributes["foreign-key"].Value;
}
if (!(xmlCol.Attributes["primary-table"] == null))
{
columnMap.PrimaryKeyTable = xmlCol.Attributes["primary-table"].Value;
}
if (!(xmlCol.Attributes["primary-column"] == null))
{
columnMap.PrimaryKeyColumn = xmlCol.Attributes["primary-column"].Value;
}
if (!(xmlCol.Attributes["sequence"] == null))
{
columnMap.Sequence = xmlCol.Attributes["sequence"].Value;
}
if (!(xmlCol.Attributes["specific-type"] == null))
{
columnMap.SpecificDataType = xmlCol.Attributes["specific-type"].Value;
}
if (!(xmlCol.Attributes["allow-null"] == null))
{
columnMap.AllowNulls = ParseBool(xmlCol.Attributes["allow-null"].Value);
}
if (!(xmlCol.Attributes["auto-inc"] == null))
{
columnMap.IsAutoIncrease = ParseBool(xmlCol.Attributes["auto-inc"].Value);
}
if (!(xmlCol.Attributes["primary"] == null))
{
columnMap.IsPrimaryKey = ParseBool(xmlCol.Attributes["primary"].Value);
}
if (!(xmlCol.Attributes["foreign"] == null))
{
columnMap.IsForeignKey = ParseBool(xmlCol.Attributes["foreign"].Value);
}
if (!(xmlCol.Attributes["fixed"] == null))
{
columnMap.IsFixedLength = ParseBool(xmlCol.Attributes["fixed"].Value);
}
if (!(xmlCol.Attributes["seed"] == null))
{
columnMap.Seed = System.Convert.ToInt32(xmlCol.Attributes["seed"].Value);
}
if (!(xmlCol.Attributes["inc"] == null))
{
columnMap.Increment = System.Convert.ToInt32(xmlCol.Attributes["inc"].Value);
}
if (!(xmlCol.Attributes["length"] == null))
{
columnMap.Length = System.Convert.ToInt32(xmlCol.Attributes["length"].Value);
}
if (!(xmlCol.Attributes["prec"] == null))
{
columnMap.Precision = System.Convert.ToInt32(xmlCol.Attributes["prec"].Value);
}
if (!(xmlCol.Attributes["scale"] == null))
{
columnMap.Scale = System.Convert.ToInt32(xmlCol.Attributes["scale"].Value);
}
ArrayList metaData = columnMap.MetaData;
DeserializeMetaData(xmlCol, ref metaData);
}
public virtual void TouchTable(ITableMap tableMap, int exceptionLimit)
{
string sql = GetTouchStatement(tableMap, exceptionLimit);
IDataSource dataSource = this.Context.GetDataSource(tableMap.SourceMap);
using (IDataReader dr = this.Context.SqlExecutor.ExecuteReader(sql, dataSource)) { ; }
}
/// <summary>
/// Build the core lines for the merge statement
/// </summary>
public IEnumerable <string> BuildCoreMergeLines <T>(Contracts.IMergeSerializer <T> mergeSerializer, MergeDefinition <T> mergeDefinition, ITableMap <T> tableMap)
{
if (mergeSerializer is XmlMergeSerializer <T> )
{
//--Create an internal representation of the XML document
yield return("declare @preparedDoc int;");
yield return("exec sp_xml_preparedocument @preparedDoc output, @serialized;");
yield return("");
}
var sourceColumns = (mergeDefinition.IncludeInsert, mergeDefinition.IncludeUpdate, mergeDefinition.IncludeDelete) switch
{
(true, false, _) => tableMap.KeyColumns.Union(tableMap.InsertColumns).ToList(),
(true, true, _) => tableMap.KeyColumns.Union(tableMap.UpsertColumns).ToList(),
(false, true, _) => tableMap.KeyColumns.Union(tableMap.UpdateColumns).ToList(),
(false, false, true) => tableMap.KeyColumns,
_ => throw new ArgumentException("At least one of insert, update, or delete must be included in the merge."),
};
public virtual ITableMap GetTableMap()
{
if (fixedGetTableMap)
return fixedValueGetTableMap;
ITableMap tableMap = null;
if (this.Table == "")
{
tableMap = m_ClassMap.GetTableMap();
}
else
{
ISourceMap sourceMap = GetSourceMap();
if (sourceMap != null)
{
tableMap = GetSourceMap().GetTableMap(this.Table);
}
}
if (isFixed)
{
fixedValueGetTableMap = tableMap;
fixedGetTableMap = true;
}
return tableMap;
}
public virtual ArrayList GetPropertyMapsForTable(ITableMap tableMap)
{
ArrayList listPropertyMaps = new ArrayList();
foreach (IClassMap classMap in m_ClassMaps)
{
foreach (IPropertyMap propertyMap in classMap.GetNonInheritedPropertyMaps())
{
if (propertyMap.Table.Length > 0)
{
if (propertyMap.GetTableMap() == tableMap)
{
listPropertyMaps.Add(propertyMap);
}
}
}
}
return listPropertyMaps;
}
public void Visit(ITableMap tableMap)
{
;
}
public void Visit(ITableMap tableMap)
{
;
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap)
{
return(GetSqlTableAlias(tableMap, ""));
}
public override void Map <TRecord>(ITableMap <TRecord> tableMap)
{
tableMap.Map(x => x.ClientId).Ignore(SqlOperation.Update);
}
private static string GetColumns(ITableMap tableMap)
{
return string.Join(", ", tableMap.Columns.Select(x => Enclose(x.ColumnName)));
}
protected MergeSerializerBase(ITableMap <T> tableMap)
{
TableMap = tableMap;
Aliases = BuildShortColumnNames(TableMap.InstanceColumns.Select(c => c.Value));
}
public virtual void TouchTable(ITableMap tableMap, int exceptionLimit)
{
GetSqlEngine(tableMap.SourceMap.SourceType).TouchTable(tableMap, exceptionLimit);
}
public SqlTable GetSqlTable(ITableMap tableMap)
{
SqlTable sqlTable = FindSqlTable(tableMap.Name) ;
if (sqlTable == null)
sqlTable = new SqlTable(this, tableMap) ;
return sqlTable;
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap)
{
return GetSqlTableAlias(tableMap, "");
}
private static string GetSchemaAndTable(ITableMap tableMap)
{
return string.Format("{0}.{1}", Enclose(tableMap.Schema), Enclose(tableMap.Table));
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap, string alias)
{
SqlTableAlias sqlTableAlias = FindSqlTableAlias(tableMap.Name, alias) ;
if (sqlTableAlias == null)
{
SqlTable sqlTable = GetSqlTable(tableMap);
sqlTableAlias = new SqlTableAlias(this, sqlTable, alias) ;
}
return sqlTableAlias;
}
public SqlTable(SqlDatabase sqlDatabase, ITableMap tableMap)
{
this.Parent = sqlDatabase;
this.tableMap = tableMap;
sqlDatabase.SqlTables.Add(this);
}
public SqlTable GetSqlTable(ITableMap tableMap)
{
return this.sqlDatabase.GetSqlTable(tableMap);
}
public SqlTableAlias GetTableAlias(ITableMap tableMap, object hash)
{
string alias;
if (hash is IPropertyMap)
{
IPropertyMap propertyMap = (IPropertyMap) hash;
if (!propertyMap.IsCollection)
{
if (propertyMap.ClassMap == this.rootClassMap || propertyMap.ClassMap == this.baseClassMap)
{
hash = this.baseClassMap;
// if (propertyMap.GetTableMap() == propertyMap.ClassMap.GetTableMap())
// {
// hash = this.baseClassMap;
// }
// else
// {
// hash = propertyMap.Name;
// }
}
}
else
{
hash = propertyMap.Name;
}
}
else if (hash is IClassMap)
{
if (hash == this.rootClassMap)
hash = this.baseClassMap;
}
else if (hash is TableJoin)
{
hash = ((TableJoin) hash).GetPropertyPath();
}
if (!tableAliases.Contains(tableMap))
{
tableAliases[tableMap] = new Hashtable();
}
Hashtable propertyTableAliases = (Hashtable) tableAliases[tableMap];
if (propertyTableAliases.Contains(hash))
{
alias = (string) propertyTableAliases[hash];
return this.select.GetSqlTableAlias(tableMap, alias);
}
alias = CreateTableAlias();
propertyTableAliases[hash] = alias;
return this.select.GetSqlTableAlias(tableMap, alias);
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap)
{
return this.sqlDatabase.GetSqlTableAlias(tableMap);
}
protected virtual void DoDeepCopy(ITableMap tableMap)
{
IColumnMap cloneColumnMap;
foreach (IColumnMap columnMap in this.ColumnMaps)
{
cloneColumnMap = (IColumnMap) columnMap.DeepClone();
cloneColumnMap.TableMap = tableMap;
}
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap, string alias)
{
return this.sqlDatabase.GetSqlTableAlias(tableMap, alias);
}
protected virtual string SerializeTableMap(ITableMap tableMap)
{
StringBuilder xml = new StringBuilder();
xml.Append(" <table name=\"" + tableMap.Name + "\""); // do not localize
if (tableMap.IsView)
{
xml.Append(" view=\"true\"");
}
xml.Append(">\r\n");
xml.Append(SerializeMetaData(tableMap.MetaData, " "));
foreach (IColumnMap columnMap in tableMap.ColumnMaps)
{
xml.Append(SerializeColumnMap(columnMap));
}
xml.Append(" </table>\r\n"); // do not localize
return xml.ToString();
}
public virtual void VerifyTableMap(ITableMap tableMap)
{
if (tableMap.Name.Length < 1)
{
HandleVerifyException(tableMap, "Table name must not be empty! (Source: '" + tableMap.SourceMap.Name + "')", "Name"); // do not localize
}
if (recursive)
{
foreach (IColumnMap columnMap in tableMap.ColumnMaps)
{
columnMap.Accept(this);
}
}
Hashtable hashNames = new Hashtable();
foreach (IColumnMap columnMap in tableMap.ColumnMaps)
{
if (hashNames.ContainsKey(columnMap.Name.ToLower(CultureInfo.InvariantCulture)))
{
HandleVerifyException(tableMap, "Column names must not appear in duplicates! (Source: '" + tableMap.SourceMap.Name + "', Table: '" + tableMap.Name + "', Column name: '" + columnMap.Name + "')", "ColumnMaps"); // do not localize
}
hashNames[columnMap.Name.ToLower(CultureInfo.InvariantCulture)] = "1";
}
}
/// <summary>
/// Use sql merge to get all of the generated values back for all rows AND be able to map them to the correct objects
/// </summary>
/// <param name="mergeSerializer">When this method returns, contains a <see cref="Contracts.IMergeSerializer"/> that can be used to serialize collections into what's expected by the @serialized parameter</param>
/// <param name="mergeDefinition">Should inserts/updates/deletes be included in the merge statement? Should there be additional filtering?</param>
/// <param name="tableMap">Optionally specify a table map to be used to build out the query</param>
/// <remarks>
/// Note: The OPENJSON function is available only under compatibility level 130 or higher.
/// If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function.
/// </remarks>
/// <seealso cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql"/>
/// <returns>
/// Creates sql which expects a @serialized parameter whose value is a json or xml array string; if xml, the main and child nodes are named '_'.
/// The properties of the collection items are named _0 through _z, _10 through _zz, _100 through _zzz, and so-on.
/// A collection item property '_' is expected, which contains the index of the item within the array.
/// </returns>
public string Merge <T>(out Contracts.IMergeSerializer <T> mergeSerializer, MergeDefinition <T> mergeDefinition, ITableMap <T>?tableMap = null, bool useJson = false)
{
//--Build a table map and figure out which columns should be serialized
tableMap ??= GetTableMap <T>();
//--Build a merge serializer and the core of the merge statement
mergeSerializer = useJson ? new JsonMergeSerializer <T>(tableMap) : new XmlMergeSerializer <T>(tableMap);
var outputColumns = BuildOutputColumns(mergeDefinition, tableMap);
var merge = new System.Text.StringBuilder();
merge.AppendLine("declare @outputResult table(");
merge.AppendLine(string.Join($",{Environment.NewLine}", outputColumns.Select(column => $" {column.columnIdentifier} {column.sqlType}")));
merge.AppendLine(");");
merge.AppendLines(BuildCoreMergeLines(mergeSerializer, mergeDefinition, tableMap));
//--Build the components of the merge
merge.AppendLines(BuildInsertLines(mergeDefinition, tableMap));
merge.AppendLines(BuildUpdateLines(mergeDefinition, tableMap));
merge.AppendLines(BuildDeleteLines(mergeDefinition));
//--Build the merge's output statement so we can determine which records were inserted/updated/deleted
merge.AppendLine($"output {string.Join(", ", outputColumns.Select(oc => oc.value))} into @outputResult({string.Join(", ", outputColumns.Select(column => column.columnIdentifier))});");
merge.Append("select * from @outputResult;");
return(merge.ToString());
}
public virtual void Visit(ITableMap tableMap)
{
VerifyTableMap(tableMap);
}
public SqlAliasTableSource AddSqlAliasTableSource(ITableMap tableMap)
{
SqlTableAlias sqlTableAlias = this.SqlStatement.GetSqlTableAlias(tableMap);
return(AddSqlAliasTableSource(sqlTableAlias));
}
public virtual void TouchTable(ITableMap tableMap, int exceptionLimit)
{
;
}
public virtual ArrayList GetClassMapsForTable(ITableMap tableMap)
{
ArrayList listClassMaps = new ArrayList();
foreach (IClassMap classMap in m_ClassMaps)
{
if (classMap.GetTableMap() == tableMap)
{
listClassMaps.Add(classMap);
}
}
return listClassMaps;
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap, string alias)
{
return(this.sqlDatabase.GetSqlTableAlias(tableMap, alias));
}
public virtual void SetTableMap(ITableMap value)
{
m_TableMap = value;
}
public SqlTable GetSqlTable(ITableMap tableMap)
{
return(this.sqlDatabase.GetSqlTable(tableMap));
}
public override void Map <TRecord>(ITableMap <TRecord> tableMap)
{
tableMap.Map(x => x.ModifiedByUserId);
tableMap.Map(x => x.ModifiedDateUtc).Generated(allowUpdates: true);
}
public SqlTableAlias GetSqlTableAlias(ITableMap tableMap)
{
return(this.sqlDatabase.GetSqlTableAlias(tableMap));
}
public override void Map <TRecord>(ITableMap <TRecord> tableMap) => tableMap.Map(x => x.Id).Key();
/// <summary> /// Configures component column mappings for a table's map /// </summary> public abstract void Map<TRecord>(ITableMap<TRecord> tableMap) where TRecord : TComponent;
public ParameterBuilderFactory(TableMap tableMap)
{
_tableMap = tableMap;
}
public virtual void TouchTable(ITableMap tableMap, int exceptionLimit)
{
GetPersistenceEngine(tableMap.SourceMap).TouchTable(tableMap, exceptionLimit);
}
