Enter your search terms Submit search form

SQLBuilder

For example, instead of writing, as most programmers do (in VB .NET)

Dim strQuery As String = "SELECT tblUsers.FirstName, tblUsers.LastName FROM" & _
                         "tblUsers WHERE tblUsers.City = '" & strCity.ToString() & "'"
Dim cmd As IDbCommand = Connection.CreateCommand()
cmd.CommandText = strQuery
dim reader As IDataReader = cmd.ExecuteReader()
'...

Dim query As New Query(tblUsers)
query.AddFields(tblUsers.FirstName, tblUsers.LastName)
query.AddCondition(tblUsers.City, Expr.OP_EQUAL, strCity)
dim reader as IDataReader = query.ExecuteReader(Connection)
'...

In contrast to other solutions which map the content of a database to a structure, and generates only simple SQL statement, letting the hard computation to the program, SQLBuilder allows you to build SQL statement of any complexity, including UPDATE, DELETE and INSERT statements.

Features

• You do not need to worry about SQL syntax. SQLBuilder generates the correct SQL statement for you
• You do need to worry about formatting constants; SQLBuilder do it for you. No headache with date/time formats and no risk of SQL injection. (SQLBuilder in fact makes use of SQL parameters whenever possible for highest security)
• Tables and fields are represented by regular objects of the programming language. If a table or a field is renamed the compiler will show you all the references to update
• You do not need to worry about differences between various database providers. SQLBuilder automatically adapts the query for SQL Server, Access, Oracle, MySQL or even FileMaker PRO
• SQLBuilder is able to check many semantic errors and to report clear error messages before the command is passed to the database
• Support for SQL SELECT, INSERT, UPDATE and DELETE statements and the following constructs:
  • WHERE conditions with any expression
  • INNER, LEFT, RIGHT and FULL JOIN with any join condition
  • GROUP BY, HAVING and ORDER BY query constructs
  • Query operators: UNION, UNION ALL, INTERSECT, MINUS
  • Selection of fields as well as any expressions and the * wildcard
  • Use of subqueries as expression (with SELECT or WHERE), as value list (with IN, EXISTS, ANY, ALL or INSERT) and as views (with FROM or JOIN)
  • Use of alias for fields, expressions, tables and subqueries
  • Use of SELECT DISTINCT, and queries with limited number of returned rows (Oracle's RowNum, SQL Server's TOP)
• Support and automatic type checking of expressions of any complexity, using any combination of:
  • Fields and constants of numeric, string, boolean, date/time, or binary type
  • Operators: AND, OR, NOT, +, -, *, /, =, <>, <, >, <=, >=, ||, LIKE, etc
  • Functions: Mod, Cast, DatePart, Min, Max, Count, Avg, Lower, IsNull etc and user-defined functions
  • Set predicates: IN, ANY, ALL, EXISTS
  • Other constructs: CASE WHEN, IS NULL, NULL
• Expressions that are not recognized by a target database are automatically replaced by an equivalent expression
• SQLBuilder offers you constructs that are common to all databases as well as a lot of vendor-specific and SQLBuilder-specific extensions. But it automatically converts constructs that are not supported by a target database to an equivalent, supported construct:
  • Conversion of INNER, LEFT, RIGHT and FULL JOIN's to the old Oracle8's syntax (putting the join condition in the WHERE clause)
  • Conversion of JOIN combinations that are not supported by some databases
  • Conversion of SQL Server's multi-table UPDATE and DELETE statements into equivalent statements for database that do not support them
  • Convertion of real Boolean fields (Access) in expressions
  • Conversion of FULL JOIN, RIGHT JOIN or INNER JOIN to LEFT JOIN or INNER JOIN
  • Conversion of BD2's multi-row insert (and more) for other database
  • Conversion of Oracle's multi-columns IN and SET expressions for other database
  • Conversion of some set predicates

---