public void TestRemoveSqlPunctuation1()
{
const string input = "select * from [database].[schema].[table];";
const string expected = "select * from database schema table ";
Assert.AreEqual(expected, SqlStrings.RemoveSqlPunctuation(input));
}
/// <summary>
/// removes extra brackets, and double-quotes around object names. removes default schemas and unnecessary semi-colons.
/// </summary>
public static string RemoveOptionalDetails(string sql)
{
var result = sql;
var names = (from Match match in SqlStrings.MatchSqlNameOptionals(sql) select match.Value).ToList();
if (names.Any())
{
if (names.Count > 2)
{
var sb = new StringBuilder(sql);
foreach (var name in names.Distinct())
{
sb.Replace(name, name.Trim("[]\"".ToCharArray()));
}
result = sb.ToString();
}
else
{
foreach (var name in names.Distinct())
{
result = sql.Replace(name, name.Trim("[]\"".ToCharArray()));
}
}
}
return(SqlStrings.RemoveSqlOptionals(result));
}
private static string SqlTransform(object sql)
{
const string space = " ";
var clean = Strings.SplitTitleCase(SqlStrings.RemoveSqlPunctuation(sql), space).ToLower();
return(sql.ToString().ToLower() + " " + string.Join(space, clean.Split(space.ToCharArray(), StringSplitOptions.RemoveEmptyEntries).Distinct()));
}
public static string RemoveExtraDoubleQuotesAndBrackets(string sql)
{
var matches = SqlStrings.MatchSqlExtras(sql);
var names = (from Match match in matches select match.Value).ToList();
foreach (var name in names.Distinct())
{
sql = sql.Replace(name, name.Trim("[]\"".ToCharArray()));
}
return(sql);
}
/// <summary>
/// detects "noise" queries; single-line queries we're not interested in.
/// </summary>
public static bool IsNoise(string sql)
{
sql = Trim(SqlStrings.RemoveSqlComments(sql));
if (Lines(sql, false) == 1)
{
if (Statements(sql, false) == 1)
{
return(sql.StartsWith("FETCH ", IGNORE) || sql.StartsWith("SET ", IGNORE) || sql.Equals("begin transaction", IGNORE) || sql.Equals("commit transaction", IGNORE));
}
}
return(false);
}
public static IEnumerable <string> FindTables(string sql)
{
sql = RemoveExtraDoubleQuotesAndBrackets(sql);
var tables = new List <string>();
var matches = SqlStrings.MatchSqlTables(sql);
foreach (Match match in matches)
{
var table = match.Groups["table"].Value;
tables.Add(table);
}
return(tables.Distinct());
}
private async Task _SetRightOrderNumber(LogicParameter lp)
{
ISqlFormatter fSQL = lp.SqlFormatter;
string strWhereClause = SqlStrings.Format(lp.Session.Database().SystemIdentifier,
"SDL_AppProfile_MaxOrderNumber",
fSQL.AndRelation(
fSQL.UidComparison("UID_SDLDomainRD", lp.Entity.GetValue <string>("UID_SDLDomainRD")),
fSQL.UidComparison("UID_Profile", lp.Entity.GetValue <string>("UID_Profile"), CompareOperator.NotEqual)
));
// try to get the highest OrderNumber
var trumber = await lp.Session.Source().TryGetSingleValueAsync <double>("ApplicationProfile", "OrderNumber", strWhereClause, lp.CancellationToken).ConfigureAwait(false);
double number = trumber.Success ? trumber.Result : 0;
if (number < 10000)
{
number += 10000;
}
lp.Entity.SetValue("OrderNumber", number + 1);
}
public static string ReplaceParameters(string sql)
{
sql = SqlStrings.ReplaceSqlEmptySingleQuotes(sql, "1");
sql = SqlStrings.ReplaceSqlColumnOperatorValues(sql, "${operator} @Parameter");
return(sql); //SqlStrings.ReplaceSqlParametersInFunctions(sql, "@Parameter");
}
/// <summary>
/// Prüft, ob ein Kontokorrent vorhanden ist
/// </summary>
/// <param name="mandant">Mandant</param>
/// <param name="kto">zu prüfendes Konto</param>
/// <param name="istDebitor">Angabe, ob Debitor oder Kreditor</param>
/// <returns>true - Konto vorhanden, false - Konto nicht vorhanden</returns>
public static bool KontokorrentExists(Mandant mandant, string kto, bool istDebitor)
{
var ktoArt = istDebitor ? "D" : "K";
return(mandant.MainDevice.Lookup.RowExists("Kto", "KHKKontokorrent",
$"Mandant={mandant.Id} AND Kto={SqlStrings.ToSqlString(kto)} AND KtoArt={SqlStrings.ToSqlString(ktoArt)}"));
}
/// <summary>
/// Prüft, ob ein Ansprechpartner mit Email und Adressnummer vorhanden ist
/// </summary>
/// <param name="mandant">Mandant</param>
/// <param name="adresse">Adresse des Ansprechpartners</param>
/// <param name="email">Email des Ansprechpartners</param>
/// <returns></returns>
public static bool AnsprechpartnerExists(Mandant mandant, int adresse, string email)
{
return(mandant.MainDevice.Lookup.RowExists("Nummer", "KHKAnsprechpartner", $"Mandant={mandant.Id} AND Adresse={adresse} AND EMail={SqlStrings.ToSqlString(email)}"));
}
/// <summary>
/// Aktualisiert die Teilnehmer zu einem Termin
/// </summary>
/// <param name="mandant"></param>
/// <param name="seminarterminID"></param>
public static void UpdateSeminarterminTeilnehmer(Mandant mandant, string seminarterminID)
{
var seminartermin = SeminarData.GetSeminartermin(mandant, seminarterminID);
var countTeilnehmer = mandant.MainDevice.Lookup.RowCount("PSDSeminarbuchungen", $"Mandant={mandant.Id} AND SeminarterminID={SqlStrings.ToSqlString(seminarterminID)}");
seminartermin.AnzahlTeilnehmer = ConversionHelper.ToInt16(countTeilnehmer);
SeminarData.UpdateOrCreateSeminartermin(mandant, seminartermin);
}
private static void FillPrimaryKey(Database database, string connectionString)
{
int lastId = 0;
int parentId = 0;
bool change = false;
ISchemaBase table = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(ConstraintSQLCommand.GetPrimaryKey(database.Info.Version, null), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
Constraint con = null;
while (reader.Read())
{
if (parentId != (int)reader["ID"])
{
parentId = (int)reader["ID"];
if (reader["ObjectType"].ToString().Trim().Equals("U"))
{
table = database.Tables.Find(parentId);
}
else
{
table = database.TablesTypes.Find(parentId);
}
change = true;
}
else
{
change = false;
}
if ((lastId != (int)reader["Index_id"]) || (change))
{
var name = (string)reader["Name"];
var owner = (string)reader["Owner"];
con = new Constraint(table);
con.Id = (int)reader["Index_id"];
con.Name = SqlStrings.HasHexEnding(name) ? AlternateName(owner, con.Parent.Name) : name;
con.Owner = owner;
con.Type = Constraint.ConstraintType.PrimaryKey;
con.Index.Id = (int)reader["Index_id"];
con.Index.AllowPageLocks = (bool)reader["allow_page_locks"];
con.Index.AllowRowLocks = (bool)reader["allow_row_locks"];
con.Index.FillFactor = (byte)reader["fill_factor"];
con.Index.IgnoreDupKey = (bool)reader["ignore_dup_key"];
con.Index.IsAutoStatistics = (bool)reader["ignore_dup_key"];
con.Index.IsDisabled = (bool)reader["is_disabled"];
con.Index.IsPadded = (bool)reader["is_padded"];
con.Index.IsPrimaryKey = true;
con.Index.IsUniqueKey = false;
con.Index.Type = (Index.IndexTypeEnum)(byte) reader["type"];
con.Index.Name = con.Name;
if (database.Options.Ignore.FilterTableFileGroup)
{
con.Index.FileGroup = reader["FileGroup"].ToString();
}
lastId = (int)reader["Index_id"];
if (reader["ObjectType"].ToString().Trim().Equals("U"))
{
((Table)table).Constraints.Add(con);
}
else
{
((TableType)table).Constraints.Add(con);
}
}
ConstraintColumn ccon = new ConstraintColumn(con);
ccon.Name = (string)reader["ColumnName"];
ccon.IsIncluded = (bool)reader["is_included_column"];
ccon.Order = (bool)reader["is_descending_key"];
ccon.KeyOrder = (byte)reader["key_ordinal"];
ccon.Id = (int)reader["column_id"];
ccon.DataTypeId = (int)reader["user_type_id"];
con.Columns.Add(ccon);
}
}
}
}
}
