public void DeleteSelectedTableJoin(string strParms) { CollectionInvokeResult <TableJoin> tableJoinResult = new CollectionInvokeResult <TableJoin> { Success = true }; List <IBatisNetBatchStatement> tableJoinStatements = new List <IBatisNetBatchStatement>(); try { //InvokeResult invokeResultResult = new InvokeResult { Success = true }; //查询表关系Id进行删除操作 var dictionary = new StringObjectDictionary().MixInJson(strParms); tableJoinResult.rows = BuilderFactory.DefaultBulder().List <TableJoin>(dictionary); //得到id for (int i = 0; i < tableJoinResult.rows.Count; i++) { string statementName = new TableJoin().GetDeleteMethodName(); TableJoinPK pk2 = new TableJoinPK { Id = tableJoinResult.rows[i].Id }; tableJoinStatements.Add(new IBatisNetBatchStatement { StatementName = statementName, ParameterObject = pk2, Type = SqlExecuteType.DELETE }); } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(tableJoinStatements); } catch (Exception ex) { tableJoinResult.Success = false; tableJoinResult.ErrorMessage = ex.Message; throw new Exception("删除表关系出错"); } }
public ModelInvokeResult <TableJoinPK> Update(string strtableJoinId, TableJoin tableJoinInfo) { ModelInvokeResult <TableJoinPK> result = new ModelInvokeResult <TableJoinPK> { Success = true }; try { List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); /***********************begin 自定义代码*******************/ tableJoinInfo.OperatedBy = NormalSession.UserId.ToGuid(); tableJoinInfo.OperatedOn = DateTime.Now; /***********************end 自定义代码*********************/ statements.Add(new IBatisNetBatchStatement { StatementName = tableJoinInfo.GetUpdateMethodName(), ParameterObject = tableJoinInfo.ToStringObjectDictionary(false), Type = SqlExecuteType.UPDATE }); /***********************begin 自定义代码*******************/ /***********************此处添加自定义代码*****************/ /***********************end 自定义代码*********************/ BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); result.instance = new TableJoinPK { }; } catch (Exception ex) { result.Success = false; result.ErrorMessage = ex.Message; } return(result); }
private SimpleQuery GetDetail(IDictionary <string, object> row, TableJoin join) { var criteria = ExpressionHelper.CriteriaDictionaryToExpression(join.Detail.ActualName, new Dictionary <string, object> { { join.DetailColumn.ActualName, row[join.MasterColumn.HomogenizedName] } }); return(new SimpleQuery(_adapter, null, join.Detail.ActualName).Where(criteria)); }
public static List <TableJoin> Join(this List <TableJoin> list, Table table, string joinField, JoinColumn foreignColumn, JoinType joinType = JoinType.Join, string extra = null) { var join = new TableJoin() { JoinType = joinType, JoinTable = table, JoinField = new Field(joinField), ForeignColumn = foreignColumn, Extra = extra }; list.Add(join); return(list); }
public static SelectQuery Join(this SelectQuery query, Table table, string joinField, JoinColumn foreignColumn, JoinType joinType = JoinType.Join, string extra = null) { var join = new TableJoin() { JoinType = joinType, JoinTable = table, JoinField = new Field(joinField), ForeignColumn = foreignColumn, Extra = extra }; query.TableJoins.Add(join); return(query); }
public string BuildSelectCountSql(SqlFilter filter) { SelectPart.Clear(); WherePart.Clear(); TableJoin.Clear(); OrderByPart.Clear(); Parameters.Clear(); string sql = ""; sql = _buildCount(filter); return(sql.ToString()); }
public InvokeResult NullifySelected(string strtableJoinIds) { InvokeResult result = new InvokeResult { Success = true }; try { List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); string[] arrTableJoinIds = strtableJoinIds.Split("|".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); if (arrTableJoinIds.Length == 0) { result.Success = false; result.ErrorCode = 59996; return(result); } string statementName = new TableJoin().GetUpdateMethodName(); foreach (string strtableJoinId in arrTableJoinIds) { TableJoin tableJoinInfo = new TableJoin { Id = int.Parse(strtableJoinId), Status = 0 }; /***********************begin 自定义代码*******************/ TableJoinPK pk = new TableJoinPK { Id = int.Parse(strtableJoinId) }; DeleteCascade(statements, pk, "update"); tableJoinInfo.OperatedBy = NormalSession.UserId.ToGuid(); tableJoinInfo.OperatedOn = DateTime.Now; /***********************end 自定义代码*********************/ statements.Add(new IBatisNetBatchStatement { StatementName = statementName, ParameterObject = tableJoinInfo.ToStringObjectDictionary(false), Type = SqlExecuteType.UPDATE }); } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); } catch (Exception ex) { result.Success = false; result.ErrorMessage = ex.Message; } return(result); }
public void RenderTest_WithDboAndDmacrSchema_AndFirstJoin() { string fromSchema = "dbo"; string toSchema = "dmacr"; string fromTable = "dboTable"; string toTable = "dmacrTable"; string fromField = "dboColumn"; string toField = "dmacrColumn"; int index = 0; string joinMethod = "INNER"; bool isOneToOne = false; TableJoin tj = new TableJoin(fromSchema, toSchema, fromTable, toTable, fromField, toField, index, joinMethod, isOneToOne); string expected = String.Format("[{0}].[{1}] {2} JOIN [{3}].[{4}] ON [{0}].[{1}].[{5}] = [{3}].[{4}].[{6}]", fromSchema, fromTable, joinMethod, toSchema, toTable, fromField, toField); string actual = tj.Render(); Assert.AreEqual(expected, actual); }
public void SetupJoin(IPropertyMap propertyMap, IPropertyMap parentMap, string propertyPath, JoinType joinType) { TableJoin tableJoin = GetTableJoinForPropertyPath(propertyPath); if (tableJoin == null) { tableJoin = new TableJoin(); tableJoin.PropertyMap = propertyMap; tableJoin.JoinType = joinType; if (parentMap == null) { tableJoin.JoinTree = this; this.propertyPathTraverser.SqlEmitter.GetTableAlias(propertyMap.MustGetTableMap(), propertyMap); } else { //make sure the table has an alias tableJoin.Parent = GetTableJoinForPropertyPath(propertyPath.Substring(0, propertyPath.Length - propertyMap.Name.Length - 1)); this.propertyPathTraverser.SqlEmitter.GetTableAlias(propertyMap.MustGetTableMap(), tableJoin.Parent); } } }
public string BuildSelectSql(SqlFilter filter) { SelectPart.Clear(); WherePart.Clear(); TableJoin.Clear(); OrderByPart.Clear(); Parameters.Clear(); //if (filter.SP.Count > 0) //{ // if (filter.Parmenters.Count > 0 && !string.IsNullOrEmpty(filter.SP.First().Value)) // { // string[] parms = filter.SP.First().Value.Split(','); // foreach (string parm in parms) // { // if (filter.Parmenters.ContainsKey("@" + parm)) // { // Parameters["@" + parm] = new SqlParameter("@" + parm, filter.Parmenters["@" + parm]); // } // } // } // return filter.SP.First().Key; //} string sql = ""; if (filter.Page > 0) { sql = _buildHasPage(filter); } else { sql = _buildNonePage(filter); } return(sql.ToString()); }
public void ConstructorTest() { string fromSchema = "dbo"; string toSchema = "dmacr"; string fromTable = "dboTable"; string toTable = "dmacrTable"; string fromField = "dboColumn"; string toField = "dmacrColumn"; int index = 0; string joinMethod = "INNER"; bool isOneToOne = false; TableJoin tj = new TableJoin(fromSchema, toSchema, fromTable, toTable, fromField, toField, index, joinMethod, isOneToOne); Assert.AreEqual(fromSchema, tj.FromSchema); Assert.AreEqual(toSchema, tj.ToSchema); Assert.AreEqual(fromTable, tj.FromTable); Assert.AreEqual(toTable, tj.ToTable); Assert.AreEqual(fromField, tj.FromField); Assert.AreEqual(toField, tj.ToField); Assert.AreEqual(index, tj.Index); Assert.AreEqual(joinMethod + " JOIN", tj.JoinMethod); Assert.AreEqual(isOneToOne, tj.IsOneToOne); }
public InvokeResult DeleteSelected(string strtableJoinIds) { InvokeResult result = new InvokeResult { Success = true }; try { List <IBatisNetBatchStatement> statements = new List <IBatisNetBatchStatement>(); string[] arrTableJoinIds = strtableJoinIds.Split("|".ToCharArray(), StringSplitOptions.RemoveEmptyEntries); if (arrTableJoinIds.Length == 0) { result.Success = false; result.ErrorCode = 59996; return(result); } string statementName = new TableJoin().GetDeleteMethodName(); foreach (string strtableJoinId in arrTableJoinIds) { TableJoinPK pk = new TableJoinPK { Id = int.Parse(strtableJoinId) }; //DeleteCascade(statements, pk); statements.Add(new IBatisNetBatchStatement { StatementName = statementName, ParameterObject = pk, Type = SqlExecuteType.DELETE }); } BuilderFactory.DefaultBulder().ExecuteNativeSqlNoneQuery(statements); } catch (Exception ex) { result.Success = false; result.ErrorMessage = ex.Message; } return(result); }
private IDictionary <string, object> GetMaster(IDictionary <string, object> row, TableJoin masterJoin) { var criteria = new Dictionary <string, object> { { masterJoin.MasterColumn.ActualName, row[masterJoin.DetailColumn.HomogenizedName] } }; return(_adapter.Find(masterJoin.Master.ActualName, ExpressionHelper.CriteriaDictionaryToExpression(masterJoin.Master.ActualName, criteria)).FirstOrDefault()); }
private string EmitLeftOuterJoin(TableJoin tableJoin, FromTable fromTable) //, StringBuilder whereBuilder) { StringBuilder clause = new StringBuilder() ; IPropertyMap propertyMap = tableJoin.PropertyMap ; ITableMap tableMap = propertyMap.MustGetTableMap(); ArrayList columnMaps = tableJoin.ColumnMaps; object hashObject = tableJoin.Parent; if (hashObject == null) { hashObject = propertyMap; } SqlTableAlias tbl = GetTableAlias(tableMap, hashObject); SqlColumnAlias col; SqlTableAlias idtbl = null; SqlColumnAlias idcol; ITableMap idTableMap; foreach (IColumnMap columnMap in columnMaps) { if (idtbl == null) { idTableMap = columnMap.MustGetPrimaryKeyTableMap(); idtbl = GetTableAlias(idTableMap, tableJoin); } col = tbl.GetSqlColumnAlias(columnMap); idcol = idtbl.GetSqlColumnAlias(columnMap.MustGetPrimaryKeyColumnMap()); fromTable.Alias = idtbl; fromTable.LinksToAlias = tbl; fromTable.Columns.Add(idcol); fromTable.LinksToColumns.Add(col); this.includedTableAliases.Add(idtbl); } if (tableJoin.BaseTableMap != null) { columnMaps = tableJoin.BaseColumnMaps; tbl = GetTableAlias(tableMap, hashObject); idtbl = null; foreach (IColumnMap columnMap in columnMaps) { if (idtbl == null) { if (hashObject is IPropertyMap) { hashObject = ((IPropertyMap) hashObject).ClassMap ; } idtbl = GetTableAlias(columnMap.MustGetPrimaryKeyTableMap(), hashObject); } col = tbl.GetSqlColumnAlias(columnMap); idcol = idtbl.GetSqlColumnAlias(columnMap.MustGetPrimaryKeyColumnMap()); // fromTable.Alias = idtbl; // fromTable.LinksToAlias = tbl; // fromTable.Columns.Add(idcol); // fromTable.LinksToColumns.Add(col); // this.includedTableAliases.Add(idtbl); fromTable.Alias = tbl; fromTable.LinksToAlias = idtbl; fromTable.Columns.Add(col); fromTable.LinksToColumns.Add(idcol); this.includedTableAliases.Add(tbl); // SqlSearchCondition search = select.SqlWhereClause.GetNextSqlSearchCondition(); // search.GetSqlComparePredicate(col, SqlCompareOperatorType.Equals, idcol); } } return clause.ToString(); }
private void CreateJoin(TableJoin tableJoin) { IPropertyMap propertyMap = tableJoin.PropertyMap; tableJoin.TableMap = propertyMap.MustGetTableMap(); tableJoin.ColumnMaps = propertyMap.GetAllColumnMaps(); //fails on self-join //if (tableJoin.TableMap != propertyMap.ClassMap.GetTableMap()) ArrayList idColumns = propertyMap.GetAllIdColumnMaps(); if (idColumns.Count > 0 ) { tableJoin.BaseTableMap = propertyMap.ClassMap.MustGetTableMap(); tableJoin.BaseColumnMaps = idColumns; } }
private void EmitOldStyleInnerJoin(TableJoin tableJoin) { IPropertyMap propertyMap = tableJoin.PropertyMap; ITableMap tableMap = propertyMap.MustGetTableMap(); ArrayList columnMaps = tableJoin.ColumnMaps; object hashObject = tableJoin.Parent; if (hashObject == null) { hashObject = propertyMap; } SqlTableAlias tbl = GetTableAlias(tableMap, hashObject); SqlColumnAlias col; SqlTableAlias idtbl = null; SqlColumnAlias idcol; foreach (IColumnMap columnMap in columnMaps) { if (idtbl == null) { idtbl = GetTableAlias(columnMap.MustGetPrimaryKeyTableMap(), tableJoin); } col = tbl.GetSqlColumnAlias(columnMap); idcol = idtbl.GetSqlColumnAlias(columnMap.MustGetPrimaryKeyColumnMap()); SqlSearchCondition search = select.SqlWhereClause.GetNextSqlSearchCondition(); search.GetSqlComparePredicate(col, SqlCompareOperatorType.Equals, idcol); } if (tableJoin.BaseTableMap != null) { columnMaps = tableJoin.BaseColumnMaps; tbl = GetTableAlias(tableMap, hashObject); idtbl = null; foreach (IColumnMap columnMap in columnMaps) { if (idtbl == null) { //TODO: examine this line , this causes a bug with an extra table alias in some cases //the from "classmap" already got an alias , and this line uses a new hash key and thus creating an extra alias //if (hashObject is IPropertyMap) { hashObject = tableJoin ; } //FIX: Roger, if the classmap is the root class then use the classmap as key if (hashObject is IPropertyMap) { IClassMap classMap = ((IPropertyMap) hashObject).ClassMap ; if (classMap == this.RootClassMap) { hashObject = classMap; } else { hashObject = tableJoin; } } idtbl = GetTableAlias(columnMap.MustGetPrimaryKeyTableMap(), hashObject); } col = tbl.GetSqlColumnAlias(columnMap); idcol = idtbl.GetSqlColumnAlias(columnMap.MustGetPrimaryKeyColumnMap()); SqlSearchCondition search = select.SqlWhereClause.GetNextSqlSearchCondition(); search.GetSqlComparePredicate(col, SqlCompareOperatorType.Equals, idcol); } } }
public void Visit(TableJoin component) { sql.Append(" CROSS JOIN "); component.ForeignTable.Accept(this); }
public void Visit(TableJoin component) { throw new NotImplementedException(); }
private IEnumerable <IDictionary <string, object> > GetDetail(IDictionary <string, object> row, TableJoin join) { var criteria = new Dictionary <string, object> { { join.DetailColumn.ActualName, row[join.MasterColumn.HomogenizedName] } }; return(_adapter.Find(join.Detail.ActualName, ExpressionHelper.CriteriaDictionaryToExpression(join.Detail.ActualName, criteria))); }