namespace KimismeDemo

{

    public partial class Form2 : Form

    {

        private ISession session;

        private ISessionFactory factory;

        private ITransaction trans;

        public Form2()

        {

            InitializeComponent();

        }

        #region 1.初始化NH - private void Form2_Load(object sender, EventArgs e)

        private void Form2_Load(object sender, EventArgs e)

        {

            Configuration config = new Configuration().AddAssembly("Kimisme");

            factory = config.BuildSessionFactory();

            session = factory.OpenSession();

            dgvList.AutoGenerateColumns = false;

        }

        #endregion

        #region 2.0 执行 sql语句 - private void tsmiExecuteSql_Click(object sender, EventArgs e)

        private void tsmiExecuteSql_Click(object sender, EventArgs e)

        {

            string strSql = "select * from T_Student where sId >1";

            ISQLQuery sqlQuery = session.CreateSQLQuery(strSql).AddEntity(typeof(Student));

            IList<Student> stuList = sqlQuery.List<Student>();

            dgvList.DataSource = stuList.ToList();

        }

        #endregion

        #region 3.0 执行存储过程 -  private void btnExecuteStoreProc_Click(object sender, EventArgs e)

        private void btnExecuteStoreProc_Click(object sender, EventArgs e)

        {

            trans = session.BeginTransaction();

            IList<Student> stuList = new List<Student>();

            ISessionFactoryImplementor imp = factory as ISessionFactoryImplementor;

            IDbConnection conn = imp.ConnectionProvider.GetConnection();

            IDbCommand cmd = imp.ConnectionProvider.GetConnection().CreateCommand();

            try

            {

                cmd.CommandText = "Pro_GetStudent";

                cmd.CommandType = CommandType.StoredProcedure;

                IDbDataParameter parameter = cmd.CreateParameter();

                parameter.ParameterName = "StudentId";

                parameter.Value = 4;

                cmd.Parameters.Add(parameter);

                cmd.Connection = conn;

                IDataReader read = cmd.ExecuteReader();

                while (read.Read())

                {

                    Student stu = new Student();

                    stu.Id = int.Parse(read.GetValue(0).ToString());

                    stu.Name = read.GetValue(1).ToString();

                    stu.Age = int.Parse(read.GetValue(2).ToString());

                    stuList.Add(stu);

                }

                trans.Commit();

                dgvList.DataSource = stuList.ToList();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        #endregion

        #region 4.0 执行hql语句 -private void tsmiExecuteHql_Click(object sender, EventArgs e)

        private void tsmiExecuteHql_Click(object sender, EventArgs e)

        {

            string strHql = "from Student stu where stu.Id >:stuId";

            IList<Student> stuList = session.CreateQuery(strHql).SetInt32("stuId", 7).List<Student>();

            dgvList.DataSource = stuList.ToList();

        }

        #endregion

        #region 5.0 执行linq语句-聚合函数  - Max

        private void tsmiMax_Click(object sender, EventArgs e)

        {

            var stuList = session.QueryOver<Student>().Where(s => s.Id > 0).List();

            int maxAge = (from s in stuList select s.Age).Max();

            MessageBox.Show(maxAge.ToString());

        }

        #endregion

        #region 5.1 执行linq语句-聚合函数  - Min

        private void tsmiMin_Click(object sender, EventArgs e)

        {

            var stuList = session.QueryOver<Student>().Where(s => s.Id > 0).List();

            int minAge = (from s in stuList select s.Age).Min();

            MessageBox.Show(minAge.ToString());

        }

        #endregion

        #region 5.2执行linq语句-聚合函数  - Avg

        private void tsmiAvg_Click(object sender, EventArgs e)

        {

            var stuList = session.QueryOver<Student>().Where(s => s.Id > 0).List();

            double avgAge = (from s in stuList select s.Age).Average();

            MessageBox.Show(avgAge.ToString());

        }

        #endregion

        #region 5.3执行linq语句-聚合函数  - Sum

        private void tsmiSum_Click(object sender, EventArgs e)

        {

            var stuList = session.QueryOver<Student>().Where(s => s.Id > 0).List();

            int sumAge = (from s in stuList select s.Age).Sum();

            MessageBox.Show(sumAge.ToString());

        }

        #endregion

        #region 5.4 执行linq语句-聚合函数  - Count

        private void tsmiCount_Click(object sender, EventArgs e)

        {

            var stuList = session.QueryOver<Student>().Where(s => s.Id > 0).List();

            int countAge = (from s in stuList select s.Age).Count();

            MessageBox.Show(countAge.ToString());

        }

        #endregion

    }

}