That is, when using ADO. NET, lightweight dynamic generation and updating SQL, ado. netsql

That is, when using ADO. NET, lightweight dynamic generation and updating SQL, ado. netsql

For whatever reason, sometimes framework staff abandon NH or EF and use native databases to access objects.

For beautiful programming, use the lightweight dynamic generation and update SQL extension method I wrote.

We still remember to write this when updating only modified fields in EF:

                        var e = db.Entry(d);                        e.State = EntityState.Unchanged;                        d.UploadTime = DateTime.Now;                        e.Property("UploadTime").IsModified = true;                        db.SaveChanges();

The purpose is to make the generated update SQL only update the UploadTime field. Otherwise, all fields will be updated, so you need to query and update the fields first.
Since data warehousing is mentioned, it is best to have only one method to update an object on the data layer, but can an updated SQL meet the requirements?

For the above purpose, I wrote such a lightweight extension method:

Public static class SqlStringEx {// <summary> // dynamically generates an update SQL statement based on the value assignment of the object model. To avoid getting and saving, alternatively, avoid writing multiple update SQL statements as needed /// </summary> /// <typeparam name = "T"> </typeparam> /// <param name = "t "> </param> /// <param name =" tableName "> data table name </param> /// <param name =" conditionColumnName "> name of the condition column when updating, this column name must be included in parameters </param> /// <param name = "parameters"> </param> /// <returns> </returns> public static string GenerateUpdateSql <T> (this T t, string tableName, string conditionColumnName, out List <SqlParamet Er> parameters) where T: new () {parameters = new List <SqlParameter> (); StringBuilder res = new StringBuilder (); res. appendFormat ("update {0} set", tableName); var props = t. getType (). getProperties (); string wherePart = null; foreach (PropertyInfo item in props) {var propVal = item. getValue (t, null); var hasValue = propVal! = Null &&! PropVal. equals (0); if (hasValue) {var filedname = item. getFieldName (); parameters. add (new SqlParameter {Value = propVal, ParameterName = filedname}); if (filedname. equals (conditionColumnName. toLower () {wherePart = string. format ("where {0 }=@{ 0}", filedname);} else {res. appendFormat ("{0 }=@{ 0},", filedname) ;}} res. remove (res. length-1, 1); res. append (wherePart); return res. toString ();}}

For the GetFieldName () method, see the previous article.

With this method, an object on the data layer only needs an update method, and you do not need to splice the updated SQL statement yourself.

            List<SqlParameter> parameters =null;            var saveSql = model.GenerateUpdateSql("TableName", "ID", out parameters);

Note,The default value of the model attribute can be set to 0. The default value of the reference type and non-null type is null. When the value of the model attribute is not the default value, the default value is updated..
SaveSql is the SQL to be updated, and parameters is the updated parameter. The ID parameter can also be a column, but the value of the Column Used as the condition in the model cannot be the default value.

Is there any convenience? You are welcome to sell bricks.

Although the wheel is repeated, convenience can make up for everything.

High performance requirements, please be careful, because reflection is used internally.