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