Generics Examples - Passing paramaters to Stored Procedures

In this post begins a series of examples of application of Generics that were useful to me throughout several developments. In this delivery a generic method that receives an instance of some entity of our domain and a DbCommand, and based on the SqlParameterAttributes attributes “reflected” from the entity maps the value of the properties with the parameters of stored procedure of the received DbCommand. Using this only method and “decorating” with the SqlParameterAttribute attributes the suitable properties of our classes it is possible to simplify the stored procedures calls.

The Method

public static void MatchStoreProceduresParams<T>(T entity, DbCommand command)
{
    Type type = entity.GetType();
    foreach (PropertyInfo entityProperty in type.GetProperties())
    {
        SqlParameterAttribute[] propSqlAttributeArray = (SqlParameterAttribute[])entityProperty.GetCustomAttributes(typeof(SqlParameterAttribute), false);
        if (propSqlAttributeArray.Length > 0)
        {
            SqlParameterAttribute propSqlAttribute = propSqlAttributeArray[0];
            if (propSqlAttribute != null)
            {
                SqlParameter sqlParam = new SqlParameter();
                sqlParam.ParameterName = propSqlAttribute.Name;
                sqlParam.SqlDbType = propSqlAttribute.SqlDbType;
                sqlParam.Value = entityProperty.GetValue(entity, null);
                if (propSqlAttribute.IsDirectionDefined)
                {
                    sqlParam.Direction = propSqlAttribute.Direction;    
                }
                command.Parameters.Add(sqlParam);
            }
        }

    }
}

Using the method

public static int CustomerSave(CustomerInfo customer)
{
    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbCommand = db.GetStoredProcCommand("CustomerUpdate");
    Utils.MatchStoreProceduresParams(customer, dbCommand);
    return db.ExecuteNonQuery(dbCommand);
}

Decorando la clase

public class CustomerInfo
{
    int _id;
    string _name;
    int _age;
    decimal _balance;

    public CustomerInfo()
    {

    }
    
    [SqlParameter("@CustomerId", System.Data.SqlDbType.Int)]
    public int Id
    {
        get { return _id; }
        set { _id = value; }
    }
    
    [SqlParameter("@FirstName", System.Data.SqlDbType.Char)]
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }

    [SqlParameter("@Age", System.Data.SqlDbType.Int)]
    public int Age
    {
        get { return _age; }
        set { _age = value; }
    }

    [SqlParameter("@Balance", System.Data.SqlDbType.Decimal)]
    public decimal Balance
    {
        get { return _balance; }
        set { _balance = value; }
    }
}

Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables example

The telerik radgrid is one of the most complete grid control in the market. Have a cool set of features and covers the most commonly scenaries.
A common UI requirement is show Hierarchycal data, this data can be loaded in several ways with or without postbacks changing the property "HierarchyLoadMode". In case that the set of data to show is not great (use your criteria), you can choose load all data in the client obtaining a more responsive UI. To minimize roundtrips to database a good choice is code an stored procedure that returns multiple results and obtain a DataSet with multiple tables.
I make an example based on the AdventureWorks db to show the Departments and their respective employees.

The Stored Procedure:
This SP gets the data from Departments and Employees returning two results that share one column (DepartmentId) to make the parent/child relation.

CREATE PROCEDURE [HumanResources].[Department_Get_DepartmentAndEmployees]
AS

/* Get the departments */
SELECT HRD.DepartmentID, HRD.Name as DepartmentName
FROM HumanResournces.Department HRD
ORDER BY DepartmentName

/* Get the employees */
SELECT PC.FirstName, 
       PC.LastName,
       HRE.Title,
       HRDep.DepartmentId
FROM
HumanResources.Employee HRE
INNER JOIN Person.Contact PC ON PC.ContactId = HRE.ContactId
INNER JOIN HumanResources.EmployeeDepartmentHistory HRDep ON HRDep.EmployeeId = HRE.EmployeeId

The .aspx Code:
Note the ParentTableRelation tag uses the DepartmentId field to make the relation from the Employees table to the Department table.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables</title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="Scriptmanager1" runat="server">
    </asp:ScriptManager>
    <telerik:RadGrid ID="gvEmployeesByDepartment" runat="server" Width="95%" AutoGenerateColumns="False"
        GridLines="None" ShowHeader="True" Skin="Web20">
        <MasterTableView DataMember="Departments" DataKeyNames="DepartmentId" Width="100%"
            Name="Customers" HierarchyLoadMode="Client">
            <DetailTables>
                <telerik:GridTableView DataMember="Employees" DataKeyNames="DepartmentId" Width="100%"
                    runat="server" ShowHeader="true">
                    <ParentTableRelation>
                        <telerik:GridRelationFields DetailKeyField="DepartmentId" MasterKeyField="DepartmentId" />
                    </ParentTableRelation>
                    <RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                        Visible="False">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </RowIndicatorColumn>
                    <ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                        Visible="False" Resizable="False">
                        <HeaderStyle Width="20px"></HeaderStyle>
                    </ExpandCollapseColumn>
                    <Columns>
                        <telerik:GridBoundColumn DataField="Title" UniqueName="Title" ReadOnly="true" HeaderText="Title">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="FirstName" UniqueName="FirstName" ReadOnly="true"
                            HeaderText="First Name">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="LastName" UniqueName="LastName" ReadOnly="true"
                            HeaderText="Last Name">
                        </telerik:GridBoundColumn>
                    </Columns>
                </telerik:GridTableView>
            </DetailTables>
            <RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                Visible="False">
                <HeaderStyle Width="20px"></HeaderStyle>
            </RowIndicatorColumn>
            <ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
                Resizable="False">
                <HeaderStyle Width="20px"></HeaderStyle>
            </ExpandCollapseColumn>
            <Columns>
                <telerik:GridBoundColumn DataField="DepartmentName" UniqueName="DepartmentName" HeaderText="Department">
                </telerik:GridBoundColumn>
            </Columns>
        </MasterTableView>
    </telerik:RadGrid>
    </form>
</body>
</html>

The .cs Code:

using System;
using System.Data;
using System.Web.UI;
using System.Data.SqlClient;

public partial class _Default :System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
            BindDepartmentsAndEmployees();
    }

    private void BindDepartmentsAndEmployees()
    {
        SqlConnection conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;User ID=marketpoint;Initial Catalog=AdventureWorks;Data Source=(local)\sqlexpress");
        SqlCommand cmd = new SqlCommand("[HumanResources].[Department_Get_DepartmentAndEmployees]", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet dsDepartmentAndEmployees = new DataSet();
        da.Fill(dsDepartmentAndEmployees);
        if(da != null)
        {
            // Renames Dataset table to get more clear names in grid declarative properties
            dsDepartmentAndEmployees.Tables[0].TableName = "Departments";
            dsDepartmentAndEmployees.Tables[1].TableName = "Employees";
            gvEmployeesByDepartment.DataSource = dsDepartmentAndEmployees;
            gvEmployeesByDepartment.DataBind();
        }
    }
}

The UI Result (Click to Enlarge):

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()))