/// <summary>
/// 生成连接表条件字符串
/// </summary>
/// <returns></returns>
public static string GetJoinConditionString(IEnumerable<NeedField> needFields, JoinCriterion joinCriterion)
{
StringBuilder result = new StringBuilder();
IDictionary<int, string> tableName = new Dictionary<int, string>();
int index = 0;
foreach (var item in needFields) //去除重复的数据库表名
{
if (!tableName.Values.Contains(item.TableName))
{
tableName.Add(index, item.TableName);
index++;
}
}
if (tableName.Keys.Count() == 1)
{
return " FROM " + tableName[0];
}
if (tableName.Keys.Count() > 1)
{
result.Append(" FROM ").Append(tableName[0]);
if (joinCriterion.DefaultJoinFieldName == "" || joinCriterion.DefaultJoinFieldName == null)
{
if (tableName.Keys.Count() != joinCriterion.JoinFieldDictionary.Keys.Count())
{
throw new KeyNotFoundException("连接的表数目和字段数目不一致");
}
for (int itemNo = 1; itemNo < index; itemNo++)
{
result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]",
GetJoinType(joinCriterion), tableName[itemNo], tableName[0],
joinCriterion.JoinFieldDictionary[tableName[0]], tableName[itemNo], joinCriterion.JoinFieldDictionary[tableName[itemNo]]));
}
}
else
{
for (int itemNo = 1; itemNo < index; itemNo++)
{
if (joinCriterion.DefaultJoinFieldName != "" || joinCriterion.DefaultJoinFieldName != null)
result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]",
GetJoinType(joinCriterion), tableName[itemNo], tableName[0],
joinCriterion.DefaultJoinFieldName, tableName[itemNo], joinCriterion.DefaultJoinFieldName));
}
}
}
else
{
throw new Exception("连接字符串生产错误");
}
return result.ToString();
}
/// <summary>
/// 获得连接方式
/// </summary>
/// <param name="joinCriterion"></param>
/// <returns></returns>
private static string GetJoinType(JoinCriterion joinCriterion)
{
switch (joinCriterion.JoinType)
{
case JoinType.JOIN:
return("JOIN");
case JoinType.INNER_JOIN:
return("INNER JOIN");
case JoinType.LEFT_JOIN:
return("LEFT JOIN");
case JoinType.RIGHT_JOIN:
return("RIGHT JOIN");
case JoinType.FULL_JOIN:
return("FULL JOIN");
default:
throw new Exception("没有对应的连接方式");
}
}
/// <summary>
/// 生成连接表条件字符串
/// </summary>
/// <returns></returns>
public static string GetJoinConditionString(IEnumerable <NeedField> needFields, JoinCriterion joinCriterion)
{
StringBuilder result = new StringBuilder();
IDictionary <int, string> tableName = new Dictionary <int, string>();
int index = 0;
foreach (var item in needFields) //去除重复的数据库表名
{
if (!tableName.Values.Contains(item.TableName))
{
tableName.Add(index, item.TableName);
index++;
}
}
if (tableName.Keys.Count() == 1)
{
return(" FROM " + tableName[0]);
}
if (tableName.Keys.Count() > 1)
{
result.Append(" FROM ").Append(tableName[0]);
if (joinCriterion.DefaultJoinFieldName == "" || joinCriterion.DefaultJoinFieldName == null)
{
if (tableName.Keys.Count() != joinCriterion.JoinFieldDictionary.Keys.Count())
{
throw new KeyNotFoundException("连接的表数目和字段数目不一致");
}
for (int itemNo = 1; itemNo < index; itemNo++)
{
result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]",
GetJoinType(joinCriterion), tableName[itemNo], tableName[0],
joinCriterion.JoinFieldDictionary[tableName[0]], tableName[itemNo], joinCriterion.JoinFieldDictionary[tableName[itemNo]]));
}
}
else
{
for (int itemNo = 1; itemNo < index; itemNo++)
{
if (joinCriterion.DefaultJoinFieldName != "" || joinCriterion.DefaultJoinFieldName != null)
{
result.Append(String.Format(" {0} {1} ON {2}.[{3}]={4}.[{5}]",
GetJoinType(joinCriterion), tableName[itemNo], tableName[0],
joinCriterion.DefaultJoinFieldName, tableName[itemNo], joinCriterion.DefaultJoinFieldName));
}
}
}
}
else
{
throw new Exception("连接字符串生产错误");
}
return(result.ToString());
}
/// <summary>
/// 返回最近两天的历史数据表
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
private DataTable GetHistoryDataTable(string viewName)
{
bool flag = false;
DataTable result = new DataTable();
IList<NeedField> needFields = new List<NeedField>();
IEnumerable<DataSetInformation> dataSetInfor = GetDataSetInformation(viewName);
foreach (var item in dataSetInfor)
{
if (flag == false)
{
needFields.Add(new NeedField
{
FieldName = "v_date",
TableName = item.TableName,
VariableName = "date"
});
flag = true;
}
needFields.Add(new NeedField
{
FieldName = item.FieldName,
TableName = item.TableName,
VariableName = item.ViewId
});
}
JoinCriterion joinCriterion = new JoinCriterion
{
DefaultJoinFieldName = "v_date",
JoinType = JoinType.FULL_JOIN
};
ComplexQuery cmpquery = new ComplexQuery(needFields,joinCriterion);
//cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(1).Date, CriteriaOperator.LessThan);
//cmpquery.AddCriterion("v_date", DateTime.Now.AddDays(-1).Date, CriteriaOperator.MoreThan);
result = dataFactory.Query(cmpquery);
return result;
}
public ComplexQuery(JoinCriterion joinCriterion, IEnumerable<NeedField> needFields, IEnumerable<Criterion> criterions)
{
_joinCriterion = joinCriterion;
_needFields = needFields.ToList();
_criterions = criterions.ToList();
}
public ComplexQuery(IEnumerable<NeedField> needFields, JoinCriterion joinCriterion)
{
_joinCriterion = joinCriterion;
_needFields = needFields.ToList();
_criterions = new List<Criterion>();
}
public void AddJoinCriterion(string defaultJoinFieldName, JoinType joinType)
{
JoinCriterion join = new JoinCriterion
{
DefaultJoinFieldName = defaultJoinFieldName,
JoinType = joinType
};
_joinCriterion = join;
}
/// <summary>
/// 连接字段字典，键为表名，值为字段名
/// </summary>
/// <param name="joinFieldDictionary"></param>
/// <param name="joinType"></param>
public void AddJoinCriterion(IDictionary<string, string> joinFieldDictionary, JoinType joinType)
{
JoinCriterion join = new JoinCriterion();
join.JoinFieldDictionary = joinFieldDictionary;
join.JoinType = joinType;
_joinCriterion = join;
}
/// <summary>
/// 获得连接方式
/// </summary>
/// <param name="joinCriterion"></param>
/// <returns></returns>
private static string GetJoinType(JoinCriterion joinCriterion)
{
switch (joinCriterion.JoinType)
{
case JoinType.JOIN:
return "JOIN";
case JoinType.INNER_JOIN:
return "INNER JOIN";
case JoinType.LEFT_JOIN:
return "LEFT JOIN";
case JoinType.RIGHT_JOIN:
return "RIGHT JOIN";
case JoinType.FULL_JOIN:
return "FULL JOIN";
default:
throw new Exception("没有对应的连接方式");
}
}
public AutoCreateView(string connectionString, IEnumerable<NeedField> needFields, JoinCriterion joinCriterion)
{
_connectionString = connectionString;
_needFields = needFields;
_joinCriterion = joinCriterion;
}