首页 > 代码库 > C# ORM简单原理

C# ORM简单原理

2024-07-15 02:47:36   227人阅读

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

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

 

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

 

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

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

 

 

[c-sharp] view plaincopyprint?
  1. public class DataMessageClass
  2. {
  3. public enum DataMessage
  4. {
  5. 数据连接失败,系统忙请等待,操作成功,可继续操作
  6. }
  7. }

标记实体映射基类

 

[c-sharp] view plaincopyprint?
  1. /// <summary>
  2. /// DataBase 的摘要说明。
  3. /// </summary>
  4. public interface IDataBase
  5. {
  7. }  

 

 

 

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

 

 

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

  6 public class DataAccess  7 {  8 private static string mConnectStr=string.Empty;  9 private static System.Data.SqlClient.SqlConnection mSqlConnect; //连接 10 private static System.Data.SqlClient.SqlCommand mSqlCmd; // 执行命令 11 private static System.Data.SqlClient.SqlDataAdapter mDataAdapter; //装配件 12 private const string mConnectKey="TestConnect"; // 数据库连接字符串键名 13 public DataAccess() 14 { 15 try 16 { 17 // 18 // TODO: 在此处添加构造函数逻辑 19 // 20 mConnectStr=GetConnectValue(); 21 mSqlConnect= new SqlConnection(mConnectStr); 22 mSqlConnect.Open(); 23 } 24 catch 25 { 26 mSqlConnect= new SqlConnection(mConnectStr); 27 } 28 } 29 public void Close() 30 { 31 if (mSqlConnect != null && mSqlConnect.State != ConnectionState.Closed) 32 { 33 mSqlConnect.Close(); 34 } 35 } 36 /// <summary> 37 /// 初始化数据连接对象 38 /// </summary> 39 /// <param name="ConnectStr"></param> 40 public DataAccess(string ConnectStr) 41 { 42 // 43 // TODO: 在此处添加构造函数逻辑 44 // 45 mConnectStr=ConnectStr; 46 mSqlConnect= new SqlConnection(mConnectStr); 47 mSqlConnect.Open(); 48 } 49 /// <summary> 50 /// 获得连接字符串的值 51 /// </summary> 52 /// <returns></returns> 53 private string GetConnectValue() 54 { 55 return System.Configuration.ConfigurationSettings.AppSettings[mConnectKey].ToString(); 56 } 57 private DataMessageClass.DataMessage CheckOpenState() 58 { 59 if(mSqlConnect.State==System.Data.ConnectionState.Broken || mSqlConnect.State==System.Data.ConnectionState.Closed) 60 { 61 mSqlConnect.Close(); 62 mSqlConnect.ConnectionString = mConnectStr; 63 try 64 { 65 lock(mSqlConnect) 66 { 67 mSqlConnect.Open(); 68 } 69 } 70 catch(System.Exception ex) 71 { 72 return DataMessageClass.DataMessage.数据连接失败; 73 } 74 } 75 if(mSqlConnect.State==System.Data.ConnectionState.Executing || mSqlConnect.State==System.Data.ConnectionState.Fetching || mSqlConnect.State==System.Data.ConnectionState.Connecting) 76 { 77 return DataMessageClass.DataMessage.系统忙请等待; 78 } 79 return DataMessageClass.DataMessage.可继续操作; 80 } 81 /// <summary> 82 /// 执行Sql语句 带参数 83 /// </summary> 84 /// <param name="ExeSqlStr">语句</param> 85 /// <param name="ExeSqlParameter">参数</param> 86 /// <returns></returns> 87 public DataMessageClass.DataMessage ExeSQL(string ExeSqlStr,System.Data.SqlClient.SqlParameter[] ExeSqlParameter) 88 { 89 DataMessageClass.DataMessage tDataMessage; 90 tDataMessage=CheckOpenState(); 91 if (tDataMessage==DataMessageClass.DataMessage.可继续操作) 92 { 93 mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect); 94 foreach(System.Data.SqlClient.SqlParameter tParameter in ExeSqlParameter) 95 { 96 if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);} 97 } 98 mSqlCmd.ExecuteNonQuery(); 99 }100 return DataMessageClass.DataMessage.操作成功;101 }102 /// <summary>103 /// 执行Sql语句 不带参数104 /// </summary>105 /// <param name="ExeSqlStr">语句</param>106 /// <returns></returns>107 public DataMessageClass.DataMessage ExeSQL(string ExeSqlStr)108 {109 DataMessageClass.DataMessage tDataMessage;110 tDataMessage=CheckOpenState();111 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)112 {113 mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect);114 mSqlCmd.ExecuteNonQuery();115 }116 return DataMessageClass.DataMessage.操作成功;117 }118 /// <summary>119 /// 执行Sql语句 不带参数 返回一个值120 /// </summary>121 /// <param name="ExeSqlStr">语句</param>122 /// <returns></returns>123 public object ExeSQLReturnValue(string ExeSqlStr,System.Data.SqlClient.SqlParameter[] ExeSqlParameter)124 {125 DataMessageClass.DataMessage tDataMessage;126 tDataMessage=CheckOpenState();127 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)128 {129 mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect);130 foreach(System.Data.SqlClient.SqlParameter tParameter in ExeSqlParameter)131 {132 if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);};133 }134 return mSqlCmd.ExecuteScalar();135 }136 return null;137 }138 /// <summary>139 /// 执行Sql语句 不带参数 返回一个值140 /// </summary>141 /// <param name="ExeSqlStr">语句</param>142 /// <returns></returns>143 public object ExeSQLReturnValue(string ExeSqlStr)144 {145 DataMessageClass.DataMessage tDataMessage;146 tDataMessage=CheckOpenState();147 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)148 {149 mSqlCmd=new SqlCommand(ExeSqlStr,mSqlConnect);150 return mSqlCmd.ExecuteScalar();151 }152 return null;153 }154 /// <summary>155 /// 获得查询表记录 带参数156 /// </summary>157 /// <param name="SelSqlStr">语句</param>158 /// <param name="SelSqlParameter">参数</param>159 /// <returns>数据集合</returns>160 public System.Data.DataSet GetSelectRow(string SelSqlStr,System.Data.SqlClient.SqlParameter[] SelSqlParameter)161 {162 DataMessageClass.DataMessage tDataMessage;163 tDataMessage=CheckOpenState();164 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)165 {166 mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect);167 foreach(System.Data.SqlClient.SqlParameter tParameter in SelSqlParameter)168 {169 if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);};170 }171 // 装入数据172 System.Data.DataSet TempDataSet = new DataSet();173 mDataAdapter=new SqlDataAdapter(mSqlCmd);174 mDataAdapter.Fill(TempDataSet);175 return TempDataSet;176 }177 return null;178 }179 /// <summary>180 /// 获得查询表记录 不带参数181 /// </summary>182 /// <param name="SelSqlStr">语句</param>183 /// <returns>set数据集合</returns>184 public System.Data.DataSet GetSelectRow(string SelSqlStr)185 {186 DataMessageClass.DataMessage tDataMessage;187 tDataMessage=CheckOpenState();188 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)189 {190 mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect);191 // 装入数据192 System.Data.DataSet TempDataSet = new DataSet();193 mDataAdapter=new SqlDataAdapter(mSqlCmd);194 mDataAdapter.Fill(TempDataSet);195 return TempDataSet;196 }197 return null;198 }199 /// <summary>200 /// 获得查询表记录 不带参数201 /// </summary>202 /// <param name="SelSqlStr">语句</param>203 /// <returns>Reader数据集合</returns>204 public System.Data.SqlClient.SqlDataReader GetSelectRowReader(string SelSqlStr)205 {206 DataMessageClass.DataMessage tDataMessage;207 tDataMessage=CheckOpenState();208 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)209 {210 mSqlCmd=new SqlCommand(SelSqlStr,mSqlConnect);211 // 装入数据212 System.Data.SqlClient.SqlDataReader TDataReader;213 TDataReader=mSqlCmd.ExecuteReader();214 return TDataReader;215 }216 return null;217 }218 /// <summary>219 /// 分页读取220 /// </summary>221 /// <param name="SqlStr">语句</param>222 /// <param name="SelSqlParameter">参数</param>223 /// <param name="DataSetName">名称</param>224 /// <param name="PageIndex">当前页面</param>225 /// <param name="MaxReocrd">记录数</param>226 /// <returns></returns>227 public System.Data.DataSet GetPageDataSet(string SqlStr,System.Data.SqlClient.SqlParameter[] SelSqlParameter,string DataSetName,int PageIndex,int MaxReocrd)228 {229 DataMessageClass.DataMessage tDataMessage;230 tDataMessage=CheckOpenState();231 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)232 {233 mSqlCmd=new SqlCommand(SqlStr,mSqlConnect);234 foreach(System.Data.SqlClient.SqlParameter tParameter in SelSqlParameter)235 {236 if(tParameter!=null){mSqlCmd.Parameters.Add(tParameter);};237 }238 // 装入数据239 System.Data.DataSet TempDataSet = new DataSet();240 mDataAdapter=new SqlDataAdapter(mSqlCmd);241 mDataAdapter.Fill(TempDataSet,(PageIndex-1) * MaxReocrd, MaxReocrd,DataSetName);242 return TempDataSet;243 }244 return null;245 }246 /// <summary>247 /// 分页读取 不带参数248 /// </summary>249 /// <param name="SqlStr">语句</param>250 /// <param name="SelSqlParameter">参数</param>251 /// <param name="DataSetName">名称</param>252 /// <param name="PageIndex">当前页面</param>253 /// <param name="MaxReocrd">记录数</param>254 /// <returns></returns>255 public System.Data.DataSet GetPageDataSet(string SqlStr,string DataSetName,int PageIndex,int MaxReocrd)256 {257 DataMessageClass.DataMessage tDataMessage;258 tDataMessage=CheckOpenState();259 if (tDataMessage==DataMessageClass.DataMessage.可继续操作)260 {261 mSqlCmd=new SqlCommand(SqlStr,mSqlConnect);262 // 装入数据263 System.Data.DataSet TempDataSet = new DataSet();264 mDataAdapter=new SqlDataAdapter(mSqlCmd);265 mDataAdapter.Fill(TempDataSet,(PageIndex-1) * MaxReocrd, MaxReocrd,DataSetName);266 return TempDataSet;267 }268 return null;269 }270 /// <summary>271 /// 获得一个对象272 /// </summary>273 /// <param name="TDataBase"></param>274 public IDataBase GetOnlyObject(IDataBase TDataBase)275 {276 // 生成条件277 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute;278 string[] KeyS= TDataObject.KeyS.Split(new char[]{‘,‘});279 string TableName =TDataObject.Table;280 System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[KeyS.Length];281 System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields();282 string SelSql="Select * From " + TableName + " ";283 for (byte index=0; index<KeyS.Length;index++)284 {285 if (index==0)286 SelSql += " Where " + KeyS[index] + " = @" + KeyS[index] + " " ;287 else288 SelSql += " and " + KeyS[index] + " = @" + KeyS[index] + " " ;289 TSqlParameter[index]=new SqlParameter("@" + KeyS[index] ,TDataBase.GetType().GetField(KeyS[index]).GetValue(TDataBase));290 }291 // 得到记录292 System.Data.DataSet TDataSet= new DataSet();293 TDataSet=GetSelectRow(SelSql,TSqlParameter);294 if (TDataSet.Tables[0].Rows.Count ==0) {return null;}295 // 把数据分配到字段296 foreach (System.Reflection.FieldInfo TFieldInfo in mFieldInfo)297 {298 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]);299 }300 return TDataBase;301 }302 /// <summary>303 /// 得到初始值304 /// </summary>305 /// <param name="TType"></param>306 /// <returns></returns>307 private object GetTypeValue(System.Type TType)308 {309 if(TType.FullName.Equals(System.TypeCode.DateTime.GetType()))310 {311 return System.DateTime.Now;312 }313 return null;314 }315 /// <summary>316 /// 添加一个对象317 /// </summary>318 /// <param name="TDataBase"></param>319 public object AddOnlyObjectReturnIndex(IDataBase TDataBase)320 {321 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute;322 string[] KeyS= TDataObject.KeyS.Split(new char[]{‘,‘});323 string TableName =TDataObject.Table;324 System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields();325 System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length];326 string SelSql="insert into " + TableName + " ";327 string FieldName =string.Empty;328 string ValueName =string.Empty;329 for (byte index=0 ; index <mFieldInfo.Length ; index ++)330 {331 if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0)332 {333 FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute;334 if (TFieldObject.Type==0)335 {336 FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ;337 ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ;338 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,System.Guid.NewGuid().ToString());339 }340 else if (TFieldObject.Type==1)341 {342 // 此字段为自增列放弃343 }344 }345 else346 {347 FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ;348 ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ;349 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase));350 }351 }352 SelSql += " (" + FieldName + ") VALUES (" + ValueName + ") SELECT @@IDENTITY AS ‘Identity‘";353 return ExeSQLReturnValue(SelSql,TSqlParameter);354 }355 /// <summary>356 /// 添加一个对象357 /// </summary>358 /// <param name="TDataBase"></param>359 public void AddOnlyObject(IDataBase TDataBase)360 {361 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute;362 string[] KeyS= TDataObject.KeyS.Split(new char[]{‘,‘});363 string TableName =TDataObject.Table;364 System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields();365 System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length];366 string SelSql="insert into " + TableName + " ";367 string FieldName =string.Empty;368 string ValueName =string.Empty;369 for (byte index=0 ; index <mFieldInfo.Length ; index ++)370 {371 if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0)372 {373 FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute;374 if (TFieldObject.Type==0)375 {376 FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ;377 ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ;378 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,System.Guid.NewGuid().ToString());379 }380 else if (TFieldObject.Type==1)381 {382 // 此字段为自增列放弃383 }384 }385 else386 {387 FieldName += FieldName.Length > 0 ? "," + mFieldInfo[index].Name : mFieldInfo[index].Name ;388 ValueName += ValueName.Length > 0 ? "," + "@" + mFieldInfo[index].Name : "@" + mFieldInfo[index].Name ;389 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase));390 }391 }392 SelSql += " (" + FieldName + ") VALUES (" + ValueName + ")";393 ExeSQL(SelSql,TSqlParameter);394 }395 /// <summary>396 /// 更新一个对象397 /// </summary>398 /// <param name="TDataBase"></param>399 public void UpdateOnlyObject(IDataBase TDataBase)400 {401 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute;402 string[] KeyS= TDataObject.KeyS.Split(new char[]{‘,‘});403 Array TArray = Array.CreateInstance(typeof(string),KeyS.Length);404 KeyS.CopyTo(TArray,0);405 string TableName =TDataObject.Table;406 System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields();407 System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[mFieldInfo.Length];408 string SelSql="Update " + TableName + " Set ";409 string FieldValueName =string.Empty;410 string WhereName = string.Empty;411 for (byte index=0 ; index <mFieldInfo.Length ; index ++)412 {413 if(mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false).Length!=0)414 {415 FieldObjectAttribute TFieldObject =mFieldInfo[index].GetCustomAttributes(typeof(FieldObjectAttribute),false)[0] as FieldObjectAttribute;416 if (TFieldObject.Type==1)417 {418 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase));419 }420 }421 else422 {423 if(Array.IndexOf(TArray,mFieldInfo[index].Name)==-1)424 {425 FieldValueName += FieldValueName.Length > 0 ? ", " + mFieldInfo[index].Name + "=@" + mFieldInfo[index].Name : mFieldInfo[index].Name + "=@" + mFieldInfo[index].Name;426 }427 TSqlParameter[index]=new SqlParameter("@" + mFieldInfo[index].Name ,TDataBase.GetType().GetField(mFieldInfo[index].Name).GetValue(TDataBase));428 }429 }430 for (byte index=0; index<KeyS.Length;index++)431 {432 WhereName += WhereName.Length>0 ? " and " + KeyS[index] + " = @" + KeyS[index] :" Where " + KeyS[index] + " = @" + KeyS[index] ;433 }434 SelSql += FieldValueName + WhereName;435 ExeSQL(SelSql,TSqlParameter);436 }437 /// <summary>438 /// 删除一个对象439 /// </summary>440 /// <param name="TDataBase"></param>441 public void DelOnlyObject(IDataBase TDataBase)442 {443 DataObjectAttribute TDataObject =TDataBase.GetType().GetCustomAttributes(typeof(DataObjectAttribute),false)[0] as DataObjectAttribute;444 string[] KeyS= TDataObject.KeyS.Split(new char[]{‘,‘});445 string TableName =TDataObject.Table;446 System.Reflection.FieldInfo[] mFieldInfo=TDataBase.GetType().GetFields();447 System.Data.SqlClient.SqlParameter[] TSqlParameter=new SqlParameter[KeyS.Length];448 string SelSql="delete " + TableName ;449 string FieldValueName =string.Empty;450 for (byte index=0; index<KeyS.Length;index++)451 {452 FieldValueName += FieldValueName.Length>0 ? " and " + KeyS[index] + " = @" + KeyS[index] :" Where " + KeyS[index] + " = @" + KeyS[index] ;453 TSqlParameter[index]=new SqlParameter("@" + KeyS[index] ,TDataBase.GetType().GetField(KeyS[index]).GetValue(TDataBase));454 }455 SelSql+=FieldValueName;456 ExeSQL(SelSql,TSqlParameter);457 }458 }

 

 

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

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

 

 1 /// <summary> 2 /// "UserInfo" 标示目标表名 3 /// "Guid" 代表主键， 可以通过逗号分隔来指定多个主键 4 /// </summary> 5 [DataAccess.DataObject("UserInfo", "Guid")] 6 public class UserInfo : IDataBase 7 { 8 /// <summary> 9 /// FieldObject(0) 标示GUID 类型的主键，FieldObject(1) 标示自增性主键10 /// </summary>11 [FieldObject(0)]12 public string Guid = string.Empty;13 public string UserName = string.Empty;14 public string UserEMail = string.Empty;15 public string UserTel = string.Empty;16 public string UserSex = string.Empty;17 public string UserDec = string.Empty;18 public string RegIpAddres = string.Empty;19 }

第二步：配置连接字符串

1 <appSettings>2 <add key ="ORMConnect" value =http://www.mamicode.com/"Data Source=???.??.??.???,1433;Initial Catalog=?????;Persist Security Info=True;User ID=?????;Password=??????"/>3 </appSettings >

 

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

 1 [c-sharp] view plaincopyprint? 2  3 DataAccess.DataAccess ormAccess= new DataAccess.DataAccess(); 4  5 UserInfo info = new UserInfo(); 6 info.UserName = "TestUser" ; 7  8 info.UserDec = http://www.apace.com.cn; 9 10 info.UserEMail = G_Jack@126.com;11 info.UserTel = "9988996";12 info.RegIpAddres = “testip”;13 14 //测试新增15 16  17 18 ormAccess.AddOnlyObject(info);19 20 //测试更新21 22 ormAccess.UpdateOnlyObject(info)23 24 //测试删除25 26 ormAccess.DelOnlyObject(info)

 

 

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


联系
我们