Using Stored Procedure in NHibernate

Stored Procedure approaches in NHibernate:

// Not readable
// And error-prone, order of parameters could be mistaken
{
 var people = session.CreateSQLQuery("exec GetPerson ?, ?")
  .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<GetPerson>())
  .SetString(0, "B")
  .SetString(1, "M")
  .List<GetPerson>();
}


// Readable
// Yet error-prone, order of parameters could be mistaken
{
 var people = session.CreateSQLQuery("exec GetPerson @lastName = ?, @firstName = ?")
  .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<GetPerson>())
  .SetString(0, "B")
  .SetString(1, "M")
  .List<GetPerson>();
}



// Readable
// Yet error-prone, order of parameters could be mistaken
{
 var people = session.CreateSQLQuery("exec GetPerson :lastName, :firstName")
  .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<GetPerson>())
  .SetString("lastName", "B")
  .SetString("firstName", "M")
  .List<GetPerson>();
}


// Readable
// Order of parameters doesn't matter. And if wrong parameter is passed, it's readily apparent
// The best approach
{
 var people = session.CreateSQLQuery("exec GetPerson @firstName = :firstName, @lastName = :lastName")
  .SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<GetPerson>())
  .SetString("lastName", "B")
  .SetString("firstName", "M")
  .List<GetPerson>();
}

Happy Coding!