/// <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();
        }
        /// <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();
        }
Example #3
0
 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");
 }
        /// <summary>
        /// Builds a delete clause
        /// </summary>
        /// <param name="table"></param>
        /// <param name="inputPKColumns">PK columns for reference</param>
        /// <param name="inputPKValues">PK values for reference</param>
        /// <returns></returns>
        public SqlStatement GetDelete(SqlStatement table, IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
        {
            if (inputPKColumns.Count == 0)
                return SqlStatement.Empty;

            var deleteBuilder = new SqlStatementBuilder("DELETE FROM ");
            deleteBuilder.Append(table);
            deleteBuilder.Append(" WHERE ");
            bool valueSet = false;
            for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
            {
                if (valueSet)
                    deleteBuilder.Append(" AND ");
                deleteBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
                valueSet = true;
            }
            return deleteBuilder.ToSqlStatement();
        }
        /// <summary>
        /// Builds an update clause
        /// </summary>
        /// <param name="table"></param>
        /// <param name="inputColumns">Columns to be inserted</param>
        /// <param name="inputValues">Values to be inserted into columns</param>
        /// <param name="outputParameters">Expected output parameters</param>
        /// <param name="outputExpressions">Expressions (to help generate output parameters)</param>
        /// <param name="inputPKColumns">PK columns for reference</param>
        /// <param name="inputPKValues">PK values for reference</param>
        /// <returns></returns>
        public SqlStatement GetUpdate(SqlStatement table, IList<SqlStatement> inputColumns,
            IList<SqlStatement> inputValues,
            IList<SqlStatement> outputParameters, IList<SqlStatement> outputExpressions,
            IList<SqlStatement> inputPKColumns, IList<SqlStatement> inputPKValues)
        {
            if (inputColumns.Count == 0)
                return SqlStatement.Empty;

            var updateBuilder = new SqlStatementBuilder("UPDATE ");
            updateBuilder.Append(table);
            updateBuilder.Append(" SET ");
            bool valueSet = false;
            for (IEnumerator<SqlStatement> column = inputColumns.GetEnumerator(), value = inputValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
            {
                if (valueSet)
                    updateBuilder.Append(", ");
                updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
                valueSet = true;
            }
            updateBuilder.Append(" WHERE ");
            valueSet = false;
            for (IEnumerator<SqlStatement> column = inputPKColumns.GetEnumerator(), value = inputPKValues.GetEnumerator(); column.MoveNext() && value.MoveNext(); )
            {
                if (valueSet)
                    updateBuilder.Append(" AND ");
                updateBuilder.AppendFormat("{0} = {1}", column.Current, value.Current);
                valueSet = true;
            }
            return updateBuilder.ToSqlStatement();
        }
 public override SqlStatement GetLiteralLimit(SqlStatement select, SqlStatement limit)
 {
     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 TOP ({0}) {1}", limit, selectBuilder.ToSqlStatement());
     }
     throw new ArgumentException("S0051: Unknown select format");
 }