Fluent Nhibernate and Stored Procedures

sql:存储过程

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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

　　

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

/// <summary>        /// 存储过程        /// </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 =>           //     {           //         a.FluentMappings.AddFromAssemblyOf<Form1>();           //         a.HbmMappings.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=520;").ShowSql())                            .Mappings(m => m                            //.FluentMappings.PersistenceModel                            //.FluentMappings.AddFromAssembly();                                           .FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())) //用法注意                            //.Mappings(m => m               //.FluentMappings.AddFromAssemblyOf<Form1>())               //.Mappings(m => m               //.HbmMappings.AddFromAssemblyOf<Department>())               //.BuildConfiguration()               .BuildSessionFactory();            return isessionFactory;        }          /// <summary>        /// 存储过程 涂聚文测试成功。 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 GetDepartmentId @Id=:Id";// @"exec GetDepartmentId :Id";                            IQuery query = st.CreateSQLQuery(sql)  //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(); //不能强制转化                              //IList<Department> result = query.List<Department>(); //不是泛值中的集合                            Department dep=new Department();                            dep = (Department)clients; //无法将类型为“System.Object[]”的对象强制转换为类型                            //2                            //var clients = st.GetNamedQuery("GetDepartmentId")                            //        .SetParameter("Id", 1)                            //        .SetResultTransformer(Transformers.AliasToBean(typeof(Department)))                            //        .List<Department>().ToList();                            MessageBox.Show(dep.DepName);                        }                    }                }

　　参考：http://stackoverflow.com/questions/6373110/nhibernate-use-stored-procedure-or-mapping

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83

/// <summary>    /// 存储过程操作    /// </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);    }