The design and writing of simple ORM tools that have been used in their own projects

Http://www.cnblogs.com/szp1118/archive/2011/03/30/ORM.html

In a previous project, I wrote a simple ORM gadget, this time re-organize and refactor the code, the reason is simple because the gadget only realizes the simple function of adding and removing changes, does not have the data cache, delay loading, related operations and other advanced features. Because it is simple, it is not cumbersome to use, the code is not very complex, but at least more than 70% of the data layer can reduce the amount of code written, you can reduce at least 50% of the SQL statement writing.

　　

Design idea: non-null attributes in an entity class are handled as parameters in the SQL statement (different locations in the SQL statements that are added and deleted)

The next step is to start with this design idea:

Let's take a look at two simple classes and a database table:

Class Userentity is inherited from Userbriefentity, and of course only one userentity class is defined, and two classes are defined because it is appropriate to use less-than-field userbriefentity classes in some cases in the list medium. The Userentity class containing all the fields is suitable for use in the details display.

Design ideas:

1. A table in the database needs to correspond to the entity class, the fields in the table correspond to the attributes of the entity class, not all of them should correspond to each other, you can have the property does not correspond to the table, but in general, the database field should correspond to a property, otherwise it will not be able to manipulate the field. Sometimes the list page and the detail page need to get different data (the list page is a few fields), in which case you can define a small number of field entity classes, and then define an entity class for all fields (inheriting to the previous class).

2. Because the basic types of. NET, such as Int,bool, are value types, meaning they cannot be assigned null, they all have default initial values and int is 0,bool to false, so that automatic processing cannot tell whether it is the default assignment or the user's own assignment. So the attribute in the entity class must be a reference type, and for. NET basic types it's going to be nullable (. NET 2.0 new), such as int? , bool? This defines the attribute type of the entity class.

Additions and deletions to the specific treatment:

NEW:

Insert operations on a table, the addition of a table is nothing more than the number of fields in the SQL statement, this can be done by automatically generating SQL statements, the new SQL statement should say that 100% can be automatically generated, The principle of generating SQL statements is that properties of non-null values in entity classes are processed as fields that need to be added, and null-valued properties are not generated into SQL statements.

For example, the following code:

Zhezhe.Common.DataAccess.EntityHelper.Insert (
New Userbriefentity {Id = Guid.NewGuid (), age = 111, Birthday = new DateTime (1980, 12, 23)});

We have a new Userbriefentity object that assigns three properties, the SQL statement that is generated automatically is as follows:

INSERT into T_user (id,age,birthday) VALUES (@Id, @Age, @Birthday)

Note that the SQL statements generated by this tool are parameterized, so there is no SQL injection vulnerability.

The following code and automatically generated SQL statements

Zhezhe.Common.DataAccess.EntityHelper.Insert (
New Userbriefentity {Id = Guid.NewGuid (), Name = "WANGLX", UserName = "WLX"}); INSERT into T_user (id,username,realname) VALUES (@Id, @UserName, @REALNAME)

There is no problem using the Userentity class, and the sample code and the automatically generated SQL statements are as follows:

Zhezhe.Common.DataAccess.EntityHelper.Insert (
New Userentity {Id = Guid.NewGuid (), age =, Birthday = new DateTime (1981, 1, one), SN = 12034012, Pwd = "111111", Sex = false}); INSERT into T_user (password,sex,sn,id,age,birthday) VALUES (@PASSWORD, @Sex, @SN, @Id, @Age, @Birthday)

Zhezhe.Common.DataAccess.EntityHelper.Insert (
New Userentity {Id = Guid.NewGuid (), Childrennumber = 2, Desc = "haha", Grade = grade.normal, SN = 1234}); INSERT into T_us ER (Description,grade,childrennumber,sn,id) VALUES (@DESCRIPTION, @Grade, @ChildrenNumber, @SN, @Id)

The return value of the Insert method is the number of bars in which the data is inserted.

As can be seen from the example code above, the principle of automatically generating an INSERT statement is entirely based on the idea that the non-null attribute in the entity class will be treated as a parameter in the SQL statement, as long as the non-null attribute is stitched into the SQL statement. The Insert method signature is as follows: public static int insert<t> (T instance) conclusion: Insert SQL statement 100% can be automatically generated

Delete:

DeleteThe operation is also for a table, delete operation is more complex, mainlywhereConditions are different, automatically generatedSQLStatement can not be able to generate a variety of complex conditions, if you want to design complex words will be very complex, or to simplify some of it, this tool only automatically generated entity class non-NULLproperty aswhereCondition "and" equality judgmentSQLstatement, specifically, if all the attributes in the entity class areNULL, then the generatedDELETE SQLThe statement will not have any conditions, so that the entire table data is deleted, if the entityAproperty has a value of"A",The remaining property values areNULL, the generatedSQLIn the statementwhereConditions area = "a"IfAProperty value is"a"，BProperty value is"B", then the correspondingwhereConditions area = "a" and b= "B", auto-generatedDELETENone in the statement or(OR)Judge.

The sample code is as follows: Zhezhe.Common.DataAccess.EntityHelper.Delete (new Userentity () {age = $, Grade = Grade.diamond}); The automatically generated SQL statements are as follows: Delete from T_user WHERE [email protected] and [email protected] as can be seen from the example code above, the principle of automatically generating DELETE statements is also exactly the same as in the entity class Non-null properties are handled as arguments in SQL statements, as long as the non-null attribute is stitched into the where condition of the SQL statement. During stitching, you receive the following restrictions: 1. The condition can only be = number, 2. The condition can only be an and relationship. This logic is generated because it is most commonly used in this way. Conclusion: Delete SQL statement 50% can be generated automatically, if combined with the following first query and then delete the method can be done 90% of the DELETE statement can be automatically generated, without handwriting. The signature of the Delete method is as follows: public static int delete<t> (T instance) Modify:The update operation is also for a table, the update operation is more complex than insert and delete, all the parameters of the INSERT statement are in the same position (after values), all the parameters of the DELETE statement are also in the same position (where behind) , and the parameter position of the UPDATE statement is two places, one is after the set, the other is where, the non-null attribute in the entity class is not known where it was placed in both locations as a SQL statement argument, and this is handled as follows: The primary key attribute as the Where condition , the other attributes are set statements, which means that the automatically generated UPDATE statement can only be updated based on the primary key field as a condition. The sample code is as follows: Zhezhe.Common.DataAccess.EntityHelper.Update (new userentity {Id = list3[0]. Id, Birthday = new DateTime (1985, 1, 4), Desc = "I have been Modified"}); The automatically generated UPDATE statement is as follows: Update t_user SET [email protected], [Emai L protected] where [email protected] Note: Here userentity the primary key field property of the entity must be assigned, otherwise it will report an exception (the Primary key field property may be modified in the future if NULL, the data for the entire table is updated).conclusion: Update SQL statement 30% can be generated automatically, if combined with the following first query and then update the method, you can do 80% of the DELETE statement can be automatically generated, without handwriting. The signature of the Update method is as follows: public static int update<t> (T instance) Enquiry:Select is the most complex, the above additions and deletions are for a table, and the return value is an integer type of the affected number of rows, while select may be for a few table operations, the return value is a result set, if you use ADO, you need to store to the DataTable, or assign a value to an object by DataReader. The method for select operation in this program is the GetList method, which has multiple overloaded methods. All the returned result sets in this program are converted to the collection of objects, all in a DataReader way, without using a DataTable. The field list in the result set also requires corresponding entity correspondence. In this program, only one case is automatically generated by the SELECT SQL statement: a single table (view) query, the condition is a non-null attribute, the condition is only equal, the conditions are only for and connections between the condition, the entire DELETE statement is the same as the conditional generation method. The method signature is as follows: public static ilist<t> getlist<t> (T instance)

The sample code is as follows:

var list1 = zhezhe.common.dataaccess.entityhelper.getlist<userentity> (new userentity () {age = 20});

The automatically generated SQL statements are as follows:

SELECT * from T_user WHERE 1=1 and [email protected]

Note: The above 1=1 condition is only when the unconditional time statement still does not have the error processing way (believe many people have written this), of course, can also be in no condition when the WHERE keyword removed, here in order to facilitate the addition of 1=1 conditions.

If you want to query the data for an entire table, you can do this:

var list1_1 = zhezhe.common.dataaccess.entityhelper.getlist<userentity> (new Userentity () {});

The automatically generated SQL statements are as follows:

SELECT * from T_user WHERE 1=1

The generic parameters of the above method can be userentity or userbriefentity, as long as the properties in the class have corresponding fields in the result set of the query (the fields in the query result set are ignored if there are no attributes in the class).

Conclusion: The SQL statement of select estimates that only 10%-20% can be generated automatically, only if the query for the fields of a single table is automatically generated.

The above additions and deletions are the main API methods, all SQL statements are automatically generated. In addition to the above methods, several API methods are defined:

Delete operations we often encounter the situation of bulk deletion, such as the user through the multi-select bulk delete data, this program defines the primary key to delete data in bulk, the signature is as follows:

public static int Batchdelete<te, tk> (tk[] IDs)

Generic parameter te is a deleted entity (the entity needs to know which one to delete), and the generic delete TK is the specific type of the entity attribute that corresponds to the primary key field.

The sample code is as follows:

Zhezhe.common.dataaccess.entityhelper.batchdelete<userentity,guid?> (List1. Select (e = e.id). ToArray ());

List1 is the result set in the preceding query, as described earlier in this article.

The SQL statement generated automatically by the method call is as follows:

DELETE from T_user WHERE [email protected]_id_0 or [email protected]_id_1 or [email protected]_id_2 or [email prot Ected]_id_3 or [email protected]_id_4 or [email protected]_id_5 or [email protected]_id_6

The number of parameters is determined by the number of arrays tk[] IDs.

If you do not have this bulk Delete method can also be removed by looping the previous Delete method, but not high efficiency, of course, this bulk deletion method is only for the primary key bulk Delete.

Update also defines a method for batch updates through the primary key, signed as follows:

public static int Batchupdate<te, tk> (tk[] IDs, TE instance)

Generic parameter TE defines the updated entity, and TK defines the primary key attribute type for the entity.

The sample code is as follows:

Zhezhe.Common.DataAccess.EntityHelper.BatchUpdate (List1. Select (e = e.id). ToArray (), new userentity {QQ = "2222222", name = "renamed"});

List1 is the result set in the preceding query, as described earlier in this article.

The automatically generated SQL statements are as follows:

UPDATE t_user SET [email protected], [email protected] where [email protected]_id_0 OR [email protected]_id_1 or [email protected]_id_2 or [email protected]_id_3

The number of parameters in the condition is tk[] The number of elements in the IDs array is determined.

For query operations, this program defines multiple overloaded functions that can customize SQL statements, the main one being the following:

public static ilist<t> Getlist<t, tw> (String sql, sqlparameter[] parms, CommandType CT, TW instance)

The generic parameter T is the entity type in the collection that is actually returned, the attribute that needs to be mapped in the entity type must be included in the query result (the field value in the result can be null), and TW defines another entity that can be used as a parameter to the query. The parms parameter can be passed in by itself. The parameters of the final SQL statement are the and of the parameters in the parms and the non-null properties of the TW entity.

Of course, a custom SQL statement can have no parameters, can have only parameters from the TW entity, or it can have only parameters from parms, so the method also defines the following overloaded method:

public static ilist<t> getlist<t> (String sql, sqlparameter[] parms, CommandType CT)
{
Return entityhelper.getlist<t, util.nopropertyclass> (SQL, parms, CT, New Util.nopropertyclass ());
}

public static ilist<t> getlist<t> (String sql, sqlparameter[] parms, CommandType CT, T instance)
{
Return entityhelper.getlist<t, t> (SQL, parms, CT, instance);
}

public static ilist<t> Getlist<t, tw> (String sql, CommandType ct, TW instance)
{
Return entityhelper.getlist<t, tw> (SQL, NULL, CT, instance);
}

public static ilist<t> getlist<t> (String sql, CommandType ct, T instance)
{
Return entityhelper.getlist<t, t> (SQL, NULL, CT, instance);
}

public static ilist<t> getlist<t> (String sql, CommandType ct) where t:class
{
return entityhelper.getlist<t> (SQL, NULL, CT, NULL);
}

The sample code is as follows:

1. Query statement without parameters

var lsit4 = zhezhe.common.dataaccess.entityhelper.getlist<userbriefentity> ("select * from T_user where age>= and Sex=1 ", CommandType.Text);

2. The query statement with two parameters, these two parameters are not manually passed in, but by passing in a userentity entity type obtained, the entity type instance must contain this two parameters corresponding to the property, and the property value must be non-null, as follows:

var list5 = zhezhe.common.dataaccess.entityhelper.getlist<userbriefentity, userentity> (
"SELECT * from T_user where age>[email protected] and [email protected]", CommandType.Text, new userentity {age = 22, Sex = true});

As you can see, the resulting set of entity types and the entity types that take arguments can be different, in summary, as long as the non-null attribute can contain the same parameter name.

Note: The parameters in the custom SQL statement are named, and the default and field names are the same. This is the default naming convention because the parameters are automatically obtained from the entity instance.

3. Two table cases: The return result set entity is a orderentity type, and the entity type that takes the parameter is userentity

var list6 = zhezhe.common.dataaccess.entityhelper.getlist<orderentity,userentity> (
"SELECT * from OrderEntity where user_id in (select ID from t_user where [email protected])", CommandType.Text, New Useren tity {Name = "Zhezhe1"});

4. When you pass in parameters

var list7 = zhezhe.common.dataaccess.entityhelper.getlist<orderentity> (
"SELECT * from OrderEntity where user_id in (select ID from T_user where [email protected]_realname)", New sqlparameter[] {New SqlParameter ("@MY_REALNAME", "Zhezhe1")}, CommandType.Text);

Because it is self-passed parameters, so the name of the parameters can be arbitrary name, here named @MY_REALNAME

5. Combine incoming parameters and get parameters from entity instances

var list8 = zhezhe.common.dataaccess.entityhelper.getlist<orderentity,userentity> (
"SELECT * from OrderEntity where user_id in (select ID from T_user where [email protected]_realname and [email protected]) ", new sqlparameter[] {new SqlParameter (" @MY_REALNAME "," Zhezhe1 ")}, CommandType.Text, new userentity {age = 22});

Parameter @MY_REALNAME come from yourself, parameter @AGE get from entity

The corresponding entity of the result of the select above can be defined by itself, as long as it can correspond to the field in the select result, not necessarily with the table, because the result of select may come from more than one table.

Summary: Although custom SQL statements require their own handwritten SQL, but basically can handle all kinds of complex queries, these APIs are mainly in the following points to save you time and effort, 1. Parameters can be obtained through the entity instance, not necessarily all of them to pass through, eliminating a lot of code to define the parameters, 2. The returned result program is automatically converted to the collection of corresponding entities, eliminating a large number of DataReader to fetch the data assignment code.

The previous article mentioned that delete and modify operations can be done by first querying and then executing the delete or modify method. You can first get the results from a custom SQL query before you perform the bulk delete and bulk update operations.

The above is just a general introduction and design ideas, the specific code implementation will be discussed in the next article, and will provide code download.

The design and writing of simple ORM tools that have been used in their own projects