EF Schemas ~ Filter navigation properties, etc., splicing SQL strings

Splicing T-SQL string and making it versatile

Benefit: Establish a connection with the server, send a SQL command to the server, you can implement

The code is as follows:

 1//<summary> 2///Build INSERT statement string 3//The primary key is self-increment, if the primary key value is 0, we insert the primary keys into the SQL string 4//&LT;/SUMMARY&G T          5//<typeparam name= "TEntity" ></typeparam> 6//<param name= "entity" ></param> 7 <returns></returns> 8 Private tuple<string, object[]> Createinsertsql<tentity&gt ;(TEntity entity) where Tentity:class 9 {ten if (entity = = null) One throw new Argument Exception ("The database entity can is not null."); Type EntityType = entity. GetType (); var table = Entitytype.getproperties ().                Where (i = i.propertytype! = typeof (EntityKey) && I.propertytype! = typeof (EntityState) 16 && I.getvalue (entity, NULL)! = Null17 && (i.propertytype.isvaluetype | | i.proper Tytype = = typeof (String)) 18. ToArray ();//filter primary key, sailing properties, state properties, etc. list<string> PKList = Getprimarykey<tentity> (). Select (i = i.name). ToList (); list<object> arguments = new list<object> (); StringBuilder Fieldbuild ER = new StringBuilder (), StringBuilder valuebuilder = new StringBuilder (), and FieldBuilder. Append ("INSERT into" + string.)                 Format ("[{0}]", Entitytype.name) + "("), member foreach (var. in table) 28 {29 if (Pklist.contains (member. Name) && convert.tostring (member. GetValue (entity, null)) = = "0") continue;31 object value = member. GetValue (entity, NULL), + if (value! = null) (arguments. Count! = 0) * {fieldbuilder. Append (","); PNs Valuebuilder. Append (",");}39-FieldBuilder. Append (member. Name), if (MeMber. PropertyType = = typeof (String) | | Member. PropertyType = = typeof (DateTime)) Valuebuilder. Append ("' {" + arguments. Count + "}"); Else44 Valuebuilder. Append ("{" + arguments. Count + "}"), if (value. GetType () = = typeof (String)), with value = value. ToString (). Replace ("'", "char"); arguments. ADD (value),}50}51 FieldBuilder. Append (") Values ("); FieldBuilder. Append (Valuebuilder. ToString ()); FieldBuilder. Append (");"); tuple<string return new, object[]> (FieldBuilder. ToString (), arguments. ToArray ()); 58}

For bulk update operations in the EF schema, we need to manually label the navigation properties of the entity because there is no special description in the entity generated by EF, so we have to tell the system which property is the navigation property, and the navigation property is that we do not go to the update.

1     ///<SUMMARY>2///Properties 3//</summary>4 public class Navigationattribute: Attribute5     {6 7     }

For entities to be bulk updated, we need to add this feature to the navigation properties

1 public    class User2     {3 public         int UserID {get; set;} 4         [navigation]5 public         user_extension user_extension {get; set;} 6     }

And for us to build the bulk UPDATE statement, look at the code, it needs to filter the navigation properties

 1//<summary> 2//Build UPDATE statement string 3//</summary> 4//<typeparam name= " TEntity "></typeparam> 5//<param name=" entity "></param> 6//&LT;RETURNS&GT;&LT;/R Eturns> 7 Private tuple<string, object[]> createupdatesql<tentity> (TEntity entity) where TEntity:  Class 8 {9 if (entity = = null)-Throw new ArgumentException ("The database entity can Not is null. "); One list<string> pklist = getprimarykey<tentity> (). Select (i = i.name). ToList (); Type EntityType = entity. GetType (); var table = Entitytype.getproperties ().                 Where (i =>15!pklist.contains (i.name) && i.getvalue (entity, NULL)! = NULL17 && I.propertytype! = typeof (EntityState) &&! (I.getcustomattributes (FALSE). Length > 019 && I.getcustomattributes (False). Where (j = j.gettype () = = typeof (Navigationattribute)) = null) && (I.propertytype.isvaluetyp e | | I.propertytype = = typeof (String))//Filter navigation property 21). ToArray (); 22 23//Filter primary key, sailing properties, status attributes, etc. (pklist = = NULL | | pklist.count = = 0) thr ow new ArgumentException ("The Table entity has not a primary key."); list<object> arguments = new list<object> (); StringBuilder builder = new Stringbu Ilder (); foreach (var change in table), {pklist.contains (change). Name)) continue;33 if (arguments. Count! = 0) The builder. Append (","); Append (change. Name + "= {" + arguments. Count + "}"); PropertyType = = typeof (String) | | Change. PropertyType = = typeof (DateTime)) PNs arguments. ADD ("'" + change.) GetValue (entity, NULL). ToString (). Replace ("'", "char") + "'"), else39 arguments. ADD (change. GetValue (entity, NULL)),}41 if (builder. Length = = 0) The throw new Exception ("No attributes are updated"); Insert (0, "UPDATE" + string.) Format ("[{0}]", entitytype.name) + "SET");             Append ("WHERE"); firstprimarykey bool = true;49 (var primaryfield in Pklist) 51                     {firstprimarykey) Firstprimarykey = false;54 Else55 Builder. Append ("and"), "the" and "the", "the" and "." Object val = Entitytype.getproperty (Primaryfield) GetValue (entity, NULL); Append (Getequalstatment (Primaryfield, arguments. Count)); arguments. Add (Val),}61 return new tuple<string, object[]> (builder. Tostring (), arguments. ToArray ()); 62 63}

And the Update method exposed by the subclass, we have a wrapper, it by manipulating the enumeration to do you want to insert,update or delete, see the code

 1//<summary> 2//Execute SQL, according to type of SQL Operation 3///</summary> 4//<typeparam N Ame= "TEntity" ></typeparam> 5//<param name= "list" ></param> 6//<param name= "sq LType "></param> 7//<returns></returns> 8 protected string dosql<tentity> (IEn Umerable<tentity> list, SqlType sqltype) where Tentity:class 9 {ten StringBuilder sqlstr = new                     StringBuilder (); switch (SqlType) (sqltype.insert:14) List. ToList (). ForEach (i =>15 {tuple<string, object[]> sql = Createinsertsql (i); 1 7 Sqlstr. AppendFormat (SQL. ITEM1, SQL.                     ITEM2); break;20 Case sqltype.update:21 List. ToList ().           ForEach (i =>22 {23              tuple<string, object[]> sql = Createupdatesql (i), and sqlstr. AppendFormat (SQL. ITEM1, SQL.                     ITEM2); break;27 Case sqltype.delete:28 List. ToList (). ForEach (i =>29 {tuple<string, object[]> sql = Createdeletesql (i); 3 1 sqlstr. AppendFormat (SQL. ITEM1, SQL. ITEM2); break;34 default:35 throw new ArgumentException ("Please enter the correct parameters");}37 return SQLSTR. ToString (); 38}

EF Schemas ~ Filter navigation properties, etc., splicing SQL strings