Пример #1
0
        /// <summary>
        /// Creates a SQL query to select some columns matching the specified <see cref="QueryConstraint"/> from a table.
        /// </summary>
        /// <param name="tableName">Name of the table to select records for.</param>
        /// <param name="constraint">The <see cref="QueryConstraint"/> to be applied for record selection.
        /// If <paramref name="constraint"/> is <c>null</c>, no constraints are respected.</param>
        /// <param name="columns">The columns to be respected within selection.</param>
        /// <exception cref="InvalidOperationException">Thrown, if this method is 
        /// called for a <see cref="QueryType"/> other than <see cref="QueryType.Select"/>.</exception>
        /// <returns>
        /// The SQL query.
        /// </returns>
        internal string CreateSelectSql(string tableName, QueryConstraint constraint, params string[] columns)
        {
            // Throw exception, if this method is called for wrong query type

            if (type != QueryType.Select)
            {
                throw new InvalidOperationException(string.Format("Can't create select SQL statement for {0}!",
                                                                    Enum.GetName(typeof(QueryType), type)));
            }

            StringBuilder columnBuilder = new StringBuilder();

            // Define table / columns to be respected

            if (columns == null || columns.Length == 0)
            {
                // No columns specified => select all columns

                builder.Append(string.Format(" * from {0}", tableName));
            }
            else
            {
                // Define table and columns to respected

                foreach (var column in columns)
                {
                    columnBuilder.Append(column).Append(Separator);
                }
                builder.Append(columnBuilder.ToString().Trim(Separator));
                builder.AppendLine(string.Format(" from {0} ", tableName));
            }

            // No constraint specified => select all records

            if (constraint != null && !string.IsNullOrEmpty(constraint.Get()))
            {
                builder.Append(string.Format(" where {0}", constraint.Get()));
            }

            return builder.ToString();
        }
Пример #2
0
        /// <summary>
        /// Creates a SQL query to update a single record.
        /// </summary>
        /// <param name="tableName">Name of the table to update.</param>
        /// <param name="updateColumnValues">The column values to be updated.</param>
        /// <param name="constraint">The <see cref="QueryConstraint"/> defining update constraints.</param>
        /// <exception cref="InvalidOperationException">Thrown, if this method is 
        /// called for a <see cref="QueryType"/> other than <see cref="QueryType.Update"/>.</exception>
        /// <returns>The SQL query.</returns>
        internal string CreateUpdateSql(string tableName, IEnumerable<Pair<string, object>> updateColumnValues, QueryConstraint constraint)
        {
            // Throw exception, if this method is called for wrong query type

            if (type != QueryType.Update)
            {
                throw new InvalidOperationException(string.Format("Can't create update SQL statement for {0}!",
                                                                    Enum.GetName(typeof(QueryType), type)));
            }

            // Define table

            builder.AppendLine(tableName);
            builder.Append(" set ");

            StringBuilder updateValueBuilder = new StringBuilder();

            // Define column values to be updated

            foreach (var updateColumnValue in updateColumnValues)
            {
                string columnName = updateColumnValue.Left;
                object value = updateColumnValue.Right;
                bool isTimeStamp = columnName.ToLower() == "timestamp";

                // Special formatting to be applied for timestamp

                string formattedValue = (isTimeStamp) ? FormatValue(value) : FormatValue(value, true);

                updateValueBuilder.Append(string.Format("{0} = {1}{2}",
                                                        updateColumnValue.Left,
                                                        formattedValue,
                                                        Separator)
                                            );
            }
            builder.AppendLine(updateValueBuilder.ToString().Trim(Separator));

            // Get the constraints, append them to where clause and return

            return builder.Append(string.Format(" where {0}", constraint.Get())).ToString();
        }
Пример #3
0
        /// <summary>
        /// Creates an SQL query to delete records from specified table matching the
        /// specified <see cref="QueryConstraint"/>.
        /// </summary>
        /// <param name="tableName">Name of the table to delete records for..</param>
        /// <param name="constraint">The <see cref="QueryConstraint"/> providing constraints.</param>
        /// <exception cref="InvalidOperationException">Thrown, if this method is 
        /// called for a <see cref="QueryType"/> other than <see cref="QueryType.Delete"/>.</exception>
        /// <returns>The SQL query.</returns>
        internal string CreateDeleteSql(string tableName, QueryConstraint constraint)
        {
            // Throw exception, if this method is called for wrong query type

            if (type != QueryType.Delete)
            {
                throw new InvalidOperationException(string.Format("Can't create delete SQL statement for {0}!",
                                                                    Enum.GetName(typeof(QueryType), type)));
            }

            if (constraint == null)
            {
                // Defining no constraints tries to delete all records => DELETE FROM [TABLE]

                return builder.Append(tableName).ToString();
            }

            // Append constraints to where clause and return

            return builder.Append(string.Format(" {0} where {1}", tableName, constraint.Get())).ToString();
        }