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