Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to understand LINQ To SQL and ORM". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand LINQ To SQL and ORM".

Understanding of LINQ To SQL and ORM 1.

Without the LINQ source code, I don't know how the intermediate data is handled, especially how the cache is handled.

Understanding of LINQ To SQL and ORM 2.

Using LINQ to translate to stored procedures / Sql statements is very inefficient, for complex ones that have certain requirements for efficiency, you still have to write stored procedures. And how to cache the translated stored procedure / Sql statement, it has to be retranslated when it is executed the second time, which can not make good use of the cache.

Understanding of LINQ To SQL and ORM 3. Tool support.

This is very important. Without the support of powerful tools, using ORM is a nightmare. LINQ tool support overall, I don't feel very friendly with support in VS2008 right now. At least one thing, we usually write comments on this field in the comments of the table, and in many cases the header of this field corresponds to the interface, this tool can not be generated directly into the project, because we cannot control it, moreover, the current project is not completed by one person, if a class has dozens of fields, it is difficult to remember to explain, their own tools can add comments to facilitate development.

Understanding of LINQ To SQL and ORM 4, integration with the business layer.

Usually ORM mapping is to form a mapping relationship between objects and relational tables, the goal of mapping is to facilitate the logical processing of the business layer, if it can not be seamlessly combined with the business layer, then there may not be much benefit for the development of the project. At least in the ORM, and my business layer can be very friendly integration together.

Understanding of LINQ To SQL and ORM 5. Support of multiple data sources.

For me, this is also very critical. By multiple data sources, I mean that there are multiple databases in a project, such as basic data, customer management, business operation, rights management, etc., these are relatively independent modules, and when developing, these can be developed as separate projects, each with its own independent database, and in multiple projects, these modules are very similar, such as permissions modules are basically universal. There is no need to repeat development. If you can achieve the separation of the database layer, you can achieve faster speed when developing new projects. Of course, it can also be easy to merge two databases into one, or separate a database into two, only involving changes to the configuration file, which can be ignored during development.

Understanding of LINQ To SQL and ORM 6. Processing of variable queries.

That's the equivalent of concatenating strings. It might be said that this is the strength of LINQ, but what about a distributed project? And how to deal with it. Friends who know ORM should know that IBatis.net and NHibnate, I home parameter processing is very convenient, N home is not as intuitive as I, but for object-oriented processing I seem to have too many deficiencies. For remoting to be able to pass parameters that are only exposed to the mapping layer, this may be critical to some parts of the program. I have a random name in my ORM, called General query. Queries used in business logic are usually written in stored procedures, and functions in the business code can be generated directly through tools.

Understanding of LINQ To SQL and ORM 7. Support and handling of generics and inheritance.

Generics, can reduce a lot of code, at the same time, can also reduce a lot of human errors.

Because people's energy is always limited, if you spend your energy in one place, then you must pay less attention to other aspects, and so do I. Maybe I put too much energy into my ORM, ignoring other aspects, please give criticism and correction. Next, I'll give a detailed introduction to my ORM mapping.

The ORM mapping understood by LINQ To SQL and ORM is mainly divided into the following aspects:

Understanding of LINQ To SQL and ORM 1. Translation of objects and parameters

A) the expression of the object of understanding of LINQ To SQL and ORM. Object mapping, I use the custom attribute method.

Namespace LG.Common.Enities {using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using Unie2e.Common; using Unie2e.ORM; using Unie2e.ORM.Mapping [System.SerializableAttribute ()] [Unie2e.ORM.Mapping.TableAttribute ("City", MapFileName= "CityCustParam.Xml")] public sealed class CityEntity: total length of Unie2e.ORM.Mapping.Entity {/ * * / mapping field / private const int length = 5

Mapping Fields for understanding of LINQ To SQL and ORM

# region mapping field / * * / City Id / [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityId", true, SqlDbType. UniqueIdentifier, 0,0,0, "city Id")] public Guid CityId = System.Guid.NewGuid (); / * / province Id / [Unie2e.ORM.Mapping.FieldAttribute ("City", "ProvinceId", false, SqlDbType.UniqueIdentifier, 0,0,0, "province Id")] public Guid ProvinceId = System.Guid.Empty / * * / City Code / / [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityCode", false, SqlDbType.VarChar, 50,0,0, "City Code")] public String CityCode / * * / City name / / [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityName", false, SqlDbType.VarChar, 50,0,0, "City name")] public String CityName / * * / remarks / / [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityComment", false, SqlDbType.VarChar, 200,0,0, "remarks")] public String CityComment; # endregion

Overloaded base class functions for the understanding of LINQ To SQL and ORM

Public override void Initialize (object [] parameters) {if ((length! = parameters.Length)) {throw new E2EException ("the number of parameters is not equal to the number of fields");} CityId = ((System.Guid) (parameters [0])); ProvinceId = ((System.Guid) (parameters [1])); CityCode = ((string) (parameters [2])); CityName = (string) (parameters [3]); CityComment = (string) (parameters [4])) } public override object [] ToArray () {object [] objs = new object [5]; objs [0] = CityId; objs [1] = ProvinceId; objs [2] = CityCode; objs [3] = CityName; objs [4] = CityComment; return objs } # endregion} [System.SerializableAttribute ()] [Unie2e.ORM.Mapping.TableAttribute ("City")] public abstract class CityFindParam: EntityParam where T: IEntity {/ * * / the total length of the mapped field / private const int length = 6

Mapping Fields for understanding of LINQ To SQL and ORM

[Unie2e.ORM.Mapping.FieldAttribute ("City", "Method", SqlDbType.VarChar, 50,0,0, "")] public String Method; [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityId", SqlDbType.UniqueIdentifier, 0,0,0, ")] public Guid CityId = System.Guid.Empty [Unie2e.ORM.Mapping.FieldAttribute ("City", "ProvinceId", SqlDbType.UniqueIdentifier, 0,0,0, ")] public Guid ProvinceId = System.Guid.Empty; [Unie2e.ORM.Mapping.FieldAttribute (" City "," CityCode ", SqlDbType.VarChar, 50,0,0,")] public String CityCode; [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityName", SqlDbType.VarChar, 50,0,0, "")] public String CityName [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityComment", SqlDbType.VarChar, 200,0,0, "")] public String CityComment; # endregion

Overloaded base class functions for the understanding of LINQ To SQL and ORM

Public override object [] ToArray () {object [] objs = new object [6]; objs [0] = Method; objs [1] = CityId; objs [2] = ProvinceId; objs [3] = CityCode; objs [4] = CityName; objs [5] = CityComment; return objs;} public override void Initialize (object [] parameters) {if (length! = parameters.Length) {throw new E2EException ("the number of parameters is not equal to the number of fields") } Method = ((String) (parameters [0])); CityId = ((Guid) (parameters [1])); ProvinceId = ((Guid) (parameters [2])); CityCode = ((String) (parameters [3])); CityName = ((String) (parameters [4])); CityComment = ((String) (parameters [5]) } # endregion} [System.SerializableAttribute ()] [Unie2e.ORM.Mapping.TableAttribute ("City")] public sealed class CityFindParam: CityFindParam {} [System.SerializableAttribute ()] [Unie2e.ORM.Mapping.TableAttribute ("City")] public abstract class CityActionParam: EntityParam where T: total length of IEntity {/ * * / mapping field / private const int length = 2 Mapping field # region Mapping Field [Unie2e.ORM.Mapping.FieldAttribute ("City", "Method", SqlDbType.VarChar, 50,0,0, "")] public String Method; [Unie2e.ORM.Mapping.FieldAttribute ("City", "CityId", SqlDbType.UniqueIdentifier, 0,0,0, "")] public Guid CityId = System.Guid.Empty; # endregion

Overloaded base class functions for the understanding of LINQ To SQL and ORM

Public override object [] ToArray () {object [] objs = new object [2]; objs [0] = Method; objs [1] = CityId; return objs;} public override void Initialize (object [] parameters) {if ((length! = parameters.Length)) {throw new E2EException ("the number of parameters is not equal to the number of fields");} Method = ((String) (parameters [0])); CityId = (Guid) (parameters [1]) } # endregion} [System.SerializableAttribute ()] [Unie2e.ORM.Mapping.TableAttribute ("City")] public sealed class CityActionParam: CityActionParam {}} [Unie2e.ORM.Mapping.TableAttribute ("City", MapFileName= "CityCustParam.Xml")]

The relationship between the mapping object and the database table or view is given, and the configuration file of the corresponding general query is given by MapFileName. Of course, the Sql statement can also be written in the configuration file to achieve other actions besides the query. Because the tool does not have support for other code-generating functions, it is usually handled through the tool to generate stored procedures.

In Entity, there are two overloaded functions: public override void Initialize (object [] parameters) and public override object [] ToArray (), which are designed to speed up data loading. The bottom layer uses DataReader to read data and load data as little as possible from the database to objects, because the code is generated by tools and there are no errors. It is important to pay attention to a little when using tools to write stored procedures.

[Unie2e.ORM.Mapping.FieldAttribute ("City", "CityId", true, SqlDbType.UniqueIdentifier, 0,0,0, "City Id")]

The mapping relationship between the object Field and the columns of the table or view is given. * for annotations, Summary will be generated for the business data of BO. The representation of the interface is eventually generated, as well as the header of the configuration file of the list.

In addition to Entity, there are CityFindParam, CityFindParam,CityActionParam,CityActionParam these classes, CityFindParam is mainly to achieve variable return data, the default is to return the complete table data, sometimes, the database table or view of many fields, more important places, may only be required to return some of the fields, used in this area of processing. CityActionParam is mainly used to optimize the performance of individual places for special processing such as batch updates.

B) the target translation of LINQ To SQL and ORM

Object translation is a call that translates object properties into corresponding stored procedures. My ORM mapping is mainly about object parameter processing. Adding, deleting, and modifying are all mappings to complete objects, and special processing is handled through Action.

SqlParameter [] BuilderSqlParameters (Type type,SQLActionType actionType) {FieldInfo [] infos = type.GetFields (); SqlParameter [] parameters = new SqlParameter [infos.Length]; for (int I = 0; I

< infos.Length; i++) { object[] objs = infos[i].GetCustomAttributes(typeof(FieldAttribute), false); FieldAttribute attr = objs[0] as FieldAttribute; SqlParameter sp; if (attr.Length != 0) sp = new SqlParameter("@" + attr.ColumnName, attr.SqlDbType, attr.Length); else { sp = new SqlParameter("@" + attr.ColumnName, attr.SqlDbType); } if (attr.IsKey&&actionType== SQLActionType .Insert) sp.Direction = ParameterDirection.InputOutput; parameters[i]=sp; } } return parameters; } ***次执行的时候，遍历所有字段，根据FieldAttribute 生成调用存储过程的Command，并缓存，第二次执行的时候，直接从缓存里取出Command 的Clone（）。此处调用Clone ，返回的是Command的DeepCopy！！！也是微软在2.0里面新添加的函数，我估计新加的函数的用处也就是在这里了，极大的提高了处理速度。接下来就是存储过程的赋值了。 void AssignParameterValues(object instance, SqlCommand cmd) { SqlParameterCollection sps = cmd.Parameters; IEntity idb =instance as IEntity; if (idb == null) throw new Exception("存储过程赋值出错,要具有IEntity接口"); object[] values = idb.ToArray(); if (values.Length != sps.Count ) throw new Exception("参数个数不一致"); for (int i = 0; i < values.Length; i++) { object val = values[i]; //对于时间的空值等，这里还会报错。一般要求要有初始化。 sps[i].Value =null == val ？System.DBNull.Value:val; } } 刚刚介绍了在Entity和ParamEntity里面有两个重载的虚函数函数，其中一个是：ToArray（），在这里用到了。如果是通过反射得到实例的值，那效率实在是太低了，这里用了IEntity的ToArray（），因为映射的顺序位置都是固定的，所以可以通过数组的方式实现。这也是工具的重要的地方，靠手工处理，错误的几率太高了。 现在存储过程是可以正常调用了，正常的 增、改、删，是可以处理了，下面介绍取数据。 c)LINQ To SQL和ORM的理解之数据读取加载 数据加载主要是通过DataReader实现的。 object GetFindResult(IDataReader reader, Type targetType) { Type target = targetType; object[] fields = new object[reader.FieldCount]; IEntity t = （IEntity）Activator.CreateInstance(target); Type constructed = typeof(ORMCollection). MakeGenericType(target); IList list = (IList)Activator.CreateInstance(constructed); while (reader.Read()) { for (int i = 0; i < fields.Length; i++) { fields[i] = reader.IsDBNull(i) ? null : reader[i]; } t.Initialize(fields); list.Add(t.Clone()); } return list; } object GetFindResult(IDataReader reader, Type targetType, int currentpage, int pagesize) 数据加载提供了2个函数，一个是带分页一个是不带分页。IEntity提供的几个接口在这里都用了，也主要是为了这里准备的。也主要是在此实现了数据的快速加载。主要体现在两个上面，一个是t.Initialize(fields)，Initialize 在上面是介绍了，不用通过反射初始化及赋值，再者是 t.Clone()，用的也是DeepCopy，不是用Activator 创建新的对象实例，因为Activator 构造对象的时候速度跟DeepCopy相比，要慢了很多。 还有一个是我自封的通用查询，也就是通过MapFileName 找到配置文件，把Sql配置文件加载进来，翻译成Command加到缓存里面，后期的处理与上面的一样，在这里就不再过多的介绍了，只把关键代码列出来。 string BuildFind(FieldInfo[] pis, FinderParam finderParam, out SqlParameter[] parameters) { ORMCollection items = finderParam.ParamItemCollection; if (items==null) throw new E2EException("ParamItemCollection参数不能为空"); StringBuilder sb = new StringBuilder(); List parameterList = new List(); foreach (FinderParamItem var in items) { FieldInfo pi = Array.Find(pis, delegate(FieldInfo obj) { return obj.Name.Equals(var.PropertyName, StringComparison.OrdinalIgnoreCase); }); if (pi == null) throw new E2EException("不存在参数："+var.PropertyName); object[] attribs = pi.GetCustomAttributes( typeof(FieldAttribute), false); if (attribs.Length == 0) continue; FieldAttribute attr = attribs[0] as FieldAttribute; string typeName = pi.FieldType.Name; if (sb.Length != 0) { switch (var.JoinKey) { case JoinKey.与: sb.Append(" AND "); break; case JoinKey.或: sb.Append(" OR "); break; default: break; } } sb.Append(" ("); sb.Append(attr.TableName+"."+attr.ColumnName); switch (var.ComparisonKey) { case ComparisonKey.等于: sb.Append("="); break; case ComparisonKey.不等于: sb.Append(""); break; case ComparisonKey.大于: sb.Append(">

"); break; case ComparisonKey. Less than: sb.Append (" = "); break; case ComparisonKey. Less than or equal to: sb.Append ("

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.