/// <summary> /// The BuildCountQuery /// </summary> /// <param name="table">The table<see cref="ISqlTableIdentifier"/></param> /// <param name="filter">The filter<see cref="ISqlConditionExpression"/></param> /// <returns>The <see cref="IParameterizedQuery"/></returns> public IParameterizedQuery BuildCountQuery(ISqlTableIdentifier table, ISqlConditionExpression filter) { string conditions; IEnumerable <ISqlParameter> parameters; if (filter != null) { IUncheckedSqlCondition uncheckedCondition = ConditionStringBuilder.Build(filter); conditions = uncheckedCondition.SqlConditionString; parameters = uncheckedCondition.Parameters; foreach (string column in uncheckedCondition.UncheckedIdentifiers) { Logger.LogTrace($"Checking existence of {table.Table}.{column}"); if (!Connection.CheckIfColumnExists(table, column)) { throw new InvalidOperationException($"Column {table.Table}.{column} does not exist or is not accessible."); } } } else { conditions = "1 = 1"; parameters = Enumerable.Empty <ISqlParameter>(); } string tableStr = DbCommandBuilder.QuoteIdentifier(table.Table); string sql = $"SELECT COUNT(*) FROM {tableStr} WHERE ({conditions})"; return(new DbQuery(sql, parameters)); }
/// <param name="schema">The schema of the table. May be <code>null</code> as some providers (e.g. SQLite, MySQL) do not support schemata.</param> /// <param name="tableName">The name of the table.</param> /// <param name="commandBuilder">The <see cref="DbCommandBuilder"/> of the provider, may be <code>null</code>.</param> /// <returns>A select statement used to retrieve all column names in a database.</returns> private static string SelectStatement(string schema, string tableName, DbCommandBuilder commandBuilder) { var hasSchema = !string.IsNullOrEmpty(schema); var quotedSchema = hasSchema ? commandBuilder?.QuoteIdentifier(schema) ?? schema : null; var quotedTableName = commandBuilder?.QuoteIdentifier(tableName) ?? tableName; var schemaSeparator = commandBuilder?.SchemaSeparator ?? "."; var tableDescription = hasSchema ? quotedSchema + schemaSeparator + quotedTableName : quotedTableName; return($"SELECT * FROM {tableDescription} WHERE 1=0"); }
private IEnumerable <string> GenerteInsertItemCommandText(string tableName, string pkName, List <ItemToReplicate> dataForTable) { foreach (var itemToReplicate in dataForTable) { var sb = new StringBuilder("INSERT INTO ") .Append(GetTableNameString(tableName)) .Append(" (") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", "); } sb.Length = sb.Length - 2; sb.Append(") VALUES (") .Append(itemToReplicate.DocumentId) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } DbParameter param = new OdbcParameter(); RelationalDatabaseWriter.SetParamValue(param, column.Value, null); sb.Append("'").Append(param.Value).Append("'").Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile) { sb.Append(" OPTION(RECOMPILE)"); } sb.Append(";"); yield return(sb.ToString()); } }
public override string QuoteIdentifier(string ident) { if (m_builder == null) { return(ident); } try { return(m_builder.QuoteIdentifier(ident)); } catch (Exception) { return(ident); } }
internal virtual string QuoteCompoundIdentifier(string s) { if (s == null) { throw ALinq.Error.ArgumentNull("s"); } if (s.StartsWith(ParameterPrefix, StringComparison.Ordinal)) { return(s); } if (IsQuoted(s)) { return(s); } if (!s.StartsWith(QuotePrefix, StringComparison.Ordinal) && s.EndsWith(QuoteSuffix, StringComparison.Ordinal)) { int length = s.IndexOf('.'); if (length < 0) { return(builder.QuoteIdentifier(s)); } string str = s.Substring(0, length); string str2 = s.Substring(length + 1, (s.Length - length) - 1); if (!IsQuoted(str2)) { str2 = builder.QuoteIdentifier(str2); } return(QuoteCompoundIdentifier(str) + ('.' + str2)); } if (s.StartsWith(QuotePrefix, StringComparison.Ordinal) && !s.EndsWith(QuoteSuffix, StringComparison.Ordinal)) { int num2 = s.LastIndexOf('.'); if (num2 < 0) { return(builder.QuoteIdentifier(s)); } string str3 = s.Substring(0, num2); if (!IsQuoted(str3)) { str3 = builder.QuoteIdentifier(str3); } string str4 = s.Substring(num2 + 1, (s.Length - num2) - 1); return(str3 + ('.') + QuoteCompoundIdentifier(str4)); } int index = s.IndexOf('.'); if (index < 0) { return(builder.QuoteIdentifier(s)); } string str5 = s.Substring(0, index); string str6 = s.Substring(index + 1, (s.Length - index) - 1); return(QuoteCompoundIdentifier(str5) + ('.') + QuoteCompoundIdentifier(str6)); }
internal static string EscapeObjectName(DesignerDataConnection connection, string objectName) { string str = "["; string str2 = "]"; try { DbProviderFactory dbProviderFactory = SqlDataSourceDesigner.GetDbProviderFactory(connection.ProviderName); DbCommandBuilder builder = dbProviderFactory.CreateCommandBuilder(); if (dbProviderFactory == OracleClientFactory.Instance) { str = str2 = "\""; } builder.QuotePrefix = str; builder.QuoteSuffix = str2; return(builder.QuoteIdentifier(objectName)); } catch (Exception) { return(str + objectName + str2); } }
public string QuoteIdentifier(string identifier) { return(_builder.QuoteIdentifier(identifier)); }
/// <summary> /// /// </summary> /// <param name="name"></param> /// <returns></returns> public override string Quote(string name) { return(cb.QuoteIdentifier(name)); }
private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, Action <DbCommand> commandCallback = null) { var sqlReplicationTableMetrics = sqlReplicationMetrics.GetTableMetrics(tableName); var replicationInsertActionsMetrics = sqlReplicationTableMetrics.SqlReplicationInsertActionsMeter; var replicationInsertActionsHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsHistogram; var replicationInsertDurationHistogram = sqlReplicationTableMetrics.SqlReplicationInsertActionsDurationHistogram; var sp = new Stopwatch(); foreach (var itemToReplicate in dataForTable) { sp.Restart(); using (var cmd = connection.CreateCommand()) { cmd.Transaction = tx; database.WorkContext.CancellationToken.ThrowIfCancellationRequested(); var sb = new StringBuilder("INSERT INTO ") .Append(GetTableNameString(tableName)) .Append(" (") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", "); } sb.Length = sb.Length - 2; var pkParam = cmd.CreateParameter(); pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName); pkParam.Value = itemToReplicate.DocumentId; cmd.Parameters.Add(pkParam); sb.Append(") \r\nVALUES (") .Append(GetParameterName(providerFactory, commandBuilder, pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } var colParam = cmd.CreateParameter(); colParam.ParameterName = column.Key; SetParamValue(colParam, column.Value, stringParserList); cmd.Parameters.Add(colParam); sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile) { sb.Append(" OPTION(RECOMPILE)"); } var stmt = sb.ToString(); cmd.CommandText = stmt; if (commandCallback != null) { commandCallback(cmd); } try { cmd.ExecuteNonQuery(); } catch (Exception e) { log.WarnException( "Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." + Environment.NewLine + cmd.CommandText, e); replicationStatistics.RecordWriteError(e, database); hadErrors = true; } finally { sp.Stop(); var elapsedMiliseconds = sp.ElapsedMilliseconds; if (log.IsDebugEnabled) { log.Debug(string.Format("Insert took: {0}ms, statement: {1}", elapsedMiliseconds, stmt)); } var elapsedMicroseconds = (long)(sp.ElapsedTicks * SystemTime.MicroSecPerTick); replicationInsertDurationHistogram.Update(elapsedMicroseconds); replicationInsertActionsMetrics.Mark(1); replicationInsertActionsHistogram.Update(1); if (elapsedMiliseconds > LongStatementWarnThresholdInMiliseconds) { HandleSlowSql(elapsedMiliseconds, stmt); } } } } }
public override void OnIndexEntryCreated(string entryKey, Document document) { var cmd = _providerFactory.CreateCommand(); var pkParam = cmd.CreateParameter(); pkParam.ParameterName = GetParameterName("entryKey"); pkParam.Value = entryKey; cmd.Parameters.Add(pkParam); var sb = new StringBuilder("INSERT INTO ") .Append(_commandBuilder.QuoteIdentifier(destination.TableName)) .Append(" (") .Append(_commandBuilder.QuoteIdentifier(destination.PrimaryKeyColumnName)) .Append(", "); foreach (var mapping in destination.ColumnsMapping) { sb.Append(mapping.Value).Append(", "); } sb.Length = sb.Length - 2; sb.Append(") \r\nVALUES (") .Append(pkParam.ParameterName) .Append(", "); foreach (var mapping in destination.ColumnsMapping) { var parameter = cmd.CreateParameter(); parameter.ParameterName = GetParameterName(mapping.Key); var field = document.GetFieldable(mapping.Key); var numericfield = document.GetFieldable(String.Concat(mapping.Key, "_Range")); if (numericfield != null) { field = numericfield; } if (field == null || field.StringValue == Constants.NullValue) { parameter.Value = DBNull.Value; } else if (field.StringValue == Constants.EmptyString) { parameter.Value = ""; } else if (field.StringValue.Equals("False", StringComparison.InvariantCultureIgnoreCase)) { parameter.Value = false; } else if (field.StringValue.Equals("True", StringComparison.InvariantCultureIgnoreCase)) { parameter.Value = true; } else if (field is NumericField) { var numField = (NumericField)field; parameter.Value = numField.NumericValue; } else { var stringValue = field.StringValue; if (datePattern.IsMatch(stringValue)) { try { parameter.Value = DateTools.StringToDate(stringValue); } catch { parameter.Value = stringValue; } } else { DateTime time; if (DateTime.TryParseExact(stringValue, Default.DateTimeFormatsToRead, CultureInfo.InvariantCulture, DateTimeStyles.None, out time)) { parameter.Value = time; } else { parameter.Value = stringValue; } } } cmd.Parameters.Add(parameter); sb.Append(parameter.ParameterName).Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); cmd.CommandText = sb.ToString(); commands.Enqueue(cmd); }
private void InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable) { foreach (var itemToReplicate in dataForTable) { using (var cmd = connection.CreateCommand()) { cmd.Transaction = tx; var sb = new StringBuilder("INSERT INTO ") .Append(commandBuilder.QuoteIdentifier(tableName)) .Append(" (") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", "); } sb.Length = sb.Length - 2; var pkParam = cmd.CreateParameter(); pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName); pkParam.Value = itemToReplicate.DocumentId; cmd.Parameters.Add(pkParam); sb.Append(") \r\nVALUES (") .Append(GetParameterName(providerFactory, commandBuilder, pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } var colParam = cmd.CreateParameter(); colParam.ParameterName = column.Key; SetParamValue(colParam, column.Value); cmd.Parameters.Add(colParam); sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); cmd.CommandText = sb.ToString(); try { cmd.ExecuteNonQuery(); } catch (Exception e) { log.WarnException( "Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." + Environment.NewLine + cmd.CommandText, e); replicationStatistics.RecordWriteError(e, database); hadErrors = true; } } } }
private void ConfigureAdapter(DbConnection cnn, string tableName) { DbDataAdapter adapter; if (!_adapters.TryGetValue(tableName, out adapter)) { DbCommandBuilder builder = _factory.CreateCommandBuilder(); adapter = _factory.CreateDataAdapter(); adapter.SelectCommand = GetCommand(cnn, string.Format("SELECT * FROM {0}", builder.QuoteIdentifier(tableName))); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; builder.DataAdapter = adapter; // save default builder to quote identifiers if (_builder == null) { _builder = builder; } _adapters.Add(tableName, adapter); } adapter.Fill(_dataSet, tableName); }
public string EncodeTable(string table) { return(CommandBuilder.QuoteIdentifier(table)); }
/// <summary> /// Quotes the <paramref name="unquotedIdentifier"/>. /// </summary> /// <param name="unquotedIdentifier">The original unquoted identifier.</param> /// <returns><see cref="string"/></returns> protected virtual string QuoteIdentifier(string unquotedIdentifier) { return(_commandBuilder.QuoteIdentifier(unquotedIdentifier)); }
private IEnumerable <DbCommand> InsertItems(string tableName, string pkName, List <ItemToReplicate> dataForTable, bool exportCommands = false) { foreach (var itemToReplicate in dataForTable) { using (var cmd = connection.CreateCommand()) { cmd.Transaction = tx; database.WorkContext.CancellationToken.ThrowIfCancellationRequested(); var sb = new StringBuilder("INSERT INTO ") .Append(GetTableNameString(tableName)) .Append(" (") .Append(commandBuilder.QuoteIdentifier(pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } sb.Append(commandBuilder.QuoteIdentifier(column.Key)).Append(", "); } sb.Length = sb.Length - 2; var pkParam = cmd.CreateParameter(); pkParam.ParameterName = GetParameterName(providerFactory, commandBuilder, pkName); pkParam.Value = itemToReplicate.DocumentId; cmd.Parameters.Add(pkParam); sb.Append(") \r\nVALUES (") .Append(GetParameterName(providerFactory, commandBuilder, pkName)) .Append(", "); foreach (var column in itemToReplicate.Columns) { if (column.Key == pkName) { continue; } var colParam = cmd.CreateParameter(); colParam.ParameterName = column.Key; SetParamValue(colParam, column.Value, stringParserList); cmd.Parameters.Add(colParam); sb.Append(GetParameterName(providerFactory, commandBuilder, column.Key)).Append(", "); } sb.Length = sb.Length - 2; sb.Append(")"); if (IsSqlServerFactoryType && cfg.ForceSqlServerQueryRecompile) { sb.Append(" OPTION(RECOMPILE)"); } cmd.CommandText = sb.ToString(); if (exportCommands) { yield return(cmd); } try { cmd.ExecuteNonQuery();; } catch (Exception e) { log.WarnException( "Failure to replicate changes to relational database for: " + cfg.Name + " (doc: " + itemToReplicate.DocumentId + " ), will continue trying." + Environment.NewLine + cmd.CommandText, e); replicationStatistics.RecordWriteError(e, database); hadErrors = true; } } } }
private string Quote(string identifier) { return(builder.QuoteIdentifier(identifier)); }
protected override void ProcessRecord() { try { if (InputObject == null) { return; } if (_fieldSet == null) { _qualifiedTableName = GetQualifiedTableName(); CreateTable(); _useNamedParameters = TestNamedParameterSupport(); _insertStmt = "insert into " + _qualifiedTableName + " ("; _transaction = _connection.BeginTransaction(); } using (var cmd = _connection.CreateCommand()) { cmd.CommandText = _insertStmt; cmd.Transaction = _transaction; var buffer = new StringBuilder(_insertStmt); var placeholders = new StringBuilder(); string pa = "?"; var count = 0; if (InputObject is PSObject) { var obj = (PSObject)InputObject; foreach (var p in obj.Properties) { if (!p.IsGettable || !p.IsInstance) { continue; } if (_fieldSet.Contains(p.Name)) { if (count > 0) { buffer.Append(','); placeholders.Append(','); } ++count; var qualified = _builder.QuoteIdentifier(p.Name); buffer.Append(qualified); var param = cmd.CreateParameter(); if (_useNamedParameters) { pa = "@" + count.ToString(); param.ParameterName = pa; } var value = p.Value; if (value != null) { if (value is PSObject) { value = (value as PSObject).BaseObject; } param.Value = value; } cmd.Parameters.Add(param); placeholders.Append(pa); } } } else { foreach (var p in InputObject.GetType().GetProperties()) { if (!p.CanRead) { continue; } if (_fieldSet.Contains(p.Name)) { if (count > 0) { buffer.Append(','); placeholders.Append(','); } ++count; var qualified = _builder.QuoteIdentifier(p.Name); buffer.Append(qualified); var param = cmd.CreateParameter(); if (_useNamedParameters) { pa = "@" + count.ToString(); param.ParameterName = pa; } var value = p.GetValue(InputObject); if (value != null) { if (value is PSObject) { value = (value as PSObject).BaseObject; } param.Value = value; } cmd.Parameters.Add(param); placeholders.Append(pa); } } } // If there are no columns to be inserted, just skip. if (placeholders.Length == 0) { return; } buffer.Append(") values ("); buffer.Append(placeholders); buffer.Append(')'); var stmt = buffer.ToString(); WriteVerbose(stmt); cmd.CommandText = stmt; var rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected != 1) { throw new RuntimeException("Insertion failed"); } } } catch (Exception e) { if (_transaction != null) { _transaction.Rollback(); } DisposeResources(); WriteError(new ErrorRecord(e, "", ErrorCategory.NotSpecified, null)); throw new PipelineStoppedException(); } }