Dynamic SQL for NBusiness

I have been working on adding the ability to do dynamic SQL for NBusiness business objects. The point of this basically to allow a developer to quickly add new Fetch factories. Currently if you want to add a new fetch factory you need to do a few steps.

·         Create the factory method

·         Create the Criteria class

·         Add a handler to the Fetching event

·         Add a case to the fetching handler for your criteria object

·         Then call the Database Provider

·         Create the necessary stored procedure

Since needing to create new Fetch factories is a pretty common task I wanted to add a way to make it easier. NBusiness will generate all of the most common Factories and stored procedures for you (such as factories for fetching all entities or fetching by id’s or relationships) but if you want to be able to fetch by some other column type you’re stuck with creating your custom factories. For example if you wanted to search for all Customers with the first name of “justin” you’d need to create a FetchByFirstName(string name) factory.

So now rather than going through all of the steps above you can simple create your Fetch factory using dynamic SQL and pass that into the Database Provider instead. There is an object model for creating this dynamic SQL and NBusiness default templates will generate for you all of the Table and Column objects corresponding to your entities.

So for example if you have a Customer entity and you want to fetch by first name you might create a factory method that looks like this:

public static TopicCollection FetchByFirstName(string name)

{

    IQuery query = Topic.Queryable.Table.Where(

        Topic.Queryable.Name == “@name”);

       

    return new TopicCollection(

        query,

        new Parameter(“@name”, name));

}

NOTE: exact syntax may change; this is just an example of how I have it now.

Also, when we upgrade to Visual Studio 2008 these objects will be LINQ queryable. Meaning if you query your entities within a LINQ statement it will generate dynamic SQL for you to fetch the data for your entities (yes, like DLINQ).

Different Database providers will be able to implement their own Query classes so that SQL can be generated for different types of databases. For now I will just implement it for SqlServer 2005, in fact I’m probably going to push back the release of the MySql provider stuff just because as far as I know there isn’t really a demand for it at this point and it’s more work than it’s worth given that fact.