c# 诠释ORM过程

   对象/关系数据库映射（object/relational mapping，ORM）这个术语表示一种技术，用来把对象模型表示的对象映射到基于SQL的关系模型数据结构中去。

  在今日的企业环境中，把面向对象的软件和关系数据库一起使用可能是相当麻烦和浪费时间的,ORM

 

不仅仅管理.NET类到数据库表的映射（包括.NET 数据类型到SQL数据类型的映射），还提供数据查询和获取数据的方法，可以大幅度减少开发时人工使用SQL和ADO.NET处理数据的时间。

 

  现在有很多成熟的开源框架支持ORM,如：NHibernate,PDO....等 但是他们内部是怎么实现的， 通过一个非常简单的实例让我们一起来探索其中最为关键的过程. (适合ORM初学者)我们以Sql Server 为例

   定义操作类型枚举， 为增强可读性 将枚举名使用中文，在实际应用中不建议使用中文作为枚举名称。

   

   public class DataMessageClass { public enum DataMessage { 数据连接失败,系统忙请等待,操作成功,可继续操作 } }

  标记实体映射基类

   /// <summary> /// DataBase 的摘要说明。 /// </summary> public interface IDataBase { }

   自定义属性类，DataObjectAttribute 用于标记映射实体所对应的数据表 FieldObjectAttribute用于标记字段所代表的特殊属性

/// <summary> /// DataObjectAttribute 定义类的映射配置的属性类。DataObjectAttribute是一个类级别的属性。 /// </summary> [AttributeUsage(AttributeTargets.Class, AllowMultiple = false)] public class DataObjectAttribute: Attribute { private string mKeyS=string.Empty; /// <summary> /// 构造函数。 /// </summary> /// <param name="table">对象映射的数据表名。</param> public DataObjectAttribute(string table,string KeyS) { this.table = table; this.mKeyS=KeyS; } private string table; /// <summary> /// 对象映射到数据库上的表。 /// </summary> public string Table { get { return table; } set { table = value; } } public string KeyS { get { return mKeyS; } set { mKeyS = value; } } } /// <summary> /// FieldObjectAttribute 定义字段类的映射配置的属性类。DataObjectAttribute是一个类级别的属性。 /// </summary> [AttributeUsage(AttributeTargets.Field, AllowMultiple = false)] public class FieldObjectAttribute: Attribute { /// <summary> /// 字段类型 /// </summary> public enum FieldType {GUDI=0,INDEX=1} private int mFieldType; /// <summary> /// 构造函数。 /// </summary> /// <param name="table">对象映射的数据表名。</param> public FieldObjectAttribute(int Tyte) { mFieldType=Tyte.GetHashCode(); } /// <summary> /// 字段类型 /// </summary> public int Type { get { return mFieldType; } set { mFieldType = value; } } }

  需要的对数据库表和字段的简单表示我们都有了，下一步来实现对Sql Server 数据表的，Select ,Update,Insert 等操作,ORM映射实现类。

  public class DataAccess { private static string mConnectStr=string.Empty; private static System.Data.SqlClient.SqlConnection mSqlConnect; //连接 private static System.Data.SqlClient.SqlCommand mSqlCmd; // 执行命令 private static System.Data.SqlClient.SqlDataAdapter mDataAdapter; //装配件 private const string mConnectKey="TestConnect"; // 数据库连接字符串键名 public DataAccess() { try { // // TODO: 在此处添加构造函数逻辑 // mConnectStr=GetConnectValue(); mSqlConnect= new SqlConnection(mConnectStr); mSqlConnect.Open(); } catch { mSqlConnect= new SqlConnection(mConnectStr); } } public void Close() { if (mSqlConnect != null && mSqlConnect.State != ConnectionState.Closed) { mSqlConnect.Close(); } } /// <summary> /// 初始化数据连接对象 /// </summary> /// <param name="ConnectStr"></param> public DataAccess(string ConnectStr) { // // TODO: 在此处添加构造函数逻辑 // mConnectStr=ConnectStr; mSqlConnect= new SqlConnection(mConnectStr); mSqlConnect.Open(); } /// <summary> /// 获得连接字符串的值 /// </summary> /// <returns></returns> private string GetConnectValue() { return System.Configuration.ConfigurationSettings.AppSettings[mConnectKey].ToString(); } private DataMessageClass.DataMessage CheckOpenState() { if(mSqlConnect.State==System.Data.ConnectionState.Broken || mSqlConnect.State==System.Data.ConnectionState.Closed) { mSqlConnect.Close(); mSqlConnect.ConnectionString = mConnectStr; try { lock(mSqlConnect) { mSqlConnect.Open(); } } catch(System.Exception ex) { return DataMessageClass.DataMessage.数据连接失败; } } if(mSqlConnect.State==System.Data.ConnectionState.Executing || mSqlConnect.State==System.Data.ConnectionState.Fetching || mSqlConnect.State==System.Data.ConnectionState.Connecting) { return DataMessageClass.DataMessage.系统忙请等待; } return DataMessageClass.DataMessage.可继续操作; } /// <summary> /// 执行Sql语句 带参数 /// </summary> /// <param name="ExeSqlStr">语句</param> /// <param name="ExeSqlParameter">参数</param> /// <returns></returns> public DataMessageClass.DataMessage ExeSQL(string ExeSqlStr,System.Data.SqlClient.SqlParameter[] ExeSqlParameter) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect); foreach(System.Data.SqlClient.SqlParameter tParameter in ExeSqlParameter) { if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);} } mSqlCmd.ExecuteNonQuery(); } return DataMessageClass.DataMessage.操作成功; } /// <summary> /// 执行Sql语句 不带参数 /// </summary> /// <param name="ExeSqlStr">语句</param> /// <returns></returns> public DataMessageClass.DataMessage ExeSQL(string ExeSqlStr) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect); mSqlCmd.ExecuteNonQuery(); } return DataMessageClass.DataMessage.操作成功; } /// <summary> /// 执行Sql语句 不带参数 返回一个值 /// </summary> /// <param name="ExeSqlStr">语句</param> /// <returns></returns> public object ExeSQLReturnValue(string ExeSqlStr,System.Data.SqlClient.SqlParameter[] ExeSqlParameter) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect); foreach(System.Data.SqlClient.SqlParameter tParameter in ExeSqlParameter) { if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);}; } return mSqlCmd.ExecuteScalar(); } return null; } /// <summary> /// 执行Sql语句 不带参数 返回一个值 /// </summary> /// <param name="ExeSqlStr">语句</param> /// <returns></returns> public object ExeSQLReturnValue(string ExeSqlStr) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect); return mSqlCmd.ExecuteScalar(); } return null; } /// <summary> /// 获得查询表记录 带参数 /// </summary> /// <param name="SelSqlStr">语句</param> /// <param name="SelSqlParameter">参数</param> /// <returns>数据集合</returns> public System.Data.DataSet GetSelectRow(string SelSqlStr,System.Data.SqlClient.SqlParameter[] SelSqlParameter) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect); foreach(System.Data.SqlClient.SqlParameter tParameter in SelSqlParameter) { if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);}; } // 装入数据 System.Data.DataSet TempDataSet = new DataSet(); mDataAdapter=new SqlDataAdapter(mSqlCmd); mDataAdapter.Fill(TempDataSet); return TempDataSet; } return null; } /// <summary> /// 获得查询表记录 不带参数 /// </summary> /// <param name="SelSqlStr">语句</param> /// <returns>set数据集合</returns> public System.Data.DataSet GetSelectRow(string SelSqlStr) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect); // 装入数据 System.Data.DataSet TempDataSet = new DataSet(); mDataAdapter=new SqlDataAdapter(mSqlCmd); mDataAdapter.Fill(TempDataSet); return TempDataSet; } return null; } /// <summary> /// 获得查询表记录 不带参数 /// </summary> /// <param name="SelSqlStr">语句</param> /// <returns>Reader数据集合</returns> public System.Data.SqlClient.SqlDataReader GetSelectRowReader(string SelSqlStr) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect); // 装入数据 System.Data.SqlClient.SqlDataReader TDataReader; TDataReader=mSqlCmd.ExecuteReader(); return TDataReader; } return null; } /// <summary> /// 分页读取 /// </summary> /// <param name="SqlStr">语句</param> /// <param name="SelSqlParameter">参数</param> /// <param name="DataSetName">名称</param> /// <param name="PageIndex">当前页面</param> /// <param name="MaxReocrd">记录数</param> /// <returns></returns> public System.Data.DataSet GetPageDataSet(string SqlStr,System.Data.SqlClient.SqlParameter[] SelSqlParameter,string DataSetName,int PageIndex,int MaxReocrd) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(SqlStr,mSqlConnect); foreach(System.Data.SqlClient.SqlParameter tParameter in SelSqlParameter) { if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);}; } // 装入数据 System.Data.DataSet TempDataSet = new DataSet(); mDataAdapter=new SqlDataAdapter(mSqlCmd); mDataAdapter.Fill(TempDataSet,(PageIndex-1) * MaxReocrd, MaxReocrd,DataSetName); return TempDataSet; } return null; } /// <summary> /// 分页读取 不带参数 /// </summary> /// <param name="SqlStr">语句</param> /// <param name="SelSqlParameter">参数</param> /// <param name="DataSetName">名称</param> /// <param name="PageIndex">当前页面</param> /// <param name="MaxReocrd">记录数</param> /// <returns></returns> public System.Data.DataSet GetPageDataSet(string SqlStr,string DataSetName,int PageIndex,int MaxReocrd) { DataMessageClass.DataMessage tDataMessage; tDataMessage=CheckOpenState(); if (tDataMessage==DataMessageClass.DataMessage.可继续操作) { mSqlCmd=new SqlCommand(SqlStr,mSqlConnect); // 装入数据 System.Data.DataSet TempDataSet = new DataSet(); mDataAdapter=new SqlDataAdapter(mSqlCmd); mDataAdapter.Fill(TempDataSet,(PageIndex-1) * MaxReocrd, MaxReocrd,DataSetName); return TempDataSet; } return null; } /// <summary> /// 获得一个对象 /// </summary> /// <param name="TDataBase"></param> public IDataBase GetOnlyObject(IDataBase TDataBase) { // 生成条件 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute; string[] KeyS= TDataObject.KeyS.Split(new char[]{','}); string TableName =TDataObject.Table; System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[KeyS.Length]; System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields(); string SelSql="Select * From " + TableName + " "; for (byte index=0; index<KeyS.Length;index++) { if (index==0) SelSql += " Where " + KeyS[index] + " = @" + KeyS[index] + " " ; else SelSql += " and " + KeyS[index] + " = @" + KeyS[index] + " " ; TSqlParameter[index]=new SqlParameter("@" + KeyS[index] ,TDataBase.GetType().GetField(KeyS[index]).GetValue(TDataBase)); } // 得到记录 System.Data.DataSet TDataSet= new DataSet(); TDataSet=GetSelectRow(SelSql,TSqlParameter); if (TDataSet.Tables[0].Rows.Count ==0) {return null;} // 把数据分配到字段 foreach (System.Reflection.FieldInfo TFieldInfo in mFieldInfo) { TDataBase.GetType().GetField(TFieldInfo.Name).SetValue(TDataBase,TDataSet.Tables[0].Rows[0][TFieldInfo.Name]==System.DBNull.Value ? GetTypeValue(TFieldInfo.FieldType) :TDataSet.Tables[0].Rows[0][TFieldInfo.Name]); } return TDataBase; } /// <summary> /// 得到初始值 /// </summary> /// <param name="TType"></param> /// <returns></returns> private object GetTypeValue(System.Type TType) { if(TType.FullName.Equals(System.TypeCode.DateTime.GetType())) { return System.DateTime.Now; } return null; } /// <summary> /// 添加一个对象 /// </summary> /// <param name="TDataBase"></param> public object AddOnlyObjectReturnIndex(IDataBase TDataBase) { DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute; string[] KeyS= TDataObject.KeyS.Split(new char[]{','}); string TableName =TDataObject.Table; System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields(); System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length]; string SelSql="insert into " + TableName + " "; string FieldName =string.Empty; string ValueName =string.Empty; for (byte index=0 ; index <mFieldInfo.Length ; index ++) { if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0) { FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute; if (TFieldObject.Type==0) { FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ; ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ; TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,System.Guid.NewGuid().ToString()); } else if (TFieldObject.Type==1) { // 此字段为自增列放弃 } } else { FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ; ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ; TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase)); } } SelSql += " (" + FieldName + ") VALUES (" + ValueName + ") SELECT @@IDENTITY AS 'Identity'"; return ExeSQLReturnValue(SelSql,TSqlParameter); } /// <summary> /// 添加一个对象 /// </summary> /// <param name="TDataBase"></param> public void AddOnlyObject(IDataBase TDataBase) { DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute; string[] KeyS= TDataObject.KeyS.Split(new char[]{','}); string TableName =TDataObject.Table; System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields(); System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length]; string SelSql="insert into " + TableName + " "; string FieldName =string.Empty; string ValueName =string.Empty; for (byte index=0 ; index <mFieldInfo.Length ; index ++) { if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0) { FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute; if (TFieldObject.Type==0) { FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ; ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ; TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,System.Guid.NewGuid().ToString()); } else if (TFieldObject.Type==1) { // 此字段为自增列放弃 } } else { FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ; ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ; TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase)); } } SelSql += " (" + FieldName + ") VALUES (" + ValueName + ")"; ExeSQL(SelSql,TSqlParameter); } /// <summary> /// 更新一个对象 /// </summary> /// <param name="TDataBase"></param> public void UpdateOnlyObject(IDataBase TDataBase) { DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute; string[] KeyS= TDataObject.KeyS.Split(new char[]{','}); Array TArray = Array.CreateInstance(typeof(string),KeyS.Length); KeyS.CopyTo(TArray,0); string TableName =TDataObject.Table; System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields(); System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length]; string SelSql="Update " + TableName + " Set "; string FieldValueName =string.Empty; string WhereName = string.Empty; for (byte index=0 ; index <mFieldInfo.Length ; index ++) { if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0) { FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute; if (TFieldObject.Type==1) { TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase)); } } else { if(Array.IndexOf(TArray,mFieldInfo[index].Name)==-1) { FieldValueName += FieldValueName.Length > 0 ? ", " + mFieldInfo[index].Name + "=@" + mFieldInfo[index].Name : mFieldInfo[index].Name + "=@" + mFieldInfo[index].Name; } TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase)); } } for (byte index=0; index<KeyS.Length;index++) { WhereName += WhereName.Length>0 ? " and " + KeyS[index] + " = @" + KeyS[index] :" Where " + KeyS[index] + " = @" + KeyS[index] ; } SelSql += FieldValueName + WhereName; ExeSQL(SelSql,TSqlParameter); } /// <summary> /// 删除一个对象 /// </summary> /// <param name="TDataBase"></param> public void DelOnlyObject(IDataBase TDataBase) { DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute; string[] KeyS= TDataObject.KeyS.Split(new char[]{','}); string TableName =TDataObject.Table; System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields(); System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[KeyS.Length]; string SelSql="delete " + TableName ; string FieldValueName =string.Empty; for (byte index=0; index<KeyS.Length;index++) { FieldValueName += FieldValueName.Length>0 ? " and " + KeyS[index] + " = @" + KeyS[index] :" Where " + KeyS[index] + " = @" + KeyS[index] ; TSqlParameter[index]=new SqlParameter("@" + KeyS[index] ,TDataBase.GetType().GetField(KeyS[index]).GetValue(TDataBase)); } SelSql+=FieldValueName; ExeSQL(SelSql,TSqlParameter); } }

 

  实现就这么简单了，让我们测试其使用过程。

  第一步：定义数据映射实体

  /// <summary> /// "UserInfo" 标示目标表名 /// "Guid" 代表主键， 可以通过逗号分隔来指定多个主键 /// </summary> [DataAccess.DataObject("UserInfo", "Guid")] public class UserInfo : IDataBase { /// <summary> /// FieldObject(0) 标示GUID 类型的主键，FieldObject(1) 标示自增性主键 /// </summary> [FieldObject(0)] public string Guid = string.Empty; public string UserName = string.Empty; public string UserEMail = string.Empty; public string UserTel = string.Empty; public string UserSex = string.Empty; public string UserDec = string.Empty; public string RegIpAddres = string.Empty; }

 

 第二步：配置连接字符串

   <appSettings>
    <add key ="ORMConnect" value ="Data Source=???.??.??.???,1433;Initial Catalog=?????;Persist Security Info=True;User ID=?????;Password=??????"/>
  </appSettings >

 

 

  第三步：测试对数据表的操作

   DataAccess.DataAccess ormAccess= new DataAccess.DataAccess();

       UserInfo info = new UserInfo();
            info.UserName = "TestUser" ;     

            info.UserDec = http://www.apace.com.cn; 

            info.UserEMail = G_Jack@126.com;
            info.UserTel = "9988996";
            info.RegIpAddres = “testip”;

    //测试新增

    ormAccess.AddOnlyObject(info);

    //测试更新

   ormAccess.UpdateOnlyObject(info)

   //测试删除

   ormAccess.DelOnlyObject(info)

 

 对ORM的简单实现就到处结束了，希望能帮到大家