Fluent nhibures and Stored Procedures, fluentnhibures

Fluent nhibures and Stored Procedures, fluentnhibures

SQL: Stored Procedure

DROP TABLE DepartmentGOCREATE TABLE Department(Id  INT IDENTITY(1,1) PRIMARY KEY,DepName VARCHAR(50),PhoneNumber VARCHAR(50))GOCREATE PROCEDURE [dbo].[GetDepartmentId] ( @Id INT )AS     BEGIN        SELECT  *        FROM    Department                        WHERE   Department.Id= @Id    ENDGOEXEC GetDepartmentId 1GO

　　

 

/// <Summary> /// Stored Procedure /// </summary> /// <returns> </returns> static ISessionFactory testSession () {// var config = MsSqlConfiguration. msSql2005.ConnectionString (@ "Server = LF-WEN \ GEOVINDU; initial catalog = NHibernateSimpleDemo; User ID = sa; Password = 520 ;"). showSql (); // var db = Fluently. configure ()//. database (config )//. mappings (a => // {//. fluentMappings. addFromAssemblyOf <Form1> (); //. hbmMappi Ngs. addClasses (typeof (Department); //}); // db. buildConfiguration (); // return db. buildSessionFactory (); ISessionFactory isessionFactory = Fluently. configure (). database (MsSqlConfiguration. msSql2005. connectionString (@ "Server = GEOVINDU-PC \ GEOVIN; initial catalog = NHibernateSimpleDemo; User ID = sa; Password = 770214 ;"). showSql ()). mappings (m => m //. fluentMappings. persistenceModel //. fluentMappings. addFro MAssembly ();. fluentMappings. addFromAssembly (Assembly. getExecutingAssembly () // Usage Note //. mappings (m => m //. fluentMappings. addFromAssemblyOf <Form1> ())//. mappings (m => m //. hbmMappings. addFromAssemblyOf <Department> ())//. buildConfiguration (). buildSessionFactory (); return isessionFactory;} // <summary> // The stored procedure has been successfully tested. WIN7 /// </summary> /// <param name = "sender"> </param> /// <param name = "e"> </param> private void button3_Click (object sender, eventArgs e) {try {using (var exc = testSession () {using (var st = exc. openSession () {if (! Object. equals (st, null) {// 1 string SQL = @ "exec getaskmentid @ Id =: Id"; // @ "exec getaskmentid: Id"; IQuery query = st. createSQLQuery (SQL) // GetNamedQuery ("GetDepartmentId ")//. setInt32 ("Id", 1 ). setParameter ("Id", 1 ). setResultTransformer (Transformers. aliasToBean (typeof (Department )));//. list <Department> (); var clients = query. uniqueResult (); // query. list <Department> (). toList (); // cannot be forcibly converted // IList <Department> result = query. list <Department> (); // The set Department dep = new Department (); dep = (Department) clients; // The type cannot be set to "System. object [] "is forcibly converted to type // 2 // var clients = st. getNamedQuery ("getaskmentid ")//. setParameter ("Id", 1 )//. setResultTransformer (Transformers. aliasToBean (typeof (Department )))//. list <Department> (). toList (); MessageBox. show (dep. depName );}}}

Reference: http://stackoverflow.com/questions/6373110/nhibernate-use-stored-procedure-or-mapping

 

/// <summary>        /// Activation        ///         /// Action        /// </summary>        /// <param name="Id"></param>        /// <returns></returns>        public IEnumerable<Department> GetDeactivationList(int companyId)        {            var sessionFactory = FluentNHibernateHelper.CreateSessionFactory();// BuildSessionFactory();            var executor = new HibernateStoredProcedureExecutor(sessionFactory);            var deactivations = executor.ExecuteStoredProcedure<Department>(              "GetDepartmentId",              new[]              {                  new SqlParameter("Id", companyId),                  //new SqlParameter("startDate", startDate),                 // new SqlParameter("endDate", endDate),              });            return deactivations;        }

　　

/// <Summary> /// stored procedure operation /// </summary> public class HibernateStoredProcedureExecutor: IExecuteStoredProcedure {// <summary> /// </summary> private readonly ISessionFactory _ sessionFactory; /// <summary> ///// </summary> /// <param name = "sessionFactory"> </param> public HibernateStoredProcedureExecutor (ISessionFactory sessionFactory) {sessionFactory = FluentNHibernateHelper. createSessionFactory (); _ sessionFactory = sessionFactory ;} /// <summary> ///// </summary> /// <typeparam name = "TOut"> </typeparam> /// <param name = "procedureName "> </param> /// <param name =" parameters "> </param> /// <returns> </returns> public IEnumerable <TOut> ExecuteStoredProcedure <TOut> (string procedureName, IList <SqlParameter> parameters) {IEnumerable <TOut> result; using (var session = _ sessionFactory. openSession () {var query = session. getNamedQuery (procedureName); AddStoredProcedureParameters (query, parameters); result = query. list <TOut> ();} return result ;} /// <summary> ///// </summary> /// <typeparam name = "TOut"> </typeparam> /// <param name = "procedureName "> </param> /// <param name =" parameters "> </param> /// <returns> </returns> public TOut ExecuteScalarStoredProcedure <TOut> (string procedureName, IList <SqlParameter> parameters) {TOut result; using (var session = _ sessionFactory. openSession () {var query = session. getNamedQuery (procedureName); AddStoredProcedureParameters (query, parameters); result = query. setResultTransformer (Transformers. aliasToBean (typeof (TOut ))). uniqueResult <TOut> ();} return result ;} /// <summary> ///// </summary> /// <param name = "query"> </param> /// <param name = "parameters "> </param> // <returns> </returns> public static IQuery AddStoredProcedureParameters (IQuery query, IEnumerable <SqlParameter> parameters) {foreach (var parameter in parameters) {query. setParameter (parameter. parameterName, parameter. value);} return query ;}/// <summary >//// </summary> public interface IExecuteStoredProcedure {TOut ExecuteScalarStoredProcedure <TOut> (string procedureName, IList <SqlParameter> sqlParameters); IEnumerable <TOut> ExecuteStoredProcedure <TOut> (string procedureName, IList <SqlParameter> sqlParameters );}