What is Sql.Net

SQL Abstraction Layer

Sql.Net is a software component which allows you programmatically create SQL queries in runtime using a convenient .Net Object Model, thus creating an abstraction layer over SQL. Dynamic SQL generation is useful in several scenarios:

• Generate SQL dynamically when query structure is not known at development time (i.e. user defined reports or filters)
• Generate SQL dynamically when database structure is not known at development time (i.e. user defined tables or fields)
• Database independent pagination support
• Create a database independent data layer
• Create software analogs of views and stored procedures which require no adjustments when a database changes. Software views and stored procedures can even be used on databases which do not support such features directly (i.e. MySql)

Dynamically generated SQL vs. static SQL or stored procedures

By static SQL we mean SQL code written once in the development phase when database and query structures are known. Static SQL is usually targeted at a specific database and in many cases gets stored in stored procedures. Many applications (especially Enterprise Applications) reach a stage where some dynamic data manipulation is required and static SQL techniques no longer suffice. Common examples are custom reports and filters designed by an application user. In order to support such functionality, an application must exhibit UI to define the report or filter, UI to display the results and an engine which is able to render SQL SELECT statement from the report definition. Here Sql.Net steps in to automate the process of SQL generation in a convenient, time saving, database independent way.

Dynamic SQL generation is also needed in applications where the databases structure itself is dynamic. Many Enterprise Applications allow users to customize the way data is stored and displayed. This can involve columns being added or deleted or whole database tables dropped and created. Static SQL can not be written to work with dynamic structure because identifier names can not be substituted with variables. Therefore, SQL statements which manipulate or fetch data from non static storages must be created and rendered programmatically in runtime. Using Sql.Net for that purpose relieves your organization of the necessity to hand-code SQL renderers.

Pagination (also know as paging) is another common issue which can be easily and elegantly solved using Sql.Net. The problem with pagination is that every database has its own level of support (or the lack of thereof) for fetching pages of data. For instance, SQL Server 2000 has a very limited TOP clause, MySql has a LIMIT clause, Oracle has a ROWNUM pseudo column and SQL Server 2005 has a row_number() function. Sql Server 2000 and Oracle are the most problematic since they have no built in support for pagination, meaning dynamically generated SQL is a must. Applications using other databases can also benefit from Sql.Net structured approach to generate pagination SQL. If database independence is a requirement for your application, Sql.Net can reduce incompatibility problems concerning pagination significantly.

Supported Databases

• SQL Server
• Oracle
• MySql