NHibernate&&Linq简单的CRUD操作

      因为最近在做一些有关数据库方面的开发，其实，说白了，就是对数据库的CRUD操作，写了n多的存储过程，倒不是说存储过程多难，关键是在后期的维护方面有点头疼，没办法，研究一下ORM吧。

      下面分别是用NHibernate和Linq实现的简单的CRUD操作，当是一开始的练练手（开关环境：VS08+SQL2000）。

       ①：NHibernate

       不管三七二十一，先看看实体类吧。（数据库代码省略，映射文件对应着表结构）  

    public class User
    {
        #region 构造函数
        /// <summary>
        /// 用户信息
        /// </summary>
        public User() { }
        #endregion

        #region 属性

        private int userID;
        /// <summary>
        /// 用户ID
        /// </summary>
        public virtual int UserID
        {
            get
            {
                return userID;
            }
            set
            {
                userID = value;
            }
        }

        private string userName;
        /// <summary>
        /// 用户姓名
        /// </summary>
        public virtual string UserName
        {
            get
            {
                return userName;
            }
            set
            {
                userName = value;
            }
        }

        private string userPwd;
        /// <summary>
        /// 用户密码
        /// </summary>
        public virtual string UserPwd
        {
            get
            {
                return userPwd;
            }
            set
            {
                userPwd = value;
            }
        }

        private int userAge;
        /// <summary>
        /// 用户年龄
        /// </summary>
        public virtual int UserAge
        {
            get
            {
                return userAge;
            }
            set
            {
                userAge = value;
            }
        }
        #endregion
    }

      这里用的NHibernate是NHibernate-2.1.2.GA-bin,映射文件如下（注意映射文件中nh的版本号）：

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
  <class name="NHUser.User,NHUser" table="UserInfo"> //NHUser为命名空间、UserInfo为数据表名
    <id name="UserID" type="Int32" unsaved-value="null">
      <column name="UserID" length="4" sql-type="int" not-null="true" unique="true" index="PK_UserInfo"/>
      <generator class="native"/>
    </id>
    <property name="UserName" type="String">
      <column name="UserName" length="50" sql-type="nvarchar" not-null="false"/>
    </property>
    <property name="UserPwd" type="String">
      <column name="UserPwd" length="50" sql-type="nvarchar" not-null="false"/>
    </property>
    <property name="UserAge" type="Int32">
      <column name="UserAge" length="4" sql-type="int" />
    </property>
  </class>
</hibernate-mapping>

      基本工作还剩下个配置文件hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string">Server=localhost;uid=sa;pwd=123456;database=NunitUser;Integrated Security=SSPI</property>
    <property name="dialect">NHibernate.Dialect.MsSql2000Dialect</property>
    <property name="adonet.batch_size">100</property>
    <property name='proxyfactory.factory_class'>
      NHibernate.ByteCode.Castle.ProxyFactoryFactory,NHibernate.ByteCode.Castle
    </property>
    <mapping assembly="NHUser"/>
  </session-factory>
</hibernate-configuration>

      OK！准备工作over了，下面看看CRUD的操作代码吧

namespace NHUser
{
    public partial class NHCRUD : Form
    {
        public NHCRUD()
        {
            InitializeComponent();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            try
            {
                string username = this.tbUserName.Text.ToString();
                string userpwd = this.tbUserPwd.Text.ToString();
                int userage = Convert.ToInt32(this.tbUserAge.Text.ToString());

                //定义配置文件
                Configuration cfg = new Configuration();
                cfg.Configure();

                //创建会话工厂
                ISessionFactory factory = cfg.BuildSessionFactory();
                ISession session = factory.OpenSession();
                //用户赋值
                User user = new User();
                user.UserName = username;
                user.UserPwd = userpwd;
                user.UserAge = userage;

                //开启事务
                ITransaction trans = session.BeginTransaction();
                try
                {
                    session.Save(user);
                    //Commit
                    trans.Commit();
                    MessageBox.Show("用户数据插入成功！", "系统提示！");
                    this.Close();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    MessageBox.Show("用户数据插入失败！" + ex.Message, "系统提示！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示！");
            }
        }

        /// <summary>
        /// 查询指定数据
        /// </summary>
        private void btnRead_Click(object sender, EventArgs e)
        {
            //定义配置文件
            Configuration cfg = new Configuration();
            cfg.Configure();
            //创建会话工厂
            ISessionFactory factory = cfg.BuildSessionFactory();
            using (ISession session = factory.OpenSession())
            {
                IList users = session.CreateCriteria(typeof(User)).Add(Expression.Eq("UserName", "dukeyongwang")).List();

                foreach (User user in users)
                {
                    Console.WriteLine("UserID:" + user.UserID + "——>UserName:" + user.UserName + "——>UserPwd:" + user.UserPwd + "——>UserAge:" + user.UserAge);
                }
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            //定义配置文件
            Configuration cfg = new Configuration();
            cfg.Configure();
            //创建会话工厂
            ISessionFactory factory = cfg.BuildSessionFactory();

            try
            {
                using (ISession session = factory.OpenSession())
                {
                    User user = session.Load(typeof(User), 5) as User;

                    Console.WriteLine("修改前==>>UserID:" + user.UserID + "——>UserName:" + user.UserName + "——>UserPwd:" + user.UserPwd + "——>UserAge:" + user.UserAge);

                    user.UserName = "dukeyongwang";
                    user.UserPwd = "dkwang";
                    user.UserAge = 25;

                    using (ITransaction trans = session.BeginTransaction())
                    {
                        session.Update(user, user.UserID);

                        User updateUser = session.Load(typeof(User), user.UserID) as User;

                        //修改后
                        Console.WriteLine("修改后==>>UserID:" + updateUser.UserID + "——>UserName:" + updateUser.UserName + "——>UserPwd:" + updateUser.UserPwd + "——>UserAge:" + updateUser.UserAge);

                        trans.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改时错误：" + ex.Message, "错误提示！");
            }
        }


        /// <summary>
        /// 删除(可通过查询出来的实体对象，然后删除)
        /// </summary>
        private void btnDel_Click(object sender, EventArgs e)
        {
           //定义配置文件
            Configuration cfg = new Configuration();
           cfg.Configure();
           //创建会话工厂
            ISessionFactory factory = cfg.BuildSessionFactory();

            using (ISession session = factory.OpenSession())
            {
                using (ITransaction trans = session.BeginTransaction())
                {
                    User newUser = session.Load(typeof(User), 6) as User;
                    //删除
                    session.Delete(newUser);
                    
                    trans.Commit();
                }
            }
        }
    }
}

     运行以上代码，就可以实现数据的CRUD，感觉没有SQL，代码清爽了许多。呵呵。。。。

     ②：Linq

     在linq中少了NHibernate的映射文件，这部分实现在实体类中通过TableAttribute和ColumnAttribute实现，少建一个xml文件，也许你会说，现在的映射文件可以自动生成，好吧，我承认的确是可以。

[Table(Name="UserInfo")]
    public class User
    {
        private int userID;
        /// <summary>
        /// 用户ID
        /// </summary>
        [Column(DbType="int",IsPrimaryKey=true,IsDbGenerated=true)]
        public int UserID
        {
            get
            {
                return userID;
            }
            set
            {
                userID = value;
            }
        }

        private string userName;
        /// <summary>
        /// 用户姓名
        /// </summary>
        [Column(DbType="nvarchar(50)")]
        public string UserName
        {
            get
            {
                return userName;
            }
            set
            {
                userName = value;
            }
        }

        private string userPwd;
        /// <summary>
        /// 用户密码
        /// </summary>
        [Column(DbType="nvarchar(50)")]
        public string UserPwd
        {
            get
            {
                return userPwd;
            }
            set
            {
                userPwd = value;
            }
        }

        private int userAge;
        /// <summary>
        /// 用户年龄
        /// </summary>
        [Column(DbType="int")]
        public int UserAge
        {
            get
            {
                return userAge;
            }
            set
            {
                userAge = value;
            }
        }
    }

    好了，现在就可以看看在LINQ中如何完成CRUD的操作：

namespace LinqUser
{
    public partial class LinqCRUD : Form
    {
        //映射实体
        DataContext objContext = null;

        public LinqCRUD()
        {
            InitializeComponent();
            string strConnection = "Server=localhost;uid=sa;pwd=123456;database=NunitUser;Integrated Security=SSPI";
            objContext = new DataContext(strConnection);
        }

        #region 数据CRUD
        /// <summary>
        /// 数据插入
        /// </summary>
        private void btnCreate_Click(object sender, EventArgs e)
        {
            try
            {
                string userName = this.tbUserName.Text.ToString();
                string userPwd = this.tbUserPwd.Text.ToString();
                int userAge = Convert.ToInt32(this.tbUserAge.Text.ToString());

                User user = new User();
                user.UserName = userName;
                user.UserPwd = userPwd;
                user.UserAge = userAge;
                
                //插入
                objContext.GetTable<User>().InsertOnSubmit(user);
                //提交
                objContext.SubmitChanges();

                MessageBox.Show("插入数据成功！", "系统提示！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入数据时错误：" + ex.Message, "系统提示！");
            }
        }

        /// <summary>
        /// 数据修改
        /// </summary>
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                var myUpdate = from user in objContext.GetTable<User>()
                               where user.UserID == 1
                               select user;
                User userUpdate = myUpdate.First<User>() as User;
                if (null == userUpdate) return;
                userUpdate.UserName = this.tbUserName.Text.ToString();
                userUpdate.UserPwd = this.tbUserPwd.Text.ToString();
                userUpdate.UserAge = Convert.ToInt32(this.tbUserAge.Text.ToString());

                //提交
                objContext.SubmitChanges();

                MessageBox.Show("修改数据成功！", "系统提示！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改数据时错误：" + ex.Message, "系统提示！");
            }
        }

        /// <summary>
        /// 数据删除
        /// </summary>
        private void btnDel_Click(object sender, EventArgs e)
        {
            try
            {
                var myDel = from user in objContext.GetTable<User>()
                            where user.UserID == 1
                            select user;
                User userDel = myDel.First<User>();
                if (null == userDel) return;

                //删除
                objContext.GetTable<User>().DeleteOnSubmit(userDel);

                //提交
                objContext.SubmitChanges();

                MessageBox.Show("删除数据成功！", "系统提示！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("删除数据时出错：" + ex.Message, "系统提示！");
            }
        }

        /// <summary>
        /// 数据查询
        /// </summary>
        private void btnRead_Click(object sender, EventArgs e)
        {
            try
            {
                var myQuery = from user in objContext.GetTable<User>()
                              where user.UserID == 1
                              select user;

                if (myQuery.Count<User>() <= 0) return;

                foreach (User user in myQuery)
                {
                    Console.WriteLine("用户信息：UserID——>" + user.UserID + "、UserName——>" + user.UserName + "、UserPwd——>" + user.UserPwd + "、UserAge——>" + user.UserAge);
                }

                MessageBox.Show("查询数据成功！", "系统提示！");

            }
            catch (Exception ex)
            {
                MessageBox.Show("查询数据时错误：" + ex.Message, "系统提示！");
            }
        }
        #endregion
    }
}

      到这里，LINQ的操作也结束了。运行感受一下吧。

      先说说我的感受吧，用NHibernate明显感觉到效率没有原生的SQL语句的好，可能只是插入一条语句，没有批量的处理，Linq反而没感觉到比NHibernate效率低。如果是对数据库操作的实时性要求很高的话，可能不太适合使用ORM技术，当然了，才接触到这些东西，也许有其它的解决办法也说不定，待自己慢慢研究吧！！