C#反射实现对象关系映射（ORM）
ORM在业界广泛使用，导致程序员对数据库的操作水平不断下降，可能技术发展就是这样吧，老技术终究会被新技术取代，因为其不断地封装，使得代码操作上越来越简单。也许只有BAT和微软更注重老技术的传承了吧。
笔者也简单的封装了一个ORM框架。因为没有研究过别人的ORM到底是怎么封装的，全凭反射和动态拼sql这2个ORM的重要概念。所以封装的不好的地方轻喷
先给出类结构，注意这个类的设计必须符合JavaBean的规范，不明白什么是JavaBean的读者可以百度去查阅相关资料。这里索引列，即id必须放在第一行
class Student
{
private int id;
private string name;
private Boolean sex;
private string phone;
public int Id
{
get { return id; }
set { id = value; }
}
public string Name
{
get { return name; }
set { name = value; }
}
public Boolean Sex
{
get { return sex; }
set { sex = value; }
}
public string Phone
{
get { return phone; }
set { phone = value; }
}
}
class Course
{
private int id;
private string name;
private decimal cost;
public int Id
{
get { return id; }
set { id = value; }
}
public string Name
{
get { return name; }
set { name = value; }
}
public decimal Cost
{
get { return cost; }
set { cost = value; }
}
}
数据库中创建符合这2个类的表结构，Boolean类型在数据库中是BIT
CREATE TABLE STUDENT
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(30),
SEX BIT,
PHONE VARCHAR(20)
)
CREATE TABLE COURSE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(40),
COST DECIMAL
)
INSERT INTO STUDENT(NAME,SEX) VALUES('波多野结衣',1,'13333333333')
INSERT INTO STUDENT(NAME,SEX) VALUES('苍井空',1,'17333333333')
INSERT INTO COURSE(NAME,COST) VALUES('Java从入门到放弃',98.8)
INSERT INTO COURSE(NAME,COST) VALUES('MySql从删库到跑路',45.5)
接下来先演示一下笔者封装好的ORM
SqlConnection sqlConnection = new SqlConnection("Server=.;user=sa;pwd=find/-perm4000;database=DBTest");
//这个ReflectUtil就是ORM的封装，里面有增删改查基本功能
ReflectUtil<Student> rf1= new ReflectUtil<Student>(sqlConnection);
Student stu1=rf1.Select(1);
Student stu2=rf1.Select(2);
ReflectUtil<Course> rf2=new ReflectUtil<Course>(sqlConnection);
Course course1=rf2.Select(1);
Course course2=rf2.Select(2);
Console.WriteLine("--------------学生类----------------");
Console.Write("学号:{0}\t", stu1.Id);
Console.Write("姓名:{0}\t", stu1.Name);
Console.Write("性别:{0}\t", stu1.Sex?"女":"男");
Console.WriteLine("手机号:{0}", stu1.Phone);
Console.Write("学号:{0}\t", stu2.Id);
Console.Write("性别:{0}\t", stu2.Sex?"女":"男");
Console.WriteLine("手机号:{0}", stu2.Phone);
Console.WriteLine("--------------课程类----------------");
Console.Write("课程编号:{0}\t", course1.Id);
Console.Write("课程名称:{0}\t", course1.Name);
Console.WriteLine("课本金额:{0}", course1.Cost);
Console.Write("课程编号:{0}\t", course2.Id);
Console.Write("课程名称:{0}\t", course2.Name);
Console.WriteLine("课本金额:{0}", course2.Cost);
Console.Read();
演示插入后直接全部查出，返回的是List对象，接着上面写
Student stu3 = new Student();
stu3.Name = "加藤鹰";
stu3.Sex = false;
stu3.Phone = "15333333333";
Student stu4 = new Student();
stu4.Name = "毛衣女";
stu4.Sex = true;
stu4.Phone = "13866666666";
//这里持久化到数据库
rf1.Insert(stu3);
rf1.Insert(stu4);
Course course3 = new Course();
course3.Name = "论挑逗乳房全技巧";
course3.Cost = 568.3M;
Course course4 = new Course();
course4.Name = "加藤鹰之手的前世今生";
course4.Cost = 788.6M;
//持久化
rf2.Insert(course3);
rf2.Insert(course4);
List<Student> stuList=rf1.Select();
List<Course> courseList = rf2.Select();
//打印展示
Console.WriteLine("--------------学生类----------------");
foreach (Student stu in stuList)
{
Console.Write("学号:{0}\t", stu.Id);
Console.Write("姓名:{0}\t", stu.Name);
Console.Write("性别:{0}\t", stu.Sex ? "女" : "男");
Console.WriteLine("手机号:{0}", stu.Phone);
}
Console.WriteLine("--------------课程类----------------");
foreach(Course course in courseList)
{
Console.Write("课程编号:{0}\t", course.Id);
Console.Write("课程名称:{0}\t", course.Name);
Console.WriteLine("课本金额:{0}", course.Cost);
}
Console.Read();
再看看数据库，可以看出确实写入了
接下来演示更新操作，更新同样传入一个对象，设置的id就是需要更新的行，实际上除了id列，其余的列都会执行一次更新，如果有不想更新的列就设置原值，就以Student表为例，这里不接着上面写了
ReflectUtil rf1 = new ReflectUtil(sqlConnection);
Student stu = new Student();
stu.Id = 4;
stu.Name = “加藤鹰亲传弟子”;
stu.Sex = true;
stu.Phone = “1386666666”;
rf1.Update(stu);
List stuList = rf1.Select();
foreach (Student stu_ in stuList)
{
Console.Write(“学号:{0}\t”, stu_.Id);
Console.Write(“姓名:{0}\t”, stu_.Name);
Console.Write(“性别:{0}\t”, stu_.Sex ? “女” : “男”);
Console.WriteLine(“手机号:{0}”, stu_.Phone);
}
Console.Read();
执行删除，删除没有那么多的嘻嘻哈哈，传入一个id就行，简单粗暴
List<Student> stuList = rf1.Select();
foreach (Student stu_ in stuList)
{
Console.Write("学号:{0}\t", stu_.Id);
Console.Write("姓名:{0}\t", stu_.Name);
Console.Write("性别:{0}\t", stu_.Sex ? "女" : "男");
Console.WriteLine("手机号:{0}", stu_.Phone);
}
rf1.Delete(2);
List<Student> stuList2 = rf1.Select();
foreach (Student stu_ in stuList2)
{
Console.Write("学号:{0}\t", stu_.Id);
Console.Write("姓名:{0}\t", stu_.Name);
Console.Write("性别:{0}\t", stu_.Sex ? "女" : "男");
Console.WriteLine("手机号:{0}", stu_.Phone);
}
Console.Read();
基本功能演示完成，下面给出ReflectUtil源代码，实际编写的时候不可能一次就能成功写好的，肯定需要经过多次的测试和调整，拼接的sql在发出的时候先看看拼的格式是否正确等等。坑是肯定有的，就比如id列必须要第一个定义，否则Select(int id)最终拼接的sql会差强人意的。可能有些读者会觉得：我为啥要自己封装ORM，网上现成的多得是。没错，现成的轮子完全没必要再去造，用现成的就好了。这种思想如果不能转变就永远是个菜鸟程序员。这就是跟大牛的差别。别人造轮子，你用轮子。
class ReflectUtil<T>
{
private SqlConnection sqlConnection;
private SqlCommand sqlCommand;
public ReflectUtil(SqlConnection sqlConnection)
{
this.sqlConnection = sqlConnection;
}
//根据指定id获取一条记录
public T Select(int id)
{
if(this.sqlConnection.State==ConnectionState.Closed)
{
this.sqlConnection.Open();
}
string className = GetName(typeof(T));
//拼接sql
string sqlText = "SELECT * FROM " + className + " WHERE ID="+ id;
sqlCommand = new SqlCommand(sqlText,sqlConnection);
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
sda.Fill(ds);
T t = Select1(ds.Tables[0]);
this.sqlConnection.Close();
return t;
}
private T Select1(DataTable dt)
{
//实例化该泛型对象
T t = Activator.CreateInstance<T>();
//获取该对象的属性集合
PropertyInfo[] fields = typeof(T).GetProperties();
//获取DataTable的第一行，这里已经确定DataTable只有一条数据
DataRow dr = dt.Rows[0];
//获取列数
int count = dt.Columns.Count;
//遍历属性并赋值
foreach (PropertyInfo field in fields)
{
for(int i=0;i<count;i++)
{
if(field.Name.ToUpper()==dt.Columns[i].ColumnName.ToUpper())
{
field.SetValue(t, dr[i], null);
break;
}
}
}
return t;
}
//获取泛型对象的集合,这里获取所有
public List<T> Select()
{
if(this.sqlConnection.State==ConnectionState.Closed)
{
this.sqlConnection.Open();
}
string className = GetName(typeof(T));
//拼接sql
string sqlText = "SELECT * FROM " + className;
sqlCommand = new SqlCommand(sqlText, sqlConnection);
DataSet ds = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
sda.Fill(ds);
DataTable dt = ds.Tables[0];
List<T> arrList = Select2(dt);
this.sqlConnection.Close();
return arrList;
}
private List<T> Select2(DataTable dt)
{
List<T> arrList = new List<T>();
//获取该对象的属性集合
PropertyInfo[] fields = typeof(T).GetProperties();
//获取列数
int columnCount = dt.Columns.Count;
//获取行数
int rowCount = dt.Rows.Count;
//遍历赋值
for(int i=0;i<rowCount;i++)
{
T t = Activator.CreateInstance<T>();
for(int j=0;j<columnCount;j++)
{
foreach(PropertyInfo field in fields)
{
if(field.Name.ToUpper()==dt.Columns[j].ColumnName.ToUpper())
{
field.SetValue(t, dt.Rows[i][j], null);
break;
}
}
}
arrList.Add(t);
}
return arrList;
}
//插入一个泛型对象,返回受影响的行数
public int Insert(T t)
{
if(this.sqlConnection.State==ConnectionState.Closed)
{
this.sqlConnection.Open();
}
//得到类名
string className = GetName(typeof(T));
StringBuilder sql = new StringBuilder("INSERT INTO ");
sql.Append(className+"(");
StringBuilder sqlValue = new StringBuilder();
//拼接sql属性
PropertyInfo[] fields = t.GetType().GetProperties();
SqlParameter[] sqlParameters = new SqlParameter[fields.Length-1];
//第一个属性不需要拼接，在数据库中是自增序列
for(int i=1;i<fields.Length;i++)
{
if(i==fields.Length-1)
{
sql.Append(fields[i].Name+") VALUES(");
sqlValue.Append("@"+fields[i].Name + ")");
}
else
{
sql.Append(fields[i].Name + ",");
sqlValue.Append("@"+fields[i].Name+ ",");
}
sqlParameters[i-1] = new SqlParameter("@" + fields[i].Name, fields[i].GetValue(t));
}
sql.Append(sqlValue.ToString());
SqlCommand sqlCommand = new SqlCommand(sql.ToString(), sqlConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddRange(sqlParameters);
int count= sqlCommand.ExecuteNonQuery();
this.sqlConnection.Close();
return count;
}
//插入泛型对象集合,返回受影响的总行数
public int Insert(List<T> arrList)
{
int result = 0;
//直接调用已实现的单对象插入即可
for(int i=0;i<arrList.Count;i++)
{
result+=Insert(arrList[i]);
}
return result;
}
//修改某一泛型对象,返回受影响的行数
public int Update(T t)
{
if(this.sqlConnection.State==ConnectionState.Closed)
{
this.sqlConnection.Open();
}
//得到类名
string className = GetName(typeof(T));
StringBuilder sql = new StringBuilder("UPDATE ");
sql.AppendFormat("{0} SET ", className);
PropertyInfo[] fields = t.GetType().GetProperties();
SqlParameter[] sqlParameters = new SqlParameter[fields.Length];
//遍历属性拼sql,第一个id属性单独拼
for(int i=1;i<fields.Length;i++)
{
if(i==fields.Length-1)
{
sql.AppendFormat("{0}[email protected]{0} ", fields[i].Name);
}
else
{
sql.AppendFormat("{0}[email protected]{0},", fields[i].Name);
}
sqlParameters[i-1] = new SqlParameter("@" + fields[i].Name, fields[i].GetValue(t));
}
sql.AppendFormat("WHERE {0}[email protected]{0}", fields[0].Name);
sqlParameters[fields.Length - 1] = new SqlParameter("@" + fields[0].Name, fields[0].GetValue(t));
SqlCommand sqlCommand = new SqlCommand(sql.ToString(), sqlConnection);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.AddRange(sqlParameters);
int count=sqlCommand.ExecuteNonQuery();
this.sqlConnection.Close();
return count;
}
//修改泛型对象集合,返回受影响的总行数
public int Update(List<T> arrList)
{
int result = 0;
for(int i=0;i<arrList.Count;i++)
{
result += Update(arrList[i]);
}
return result;
}
//删除指定ID的泛型对象,并将该对象返回
public T Delete(int id)
{
if (this.sqlConnection.State == ConnectionState.Closed)
{
this.sqlConnection.Open();
}
T t = Activator.CreateInstance<T>();
t = Select(id);
string className = GetName(typeof(T));
PropertyInfo[] fields = typeof(T).GetProperties();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("DELETE {0} WHERE {1}={2}", className, fields[0].Name, id);
if (this.sqlConnection.State == ConnectionState.Closed)
{
this.sqlConnection.Open();
}
sqlCommand = new SqlCommand(sql.ToString(), sqlConnection);
sqlCommand.CommandType = CommandType.Text;
int count=sqlCommand.ExecuteNonQuery();
this.sqlConnection.Close();
return t;
}
//获取指定泛型的类名
private string GetName(Type t)
{
//得到该类名称
return t.Name;
}
}