/// <summary>
/// 设置表名
/// </summary>
/// <param name="entity"></param>
/// <param name="index"> </param>
public DataBaseAction(BaseEntity entity, int index = 0)
{
_appindex = index;
Sqlcom = new SqlCompilation(DbHelper);
_entity = entity;
GetTableNameFromEntity(entity);
}
/// <summary>
/// 重新设置当前操作
/// </summary>
/// <param name="entity"></param>
/// <param name="index"> </param>
public DataBaseAction ResetAction(BaseEntity entity, int index = 0)
{
_appindex = index;
Sqlcom = new SqlCompilation(DbHelper);
ClearPams();
_entity = entity;
GetTableNameFromEntity(entity);
return(this);
}
public DataBaseAction(int index)
{
_appindex = index;
Sqlcom = new SqlCompilation(DbHelper);
}
public DataBaseAction()
{
_appindex = 0;
Sqlcom = new SqlCompilation(DbHelper);
}
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);
}
/// <summary>
/// 设置表名,性能优于 DataAction(BaseEntity entity)
/// </summary>
/// <param name="tbName"></param>
/// <param name="index"> </param>
public DataBaseAction(string tbName, int index = 0)
{
TbName = tbName;
_appindex = index;
Sqlcom = new SqlCompilation(DbHelper);
}