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.

Mapping Sql Types to .NET Types

I bet there is an article out there somewhere that tells you exactly what .NET Types to use with the corresponding SQL column types but I couldn’t find it anywhere. After scanning for a few minutes I decided to just write something up that tells me definitively what Type to use.
In this case I needed it because I had a bug in NBusiness whith the mapping to the double. After changing the Type a few times I couldn’t figure out what Type to actually map to a double. So I created a table that has one column of each type and I used a simple SqlDataReader to figure out the .NET Type of the field.
I created a simple type mapper just to show me what I wanted. And here are the results:
Sql Type .NET Type
bigint long
binary byte[]
bit bool
char string
datetime DateTime
decimal decimal
float double
int int
money decimal
nchar string
ntext string
nvarchar string
real single
smalldatetime DateTime
smallint short
smallmoney decimal
sql_variant object
text string
tinestamp byte[]
tinyint byte
uniqueidentifier Guid
varbinary byte[]
xml string