/// <summary>
/// 生成where 语句
/// </summary>
/// <param name="keyValus"></param>
/// <param name="withRelation"> </param>
/// <returns></returns>
internal string CreateWhere(QueryField keyValus, bool withRelation, bool isTablecolom)
{
if (null == keyValus)
{
return(string.Empty);
}
StringBuilder sb = new StringBuilder();
string sqlwhere;
string contionstr = withRelation ? keyValus.Condition.ToString() : "";
if (keyValus.Relation == RelationEnum.Between)
{
sqlwhere = (string.Format(BetweenStr,
contionstr,
keyValus.FiledName,
keyValus.Value,
keyValus.Value2));
}
else if (keyValus.Relation == RelationEnum.In || keyValus.Relation == RelationEnum.NotIn)
{
sqlwhere = (string.Format(InOrNotStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
keyValus.Value));
}
else if (keyValus.Relation == RelationEnum.LikeLeft)
{
sqlwhere = (string.Format(OtherRelationStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
string.Format("{0}", keyValus.Value)));
}
else if (keyValus.Relation == RelationEnum.LikeRight)
{
sqlwhere = (string.Format(OtherRelationStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
string.Format("{0}", keyValus.Value)));
}
else if (keyValus.Relation == RelationEnum.Like)
{
sqlwhere = (string.Format(OtherRelationStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
string.Format("{0}", keyValus.Value)));
}
else if (keyValus.Relation == RelationEnum.IsNotNull || keyValus.Relation == RelationEnum.IsNull)
{
sqlwhere = string.Format(IsnullorNotnullstr,
contionstr,
keyValus.FiledName);
}
else
if (isTablecolom)
{
sqlwhere = (string.Format(OtherRelationStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
keyValus.Value));
}
else
{
sqlwhere = (string.Format(OtherRelationStr,
contionstr,
keyValus.FiledName,
GetRelationStr(keyValus.Relation),
GetDbTypeString(keyValus.Value, ConvertToDbType(keyValus.Value))));
}
sb.AppendLine(sqlwhere);
return(sb.ToString());
}
/// <summary>
/// 生成where 语句
/// </summary>
/// <param name="keyValus"></param>
/// <returns></returns>
internal string CreateWhere(QueryField keyValus)
{
return(CreateWhere(keyValus, true));
}
internal string CreateWhere(QueryField keyValus, bool withRelation)
{
return(CreateWhere(keyValus, withRelation, false));
}
internal string CreateSql(OperateEnum operate, int pagesize, int pageindex)
{
CurrentOperate = operate;
string where = CreateWhereStr();
switch (operate)
{
case OperateEnum.None:
return(_tradstr);
case OperateEnum.Insert:
string files = string.Empty;
string values = string.Empty;
if (_keyValues.Count == 0)
{
GetKetValuesWithEntity();
}
foreach (var item in _keyValues)
{
if (AppCons.IsParmes)
{
files += item.FiledName + ",";
string parmarmName = DbHelper.GetParameterName(item.FiledName);
values += parmarmName + ",";
AddParmarms(parmarmName, item.DataType, item.Value);
}
else
{
string dbvalue = DbHelper.ReturnDBValue(item.DataType, item.Value);
if (!string.IsNullOrEmpty(dbvalue))
{
files += item.FiledName + ",";
values += dbvalue + ",";
}
}
}
return(string.Format(SqlCompilation.InsertStr, TbName, files.TrimEnd(','), values.TrimEnd(',')));
case OperateEnum.Update:
string setvalues = string.Empty;
if (_keyValues.Count == 0)
{
GetKetValuesWithEntity();
}
foreach (var item in _keyValues)
{
switch (item.Type)
{
case 0:
if (AppCons.IsParmes)
{
string parmarmName = DbHelper.GetParameterName(item.FiledName);
AddParmarms(parmarmName, item.DataType, item.Value);
setvalues += string.Format("{0}={1},",
item.FiledName,
parmarmName);
}
else
{
string dbvalue = DbHelper.ReturnDBValue(item.DataType, item.Value);
if (!string.IsNullOrEmpty(dbvalue))
{
setvalues += string.Format("{0}={1},",
item.FiledName,
dbvalue);
}
}
break;
default:
setvalues += item.FiledName + ",";
break;
}
}
return(string.Format(SqlCompilation.UpdateStr, TbName, setvalues.TrimEnd(','), where));
case OperateEnum.Delete:
return(string.Format(SqlCompilation.DeleteStr, TbName, where));
case OperateEnum.Select:
{
string order = CreateOrdeyStr();
if (ViewList == null || ViewList.Count == 0)
{
return(string.Format(SqlCompilation.SelectStr, SqlClomns,
TbName,
where,
string.IsNullOrEmpty(order)
? string.Empty
: string.Format("order by {0}", order),
string.IsNullOrEmpty(_groupByValue)
? string.Empty
: string.Format(" group by {0} ", _groupByValue.Trim(','))));
}
//视图的处理
StringBuilder jointable = new StringBuilder();
List <string> tables = new List <string>();
foreach (var item in ViewList)
{
if (!tables.Contains(item.TableName1))
{
tables.Add(item.TableName1);
jointable.AppendLine(string.Format("{0} as _{0} ", item.TableName1));
}
jointable.AppendLine(SqlCompilation.GetJoinEnum(item.Join));
jointable.AppendLine(string.Format("{0} as _{0} ", item.TableName2));
for (int i = 0; i < item.Joinfields.Count; i++)
{
item.Joinfields[i].FiledName = string.Format("_{0}.{1}", item.TableName1,
item.Joinfields[i].FiledName);
item.Joinfields[i].Value = string.Format("_{0}.{1}", item.TableName2,
item.Joinfields[i].Value);
if (i == 0)
{
jointable.AppendLine("on");
jointable.AppendLine(Sqlcom.CreateWhere(item.Joinfields[i], false));
continue;
}
jointable.AppendLine(Sqlcom.CreateWhere(item.Joinfields[i]));
}
}
return(string.Format(SqlCompilation.SelectStr, SqlClomns,
jointable,
where,
string.IsNullOrEmpty(order)
? string.Empty
: string.Format("order by {0}", order),
string.IsNullOrEmpty(_groupByValue)
? string.Empty
: string.Format(" group by {0} ", _groupByValue.Trim(','))));
}
case OperateEnum.SelectPage:
{
string order = CreateOrdeyStr();
if (ViewList == null || ViewList.Count == 0)
{
_selcountstr = string.Format("select count(1) from {0} where 1=1 {1} ", TbName, where);
//return string.Format(SqlCompilation.SelectStr, SqlClomns,
// TbName,
// where,
// string.IsNullOrEmpty(order)
// ? string.Empty
// : string.Format("order by {0}", order),
// string.IsNullOrEmpty(_groupByValue)
// ? string.Empty
// : string.Format(" group by {0} ", _groupByValue.Trim(',')));
return(DbHelper.CreatePageString(TbName, SqlClomns,
where, _groupByValue.Trim(','),
string.IsNullOrEmpty(order) ? string.Empty : order
, pageindex, pagesize, out _selcountstr));
}
//视图的处理
StringBuilder jointable = new StringBuilder();
List <string> tables = new List <string>();
foreach (var item in ViewList)
{
string table1 = string.Empty;
string table2 = string.Empty;
table1 = string.Format("_{0} ", item.TableName1);
if (!tables.Contains(item.TableName1))
{
tables.Add(item.TableName1);
jointable.AppendLine(string.Format("{0} as {1} ", item.TableName1, table1));
}
jointable.AppendLine(SqlCompilation.GetJoinEnum(item.Join));
if (!tables.Contains(item.TableName2))
{
table2 = string.Format("_{0} ", item.TableName2);
}
else
{
table2 = string.Format("_{0}{1} ", item.TableName2, tables.FindAll(p => p.Equals(item.TableName2)).Count);
}
jointable.AppendLine(string.Format("{0} as {1} ", item.TableName2, table2));
tables.Add(item.TableName2);
for (int i = 0; i < item.Joinfields.Count; i++)
{
QueryField field = new QueryField();
field.FiledName = string.Format("{0}.{1}", table1,
item.Joinfields[i].FiledName);
field.Value = string.Format("{0}.{1}", table2, item.Joinfields[i].Value);
if (i == 0)
{
jointable.AppendLine("on");
jointable.AppendLine(Sqlcom.CreateWhere(field, false, true));
continue;
}
jointable.AppendLine(Sqlcom.CreateWhere(field));
}
}
return(DbHelper.CreatePageString(jointable.ToString(), SqlClomns,
where, _groupByValue.Trim(','),
string.IsNullOrEmpty(order) ? string.Empty : order
, pageindex, pagesize, out _selcountstr));
}
}
return(string.Empty);
}