/// <summary> /// Builds the statements that gets back the IDs for the inserted statement /// </summary> /// <param name="table"></param> /// <param name="autoPKColumn">Auto-generated PK columns for reference (i.e. AUTO_INCREMENT)</param> /// <param name="inputPKColumns">PK columns for reference</param> /// <param name="inputPKValues">PK values for reference</param> /// <param name="outputParameters">Expected output parameters</param> /// <param name="outputExpressions">Expressions (to help generate output parameters)</param> /// <returns></returns> public virtual SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> pkColumns, IList<SqlStatement> pkValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions) { if (autoPKColumn.Count == outputParameters.Count) return "SELECT @@IDENTITY"; var insertIds = new SqlStatementBuilder("SELECT "); insertIds.AppendFormat(" ({0})", SqlStatement.Join(", ", outputColumns)); insertIds.Append(" FROM "); insertIds.Append(table); insertIds.Append(" WHERE "); bool valueSet = false; if (autoPKColumn.Count > 0) { insertIds.AppendFormat("{0} = @@IDENTITY", autoPKColumn[0]); valueSet = true; } for (IEnumerator<SqlStatement> column = pkColumns.GetEnumerator(), value = pkValues.GetEnumerator(); column.MoveNext() && value.MoveNext();) { if (valueSet) insertIds.Append(" AND "); insertIds.AppendFormat("{0} = {1}", column.Current, value.Current); valueSet = true; } return insertIds.ToSqlStatement(); }
public UpsertQuery(DataContext dataContext, SqlStatement sql, SqlStatement idQuerySql, IList<ObjectInputParameterExpression> inputParameters, IList<ObjectOutputParameterExpression> outputParameters, IList<ObjectInputParameterExpression> primaryKeyParameters) : base(dataContext, sql,inputParameters) { OutputParameters = outputParameters; PrimaryKeyParameters = primaryKeyParameters; IdQuerySql = idQuerySql; }
private SqlResult GetRawTableData() { SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(PHNullableType) ); SqlStatement stmt = sb.GetStatement( true ); // override generated statement to make sure we execute this without any type association stmt = new SqlStatement( StatementType.Select, Broker.Provider.GetCommand(), stmt.Sql ); SqlResult sr = stmt.Execute(); return sr; }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { sql.Sql += ";SELECT LAST_INSERT_ID()"; using (sql) { var rez = sql.ExecuteScalar(); return new LastInsertId(rez); } }
//public override string GetInsert(string table, IList<string> inputColumns, IList<string> inputValues) //{ // return "BEGIN " + base.GetInsert(table, inputColumns, inputValues); //} public override SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions) { // no parameters? no need to get them back if (outputParameters.Count == 0) return ""; // otherwise we keep track of the new values return SqlStatement.Format("SELECT {0} INTO {1} FROM DUAL", SqlStatement.Join(", ", (from outputExpression in outputExpressions select outputExpression.Replace(".NextVal", ".CurrVal", true)).ToArray()), SqlStatement.Join(", ", outputParameters.ToArray())); }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { sql.Sql += ";Select SCOPE_IDENTITY() as id"; using (sql) { var rez = sql.ExecuteScalar(); return new LastInsertId(rez); } }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { if (!string.IsNullOrEmpty(idKey)) { sql.Sql += (" returning "+EscapeName(idKey)); } using(sql) { return new LastInsertId(sql.ExecuteScalar()); } }
public override SqlStatement ReviewSelectSql(SelectExpression select, SqlStatement sql) { const string ReadLockTemplate = "{0} \r\n FOR SHARE;"; const string WriteLockTemplate = "{0} \r\n FOR UPDATE;"; var flags = select.CommandInfo.Flags; if (flags.IsSet(LinqCommandFlags.ReadLock)) return string.Format(ReadLockTemplate, sql); if (flags.IsSet(LinqCommandFlags.WriteLock)) return string.Format(WriteLockTemplate, sql); return sql; }
/// <summary> /// Builds an insert clause /// </summary> /// <param name="table">Table name</param> /// <param name="inputColumns">Columns to be inserted</param> /// <param name="inputValues">Values to be inserted into columns</param> /// <returns></returns> public virtual SqlStatement GetInsert(SqlStatement table, IList<SqlStatement> inputColumns, IList<SqlStatement> inputValues) { if (inputColumns.Count == 0) return SqlStatement.Empty; var insertBuilder = new SqlStatementBuilder("INSERT INTO "); insertBuilder.Append(table); insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputColumns)); insertBuilder.Append(" VALUES"); insertBuilder.AppendFormat(" ({0})", SqlStatement.Join(", ", inputValues)); return insertBuilder.ToSqlStatement(); }
public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit) { // return string.Format("SELECT FIRST {0} FROM ({1})", limit, select); var trimSelect = "SELECT "; if (select.Count > 0 && select[0].Sql.StartsWith(trimSelect)) { var selectBuilder = new SqlStatementBuilder(select); var remaining = select[0].Sql.Substring(trimSelect.Length); selectBuilder.Parts[0] = new SqlLiteralPart(remaining); return SqlStatement.Format("SELECT FIRST {0} {1}", limit, selectBuilder.ToSqlStatement()); } throw new ArgumentException("Invalid SELECT format"); }
public ResolvedSubStatementTableInfo (string tableAlias, SqlStatement sqlStatement) { ArgumentUtility.CheckNotNullOrEmpty ("tableAlias", tableAlias); ArgumentUtility.CheckNotNull ("sqlStatement", sqlStatement); _sqlStatement = sqlStatement; _tableAlias = tableAlias; var streamedSequenceInfo = sqlStatement.DataInfo as StreamedSequenceInfo; if (streamedSequenceInfo == null) throw new ArgumentException ("For a statement to be used as a table, it must return a sequence of items.", "sqlStatement"); _itemType = streamedSequenceInfo.ResultItemType; }
public ResolvedJoinedGroupingTableInfo ( string tableAlias, SqlStatement sqlStatement, SqlGroupingSelectExpression associatedGroupingSelectExpression, string groupSourceTableAlias) : base (tableAlias, sqlStatement) { ArgumentUtility.CheckNotNull ("associatedGroupingSelectExpression", associatedGroupingSelectExpression); ArgumentUtility.CheckNotNull ("groupSourceTableAlias", groupSourceTableAlias); _associatedGroupingSelectExpression = associatedGroupingSelectExpression; _groupSourceTableAlias = groupSourceTableAlias; }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { if (idKey != null) { sql.Sql += ";SELECT last_insert_rowid()"; using (sql) { var rez = sql.ExecuteScalar(); return new LastInsertId(rez); } } sql.Execute(); return LastInsertId.Empty; }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { //sql.Sql += ";Select @@IDENTITY as id"; using (sql) { sql.Execute(); using (var idquery = new SqlStatement(sql.Db)) { idquery.SetSql("select @@IDENTITY as id"); var rez = idquery.ExecuteScalar(); return new LastInsertId(rez); } } }
protected override SqlStatement GetLiteralEqual(SqlStatement a, SqlStatement b) { // PostgreSQL return NULL (and not a boolean) for every comparaison involving // a NULL value, unless the operator used is "IS" (or "IS NOT"). Also, // using those two operators when the right-hand value is not a literal // NULL is an error. The only possibility is to explicitly check for NULL // literals and even swap the operands to make sure NULL gets to the // right place. if (b.Count == 1 && b[0].Sql == "NULL") return SqlStatement.Format("{0} IS {1}", a, b); else if (a.Count == 1 && a[0].Sql == "NULL") return SqlStatement.Format("{0} IS {1}", b, a); else return SqlStatement.Format("{0} = {1}", a, b); }
public override SqlStatement GetInsertIds(SqlStatement table, IList<SqlStatement> autoPKColumn, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues, IList<SqlStatement> outputColumns, IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions) { // No parameters? no need to get them back. if (outputParameters.Count == 0) return SqlStatement.Empty; // Otherwise we keep track of the new values. Note that we avoid null expressions // that can be present in the passed list (is this a bug above us?) IList<SqlStatement> ids = new List<SqlStatement>(); foreach (SqlStatement outputExpression in outputExpressions) { if (outputExpression != null) ids.Add(outputExpression.Replace("nextval(", "currval(", true)); } return SqlStatement.Format("SELECT {0}", SqlStatement.Join(", ", ids.ToArray())); }
protected override SqlStatement GetLiteralDateTimePart(SqlStatement dateExpression, SqlFunctionType operationType) { switch (operationType) { case SqlFunctionType.Date: return SqlStatement.Format("DATE({0})", dateExpression); case SqlFunctionType.Time: return SqlStatement.Format("DATE_PART('time', {0})", dateExpression); case SqlFunctionType.Week: return SqlStatement.Format("EXTRACT(WEEK FROM {0})", dateExpression); case SqlFunctionType.Year: return SqlStatement.Format("EXTRACT(YEAR FROM {0})", dateExpression); case SqlFunctionType.Month: return SqlStatement.Format("EXTRACT(MONTH FROM {0})", dateExpression); case SqlFunctionType.Day: return SqlStatement.Format("EXTRACT(DAY FROM {0})", dateExpression); default: Util.Throw("SQL function {0} not supported.", operationType); return null; } }
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey) { IDbDataParameter param = null; if (!string.IsNullOrEmpty(idKey)) { sql.Sql += string.Format(" returning {0} into :newid", EscapeName(idKey)); var cmd = sql.Command; param = cmd.CreateParameter(); param.ParameterName = ":newid"; param.Value = DBNull.Value; param.Direction = ParameterDirection.ReturnValue; param.DbType = DbType.Int64; cmd.Parameters.Add(param); } using (sql) { sql.Execute(); if (param == null) return LastInsertId.Empty; return new LastInsertId(param.Value); } }
/// <summary> /// Joins a list of conditions to make a HAVING clause /// </summary> /// <param name="havings"></param> /// <returns></returns> public virtual SqlStatement GetHavingClause(SqlStatement[] havings) { if (havings.Length == 0) return SqlStatement.Empty; return SqlStatement.Format("HAVING {0}", SqlStatement.Join(" AND ", havings)); }
protected override void SetStatementFrom(SqlStatement statement, SqlTable from) { var delete = (SqlDelete)statement; delete.From = from; }
protected override SqlTableRef GetStatementTable(SqlStatement statement) { var delete = (SqlDelete)statement; return(delete.Delete); }
private void UpdateList() { base.OnSectionActivated(); mpListView1.Items.Clear(); mpComboBoxCard.Items.Clear(); try { IList <Card> dbsCards = Card.ListAll(); foreach (Card card in dbsCards) { cardTypes[card.DevicePath] = RemoteControl.Instance.Type(card.IdCard); mpComboBoxCard.Items.Add(new CardInfo(card)); } } catch (Exception) {} try { SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(Card)); sb.AddOrderByField(false, "priority"); SqlStatement stmt = sb.GetStatement(true); IList <Card> cards = ObjectFactory.GetCollection <Card>(stmt.Execute()); for (int i = 0; i < cards.Count; ++i) { Card card = cards[i]; string cardType = ""; if (cardTypes.ContainsKey(card.DevicePath)) { cardType = cardTypes[card.DevicePath].ToString(); } ListViewItem item = mpListView1.Items.Add("", 0); item.SubItems.Add(card.Priority.ToString()); if (card.Enabled) { item.Checked = true; item.Font = new Font(item.Font, FontStyle.Regular); item.Text = "Yes"; } else { item.Checked = false; item.Font = new Font(item.Font, FontStyle.Strikeout); item.Text = "No"; } item.SubItems.Add(cardType); //CAM and CAM limit don't apply to non-digital cards if (cardType.ToUpperInvariant().Contains("DVB") || cardType.ToUpperInvariant().Contains("ATSC")) { if (card.CAM) { item.SubItems.Add("Yes"); } else { item.SubItems.Add("No"); } item.SubItems.Add(card.DecryptLimit.ToString()); } else { item.SubItems.Add(""); item.SubItems.Add(""); } item.SubItems.Add(card.IdCard.ToString()); item.SubItems.Add(card.Name); //check if card is really available before setting to enabled. bool cardPresent = RemoteControl.Instance.CardPresent(card.IdCard); if (!cardPresent) { item.SubItems.Add("No"); } else { item.SubItems.Add("Yes"); } //EPG grabbing doesn't apply to non-digital cards if (cardType.ToUpperInvariant().Contains("DVB") || cardType.ToUpperInvariant().Contains("ATSC")) { if (!card.GrabEPG) { item.SubItems.Add("No"); } else { item.SubItems.Add("Yes"); } } else { item.SubItems.Add(""); } item.SubItems.Add(card.DevicePath); item.Tag = card; } } catch (Exception) { MessageBox.Show(this, "Unable to access service. Is the TvService running??"); } ReOrder(); UpdateHybrids(); checkWinTVCI(); UpdateMenu(); mpListView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize); }
protected override void SetStatementWhere(SqlStatement statement, SqlExpression @where) { var delete = (SqlDelete)statement; delete.Where = where; }
/// <summary> /// Return exists /// </summary> public SqlStatement Exists(SqlStatement sqlStatement) { return(new SqlStatement(this, "SELECT CASE WHEN EXISTS (").Append(sqlStatement.Build()).Append(") THEN true ELSE false END")); }
/// <summary> /// Gets posts to display in the carousel. /// </summary> /// <returns>a collection of post previews to show on the top of the category page.</returns> public IEnumerable <PostPreview> GetRecentPostsForCarousel() { SqlStatement statement = new SqlStatement(StoredProcedures.GetRecentPostsForCarousel); return(db.ExecuteStoredProc(statement, PostPreviewMapper)); }
protected override void BuildCommand(SqlStatement selectQuery, int commandNumber) => StringBuilder.AppendLine($"SELECT {DB2iSeriesTools.IdentityColumnSql} FROM {DB2iSeriesTools.iSeriesDummyTableName()}");
public override int CommandCount(SqlStatement statement) { return(statement is SqlInsertStatement insertStatement && insertStatement.Insert.WithIdentity ? 2 : 1); }
public static List <T> ExecuteList <T>(SqlStatement sql) where T : class, IDbObject { return(GetOperator(typeof(T)).ExecuteList <T>(sql)); }
public static SqlStatement OptimizeStatement([JetBrains.Annotations.NotNull] this ISqlOptimizer optimizer, [JetBrains.Annotations.NotNull] SqlStatement statement, [JetBrains.Annotations.NotNull] MappingSchema mappingSchema) { if (optimizer == null) { throw new ArgumentNullException(nameof(optimizer)); } if (statement == null) { throw new ArgumentNullException(nameof(statement)); } if (mappingSchema == null) { throw new ArgumentNullException(nameof(mappingSchema)); } // transforming parameters to values var newStatement = statement.ProcessParameters(mappingSchema); // optimizing expressions according to new values newStatement = optimizer.OptimizeStatement(newStatement); // reset parameters newStatement.CollectParameters(); return(newStatement); }
/// <summary> /// Returns specified SQL query statement. /// </summary> /// <param name="statement">SqlStatement; SQL query statement to return</param> /// <returns>string; SQL query statement</returns> public abstract string GetSqlStatement(SqlStatement statement);
/// <summary> /// Returning statement /// </summary> public SqlStatement Returning(SqlStatement sqlStatement, params ColumnMapping[] returnColumns) { return(sqlStatement); }
/// <summary> /// Create a command from the specified contxt with sql statement /// </summary> public IDbCommand CreateCommand(DataContext context, SqlStatement stmt) { var c = stmt.Build(); return(this.CreateCommandInternal(context, CommandType.Text, c.SQL, c.Arguments.ToArray())); }
/// <summary> /// Gets the literal add. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralAdd(SqlStatement a, SqlStatement b) { return SqlStatement.Format("{0} + {1}", a, b); }
/// <summary> /// Gets the index of the literal array. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralArrayIndex(SqlStatement a, SqlStatement b) { throw new NotImplementedException(); }
/// <summary> /// Gets the literal and. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralAnd(SqlStatement a, SqlStatement b) { return SqlStatement.Format("({0}) AND ({1})", a, b); }
/// <summary> /// Gets the literal coalesce. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralCoalesce(SqlStatement a, SqlStatement b) { return SqlStatement.Format("COALESCE({0}, {1})", a, b); }
/// <summary> /// Gets all of the item request posts /// </summary> /// <returns>a collection of post previews representing all of the item request posts</returns> public IEnumerable <PostPreview> GetAllItemRequestPosts() { SqlStatement statement = new SqlStatement(StoredProcedures.GetAllItemRequestPosts); return(db.ExecuteStoredProc(statement, PostPreviewMapper)); }
/// <summary> /// Gets the literal convert. /// </summary> /// <param name="a">A.</param> /// <param name="newType">The new type.</param> /// <returns></returns> public virtual SqlStatement GetLiteralConvert(SqlStatement a, Type newType) { return a; }
protected virtual SqlStatement ProcessQuery(SqlStatement statement) { return(statement); }
public override int CommandCount(SqlStatement statement) { return(statement.IsInsertWithIdentity() ? 2 : 1); }
public IDbCommand GetDbCommand(SqlStatement sql, IDbConnection conn) { var c = GetDriverCommand(sql, conn); return(c); }
public override SqlStatement Finalize(SqlStatement statement) { CheckAliases(statement, 30); return(base.Finalize(statement)); }
protected override void SetStatementTable(SqlStatement statement, SqlTableRef table) { var delete = (SqlDelete)statement; delete.Delete = table; }
public async Task <int> ExecuteAsync(SqlStatement statement) { return(await _dbConnection.ExecuteAsync(statement.Sql, statement.GetDynamicParameters()) .ConfigureAwait(false)); }
protected override SqlExpression GetStatementWhere(SqlStatement statement) { var delete = (SqlDelete)statement; return(delete.Where); }
public async Task <IEnumerable <object> > QueryAsync(SqlStatement statement) { return(await _dbConnection .QueryAsync(statement.EffectiveType, statement.Sql, statement.GetDynamicParameters()) .ConfigureAwait(false)); }
protected override void SetStatementLimit(SqlStatement statement, SqlExpression limit) { var delete = (SqlDelete)statement; delete.Limit = limit; }
public async Task <object> QueryFirstOrDefaultAsync(SqlStatement statement) { return(await _dbConnection.QueryFirstOrDefaultAsync(statement.EffectiveType, statement.Sql, statement.GetDynamicParameters()).ConfigureAwait(false)); }
/// <summary> /// Joins a list of operands to make a SELECT clause /// </summary> /// <param name="selects"></param> /// <returns></returns> public virtual SqlStatement GetSelectDistinctClause(SqlStatement[] selects) { if (selects.Length == 0) return SqlStatement.Empty; return SqlStatement.Format("SELECT DISTINCT {0}", SqlStatement.Join(", ", selects)); }
protected override void BuildInsertQuery(SqlStatement statement, SqlInsertClause insertClause, bool addAlias) { if (statement is SqlStatementWithQueryBase withQuery && withQuery.With?.Clauses.Count > 0) { BuildInsertQuery2(statement, insertClause, addAlias); }
/// <summary> /// Gets the literal add checked. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralAddChecked(SqlStatement a, SqlStatement b) { return GetLiteralAdd(a, b); }
/// <summary> /// Checks the date the post becomes inactive. /// </summary> public void CheckAllPostsForExpirationDate() { SqlStatement statement = new SqlStatement(StoredProcedures.CheckAllPostsForExpirationDate); db.ExecuteStoredProc(statement); }
/// <summary> /// Gets the literal and also. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralAndAlso(SqlStatement a, SqlStatement b) { return GetLiteralAnd(a, b); }
/// <summary> /// Return exists /// </summary> public SqlStatement Count(SqlStatement sqlStatement) { return(new SqlStatement(this, "SELECT COUNT(*) FROM (").Append(sqlStatement.Build()).Append(") Q0")); }
/// <summary> /// Gets the literal call. /// </summary> /// <param name="a">A.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralCall(SqlStatement a) { throw new NotImplementedException(); }
protected SqlStatement ReplaceSkipWithRowNumber(SqlStatement statement) => ReplaceTakeSkipWithRowNumber(statement, query => query.Select.SkipValue != null, false);
/// <summary> /// Gets the literal conditional. /// </summary> /// <param name="a">A.</param> /// <param name="b">The b.</param> /// <param name="c">The c.</param> /// <returns></returns> protected virtual SqlStatement GetLiteralConditional(SqlStatement a, SqlStatement b, SqlStatement c) { throw new NotImplementedException(); }
protected override SqlStatement GetLiteralCount(SqlStatement a) { return("COUNT(*)"); }
public abstract LastInsertId ExecuteInsert(SqlStatement sql, string idKey);
protected override SqlStatement GetLiteralStringConcat(SqlStatement a, SqlStatement b) { return(SqlStatement.Format("CONCAT({0}, {1})", a, b)); }