Project Description
A Database agnostic SQL query builder and Data Access Layer for .NET.
Build safe SQL Statements using familiar syntax and execute on any database with minimal effort.
Written in C# using VS 2008

Now released under LGPL licence, so you can use it in your commercial software
New version includes support for database object creation and running queries against Oracle client

Features

  • Significantly reduce the boiler plate code that needs to be written when accessing a database.
  • Enable the creation of complex, dynamic SQL statements without introducing SQL injection holes.
  • Define a query syntax as close to standard SQL as possible.
  • Full CRUD and Stored Procedure support.
  • (almost) completely database agnostic
  • Database Profiling for analysis of speed and frequency
  • No limitations of an ORM framework
  • Fully qualified objects allow dynamic generation of any command
  • Never have to dispose of connections, commands or data readers again.

The code supports SQL generation for MySQL, MS SQL (inc Express), SQLLite, and MS Access via OLE Db, and now Oracle.

Quick SQL Sample

The following example code is all that is needed to load data from a database...


            DBDatabase db = DBDatabase.Create("Northwind");

            DBParam param1 = DBParam.ParamWithValue(1);

            DBQuery sel = DBQuery.SelectFields("CustomerID", "CustomerName")
                                 .From("CUSTOMERS")
                                 .WhereFieldEquals("CustomerID", param1);

            //perform all the database execution here.
            string name = null;

            db.ExecuteRead(sel, reader =>
            {
                if (reader.Read())
                    name = reader.GetString(1);
            });

There are more quick walk throughs for creating and executing queries in the documentation section:
http://dynasql.codeplex.com/documentation

And many more unit test examples in the source code.

Effect (Defect) Proximity

By supporting the declaration, execution and consumption in the same method it is much simpler to understand the effects of a change.
Small methods without interfering code for disposing, error handling, and other boiler plate code

Memory Management

Internally all connections are centralized and controlled. Connections, Commands, DataReaders will be disposed correctly.
If a method returns a disposable instance it is still the responsibility of the calling code to dispose of it of course - so transactions etc. can be supported.

Execution Profiling

Want to know what gets executed on the database? Need to measure/confirm the performance of particular statements?
Because all code runs through a central point - database profiling is supported.
These can either be defined and enabled in the configuration file, or explicitly set in the code.

Schema Extraction

The DBDatabase also supports database schema extraction and analysis via the DBSchemaProvider.
Now you can check your tables, view, sprocs and other database objects at runtime.

Exception Handling

We can't handle the errors for you, but we do our best to help. The central DBDatabase instance will raise an event to any registered handler so you can keep your code separated. It will also allow you to sink the exception, or provide a different (safer) error message.
It's not an excuse to exclude Try Catch blocks, but it does stop the need to clean up readers, commands, connections etc.

Why not LINQ

I know LINQ and it's very very clever, but sometimes the data I need back does not fit in a class. Or I want to use a generic class for different sets of data. As with all ORM tools 80-90% covered but a pain for the last 10%.
Because the implementation of reading data is in the code you write there are no limitations.

Known Limitations

  • All the statement construction is performed at runtime rather than compile time which is not ideal, but because we are always dealing with separated systems even compile time checking is not guaranteed to work at runtime.
  • Serialization to XML is supported for storage, transfer or any other purpose but any parameters with delegate providers will only be called on serialization- actual values will be deserialized.

Last edited Aug 15, 2012 at 7:40 PM by perceiveit, version 20