Skip to content

Instantly share code, notes, and snippets.

@CleanCoder
Created Jan 23, 2018
Embed
What would you like to do?
DB
using EntityFramework;
using EntityFramework.Mapping;
using EntityFramework.Reflection;
using Next.Dal.Entity;
using Next.Dal.Repertory;
using Next.Dal.Repertory.DbRepertory;
using Next.Dal.Repertory.MemoryRepertory;
using Spring.Reflection.Dynamic;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Reflection;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;
namespace Next.Dal.Util
{
public static class DBHelper
{
public static bool RequireDBExclusiveLock<TEntity>(this BusinessEntityRepertory repertory, TEntity entity) where TEntity : EntityBase
{
var db = repertory as DbBusinessEntityRepertory;
if (db == null || db.BizContext == null)
{
return false;
}
var keyProperties = EntityProperty<TEntity>.KeyProperties;
var parameterCounter = 0;
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(DbQuote(EntityProperty<TEntity>.TableName));
sb.Append(string.Format(" Set {0} = {0} WHERE ", DbQuote("IsValid")));
sb.Append(keyProperties.Select(keyProperty => string.Format("{0}=@P{1}", DbQuote(keyProperty.ColumnName), parameterCounter++))
.Aggregate((clause1, clause2) => string.Format("{0} AND {1}", clause1, clause2)));
string command = sb.ToString();
object[] parameters = keyProperties.Select(keyProperty => keyProperty.GetValue(entity)).ToArray();
return db.BizContext.Database.ExecuteSqlCommand(command, parameters) > 0;
}
private static string DbQuote(string name)
{
return string.Format("[{0}]", name);
}
public static int Update<TEntity>(this ObjectContext source, TEntity entity) where TEntity : EntityBase, new()
{
if (source == null)
throw new ArgumentNullException("source");
var provider = Locator.Current.Resolve<IMappingProvider>();
var entityMap = provider.GetEntityMap(typeof(TEntity), source);
if (entityMap == null)
throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
return InternalUpdate(source, entityMap, entity, false).Result;
}
private static async Task<int> InternalUpdate<TEntity>(ObjectContext objectContext, EntityMap entityMap, TEntity entity, bool async = false) where TEntity : EntityBase, new()
{
DbConnection updateConnection = null;
DbTransaction updateTransaction = null;
DbCommand updateCommand = null;
bool ownConnection = false;
bool ownTransaction = false;
try
{
// get store connection and transaction
var store = GetStore(objectContext);
updateConnection = store.Item1;
updateTransaction = store.Item2;
if (updateConnection.State != ConnectionState.Open)
{
updateConnection.Open();
ownConnection = true;
}
// use existing transaction or create new
if (updateTransaction == null)
{
updateTransaction = updateConnection.BeginTransaction();
ownTransaction = true;
}
updateCommand = updateConnection.CreateCommand();
updateCommand.Transaction = updateTransaction;
if (objectContext.CommandTimeout.HasValue)
updateCommand.CommandTimeout = objectContext.CommandTimeout.Value;
string updateSetSql = GetUpdateSetSql(entityMap, entity, updateCommand);
string whereSql = GetWhereSql(entityMap, entity, updateCommand);
var sqlBuilder = new StringBuilder(50);
sqlBuilder.Append("UPDATE ");
sqlBuilder.Append(entityMap.TableName);
sqlBuilder.AppendLine(" SET ");
sqlBuilder.AppendLine(updateSetSql);
sqlBuilder.Append("Where ");
sqlBuilder.AppendLine(whereSql);
updateCommand.CommandText = sqlBuilder.ToString();
int result = async ? await updateCommand.ExecuteNonQueryAsync().ConfigureAwait(false) : updateCommand.ExecuteNonQuery();
// only commit if created transaction
if (ownTransaction)
updateTransaction.Commit();
return result;
}
finally
{
if (updateCommand != null)
updateCommand.Dispose();
if (updateTransaction != null && ownTransaction)
updateTransaction.Dispose();
if (updateConnection != null && ownConnection)
updateConnection.Close();
}
}
private static string GetWhereSql<TEntity>(EntityMap entityMap, TEntity entity, DbCommand updateCommand) where TEntity : EntityBase, new()
{
var keys = EntityProperty<TEntity>.KeyProperties;
var columnInfos = EntityProperty<TEntity>.KeyProperties.SelectMany(p => GetColumnInfos(entityMap, entity, p)).ToArray();
List<string> columnSegments = new List<string>(columnInfos.Length);
for (int i = 0; i < columnInfos.Length; i++)
{
string parameterName = "p__linq__" + i;
var parameter = updateCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = columnInfos[i].Item2;
updateCommand.Parameters.Add(parameter);
columnSegments.Add(string.Format("[{0}] = @{1}", columnInfos[i].Item1, parameterName));
}
return string.Join(" AND ", columnSegments);
}
private static string GetUpdateSetSql<TEntity>(EntityMap entityMap, TEntity entity, DbCommand updateCommand) where TEntity : EntityBase, new()
{
var changedProperties = entity.GetChangedPropertyNames();
List<Tuple<string, object>> columnInfos = new List<Tuple<string, object>>();
foreach (var pName in changedProperties)
{
var property = changedProperties.Select(name => EntityProperty<TEntity>.SafeBusinessPropertyIndex[pName]).FirstOrDefault();
columnInfos.AddRange(GetColumnInfos(entityMap, entity, property));
}
List<string> columnSegments = new List<string>(columnInfos.Count);
int nameCount = 0;
foreach (var columnInfo in columnInfos)
{
if (columnInfo.Item2 == null)
{
columnSegments.Add(string.Format("[{0}] = NULL", columnInfo.Item1));
}
else
{
string parameterName = "p__update__" + nameCount++;
var parameter = updateCommand.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = columnInfo.Item2;
updateCommand.Parameters.Add(parameter);
columnSegments.Add(string.Format("[{0}] = @{1}", columnInfo.Item1, parameterName));
}
}
return string.Join(", ", columnSegments);
}
private static IEnumerable<Tuple<string, object>> GetColumnInfos<TEntity>(EntityMap entityMap, TEntity entity, SafeProperty<TEntity> property) where TEntity : EntityBase, new()
{
var propertyMap = entityMap.PropertyMaps.SingleOrDefault(p => p.PropertyName == property.PropertyName);
var cpm = propertyMap as ComplexPropertyMap;
if (cpm == null) // 简单对象
{
yield return GetUpdateColumnInfo(entityMap.PropertyMaps, entity, property);
}
else // 复杂对象,需要遍历它的所有属性
{
var propertyType = property.PropertyInfo.PropertyType;
var complexProperty = propertyType.GetProperties().Where(info => info.GetCustomAttribute<NotMappedAttribute>() == null && info.GetCustomAttribute<IgnoreDataMemberAttribute>() == null);
var inlineObject = property.GetValue(entity);
foreach (var prop in complexProperty)
yield return GetUpdateColumnInfo(cpm.TypeElements, inlineObject, prop);
}
}
private static Tuple<string, object> GetUpdateColumnInfo<TEntity>(IEnumerable<PropertyMap> propertyMap, TEntity entity, SafeProperty<TEntity> propertyInfo) where TEntity : EntityBase, new()
{
string columnName = propertyInfo.ColumnName; // propertyMap.Where(p => p.PropertyName == propertyInfo.PropertyName).Select(p => p.ColumnName).FirstOrDefault();
return Tuple.Create(columnName, propertyInfo.GetValue(entity));
}
private static Tuple<string, object> GetUpdateColumnInfo(IEnumerable<PropertyMap> propertyMap, object obj, PropertyInfo propertyInfo)
{
string columnName = propertyMap.Where(p => p.PropertyName == propertyInfo.Name).Select(p => p.ColumnName).FirstOrDefault();
return Tuple.Create(columnName, new SafeProperty(propertyInfo).GetValue(obj));
}
private static IEnumerable<Tuple<string, object>> GetMemberValue<TEntity>(TEntity entity, SafeProperty<TEntity> propertyInfo) where TEntity : EntityBase, new()
{
var propertyType = propertyInfo.PropertyInfo.PropertyType;
if (propertyType.GetCustomAttribute<ComplexTypeAttribute>() != null)
{
var complexPropertyValues = propertyType.GetProperties().Where(info => info.GetCustomAttribute<NotMappedAttribute>() == null && info.GetCustomAttribute<IgnoreDataMemberAttribute>() == null); ;
foreach (var prop in complexPropertyValues)
yield return Tuple.Create($"{propertyInfo.PropertyName}.{prop.Name}", GetPropertyValue(entity, propertyInfo, prop));
}
else
{
yield return Tuple.Create(propertyInfo.PropertyName, propertyInfo.GetValue(entity));
}
}
private static object GetPropertyValue<TEntity>(TEntity entity, SafeProperty<TEntity> propertyInfo, PropertyInfo subProperty) where TEntity : EntityBase, new()
{
return (new SafeProperty(subProperty)).GetValue(propertyInfo.GetValue(entity));
}
private static Tuple<DbConnection, DbTransaction> GetStore(ObjectContext objectContext)
{
DbConnection dbConnection = objectContext.Connection;
var entityConnection = dbConnection as EntityConnection;
// by-pass entity connection
if (entityConnection == null)
return new Tuple<DbConnection, DbTransaction>(dbConnection, null);
DbConnection connection = entityConnection.StoreConnection;
// get internal transaction
dynamic connectionProxy = new DynamicProxy(entityConnection);
dynamic entityTransaction = connectionProxy.CurrentTransaction;
if (entityTransaction == null)
return new Tuple<DbConnection, DbTransaction>(connection, null);
DbTransaction transaction = entityTransaction.StoreTransaction;
return new Tuple<DbConnection, DbTransaction>(connection, transaction);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment