public static void FillPropertySets(this IObjectAccessor objectAccessor, IMappingProvider mappingProvider, object entityType, object entity, IDictionary <string, object> propertySets, IEnumerable <string> propertyNames, IEnumerable <string> excludePropertyNames)
{
HashSet <string> exclude = new HashSet <string>(StringComparer.OrdinalIgnoreCase);
if (excludePropertyNames != null)
{
exclude = new HashSet <string>(excludePropertyNames, StringComparer.OrdinalIgnoreCase);
}
ITable table = mappingProvider.GetTable(entityType);
foreach (string propertyName in propertyNames)
{
if (!exclude.Contains(propertyName))
{
string propertyName2 = table.Columns[propertyName].PropertyName;
if (propertySets.ContainsKey(propertyName2))
{
propertySets[propertyName2] = objectAccessor.Get(entity, propertyName2);
}
else
{
propertySets.Add(propertyName2, objectAccessor.Get(entity, propertyName2));
}
}
}
}
protected string GetTableName(object entityType, IMappingProvider mappingProvider, IDictionary <object, string> tableNameMapping)
{
if (tableNameMapping != null && tableNameMapping.ContainsKey(entityType))
{
//如果tableMapping中包含键则返回，否则去缓存中取
return(tableNameMapping[entityType]);
}
ITable table = mappingProvider.GetTable(entityType);
return(table.TableName);
}
public override int Update(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, ICondition condition, IDictionary <string, object> updates)
{
ITable table = mappingProvider.GetTable(entityType);
QueryExpression queryExpression = new QueryExpression();
queryExpression.EntityType = entityType;
foreach (string propertyName in updates.Keys)
{
queryExpression.Selects.Add(propertyName);
}
queryExpression.Wheres.Add(condition);
SqlAnalyseResult sqlAnalyseResult = SqlAnalyzer.Analyse(mappingProvider, queryExpression);
var sb = new StringBuilder();
var command = db.Connection.CreateCommand();
int parameterCounter = 1;
sb.Append("update ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" set ");
var index = 0;
foreach (KeyValuePair <string, object> kv in updates)
{
if (index > 0)
{
sb.Append(",");
}
index++;
IColumn column = table.Columns[kv.Key];
sb.Append(column.ColumnName);
sb.Append("=");
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
sb.Append(" from ");
sb.Append(GetTableName(entityType, mappingProvider, tableMapping));
sb.Append(" ");
sb.Append(sqlAnalyseResult.MasterTableNameAlias);
sb.Append(" ");
if (sqlAnalyseResult.ForeignTables.Count == 0)
{
//说明是无关联表的条件，只是本表的条件
sb.Append(" where ");
GenerateSelectSql_Where(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, true, mappingProvider);
}
else
{
//说明有关联表的条件，需要多表关联
sb.Append(" where exists (");
SqlServerCreateSqlExists(sb, command.Parameters, ref parameterCounter, condition, sqlAnalyseResult, mappingProvider, tableMapping);
sb.Append(")");
}
//执行sql
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
int returnCount = db.ExecuteNonQuery(command);
return(returnCount);
}
public override int Insert(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IDictionary <string, object> inserts, IDictionary <string, object> returns)
{
ITable table = mappingProvider.GetTable(entityType);
StringBuilder sb = new StringBuilder();
SqlCommand command = new SqlCommand();
int parameterCounter = 0;
StringBuilder sbValues = new StringBuilder();
Dictionary <string, string> returnColumns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); //属性名是键，sql参数名是值
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
int index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
var column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
if (index > 0)
{
sb.Append(","); sbValues.Append(",");
}
index++;
sb.Append(column.ColumnName);
//sbValues
if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
{
sbValues.Append(column.InsertBehavior.ValueExpression);
}
else
{
sbValues.Append(CreateParameterName(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(entity);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
}
}
sb.Append(") values (");
sb.Append(sbValues);
sb.Append(")");
foreach (string propertyName in table.PrimaryProperties)
{
SqlServerColumn column = (SqlServerColumn)table.Columns[propertyName];
//取回主键值 ，本版本只支持主键值的取回
if (column.IsIdentity && column.ReturnAfterInsert)
{
string pname = CreateParameterName(parameterCounter);
returnColumns.Add(propertyName, pname);
sb.Append(";set ");
sb.Append(CreateParameterName(parameterCounter));
sb.Append("=SCOPE_Identity()");
var parameter = (SqlParameter)AddParameter(command.Parameters, ref parameterCounter, null, column.PropertyType, column.Size);
parameter.Direction = ParameterDirection.Output;
}
}
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
//执行sql
int returnCount = db.ExecuteNonQuery(command);
//read return value
foreach (KeyValuePair <string, string> item in returnColumns)
{
object objValue = command.Parameters[item.Value].Value; //GetCommandValue(cmd, pName);
returns.Add(item.Key, ConvertDbValue(objValue, table.Columns[item.Key].PropertyType));
//returns.Add(item.Key, objValue);
//item.Value.PropertyAdapter.SetValue(entity, ConvertDbValue(objValue, item.Value.PropertyAdapter.PropertyType.Type));
}
return(returnCount);
}
public override int Insert(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IDictionary <string, object> inserts, IDictionary <string, object> returns)
{
ITable table = mappingProvider.GetTable(entityType);
var sb = new StringBuilder();
var command = new MySqlCommand();
int parameterCounter = 1;
#region 循环每个属性
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
//Oracle存储过程参数有顺序之分
//List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程
Dictionary <string, string> returnColumns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); //属性名是键，sql参数名是值
int index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
var column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
sb.Append(column.ColumnName);
index++;
}
}
sb.Append(") values (");
index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
IColumn column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
{
sb.Append(column.InsertBehavior.ValueExpression);
}
else
{
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
index++;
}
}
sb.Append(")");
foreach (string propertyName in table.PrimaryProperties)
{
ColumnWithIdentity column = (ColumnWithIdentity)table.Columns[propertyName];
//取回主键值 ，本版本只支持主键值的取回
if (column.IsIdentity && column.ReturnAfterInsert)
{
string pname = CreateParameterName(parameterCounter);
returnColumns.Add(propertyName, pname);
sb.Append(";select LAST_INSERT_ID();");
break;//只能有一个
}
}
#endregion
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
//执行sql
object returnValue = db.ExecuteScalar(command);
//read return value
foreach (KeyValuePair <string, string> item in returnColumns)
{
returns.Add(item.Key, ConvertDbValue(returnValue, table.Columns[item.Key].PropertyType));
break;//只能有一个
}
return(1);
}
private static void AnalyseInner(IMappingProvider mappingProvider, object entityType,
SqlAnalyseResult sqlAnalyseResult, ObjectFiller objectFiller,
IDictionary <IPropertyExpression, string> taskPropertyNames, string prefix,
ref int tableIndexCounter, ref int outputColumnIndexCounter
)
{
ITable table = mappingProvider.GetTable(entityType);
string currentTableNameAlias = "t" + tableIndexCounter;
if (prefix == null)
{
//前缀为null时表示是起始对象
sqlAnalyseResult.MasterEntityType = entityType;
sqlAnalyseResult.MasterTableNameAlias = currentTableNameAlias;
}
Dictionary <string, Dictionary <IPropertyExpression, string> > taskObjects = new Dictionary <string, Dictionary <IPropertyExpression, string> >(StringComparer.OrdinalIgnoreCase);
objectFiller.PropertyFillers = new List <PropertyFiller>();
foreach (IPropertyExpression propertyName in taskPropertyNames.Keys)
{
PrefixParser prefixParser = new PrefixParser(propertyName.PropertyName);
if (prefixParser.HasPrefix)
{
//说明是外键对象的属性，例如"Person.City.CityId"
if (!taskObjects.ContainsKey(prefixParser.Prefix))
{
taskObjects.Add(prefixParser.Prefix, new Dictionary <IPropertyExpression, string>());
}
taskObjects[prefixParser.Prefix].Add(new PropertyExpression(prefixParser.PropertyName, propertyName.GroupFunction), taskPropertyNames[propertyName]);
}
else
{
//说明是本对象的属性
string joinedPropertyName = PrefixParser.JoinPrefix(prefix, propertyName.PropertyName);
IColumn column = null;
if (joinedPropertyName == "***")
{
//伪造一个column
//column = sqlProvider.CreateCountColumn();
}
else
{
//如果不为***则column不会为null
if (!table.Columns.ContainsKey(propertyName.PropertyName))
{
throw new MissingMemberException(entityType.ToString(), propertyName.PropertyName);
}
column = table.Columns[propertyName.PropertyName];
}
if (!sqlAnalyseResult.HasGroup && propertyName.GroupFunction != GroupFunction.None)
{
sqlAnalyseResult.HasGroup = true;
}
//sqlColumn
SqlColumn sqlColumn = new SqlColumn();
sqlColumn.FullPropertyName = new PropertyExpression(joinedPropertyName, propertyName.GroupFunction);
sqlColumn.Column = column;
//oracle等数据库需要别名
StringBuilder sb = new StringBuilder();
if (propertyName.GroupFunction != GroupFunction.None)
{
sb.Append(propertyName.GroupFunction.ToString());
sb.Append("(");
}
if (propertyName.GroupFunction == GroupFunction.Count)
{
sb.Append("*");
}
else
{
sb.Append(currentTableNameAlias);
sb.Append(".");
sb.Append(column.ColumnName);
}
if (propertyName.GroupFunction != GroupFunction.None)
{
sb.Append(")");
}
sqlColumn.TableAliasAndColumn = sb.ToString();
//filler
if (taskPropertyNames[propertyName] != null)
{
//只有输出字段才有PropertyFiller
PropertyFiller propertyFiller = new PropertyFiller();
if (propertyName.GroupFunction == GroupFunction.None)
{
propertyFiller.PropertyName = column.PropertyName;
propertyFiller.PropertyType = column.PropertyType;
}
else
{
if (!table.GroupColumns.ContainsKey(propertyName))
{
throw new MissingMemberException(string.Format("类型{0}缺少标记为GroupFunction.{1}的GroupColumnAttribute", entityType, propertyName.GroupFunction));
}
IGroupColumn groupColumn = table.GroupColumns[propertyName];
propertyFiller.PropertyName = groupColumn.PropertyName;
propertyFiller.PropertyType = groupColumn.PropertyType;
}
propertyFiller.ColumnIndex = outputColumnIndexCounter;
string outputSqlColumn = taskPropertyNames[propertyName];
if (outputSqlColumn == "")
{
//如果为空格，则使用默认f开头的计数器作为别名
sb.Append(" f");
sb.Append(outputColumnIndexCounter);
outputColumnIndexCounter++;
outputSqlColumn = sb.ToString();
}
sqlAnalyseResult.SortedOutputColumns.Add(outputSqlColumn);
objectFiller.PropertyFillers.Add(propertyFiller);
}
sqlAnalyseResult.SqlColumns.Add(sqlColumn.FullPropertyName, sqlColumn);
}
}
//分析父对象，必须在自己属性分析完后才进行
objectFiller.ObjectFillers = new List <ObjectFiller>();
foreach (string objectPeropertyName in taskObjects.Keys)
{
string tableNameAlias = "t" + ++tableIndexCounter;
if (!table.Relations.ContainsKey(objectPeropertyName))
{
throw new MissingMemberException(entityType.ToString(), objectPeropertyName);
}
IRelation relation = table.Relations[objectPeropertyName];
SqlTable sqlTable = new SqlTable();
sqlTable.TableNameAlias = currentTableNameAlias;
sqlTable.ForeignTableNameAlias = tableNameAlias;
sqlTable.ForeignEntityType = relation.ForeignEntityType;
List <SqlRelationColumn> sqlRelationColumns = new List <SqlRelationColumn>();
foreach (IRelationColumn relationColumn in relation.RelationColumns)
{
SqlRelationColumn sqlRelationColumn = new SqlRelationColumn();
sqlRelationColumn.ColumnName = relationColumn.ColumnName;
sqlRelationColumn.ForeignColumnName = relationColumn.ForeignColumnName;
StringBuilder sb = new StringBuilder();
sb.Append(sqlTable.TableNameAlias);
sb.Append('.');
sb.Append(relationColumn.ColumnName);
sb.Append('=');
sb.Append(sqlTable.ForeignTableNameAlias);
sb.Append('.');
sb.Append(relationColumn.ForeignColumnName);
sqlRelationColumn.Expression = sb.ToString();
sqlRelationColumns.Add(sqlRelationColumn);
}
sqlTable.RelationColumns = sqlRelationColumns;
sqlAnalyseResult.ForeignTables.Add(sqlTable);
ObjectFiller subObjectFiller = new ObjectFiller();
subObjectFiller.PropertyName = relation.PropertyName;
subObjectFiller.EntityType = relation.ForeignEntityType;
objectFiller.ObjectFillers.Add(subObjectFiller);
//AnalyseInner(sqlProvider, relation.PropertyAdapter.PropertyType, sqlAnalyseResult, subObjectFiller,taskObjects[objectPeropertyName], objectPeropertyName, tableIndexCounter, outputColumnIndexCounter);
string joinedPropertyName = PrefixParser.JoinPrefix(prefix, relation.PropertyName);
AnalyseInner(mappingProvider, relation.ForeignEntityType, sqlAnalyseResult, subObjectFiller, taskObjects[relation.PropertyName], joinedPropertyName, ref tableIndexCounter, ref outputColumnIndexCounter);
}
}
public override int Insert(IDbHelper db, IMappingProvider mappingProvider, IDictionary <object, string> tableMapping, object entityType, IDictionary <string, object> inserts, IDictionary <string, object> returns)
{
ITable table = mappingProvider.GetTable(entityType);
StringBuilder sb = new StringBuilder();
OracleCommand command = new OracleCommand();
int parameterCounter = 1;
#region 循环每个属性
string tableName = GetTableName(entityType, mappingProvider, tableMapping);
sb.Append("begin insert into ");
sb.Append(tableName);
sb.Append(" (");
//Oracle存储过程参数有顺序之分
//List<OracleParameter> returnParameters = new List<OracleParameter>();//取回的存储过程
Dictionary <string, string> returnColumns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); //属性名是键，sql参数名是值
int index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
IColumn column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
sb.Append(column.ColumnName);
index++;
}
}
sb.Append(") values (");
index = 0;
foreach (KeyValuePair <string, object> kv in inserts)
{
if (index > 0)
{
sb.Append(",");
}
IColumn column = table.Columns[kv.Key];
if (column.InsertBehavior.Generate)
{
if (column.InsertBehavior.ValueBehavior == ValueBehavior.UseValueExpression)
{
sb.Append(column.InsertBehavior.ValueExpression);
}
else
{
sb.Append(CreateParameterNameInStatement(parameterCounter));
//object propertyValue = column.PropertyAdapter.GetValue(obj);
//Validate(column, propertyValue);//验证如：检查字符串长度，是否为null
AddParameter(command.Parameters, ref parameterCounter, kv.Value, column.PropertyType, column.Size);
}
index++;
}
}
sb.Append(")");
//"INSERT INTO SequenceTest_Table (ID, OtherColumn)" +
//"VALUES (SequenceTest_Sequence.NEXTVAL, :OtherColumn)" +
//"RETURNING ID INTO :ID";
foreach (string propertyName in table.PrimaryProperties)
{
OracleColumn column = (OracleColumn)table.Columns[propertyName];
//取回主键值 ，本版本只支持主键值的取回
if (column.HasSequence && column.ReturnAfterInsert)
{
string pname = CreateParameterName(parameterCounter);
returnColumns.Add(propertyName, pname);
sb.Append(";select ");
sb.Append(column.Sequence);
sb.Append(".currval into ");
sb.Append(CreateParameterNameInStatement(parameterCounter));
sb.Append(" from dual");
global::Oracle.ManagedDataAccess.Client.OracleParameter parameter = (global::Oracle.ManagedDataAccess.Client.OracleParameter)AddParameter(command.Parameters, ref parameterCounter, null, column.PropertyType, column.Size);
parameter.Direction = ParameterDirection.Output;
}
}
sb.Append("; end;");
#endregion
command.CommandText = sb.ToString();
command.CommandType = CommandType.Text;
//执行sql
int returnCount = db.ExecuteNonQuery(command);
//read return value
foreach (KeyValuePair <string, string> item in returnColumns)
{
object objValue = command.Parameters[item.Value].Value; //GetCommandValue(cmd, pName);
returns.Add(item.Key, ConvertDbValue(objValue, table.Columns[item.Key].PropertyType));
//item.Value.PropertyAdapter.SetValue(obj, ConvertDbValue(objValue, item.Value.PropertyAdapter.PropertyType.Type));
}
return(returnCount);
}
