public void Test_CreateSqlFormatter_AlternateConstructor_SQLite()
{
//---------------Set up test pack-------------------
string connectionString =
new DatabaseConfig(DatabaseConfig.SqlServer, "test", "test", "test", "test", "1000").GetConnectionString
();
IDatabaseConnection dbConn = new DatabaseConnectionSQLite
("System.Data", "System.Data.SqlClient.SqlConnection", connectionString);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
ISqlFormatter defaultSqlFormatter = dbConn.SqlFormatter;
//---------------Test Result -----------------------
Assert.IsInstanceOf(typeof(SqlFormatter), defaultSqlFormatter);
SqlFormatter sqlFormatter = (SqlFormatter)defaultSqlFormatter;
Assert.IsNotNull(sqlFormatter);
Assert.AreEqual("\"", sqlFormatter.LeftFieldDelimiter);
Assert.AreEqual("\"", sqlFormatter.RightFieldDelimiter);
Assert.AreEqual("", sqlFormatter.LimitClauseAtBeginning);
Assert.AreEqual("limit", sqlFormatter.LimitClauseAtEnd);
Assert.AreEqual(sqlFormatter.LeftFieldDelimiter, dbConn.LeftFieldDelimiter);
Assert.AreEqual(sqlFormatter.RightFieldDelimiter, dbConn.RightFieldDelimiter);
// StringAssert.Contains(sqlFormatter.LimitClauseAtBeginning, dbConn.GetLimitClauseForBeginning(1));
// StringAssert.Contains(sqlFormatter.LimitClauseAtEnd, dbConn.GetLimitClauseForEnd(1));
}
/// <summary>
/// Format sql using the FormatSql method available on the given <see cref="ISqlFormatter"/>.
/// </summary>
/// <param name="sqlFormatter">The <see cref="ISqlFormatter"/> to use.</param>
/// <param name="command">The <see cref="IDbCommand"/> being represented.</param>
public static string GetFormattedSql(this ISqlFormatter sqlFormatter, IDbCommand command)
{
var commandText = command.GetReadableCommand();
var parameters = command.GetParameters();
return(sqlFormatter.GetFormattedSql(commandText, parameters, command));
}
private void UID_Application_ValueChecking(object sender, ColumnEventArgs e)
{
string strWhereClause = "";
using (NoRightsCheck())
{
if (0 < e.New.String.Length)
{
ISqlFormatter fSQL = DbObject.Connection.SqlFormatter;
//MSSQL:(UID_Application = '0f90b30e-f1cc-11d4-9700-00508b8f013d') and (isnull(IsProfileApplication, 0) = 0)
//Oracle:(UID_Application = '0f90b30e-f1cc-11d4-9700-00508b8f013d') and (nvl(IsProfileApplication, 0) = 0)
strWhereClause =
fSQL.AndRelation(
fSQL.Comparison("UID_Application", e.New.String, ValType.String, CompareOperator.Equal, FormatterOptions.None),
fSQL.Comparison("IsProfileApplication", false, ValType.Bool));
// Die zugewiesene Applikation ist keine ProfilApplikation
if (DbObject.Connection.Exists("Application", strWhereClause))
{
throw new ViException(881106, ExceptionRelevance.EndUser);
}
}
}
}
public static string FormatSelectSql(this ISqlFormatter formatter, string tableOrView, string fields, string condition)
{
string selectSqlTemplate = formatter.GetSelectSqlTemplate();
string str2 = "";
return(string.Format(selectSqlTemplate, new object[] { fields, tableOrView, str2, condition }));
}
public T QueryObject <T>(string key, object value, bool checkExist = false) where T : DbObject, new()
{
ISqlFormatter sqlFormatter = this.DbObjectOperator.SqlFormatter;
string condition = string.Format("{0} = {1}", sqlFormatter.FormatFieldName(key), sqlFormatter.FormatValue(value, false));
return(this.QueryObject <T>(condition, checkExist));
}
public string ToSql(ISqlFormatter sql)
{
var needed = Aliases.Keys;
var schema = needed.First().Schema;
var(main, joined) = (
from table in schema.Tables
let j = GetJoins(table, needed.ToHashSet())
where j.complete
orderby j.joins.Count == 0 ? 0 : j.joins.Max(x => x.Depth),
j.joins.Sum(x => x.Count)
select(table, j.joins)
).FirstOrDefault();
if (main == null)
{
var names = string.Join(",", needed.Select(x => x.RefName));
throw new InvalidOperationException($"Could not make joins between tables {names}");
}
var output = new List <string> {
$"from {main.ToSql(sql)} {this[main]}"
};
foreach (var join in joined)
{
join.ToSql(sql, this, output);
}
return(string.Join(Environment.NewLine, output));
}
/// <summary>
/// Creates the Sql that corresponds to this join
/// </summary>
/// <param name="sqlFormatter">The formatter used to construct the appropriate Sql</param>
/// <param name="aliases">The aliases to use for table names</param>
/// <returns>The sql statement.</returns>
public string CreateSQL(ISqlFormatter sqlFormatter, IDictionary <string, string> aliases)
{
//if (Joins.Count == 0) return sqlFormatter.DelimitTable(EntityName);
string tableJoinString = GetTableJoinString(this, sqlFormatter, aliases);
return(GetJoinString(sqlFormatter, this, tableJoinString, aliases));
}
public TableReferenceIdentifierByTableMultiPartIdentifierProvider(ISqlFormatter sqlFormatter, Regex predicateRegex, Regex pascalCaseRegex, TextInfo textInfo)
{
_predicateRegex = predicateRegex;
_pascalCaseRegex = pascalCaseRegex;
_textInfo = textInfo;
_sqlFormatter = sqlFormatter;
}
/// <summary>
/// Format sql using the FormatSql method available in the current <see cref="MiniProfilerBaseOptions.SqlFormatter"/>.
/// </summary>
/// <param name="sqlFormatter">The <see cref="ISqlFormatter"/> to use.</param>
/// <param name="commandText">The SQL command to format.</param>
/// <param name="parameters">The parameters for the SQL command.</param>
/// <param name="command">The <see cref="IDbCommand"/> being represented.</param>
/// <remarks>It is preferable to use this rather than accessing <see cref="ISqlFormatter.FormatSql"/> directly,
/// as this method will detect whether an <see cref="IAdvancedSqlFormatter"/> is being used, and will access it properly.
/// This may be removed in a future major version when <see cref="IAdvancedSqlFormatter"/> can be consolidated back
/// into <see cref="ISqlFormatter"/>.
/// </remarks>
public static string GetFormattedSql(this ISqlFormatter sqlFormatter, string commandText, List <SqlTimingParameter> parameters, IDbCommand command = null)
{
var advancedFormatter = sqlFormatter as IAdvancedSqlFormatter;
return(advancedFormatter?.FormatSql(commandText, parameters, command)
?? sqlFormatter.FormatSql(commandText, parameters));
}
private string ToSql(ISqlFormatter sql, IList <IColumn> select,
IFilterParameters filterParams, IEnumerable <Filter> outerFilters,
long skip, int take)
{
if (Aggregations.Count == 1)
{
return(Aggregations[0].ToSql(sql, select, outerFilters.Concat(Filters), filterParams, OrderBy, skip, take));
}
var ordering = "a0.Value0 desc";
if (select != null && OrderBy.Count != 0)
{
ordering = string.Join(", ", OrderBy.Select(FindOrderingColumn));
}
return(_aggregatedTemplate(new
{
skipAndTake = sql.SkipAndTake(skip, take),
Aggregations = Aggregations.Select(x =>
x.ToSql(sql, select, outerFilters.Concat(Filters), filterParams)),
Select = select,
ordering
}));
}
public static string GetOperatorString(this ISqlFormatter formatter, ExpressionType type)
{
string str = null;
dicOperatorStrings.TryGetValue(type, out str);
return(str);
}
public string ToSql(
ISqlFormatter sql,
IEnumerable <IColumn> selectColumns,
IEnumerable <Filter> outerFilters,
IFilterParameters filterParams,
IEnumerable <Ordering> orderings = null,
long?skip = null,
int?take = null)
{
var joins = new Joins();
var selects = selectColumns?.Select((c, i) =>
$"{sql.IdentifierPair(joins[c.Table], c.DbName)} Select{i}").ToList()
?? new List <string>();
var aggColumn = Column != null ? $"{Function}({joins.Aliased(Column, sql)})" : null;
if (aggColumn != null)
{
selects.Add($"{aggColumn} Value0");
}
if (selects.Count == 0)
{
throw new InvalidOperationException("Must select something");
}
var filters = outerFilters.Concat(Filters).Select(f => new
{
Column = joins.Aliased(f.Column, sql),
f.Operator,
Param = filterParams[f]
})
.ToList();
var groupBy = selectColumns?.Select(x => new
{
Part = joins.Aliased(x, sql)
})
.ToList();
var skipAndTake = skip != null && take != null
? sql.SkipAndTake(skip.Value, take.Value)
: null;
var orderBy = skipAndTake == null ? null :
orderings.Any() ? string.Join(", ", orderings.Select(x => $"{joins.Aliased(x.Column, sql)} {x.Direction}")) :
aggColumn != null ? $"{aggColumn} desc" :
null;
return(_template(new
{
selects,
filters,
joins = joins.ToSql(sql),
groupBy,
orderBy,
skipAndTake
}));
}
private void _RemoveOutdatedAppsInfo()
{
// remove outdated entries in MachineAppsInfo
ISqlFormatter sqlFormatter = ConnectData.Connection.SqlFormatter;
DateTime outDate;
int age;
age = _MaxAgeMachine;
if (age < int.MaxValue)
{
outDate = DateTime.UtcNow.Subtract(new TimeSpan(age, 0, 0, 0));
if (outDate > DbVal.MinDate)
{
_RemoveTableEntries(
"MachineAppsInfo",
sqlFormatter.AndRelation(
sqlFormatter.Comparison("CurrentlyActive", false, ValType.Bool),
sqlFormatter.Comparison("DeInstallDate", outDate,
ValType.Date, CompareOperator.LowerThan)));
}
}
age = _MaxAgeUser;
if (age < int.MaxValue)
{
outDate = DateTime.UtcNow.Subtract(new TimeSpan(age, 0, 0, 0));
if (outDate > DbVal.MinDate)
{
// remove outdated entries in ADSAccountAppsInfo
_RemoveTableEntries(
"ADSAccountAppsInfo",
sqlFormatter.AndRelation(
sqlFormatter.Comparison("CurrentlyActive", false, ValType.Bool),
sqlFormatter.Comparison("DeInstallDate", outDate,
ValType.Date, CompareOperator.LowerThan)));
}
}
age = _MaxAgeClientLog;
if (age < int.MaxValue)
{
outDate = DateTime.UtcNow.Subtract(new TimeSpan(age, 0, 0, 0));
if (outDate > DbVal.MinDate)
{
// remove outdated entries in ClientLog
_RemoveTableEntries(
"ClientLog",
sqlFormatter.Comparison("InstallDate",
outDate, ValType.Date,
CompareOperator.LowerThan));
}
}
}
private string GetTableJoinString(Source source, ISqlFormatter sqlFormatter, IDictionary <string, string> aliases)
{
string joinString = sqlFormatter.DelimitTable(EntityName);
joinString += " " + aliases[this.ToString()];
joinString = GetInheritanceJoinString(sqlFormatter, source, joinString, aliases);
return(joinString);
}
public GenerateSQL(Configuration configuration, Settings settings, ISqlFormatter sql_formatter, IFileWriter file_writer, IMigrationProvider migration_provider)
{
_configuration = configuration;
_settings = settings;
_sql_formatter = sql_formatter;
_file_writer = file_writer;
_migration_provider = migration_provider;
}
public GenerateSQL(Configuration configuration, Settings settings, ISqlFormatter sql_formatter, IFileWriter file_writer, IMigrationProvider migration_provider)
{
_configuration = configuration;
_settings = settings;
_sql_formatter = sql_formatter;
_file_writer = file_writer;
_migration_provider = migration_provider;
}
public override string GetFormattedStringWith(ISqlFormatter formatter, IDictionary<string, string> aliases)
{
var parts = new List<string>();
parts.Add(_functionName);
parts.Add("(");
AddParametersTo(parts, formatter, aliases);
parts.Add(")");
return string.Join("", parts);
}
private void _InsertDomains(TreeListNode tlnParent, string identParentDomain)
{
ISqlFormatter isql = clsMain.Instance.CurrentConnection.Connection.SqlFormatter;
try
{
Cursor.Current = Cursors.WaitCursor;
// create the collection
IColDbObject dbcolDomains = clsMain.Instance.CurrentConnection.Connection.CreateCol("SDLDomain");
// assign the where-clause
if (String.IsNullOrEmpty(identParentDomain))
{
dbcolDomains.Prototype.WhereClause = isql.Comparison("UID_SDLDomainParent", "", ValType.String);
}
else
{
dbcolDomains.Prototype.WhereClause = isql.FkComparison("UID_SDLDomainParent", "UID_SDLDomain", "SDLDomain",
isql.Comparison("Ident_Domain", identParentDomain, ValType.String));
}
// appand the userdefined filter
if (clsMain.Instance.DomainFilter.Length > 0)
{
dbcolDomains.Prototype.WhereClause = "(" + dbcolDomains.Prototype.WhereClause + ") and (" + clsMain.Instance.DomainFilter + ")";
}
// mark as DisplayColumn
dbcolDomains.Prototype["Ident_Domain"].IsDisplayItem = true;
// load the collection
dbcolDomains.Load();
//now do for each domain
foreach (IColElem colElem in dbcolDomains)
{
// create a doamin-node
TreeListNode tlnDomain = tlnParent.Nodes.Add(colElem.Display, 1);
tlnDomain.Tag = new NodeData(NodeType.Domain, colElem.GetValue("Ident_Domain"), "");
// insert static subitems of domain
_PrepareDomainNode(tlnDomain);
// insert all childdomains
_InsertDomains(tlnDomain, colElem.Display);
}
}
catch (Exception ex)
{
ExceptionDialog.Show(this.ParentForm, ex);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
/// <exclude/>
/// <summary>
/// Es wird geprüft, ob zu der zu diesem Profil zugehörigen Applikation Profile vorhanden sind.
/// Falls ja, wird das Flag IsProfileApplication in Application auf 1 gesetzt
/// Falls nicht, wird das Flag IsProfileApplication in Application auf 0 gesetzt.
/// </summary>
private void _CheckIsProfileApplication()
{
IColDbObject colApplicationProfile = DbObject.Connection.CreateCol("ApplicationProfile");;
ISqlFormatter fSQL = DbObject.Connection.SqlFormatter;
// Objekt wurde gelöscht
if (DbObject.IsDeleted)
{
// geloescht
// gibt es noch andere Profile ???
colApplicationProfile.Prototype.WhereClause =
fSQL.AndRelation(fSQL.Comparison("UID_Application", DbObject["UID_Application"].New.String,
ValType.String, CompareOperator.Equal, FormatterOptions.None),
fSQL.Comparison("UID_Profile", DbObject["UID_Profile"].New.String,
ValType.String, CompareOperator.NotEqual, FormatterOptions.None)
);
// nein
if (colApplicationProfile.DBCount == 0)
{
// exists the Application ( we are not in DeleteCascade )
// This Check is required because the UID_Application is filled, but the Object is already deleted
if (DbObject.Connection.Exists("Application", fSQL.Comparison("UID_Application", DbObject["UID_Application"].New.String,
ValType.String, CompareOperator.Equal, FormatterOptions.None)))
{
// auf false setzen
ISingleDbObject dbApplication = DbObject.GetFK("UID_Application").Create();
if (dbApplication != null)
{
if (dbApplication["IsProfileApplication"].New.Bool == true)
{
dbApplication["IsProfileApplication"].NewValue = false;
dbApplication.Save();
}
}
}
}
}
// Objekt wurde neu angelegt
if (!DbObject.IsLoaded)
{
// Insert
ISingleDbObject dbApplication = DbObject.GetFK("UID_Application").Create();
if (dbApplication != null)
{
if (dbApplication["IsProfileApplication"].New.Bool == false)
{
dbApplication["IsProfileApplication"].NewValue = true;
dbApplication.Save();
}
}
}
}
private bool AddStringParameter(object parameter, List <string> parts, ISqlFormatter formatter)
{
if (parameter is string)
{
var stringParameter = (parameter as string) ?? "";
parts.Add("'" + stringParameter.Replace("'", "''") + "'");
return(true);
}
return(false);
}
private bool AddQueryFieldParameter(object parameter, List<string> parts, ISqlFormatter formatter, IDictionary<string, string> aliases)
{
var queryField = parameter as QueryField;
if (queryField == null) return false;
var quotedSource = QuoteSource(queryField.Source, formatter, aliases);
var quotedField = queryField.FieldName == "*" ? "*" : formatter.DelimitField(queryField.FieldName);
var field = string.Join(string.Empty, new[] { quotedSource, quotedField });
parts.Add(field);
return true;
}
/// <exclude/>
/// <summary>
/// Beschreibung: Es wird ein Eintrag in TroubleProduct für die Applikation vorgenommen.
/// Ident_TroubleProduct = substring(NameFull, 1, 64)
/// Description = substring(NameFull, 1, 64),
/// SupportLevel = 1,
/// IsInActive = 0.
/// Voraussetzungen: der Configparm 'Helpdesk\AutoProduct\Application' ist gesetzt,
/// für die Applikation gibt es noch keinen Eintrag in TroubleProduct --> substring(application.NameFull, 1, 64) <> Ident_TroubleProduct,
/// zu dieser Applikation gibt es mindestens ein Applikationsprofil.
/// </summary>
private void _HandleTroubleProduct()
{
ISqlFormatter fSQL = DbObject.Connection.SqlFormatter;
TroubleProductHelper.HandleTroubleProduct(
DbObject.Connection,
"Application",
DbObject.ObjectWalker.GetValue("FK(UID_Application).NameFull").String,
null, null);
}
private void AddParametersTo(List<string> parts, ISqlFormatter formatter, IDictionary<string, string> aliases)
{
var parameterParts = new List<string>();
foreach (var parameter in _parameters)
{
if (AddStringParameter(parameter, parameterParts, formatter)) continue;
AddQueryFieldParameter(parameter, parameterParts, formatter, aliases);
}
parts.Add(string.Join(",", parameterParts));
}
private string QuoteSource(Source source, ISqlFormatter withFormatter, IDictionary <string, string> aliases)
{
if (source == null)
{
return(string.Empty);
}
var sourceName = aliases.ContainsKey(source.Name) ? aliases[source.Name] : source.Name;
return(withFormatter.DelimitTable(sourceName) + ".");
}
public override string GetFormattedStringWith(ISqlFormatter formatter, IDictionary <string, string> aliases)
{
var parts = new List <string>();
parts.Add(_functionName);
parts.Add("(");
AddParametersTo(parts, formatter, aliases);
parts.Add(")");
return(string.Join("", parts));
}
private bool AddStringParameter(object parameter, List<string> parts, ISqlFormatter formatter)
{
if (parameter is string)
{
var stringParameter = (parameter as string) ?? "";
parts.Add("'" + stringParameter.Replace("'", "''") + "'");
return true;
}
return false;
}
private Task Handle_TroubleProduct(LogicParameter lp)
{
ISqlFormatter fSQL = lp.SqlFormatter;
return(TroubleProductHelper.HandleTroubleProduct(
lp,
"Application",
lp.Entity.GetValue <string>("NameFull"),
"ApplicationProfile",
fSQL.UidComparison("UID_Application", lp.Entity.GetValue <string>("UID_Application"))));
}
public Application(
ISqlPreparator preparator,
ISqlFormatter formatter,
ISqlExecutor executor,
ISqlSaver saver)
{
_preparator = preparator;
_formatter = formatter;
_executor = executor;
_saver = saver;
}
private string GetInheritanceJoinString(ISqlFormatter sqlFormatter, Source source, string joinString, IDictionary <string, string> aliases)
{
foreach (Join join in source.InheritanceJoins)
{
joinString = "(" + joinString + " " + GetJoinString(sqlFormatter, join, aliases) + ")";
if (join.ToSource.InheritanceJoins.Count > 0)
{
joinString = GetInheritanceJoinString(sqlFormatter, join.ToSource, joinString, aliases);
}
}
return(joinString);
}
public string ToSql(ISqlFormatter sql,
IFilterParameters filterParams,
IEnumerable <Filter> outerFilters)
{
var result = ToSql(sql, Select, filterParams, outerFilters, Skip, Take);
if (Totals)
{
result += ";" + ToSql(sql, null, filterParams, outerFilters, 0, 1);
}
return(result);
}
private void AddParametersTo(List <string> parts, ISqlFormatter formatter, IDictionary <string, string> aliases)
{
var parameterParts = new List <string>();
foreach (var parameter in _parameters)
{
if (AddStringParameter(parameter, parameterParts, formatter))
{
continue;
}
AddQueryFieldParameter(parameter, parameterParts, formatter, aliases);
}
parts.Add(string.Join(",", parameterParts));
}
public void ToSql(ISqlFormatter sql, Joins aliases, List <string> output)
{
var table = Key.To.Table.ToSql(sql);
var alias = aliases[Key.To.Table];
var left = sql.IdentifierPair(alias, Key.To.Table.Id.DbName);
var right = sql.IdentifierPair(aliases[Key.Table], Key.DbName);
output.Add($"join {table} {alias} on {left} = {right}");
foreach (var join in Joins)
{
join.ToSql(sql, aliases, output);
}
}
private bool AddQueryFieldParameter(object parameter, List <string> parts, ISqlFormatter formatter, IDictionary <string, string> aliases)
{
var queryField = parameter as QueryField;
if (queryField == null)
{
return(false);
}
var quotedSource = QuoteSource(queryField.Source, formatter, aliases);
var quotedField = queryField.FieldName == "*" ? "*" : formatter.DelimitField(queryField.FieldName);
var field = string.Join(string.Empty, new[] { quotedSource, quotedField });
parts.Add(field);
return(true);
}
private async Task _HandleIsProfileApplication(LogicParameter lp)
{
ISqlFormatter fSQL = lp.SqlFormatter;
// Objekt wurde gelöscht
if (lp.Entity.IsDeleted())
{
// geloescht
// gibt es noch andere Profile ???
string strWhereClause =
fSQL.AndRelation(fSQL.UidComparison("UID_Application", lp.Entity.GetValue <string>("UID_Application")),
fSQL.UidComparison("UID_Profile", lp.Entity.GetValue <string>("UID_Profile"), CompareOperator.NotEqual)
);
// nein
if (!await lp.Session.Source().ExistsAsync("ApplicationProfile", strWhereClause, lp.CancellationToken).ConfigureAwait(false))
{
DbObjectKey dbokApplication = DbObjectKey.GetObjectKey("Application", lp.Entity.GetValue <string>("UID_Application"));
TryResult <IEntity> trApplication = await lp.Session.Source().TryGetAsync(dbokApplication, lp.CancellationToken).ConfigureAwait(false);
if (trApplication.Success &&
trApplication.Result.GetValue <bool>("IsProfileApplication"))
{
trApplication.Result.SetValue("IsProfileApplication", false);
await lp.UnitOfWork.PutAsync(trApplication.Result, lp.CancellationToken).ConfigureAwait(false);
}
}
}
// Objekt wurde neu angelegt
if (!lp.Entity.IsLoaded)
{
DbObjectKey dbokApplication = DbObjectKey.GetObjectKey("Application", lp.Entity.GetValue <string>("UID_Application"));
TryResult <IEntity> trApplication = await lp.Session.Source().TryGetAsync(dbokApplication, lp.CancellationToken).ConfigureAwait(false);
if (trApplication.Success && !
trApplication.Result.GetValue <bool>("IsProfileApplication"))
{
// mark as IsProfileApplication
trApplication.Result.SetValue("IsProfileApplication", true);
await lp.UnitOfWork.PutAsync(trApplication.Result, lp.CancellationToken).ConfigureAwait(false);
}
}
}
private static ObjectBaseHash _GetServerCollection(string Ident_Domain, string UID_Profile, NodeType nodeType)
{
ISqlFormatter isql = clsMain.Instance.CurrentConnection.Connection.SqlFormatter;
SqlExecutor cSQL = clsMain.Instance.CurrentConnection.Connection.CreateSqlExecutor(clsMain.Instance.CurrentConnection.PublicKey);
ObjectBaseHash colServers = new ObjectBaseHash();
ObjectServer oServer = null;
string strSQL = "";
switch (nodeType)
{
case NodeType.AppProfile:
strSQL = clsMain.Instance.CurrentConnection.Connection.SqlStrings["ApplicationProfileServer"];
break;
case NodeType.DrvProfile:
strSQL = clsMain.Instance.CurrentConnection.Connection.SqlStrings["DriverProfileServer"];
break;
case NodeType.MacType:
strSQL = clsMain.Instance.CurrentConnection.Connection.SqlStrings["MachineTypeServer"];
break;
}
// replace the Domain-Variable
strSQL = strSQL.Replace("@Ident_Domain", isql.FormatValue(Ident_Domain, ValType.String, true));
// replace UID_Profile
strSQL = strSQL.Replace("@UID_Profile", isql.FormatValue(UID_Profile, ValType.String, true));
// and now do it...
using (IDataReader rData = new CachedDataReader(cSQL.SqlExecute(strSQL)))
{
while (rData.Read())
{
// create a server-object
oServer = new ObjectServer(rData);
// and add to our Hash
colServers.Add(oServer, "UID_ApplicationServer");
}
}
return(colServers);
}
public TestSQLDatabaseProvider(Configuration configuration, Settings settings, ISqlFormatter sql_formatter)
: base(configuration, settings, sql_formatter)
{
}
private string GetJoinString(ISqlFormatter sqlFormatter, Join join, IDictionary<string, string> aliases)
{
if (join.JoinFields.Count == 0)
{
string message = string.Format("SQL cannot be created for the source '{0}' because it has a join to '{1}' without join fields",
Name, join.ToSource.Name);
throw new HabaneroDeveloperException(message, "Please check how you are building your join clause structure.");
}
Join.JoinField joinField = join.JoinFields[0];
var toSourceNameWithAlias = sqlFormatter.DelimitTable(join.ToSource.EntityName);
toSourceNameWithAlias += " " + aliases[join.ToSource.ToString()];
var fromSourceAlias = sqlFormatter.DelimitTable(join.FromSource.EntityName);
fromSourceAlias = aliases[join.FromSource.ToString()];
var toSourceAlias = sqlFormatter.DelimitTable(join.ToSource.EntityName);
toSourceAlias = aliases[join.ToSource.ToString()];
string joinString = string.Format("{0} {1} ON {2}.{3} = {4}.{5}",
join.GetJoinClause(),
toSourceNameWithAlias,
fromSourceAlias,
sqlFormatter.DelimitField(joinField.FromField.FieldName),
toSourceAlias,
sqlFormatter.DelimitField(joinField.ToField.FieldName));
if (join.JoinFields.Count > 1)
{
for (int i = 1; i < join.JoinFields.Count; i++)
{
joinField = join.JoinFields[i];
var fromSourceAlias1 = sqlFormatter.DelimitTable(join.FromSource.EntityName);
fromSourceAlias1 = aliases[join.FromSource.ToString()];
var toSourceAlias1 = sqlFormatter.DelimitTable(join.ToSource.EntityName);
toSourceAlias1 = aliases[join.ToSource.ToString()];
joinString += string.Format(" AND {0}.{2} = {1}.{3}",
fromSourceAlias1, toSourceAlias1,
sqlFormatter.DelimitField(joinField.FromField.FieldName), sqlFormatter.DelimitField(joinField.ToField.FieldName));
}
}
return joinString;
}
private string GetTableJoinString(Source source, ISqlFormatter sqlFormatter, IDictionary<string, string> aliases)
{
string joinString = sqlFormatter.DelimitTable(EntityName);
joinString += " " + aliases[this.ToString()];
joinString = GetInheritanceJoinString(sqlFormatter, source, joinString, aliases);
return joinString;
}
private string GetInheritanceJoinString(ISqlFormatter sqlFormatter, Source source, string joinString, IDictionary<string, string> aliases)
{
foreach (Join join in source.InheritanceJoins)
{
joinString = "(" + joinString + " " + GetJoinString(sqlFormatter, join, aliases) + ")";
if (join.ToSource.InheritanceJoins.Count > 0)
{
joinString = GetInheritanceJoinString(sqlFormatter, join.ToSource, joinString, aliases);
}
}
return joinString;
}
/// <summary>
/// Creates the Sql that corresponds to this join
/// </summary>
/// <param name="sqlFormatter">The formatter used to construct the appropriate Sql</param>
public string CreateSQL(ISqlFormatter sqlFormatter)
{
return CreateSQL(sqlFormatter, new Dictionary<string, string>());
}
public string CreateSQL(ISqlFormatter sqlFormatter, IDictionary<string, string> aliases)
{
//if (Joins.Count == 0) return sqlFormatter.DelimitTable(EntityName);
string tableJoinString = GetTableJoinString(this, sqlFormatter, aliases);
return GetJoinString(sqlFormatter, this, tableJoinString, aliases);
}
/// <summary>
/// Creates an ISqlStatement out of the SelectQuery given in the constructor, to be used to load
/// from a database
/// </summary>
/// <returns>An ISqlStatement that can be executed against an IDatabaseConnection</returns>
public ISqlStatement CreateSqlStatement(ISqlFormatter sqlFormatter)
{
_sqlFormatter = sqlFormatter;
SqlStatement statement = new SqlStatement(_databaseConnection);
StringBuilder builder = statement.Statement;
CheckRecordOffSetAndAppendFields(builder);
AppendMainSelectClause(statement, builder);
if (this.FirstRecordToLoad > 0)
{
AppendOrderByFirstSelect(builder);
builder.Append(" ");
AppendNoOfRecordsClauseAtEnd(builder);
AppendOrderBySecondSelect(builder);
}
return statement;
}
///<summary>
/// Gets the formatted string for this field to be used in queries
///</summary>
///<param name="formatter">An ISqlFormatter to format with</param>
///<param name="aliases">The dictionary of aliases within the context to be formatted for</param>
///<returns>A string which can be used as part of a sql statement</returns>
public virtual string GetFormattedStringWith(ISqlFormatter formatter, IDictionary<string, string> aliases)
{
return String.Format("{0}{1}",
Source != null ? aliases[Source.ToString()] + "." : "",
formatter.DelimitField(FieldName));
}
public string ToString(ISqlFormatter formatter, AddParameterDelegate addParameter)
{
return ToString(formatter, addParameter, new Dictionary<string, string>());
}
/// <summary>
/// Converts this Criteria object to a string, using field names instead of property names and entity names instead of
/// source names. The <see cref="AddParameterDelegate"/> allows a database query builder to create a parameter value
/// when adding the value to the string for use with parametrized SQL. Also see <see cref="ISqlStatement"/>.
///
/// The <see cref="ToString()"/> method uses this method with a simple delegate that converts DateTimes and Guids
/// to sensible string representations and to
/// </summary>
/// See <see cref="PropNameConverterDelegate"/>
/// <param name="formatter">A formatter for any specific database <see cref="SqlFormatter"/></param>
/// <param name="addParameter">The delegate to use to convert the value in object form to a value in string form.
/// See <see cref="AddParameterDelegate"/></param>
/// <returns>The Criteria in string form.</returns>
public string ToString(ISqlFormatter formatter, AddParameterDelegate addParameter, IDictionary<string, string> aliases)
{
if (IsComposite())
{
string rightCriteriaAsString;
if (LogicalOperator == LogicalOp.Not)
{
rightCriteriaAsString = new CriteriaDB(RightCriteria).ToString(formatter, addParameter, aliases);
return string.Format("{0} ({1})", _logicalOps[(int)LogicalOperator], rightCriteriaAsString);
}
string leftCriteriaAsString = new CriteriaDB(LeftCriteria).ToString(formatter, addParameter, aliases);
rightCriteriaAsString = new CriteriaDB(RightCriteria).ToString(formatter, addParameter, aliases);
return string.Format("({0}) {1} ({2})", leftCriteriaAsString, _logicalOps[(int)LogicalOperator],
rightCriteriaAsString);
}
string valueString;
string comparisonOperator = ComparisonOperatorString();
if (_criteria.CanBeParametrised())
{
valueString = addParameter(FieldValue);
} else
{
if (FieldValue == null)
{
valueString = "NULL";
if (this.ComparisonOperator == ComparisonOp.Equals) comparisonOperator = "IS";
if (this.ComparisonOperator == ComparisonOp.NotEquals) comparisonOperator = "IS NOT";
}
else
{
valueString = Convert.ToString(FieldValue);
}
}
string sourceEntityName = "";
string separator = "";
if (Field.Source != null)
{
var fieldSourceName = Field.Source.ChildSourceLeaf.ToString();
if (!aliases.ContainsKey(fieldSourceName))
{
var userMessage = string.Format("The source '{0}' for the property '{1}' "
+ "in the given criteria does not have an alias provided for it.",
Field.Source, Field.PropertyName);
var developerMessage = userMessage
+ " The criteria object may have not been prepared correctly before the aliases were set up.";
throw new HabaneroDeveloperException(userMessage, developerMessage);
}
sourceEntityName = aliases[fieldSourceName];
separator = ".";
}
string fieldNameString = formatter.DelimitField(Field.FieldName);
return string.Format("{0}{1}{2} {3} {4}", sourceEntityName, separator, fieldNameString, comparisonOperator, valueString);
}
public ExceptionMessageFormatter( ISqlFormatter sqlFormatter, IParameterStubCollectionFormatter parameterStubCollectionFormatter )
{
_sqlFormatter = sqlFormatter;
_parameterStubCollectionFormatter = parameterStubCollectionFormatter;
}
public SQLDatabaseProvider(Configuration configuration, Settings settings, ISqlFormatter sql_formatter)
{
_configuration = configuration;
_settings = settings;
_sql_formatter = sql_formatter;
}
private string QuoteSource(Source source, ISqlFormatter withFormatter, IDictionary<string, string> aliases)
{
if (source == null) return string.Empty;
var sourceName = aliases.ContainsKey(source.Name) ? aliases[source.Name] : source.Name;
return withFormatter.DelimitTable(sourceName) + ".";
}
