Basic Queries With Nvigorate.Data.Query

RSS

Modified: Thursday, 11 June 2009 02:59 PM by rsimmons - Categorized as: Feature Guide

» Basic Queries With Nvigorate.Data.Query

Table of Contents [Hide/Show]

Intro
   Translation
Select
   Single Table
   Multi-Table
   Using Functions
   Using Sub-queries
   Paging Results
Insert
Update
Delete
Miscellaneous
   Table Variables

Edit

Intro

The Nvigorate.Data.Query namespace is a domain model which represents the object-oriented construction of a t-sql query or command. This namespace is an area of the framework that you should expect a lot of future growth in as the end goal is to support the majority of common t-sql tasks and operations. The over-all approach was to use composition patterns in conjunction with a fluent interface approach so that the result should be a relatively readable way to create queries which can be sent across remoting boundaries and are more than a string containing the t-sql code.

The typing in this model is loose with every object ultimately inheriting from IQueryObject. While this means that there isn't currently (nor will there likely ever be) a lot of validation in terms of what constitutes a valid query structure, consumers of this model are expected to understand the t-sql they're attempting to produce.

The resulting query should be parametrized using the Parametrizer visitor class which is a one-implementation-fits-all-databases parametrizing visitor which will create a place-holder parameter for every Literal value in the query structure provided the Parametrizer has been correctly adapted to handle new IQueryObject constructs as they're added to the namespace.

Once the query is parametrized, it can then be passed through a translating visitor which turns the structure into t-sql targeting the specific database in question. If Nvigorate has a specific IDatabaseProvider and IQueryVisitor for the target database, you can simple pass the structure directly on to DataInterface and it will correctly handle the parametrization and translation for you.

Let's dive in...

Important
For now, there is currently only MS SQL Server 2005 support through the MSSQL9Provider and MSSQL9Visitor classes. Nvigorate was designed with the intention that adding support for additional databases would be possible and, for the most part, painless. Implementing an IDatabaseProvider should be trivial, implementing a fully functional IQueryVisitor will require a rock-solid understanding of the target database you're working with.

Edit

Translation

In order to parameterize and/or translate the query structure into actual SQL, you can use the extension methods provided:


// assuming the query variable implements IQueryObject you can...
var provider = new MSSQL9Provider();
var repositoryMapIndex = new MapIndex().GetRepositoryMapIndex("repositoryName");
var parameters = query.Parameterize(provider, repositoryMapIndex);
var sql = query.Translate(provider);

Notice that I had to have an instance of IDatabaseProvider and an IRepositoryMapIndex in order to parameterize the sql. I'm not going to comment on the map index for now, but the IDatabaseProvider instance is required because that essentially tells the visitors used how to translate the IQueryObject object graph into SQL.

Edit

Select

Select is represented by the Select class and you can create a select query by either using the constructor or helper method.


//constructor route
var select = new Select(
     Table.From("tableName"), 
     Select.Column("Column1") + Select.Column("Column2")
);

//helper method route
var select = Select.Query(
     Table.From("tableName"), 
     Select.Column("Column1") + Select.Column("Column2")
);