public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (!String.IsNullOrWhiteSpace(Table)) { sqlCommand.AppendCommand($"\"{Table}\"."); } if (Column == "*") { sqlCommand.AppendCommand($"*"); } else { sqlCommand.AppendCommand($"\"{Column}\""); if (DotnetType == typeof(PgSqlLTree)) { sqlCommand.AppendCommand("::text"); } //if (!String.IsNullOrWhiteSpace(Alias)) // sqlCommand.AppendCommand($" AS \"{Alias}\""); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommand($"\"{_column}\""); if (!String.IsNullOrWhiteSpace(_alias)) { sqlCommand.AppendCommand($" AS \"{_alias}\""); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommand($"\"{_name}\""); return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommand($"ADD COLUMN "); if (!ThrowIfExists) { sqlCommand.AppendCommand("IF NOT EXISTS "); } sqlCommand.AppendCommand($"{Name.ClearString()} {DataType}"); sqlCommand.AppendCommand(";"); return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommand($"\"{Name}\""); if (SortDescending) { sqlCommand.AppendCommand($" DESC"); } if (InvertNullOrder) { sqlCommand.AppendCommand($" {(SortDescending ? "NULLS LAST" : "NULLS FIRST")}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); string num = Number.ToString(); sqlCommand.AppendCommand(num); return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); var selectCommand = _select.GetSqlCommand(tableDefinition); sqlCommand.AppendCommand($"EXISTS ({selectCommand.Command})", selectCommand.Parameters); return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_groupByItemList.Any()) { sqlCommand.AppendCommand($"{String.Join(", ", _groupByItemList)}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_fromSources.Any()) { sqlCommand.AppendCommand($"{String.Join(", ", _fromSources.Select(fs => fs.GetSqlCommand(tableDefinition).Command))}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_offsetClauseItem != null) { sqlCommand.AppendCommand($"{_offsetClauseItem}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_columns.Any()) { sqlCommand.AppendCommand($"{String.Join(", ", _columns.Select(c => c.GetSqlCommand(tableDefinition).Command))}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_returningValue?.Any() == true) { sqlCommand.AppendCommand($"{String.Join(", ", _returningValue.Select(r => r.GetSqlCommand(tableDefinition).Command))}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_orderByClauseItems.Any()) { sqlCommand.AppendCommand($"{String.Join(",", _orderByClauseItems.Select(i => i.GetSqlCommand(tableDefinition).Command))}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); var values = _values.Select(val => { var col = tableDefinition.GetColumnByClrName(val._key) ?? tableDefinition.GetColumnByDbName(val._key); var expr = new PgSqlParameter(col.GetNameForDb(), val._value); return(expr); }).ToList(); var valuesIds = values.Select(v => v.UniqueId).ToList(); var sortedValues = new List <PgSqlParameter>(); if (tableDefinition != null) { foreach (var column in tableDefinition.Columns()) { var exists = values.FirstOrDefault(v => v.ColumnName.Equals(column.GetNameForDb(), StringComparison.OrdinalIgnoreCase)); if (exists != null) { exists.Column = column; sortedValues.Add(exists); } else { var defaultExpr = new PgSqlParameter(column.GetNameForDb(), "DEFAULT"); defaultExpr.Column = column; sortedValues.Add(defaultExpr); } } } else { sortedValues = values; } var sortedValuesIds = sortedValues.Select(v => v.UniqueId).ToList(); if (_values.Any()) { var parms = sortedValues.Where(ValuesClauseHelper.IsNotDefault); var parmsIds = parms.Select(p => p.UniqueId); var placeHolders = sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder); sqlCommand.AppendCommand($"{String.Join(", ", placeHolders)}", parms); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (ConflictAction == "THROW") { return(sqlCommand); } sqlCommand.AppendCommand($"ON CONFLICT"); if (ConflictAction == "DO NOTHING") { sqlCommand.AppendCommandLine(" DO NOTHING"); return(sqlCommand); } sqlCommand.AppendCommandLine($" ({String.Join(", ", tableDefinition.PrimaryKeys().Select(c => c.DbName))})"); sqlCommand.AppendCommandLine(" DO UPDATE SET"); if (_valueClauses.Any()) { foreach (var valuesClause in _valueClauses) { if (valuesClause is NamedValues namedValues) { foreach (var namedValuesValue in namedValues._values) { var expr = new PgSqlParameter(namedValuesValue._key, namedValuesValue._value).SetColum(tableDefinition); sqlCommand.AppendCommandLine($"{namedValuesValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>() { expr }); } } else if (valuesClause is PositionedValues positionedValues) { foreach (var positionedValue in positionedValues._values) { var expr = new PgSqlParameter(positionedValue._key, positionedValue._value); sqlCommand.AppendCommandLine($"{positionedValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>() { expr }); } } } sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray()) + Environment.NewLine; } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_limitClauseItem != null) { var limit = _limitClauseItem.GetSqlCommand(tableDefinition).Command; sqlCommand.AppendCommand($"{limit}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (!_includeDescendants) { sqlCommand.AppendCommand($"ONLY "); } if (!String.IsNullOrWhiteSpace(_table)) { sqlCommand.AppendCommand($"\"{_table}\"."); } sqlCommand.AppendCommand($"\"{_column}\""); if (!String.IsNullOrWhiteSpace(_alias)) { sqlCommand.AppendCommand($" AS \"{_alias}\""); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (_selectListItems.Any()) { var commands = new List <PgSqlCommand>(); foreach (var selectListItem in _selectListItems) { var comm = selectListItem.GetSqlCommand(tableDefinition); commands.Add(comm); } sqlCommand.AppendCommand($"{String.Join(",", commands.Select(c => c.Command))}", commands.SelectMany(c => c.Parameters)); } else { sqlCommand.AppendCommand("*"); } return(sqlCommand); }
//public override string ToString() //{ // var strBuilder = new StringBuilder(); // if (!_includeChildTables) // { // strBuilder.Append("ONLY "); // } // if (!String.IsNullOrWhiteSpace(_schema)) // strBuilder.Append($"\"{_schema}\"."); // strBuilder.Append($"\"{_name}\""); // if (!String.IsNullOrWhiteSpace(_alias)) // strBuilder.Append($" AS \"{_alias}\""); // return strBuilder.ToString(); //} public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); if (!_includeChildTables) { sqlCommand.AppendCommand("ONLY "); } if (!String.IsNullOrWhiteSpace(_schema)) { sqlCommand.AppendCommand($"\"{_schema}\"."); } sqlCommand.AppendCommand($"\"{_name}\""); if (!String.IsNullOrWhiteSpace(_alias)) { sqlCommand.AppendCommand($" AS \"{_alias}\""); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); switch (_forType) { case ForType.Update: sqlCommand.AppendCommand("UPDATE"); break; case ForType.NoKeyUpdate: sqlCommand.AppendCommand("NO KEY UPDATE"); break; case ForType.Share: sqlCommand.AppendCommand("SHARE"); break; case ForType.KeyShare: sqlCommand.AppendCommand("KEY SHARE"); break; } if (_ofTables.Any()) { sqlCommand.AppendCommand($" OF {String.Join(",", _ofTables)}"); } switch (_forOptions) { case ForOptions.None: break; case ForOptions.NoWait: sqlCommand.AppendCommand(" NOWAIT"); break; case ForOptions.SkipLocked: sqlCommand.AppendCommand(" SKIP LOCKED"); break; } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); var fromComm = _fromClause.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($" DELETE FROM {fromComm.Command}", fromComm.Parameters); if (_whereClause != null) { var whereComm = _whereClause.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters); } if (_returning != null) { sqlCommand.AppendCommand($"RETURNING {_returning.GetSqlCommand(tableDefinition).Command}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommandLine($"UPDATE {_updateDestination.GetSqlCommand(tableDefinition).Command}"); if (_set != null) { sqlCommand.AppendCommandLine("SET"); var setComm = _set.GetSqlCommand(tableDefinition); sqlCommand.AppendCommand($"{AddIntend(setComm.Command, 4)}", setComm.Parameters); } if (_whereClause != null) { var whereComm = _whereClause.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); var sortedValues = new List <PgSqlParameter>(); var columns = tableDefinition.Columns().ToList(); for (int i = 0; i < columns.Count; i++) { var column = columns[i]; IValueItem valueItem = null; if (_values.Count > i) { valueItem = _values[i]; } if (valueItem == null) { var defaultExpr = new PgSqlParameter(i.ToString(), "DEFAULT"); sortedValues.Add(defaultExpr); } else { var expr = new PgSqlParameter(i.ToString(), valueItem._value); sortedValues.Add(expr); } } if (_values.Any()) { sqlCommand.AppendCommand($"{String.Join(", ", sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder))}", sortedValues.Where(ValuesClauseHelper.IsNotDefault)); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); TableDefinition keyDefinition = new TableDefinition(); sqlCommand.AppendCommand($"INSERT INTO {String.Join(", ", _intoDestination.GetSqlCommand(tableDefinition).Command)}"); if (_intoColumns != null) { var intoColumnsComm = _intoColumns.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"{Environment.NewLine}\t({intoColumnsComm.Command})"); _intoColumns._columns.ForEach(c => { var col = tableDefinition?.GetColumnBuilderByDbName(c._name); if (col == null) { keyDefinition.AddColumn(new ColumnBuilder().SetDbName(c._name)); } else { keyDefinition.AddColumn(col); } }); } if (_valueClauses.Any()) { sqlCommand.AppendCommandLine($"VALUES"); foreach (var valuesClause in _valueClauses) { if (valuesClause is NamedValues namedValues) { var valuesComm = namedValues.GetSqlCommand(keyDefinition); sqlCommand.AppendCommandLine($"\t({valuesComm.Command}),", valuesComm.Parameters); } else if (valuesClause is PositionedValues) { var valuesComm = valuesClause.GetSqlCommand(keyDefinition); sqlCommand.AppendCommandLine($"\t({valuesComm.Command}),", valuesComm.Parameters); } } sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray()) + Environment.NewLine; } if (_onConflict != null) { var onConflict = _onConflict.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine(onConflict.Command, onConflict.Parameters); } if (_returningClause != null) { sqlCommand.AppendCommand($"RETURNING {_returningClause.GetSqlCommand(tableDefinition).Command}"); } return(sqlCommand); }
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition) { var sqlCommand = new PgSqlCommand(); sqlCommand.AppendCommand("SELECT "); if (_exists != null) { var existsCommand = _exists.GetSqlCommand(tableDefinition); sqlCommand.AppendCommand(existsCommand.Command, existsCommand.Parameters); return(sqlCommand); } if (_distinct != null) { if (_distinct is bool) { sqlCommand.AppendCommand($"DISTINCT "); } else if (_distinct is string) { sqlCommand.AppendCommand($"DISTINCT ON ({_distinct}) "); } } var selectComm = _selectList.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"{selectComm.Command}", selectComm.Parameters); if (_from != null) { var fromComm = _from.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"FROM {fromComm.Command}", fromComm.Parameters); } if (_where != null) { var whereComm = _where.GetSqlCommand(tableDefinition); if (!String.IsNullOrWhiteSpace(whereComm.Command)) { sqlCommand.AppendCommandLine($"WHERE {whereComm.Command}", whereComm.Parameters); } } if (_groupBy != null) { var groupByComm = _groupBy.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"GROUP BY {groupByComm.Command}"); } if (_orderBy != null) { var orderByComm = _orderBy.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"ORDER BY {orderByComm.Command}"); } if (_offset != null) { var offsetComm = _offset.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"OFFSET {offsetComm.Command}"); } if (_limit != null) { var limitComm = _limit.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"LIMIT {limitComm.Command}"); } if (_for != null) { var forComm = _for.GetSqlCommand(tableDefinition); sqlCommand.AppendCommandLine($"FOR {forComm.Command}"); } return(sqlCommand); }