因为最近在做一些有关数据库方面的开发，其实，说白了，就是对数据库的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
}
{
#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-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>
<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);
{
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();
}
}
}
}
}
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;
}
}
}
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
{
{
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
}
}
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技术，当然了，才接触到这些东西，也许有其它的解决办法也说不定，待自己慢慢研究吧！！