Friday, April 4, 2008

NetTiers: Expose custom stored procedures as componentlayer methods

Sometimes is needed a custom functionality that goes far that the CRUD functionality generated by NetTiers. To acomplish this task we can use a custom stored procedure to get the data and expose it as a method of the componentlayer (services layer).
Follow the next steps to expose a custom stored procedure:

1) Configure Codesmith properties
CustomProceduresStartsWith (example: {1}{0}_)
ProcedurePrefix (example: cust_sp_ )

2) Add a Stored Procedure
Add a SP with a name that follows the convention defined in CustomProceduresStartsWith where {1} is the placeholder for
ProcedurePrefix and {0} is the placeholder for the TableName, for example cust_sp_Contact_GetBySalesOrderCountBetween (using AdventureWorks db)

CREATE PROCEDURE [dbo].[cust_sp_Contact_GetBySalesOrderCountBetween]

@min int,
@max int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM Person.Contact C
WHERE
(SELECT COUNT(*) FROM Sales.SalesOrderHeader SOH
WHERE SOH.ContactId = C.ContactId) BETWEEN @min and @max
END

3) Run the template

4) Use the method
Now in the componentlayer for the entity we have our custom stored procedure exposed as a method

ContactService contactService = new ContactService();
gvContacts.DataSource = contactService.GetBySalesOrderCountBetween(25, 30);
gvContacts.DataBind();


Note: the return type of the custom stored procedure method can be a TList or the defined in the CustomNonMatchingReturnType property (DataSet/IDataReader).
To determine what type will be return a method compares every column order and type returned from the Sp with every column order and type of the Entity table.
Debugging a bit the template, I have reviewed the method that compares the stored procedure column type with the table column type. This method returns false if some column type returned has no equivalent (File: \TemplateLib\CommonSqlCode.cs Method: IsMatching)

if (!SqlTypesAreEquivalent(command.CommandResults[0].Columns[i].NativeType, table.Columns[i].NativeType))
return false;


If you have UDT (User-Defined Data Types) fields in your table this method will be return false and CustomNonMatchingReturnType will be setted as Method Return type.

Usually is preferible that have an TList as method return than a Dataset or a IDataReader, because in this way you have all the NetTiers functionality to manipulate the data (deepload the TList returned for example). To force return a TList when have
UDT fields you can edit the file \TemplateLib\CommonSqlCode.cs and change in the method IsMatching the above line for this:


if (!SqlTypesAreEquivalent(command.CommandResults[0].Columns[i].DataType.ToString(), table.Columns[i].DataType.ToString()))

6 comments:

Soblar said...

Hello Martin,
Do you have an idea whether this solution would affect other parts of the Nettiers framework?
Isn't it done in that way for a reason??

Thanks :)

Martín Olivares said...

In the community forums (http://community.codesmithtools.com/forums/p/7529/28405.aspx) someone asked the same, so I go to response the same ;)

"I did the modification of the template to use in a project and not have problems. I'm not 100% sure that a particular scenary won't work, but I suppose that this change not be problematic because Nettiers Component Layer mehods are based in stored procedures and returns TList<entity>.
I think the DataType comparison that I suggest is more accurate because all UDTs are based in a a Sql Data Type and when are queried returns this Sql Data Type."

Soblar said...

Thanks Martin :) I got rid of the UDTs for now. Maybe we will try it in the next project!

I don't really understand what the comparison is doing actually. How wil nettiers determine the original data type used behind the used data type anyways?

Martín Olivares said...

When NetTiers build the entities use the GetCSType method to determine the type of each field. This method don't use the NativeType property, but when build the custom stored procedures compares each table field with each sp field, using the NativeType. In the case of the field defined as UDT type the table schema returns a UDT as NativeType and the command schema ever returns Sql Data Types as NativeType. Below is a concrete sample of this:

Database: AdventureWorks
Table: Person.Contact
Field: FirstName (UDT type: Name)
Table Schema NativeType: Name
Command Schema NativeType: nvarchar

I hope that this help you.

Anonymous said...

Hello Martin. I made the changes and successfully got the method exposed, unfortunately inspite of replacing the code @ IsMatching function on CommonSqlCode is still get the DataSet as a return value. Any suggestions on where to debug?

Martín Olivares said...

Hello dnovelo, in your SP are you returning all the fields in the same order as the table? This is necesary to match the entity type that you wants add the custom SP.