NHibernate初学二之简单执行SQL及HQL

浏览数：28 / 时间：2015年06月12日

上篇文章简单介绍NHibernate之简单增删改查，本文将会简单介绍有关执行NHibernate的SQL、HQL及存储过程；

一：执行SQL语句实例，运用CreateSQLQuery方法

        public IList<SchoolModel> GetListBySql()
        {
            string strSql = "select * from T_School where studentNum>250";
            ISQLQuery sqlQuery = isession.CreateSQLQuery(strSql).AddEntity(typeof(SchoolModel));
            return sqlQuery.List<SchoolModel>();
        }

二：执行存储过程实例

2.1：首先定义一个存储过程，传入一个参数

Create PROCEDURE [dbo].[Pro_Hql] 
    @StudentNum int
AS
BEGIN
   select * from T_School where studentNum>@StudentNum
END

2.2：引入using System.Data;using NHibernate.Engine命名空间,NHibernateHelper是帮助类返回SessionFactory；当然也可以通过它来执行SQL语句

        public IList<SchoolModel> GetListByProcedure()
        {
            IList<SchoolModel> list = new List<SchoolModel>();
            ISessionFactoryImplementor imp = (ISessionFactoryImplementor)new NHibernateHelper().GetSessionFactory();
            IDbConnection conn = imp.ConnectionProvider.GetConnection();
            IDbCommand cmd = imp.ConnectionProvider.GetConnection().CreateCommand();
            try
            {
                cmd.CommandText = "Pro_Hql";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                IDbDataParameter parameter = cmd.CreateParameter();
                parameter.ParameterName = "StudentNum";
                parameter.Value = 250;
                cmd.Parameters.Add(parameter);
                cmd.Connection = conn;
                IDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    SchoolModel model = new SchoolModel();
                    model.ID = Guid.Parse(read.GetValue(0).ToString());
                    model.SchoolName = read.GetValue(1).ToString();
                    model.BuildDate = DateTime.Parse(read.GetValue(2).ToString());
                    model.Address = read.GetValue(3).ToString();
                    model.IsSenior = bool.Parse(read["IsSenior"].ToString());
                    model.StudentNum = int.Parse(read["StudentNum"].ToString());
                    list.Add(model);
                }
                
            }
            catch (Exception ex)
            {
 
            }
            return list;
        }

三：执行HQL语句实例，此处用CreateQuery并设置参数用":"，当然也可以拼接字符串;

        public IList<SchoolModel> GetListByHql()
        {
            //实体的名称跟属性大小写都要注意
            string hqlStr = "from SchoolModel c where c.StudentNum>:studentNum";
            return isession.CreateQuery(hqlStr).SetString("studentNum", "250").List<SchoolModel>();
        }