学步园

      因为最近在做一些有关数据库方面的开发，其实，说白了，就是对数据库的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 == 











														
							

返回

【上篇】django 中文编码
【下篇】LoadUserProfile 调用因下列错误而宣告失败: 拒绝访问的常见解决方法

作者: courtship

• 该日志由 courtship 于11年前发表在综合分类下，最后更新于 2012年11月04日.
• 转载请注明: NHibernate&&Linq简单的CRUD操作 | 学步园 +复制链接

抱歉!评论已关闭.