Wednesday, October 29, 2008

Codesmith Method Picker Property

I just published my first project on Codeplex. The project is a Codesmith add-in, below is the project Url, description and a screenshot. Hope that the Codesmith users find this helpful.

Project Description
This project is a custom property for Codesmith that allows select methods from assemblies.
You can choose a set of methods and use this list to any purpose as generate a webservice to expose them, make method tests or a javascript method call wrapper.

Project Url
Codesmith Method Picker Property

Sunday, June 29, 2008

jQuery Carousel Example

A cool way to show a items gallery is using a scrollable div as Amazon displays related books for the current selected book. This technique is known as "carousel". I made an example (Download source (85Kb)) that shows a list of motherboards using a jQuery Carousel Component populated dinamically via Ajax queries (useful when haves a large items quantity to show)

Server side pagination method
The request/response to the method that returns a "page" of the carousel are maded using JSON.
For simplicity the pagination is done against a generic collection, but easily can be a database query.


[ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public static List<Motherboard> GetItems(int pageIndex, int pageSize)


List<Motherboard> motherboards = new List<Motherboard>


new Motherboard("Asus P5Q Deluxe", "Asus P5Q Deluxe Motherboard", "Intel Socket T LGA775, P45 Express, ATX, 16GB DDR2, 1600MHz FSB"),

new Motherboard("GigaByte GA-P35-DS3L", "GigaByte GA-P35-DS3L Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR, 1333MHz FSB "),

new Motherboard("Intel D5400XS", "Intel D5400XS Motherboard", "Intel Socket J LGA771, P35 Express, Extended ATX, 8GB DDR2, 1600MHz FSB "),

new Motherboard("Asus M3N-HT Deluxe Mempipe", "Asus M3N-HT Deluxe Mempipe Motherboard", "Socket AM2+, nVIDIA nForce 780a, ATX, 8GB DDR2, 2600MHz FSB"),

new Motherboard("ABIT IP35 PRO XE", "ABIT IP35 PRO XE Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1600MHz FSB"),

new Motherboard("Asus Striker Extreme", "Asus Striker Extreme Motherboard", "Quad-Core/Core 2 Duo Extreme/Core 2 Duo/Pentium D/Celeron D, Socket T"),

new Motherboard("XFX MB-N780-ISH9", "XFX MB-N780-ISH9 Motherboard", "Intel Socket T LGA775, nForce 780i SLI, ATX, 8GB DDR2, 1333MHz FSB "),

new Motherboard("HP Asus P5BW-LA", "HP Asus P5BW-LA Motherboard", "Intel Socket T LGA775, P965 Express, Micro ATX, 8GB DDR2, 1066MHz FSB"),

new Motherboard("Biostar P4M900-M4", "Biostar P4M900-M4 Motherboard", "Core 2 Quad/Pentium D/Pentium 4/Celeron D, Socket T SLI, ATX, 8GB DDR2, 1333MHz Bus"),

new Motherboard("MSI P6N Diamond", "MSI P6N Diamond Motherboard", "Intel Socket T LGA775, nForce 680i SLI, ATX, 8GB DDR2, 1333MHz FS"),

new Motherboard("Intel D915GAG", "Intel D915GAG Motherboard", "Pentium 4/Celeron D, Socket T, 915G, mATX, 4GB DDR, 800MHz FSB"),

new Motherboard("ABIT IP35-E", "ABIT IP35-E Motherboard", "Intel Socket T LGA775, P35 Express, ATX, 8GB DDR2, 1333MHz FSB "),


return motherboards.GetRange((pageIndex - 1) * pageSize, pageSize);


Client side pagination method


function mycarousel_makeRequest(carousel, first, last, per_page, page)


// Lock carousel until request has been made


// Call the server side method with pageIndex and pageSize parameters


type: "POST",

url: "Default.aspx/GetItems",

data: "{'pageIndex':'" + page + "', 'pageSize':'" + per_page + "'}",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function(data) {

mycarousel_itemAddCallback(carousel, first, last, data.d, page);




function mycarousel_itemAddCallback(carousel, first, last, data, page)


// Unlock


// Set size


var per_page = carousel.last - carousel.first + 1;

for (var i = first; i <= last; i++)


var pos = i - 1;

var idx = Math.round(((pos / per_page) - Math.floor(pos / per_page)) * per_page);

carousel.add(i, mycarousel_getItemHTML(data[idx]));




* Buil item html


function mycarousel_getItemHTML(mother)


var item = "<div>";

item += "<img src='images/" + mother.Image + ".jpg' alt='" + mother.Title + "'>";

item += "</div>";

item += "<div class='Title'>";

item += mother.Title;

item += "</div>";

item += "<div class='Description'>";

item += mother.Description;

item += "</div>";

return item;



Download source: jQuery Carousel in (85Kb)

Monday, May 5, 2008

Generics Examples - Generating instances with two methods or how to simplify the DAL

In this delivery two methods to generate instances or collections of our entities, simplifying therefore the code of our DAL (traditionally composed by a great amount of methods and mappings “field to field” from tables to objects). Once collected the data using a DataReader, with these methods is possible to obtain a single entity instance or a strong typed collection of instances.

The Methods

// A single instance
public static void FillEntity<T>(T instance, IDataRecord datarecord)
Type instanceType = instance.GetType();
for (int i = 0; i < datarecord.FieldCount; i++)
if (datarecord[i] != DBNull.Value)
string propName = datarecord.GetName(i);
PropertyInfo propInfo = instanceType.GetProperty(propName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
if (propInfo != null)
propInfo.SetValue(instance, datarecord[propInfo.Name], null);

// A instance collection
public static List<T> FillEntities<T>(IDataReader dr)
List<T> entities = new List<T>();
while (dr.Read())
T instance = Activator.CreateInstance<T>();
Utils.FillEntity<T>(instance, dr);
return entities;

Using the methods

// A Single Instance
public static UserInfo UserGetById(string userid)
UserInfo userInfo = new UserInfo();
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("usergetbyid");
db.AddInParameter(dbCommand, "userid", DbType.String, userid);
IDataReader idr = db.ExecuteReader(dbCommand);
if (idr.Read())
Utils.FillEntity<UserInfo>(userInfo, idr);
return userInfo;

// A instance collection
public static List<OrderInfo> GetOrders(int customerid)
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand("getorders");
db.AddInParameter(dbCommand, "customerid", DbType.Int32, customerid);
return Utils.FillEntities<OrderInfo>(db.ExecuteReader(dbCommand));

Friday, April 25, 2008

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;


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; }

Sunday, April 20, 2008

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]

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

/* Get the employees */
SELECT PC.FirstName,
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" "">
<html xmlns="">
<head runat="server">
<title>Telerik Radgrid: showing hierarchical data using a Dataset with multiple tables</title>
<form id="form1" runat="server">
<asp:ScriptManager ID="Scriptmanager1" runat="server">
<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">
<telerik:GridTableView DataMember="Employees" DataKeyNames="DepartmentId" Width="100%"
runat="server" ShowHeader="true">
<telerik:GridRelationFields DetailKeyField="DepartmentId" MasterKeyField="DepartmentId" />
<RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
<HeaderStyle Width="20px"></HeaderStyle>
<ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
Visible="False" Resizable="False">
<HeaderStyle Width="20px"></HeaderStyle>
<telerik:GridBoundColumn DataField="Title" UniqueName="Title" ReadOnly="true" HeaderText="Title">
<telerik:GridBoundColumn DataField="FirstName" UniqueName="FirstName" ReadOnly="true"
HeaderText="First Name">
<telerik:GridBoundColumn DataField="LastName" UniqueName="LastName" ReadOnly="true"
HeaderText="Last Name">
<RowIndicatorColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
<HeaderStyle Width="20px"></HeaderStyle>
<ExpandCollapseColumn CurrentFilterFunction="NoFilter" FilterListOptions="VaryByDataType"
<HeaderStyle Width="20px"></HeaderStyle>
<telerik:GridBoundColumn DataField="DepartmentName" UniqueName="DepartmentName" HeaderText="Department">

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)

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

The UI Result (Click to Enlarge):

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

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

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

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

Thursday, March 6, 2008

Export Html to Pdf using iTextSharp

This is an example to export from html to pdf using iTextSharp. In this case I used a html template file with some token fields that are replaced and after exported to pdf.

private static void GeneratePDF()
// Set output folder to /pdf
string outputFolder = HttpContext.Current.Server.MapPath("pdf");
// Set output file name to a Guid based name
string pdfFileName = outputFolder + "\\" + Guid.NewGuid().ToString() + ".pdf";

Document iTextSharpDocument = new Document(new Rectangle(880, 700));
FileStream fs = new FileStream(pdfFileName, FileMode.CreateNew);
PdfWriter writer = PdfWriter.GetInstance(iTextSharpDocument, fs);

// Read the html template and replace values
string templatePath = HttpContext.Current.Server.MapPath("html/ReportTemplate.html");
TextReader templateReader = new StreamReader(templatePath);
StringBuilder sbTemplate = new StringBuilder(templateReader.ReadToEnd());
sbTemplate.Replace("[@title]", "Sales");
[@name]", "Jhon Smith");
[@date]", DateTime.Now.ToString());

XmlDocument htmlDoc = new XmlDocument();

HtmlParser.Parse(iTextSharpDocument, htmlDoc);

// Release resources


writer = null;
iTextSharpDocument = null;