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); }