using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Ant.Frame;
using Ant.Data;
using System.Reflection;
using Ant.Descriptors;
using Ant.Common;
namespace Ant.ORM
{
class OracleQueryCommandBuilder : QueryCommandBuilder
{
public override QueryCommand GetQueryCommand()
{
QueryCommand cmd = null;
EntityPersistType strtype = (!object.Equals(PersistType, null)) ? PersistType : Md.PersistType;
switch (strtype)
{
case EntityPersistType.Save:
cmd = ParserInsertCommand();
break;
case EntityPersistType.Update:
cmd = ParserUpdateCommand();
break;
case EntityPersistType.Delete:
cmd = ParserDeleteCommand();
break;
case EntityPersistType.Query:
cmd = ParserSelectCommand();
break;
case EntityPersistType.Sum:
cmd = ParserSumCommand();
break;
}
return cmd;
}
/// <summary>
/// ����Insert SQL
/// </summary>
/// <returns></returns>
private QueryCommand ParserInsertCommand()
{
QueryCommand qc = new QueryCommand(); bool IsAutoincrement = false;
List<string> columnlist = new List<string>();
List<string> paramlist = new List<string>();
List<string> valuelist = new List<string>();
var typedesc = Md.FiledMeta.MemberColumnMap;
if (Md.FiledMeta.AutoIncrement.IfNotNull())
{
IsAutoincrement = true;
typedesc.Remove(Md.FiledMeta.AutoIncrement.MemberInfo);
}
foreach (var kv in typedesc)
{
MemberInfo member = kv.Key;
Md.FiledMeta.Fields.Add(member);
MappingMemberDescriptor memberDescriptor = MetaDataManager.TryGetMappingMemberDescriptor(kv.Key, Md.FiledMeta);
var val = memberDescriptor.Column.Value;
if (!MetaDataManager.IsAutoIncrement(memberDescriptor, Md))
{
columnlist.Add(string.Format("[{0}]", memberDescriptor.Column.Name));
paramlist.Add("@" + memberDescriptor.Column.Name);
QueryParameter parameter = new QueryParameter();
parameter.ParameterName = "@" + memberDescriptor.Column.Name;
parameter.DbType = (DbType)AntUtils.TryGetDbType(memberDescriptor.Column.Type);
parameter.Direction = ParameterDirection.Input;
if (val.IsNull())
val = SetDefaultValue(parameter.DbType);
parameter.Value = val;
valuelist.Add("'" + val + "'");
qc.Parameters.Add(parameter);
}
}
string sqlstr = string.Format(ConstSql.SQL_INSERT_TABLE, Md.FiledMeta.Table.Name, columnlist.AppendStrings(), paramlist.AppendStrings(),
IsAutoincrement ? GetLastInsertID() : string.Empty);
qc.CommandText = sqlstr;
qc.SqlString = string.Format(ConstSql.SQL_INSERT_TABLE, Md.FiledMeta.Table.Name, columnlist.AppendStrings(),
valuelist.AppendStrings(), IsAutoincrement ? GetLastInsertID() : string.Empty);
return qc;
}
private QueryCommand ParserUpdateCommand()
{
QueryCommand qc = new QueryCommand(); string strwhere = string.Empty;
StringBuilder sql = new StringBuilder();//SQL���
StringBuilder sqlstr = new StringBuilder();
List<string> updateColumns = new List<string>();//������
List<string> StrupdateColumns = new List<string>();
List<string> WhereColumn = new List<string>();//������
List<string> StrWhereColumn = new List<string>();//������
sql.AppendFormat("UPDATE [{0}] SET ", Md.FiledMeta.Table.Name);
List<string> condition = new List<string>();
#region �����ֶ�
if (Md.UpdateColumns.IsNull())
{
var typedesc = Md.FiledMeta.MemberColumnMap;
//if (Md.FiledMeta.AutoIncrement.IfNotNull())
//{
// //IsAutoincrement = true;
// typedesc.Remove(Md.FiledMeta.AutoIncrement.MemberInfo);
//}
foreach (var kv in typedesc)
{
MemberInfo member = kv.Key;
MappingMemberDescriptor memberDescriptor = MetaDataManager.TryGetMappingMemberDescriptor(member, Md.FiledMeta);
if (!MetaDataManager.IsAutoIncrement(memberDescriptor, Md))
{
var column = memberDescriptor.Column;
updateColumns.Add(string.Format("[{0}]=:{0}", column.Name));
qc.Parameters.Add(":" + column.Name, (DbType)AntUtils.TryGetDbType(column.Type), column.Value);
StrupdateColumns.Add(string.Format("[{0}]='{1}'", column.Name, column.Value));
}
}
}
else
{
foreach (var column in Md.UpdateColumns)
{
if (column.Value.Value.IfNotNull())
{
updateColumns.Add(string.Format("[{0}]=:{0}", column.Key));
qc.Parameters.Add(":" + column.Key, (DbType)AntUtils.TryGetDbType(column.Value.Type), column.Value.Value);
StrupdateColumns.Add(string.Format("[{0}]='{1}'", column.Key, column.Value.Value));
}
}
}
#endregion
if (Md.FiledMeta.PrimaryKey.Column.Value.IfNotNull())//ͨ���������ò�ѯ����
{
WhereColumn.Add(string.Format("[{0}]=:{0}", Md.FiledMeta.PrimaryKey.MemberInfo.Name));
qc.Parameters.Add(":" + Md.FiledMeta.PrimaryKey.Column.Name, (DbType)AntUtils.TryGetDbType(Md.FiledMeta.PrimaryKey.Column.Type), Md.FiledMeta.PrimaryKey.Column.Value);
StrWhereColumn.Add(string.Format("[{0}]='{1}'", Md.FiledMeta.PrimaryKey.Column.Name, Md.FiledMeta.PrimaryKey.Column.Value));
}
else
{//ͨ����ѯʵ����в�ѯ
#region ��ʵ�帳ֵ�IJ�ѯ��������
foreach (var key in Md.WhereColumns.Values)
{
WhereColumn.Add(string.Format("[{0}]=:{0}", key.Name));
qc.Parameters.Add(":" + key.Name, (DbType)AntUtils.TryGetDbType(key.Type), key.Value);
StrWhereColumn.Add(string.Format("[{0}]={1}", key.Name, key.Value));
}
#endregion
}
if (updateColumns.Count > 0)//�����ֶ�
{
#region ��ʱ���
if (Md.FieldsColumns.ContainsKey("Version"))
{
//WhereColumn.Add(string.Format(" {0}+0=@{0}", Md.FieldMeteDatas["Version"].ColumnName));
WhereColumn.Add(string.Format(" CONVERT(bigint,{0})=:{0}", Md.FieldsColumns["Version"].Name));
qc.Parameters.Add(":" + Md.FieldsColumns["Version"].Name, (DbType)AntUtils.TryGetDbType(Md.FieldsColumns["Version"].Type), Md.FieldsColumns["Version"].Value);
StrWhereColumn.Add(string.Format(" CONVERT(bigint,{0})='{1}'", Md.FieldsColumns["Version"].Name, Md.FieldsColumns["Version"].Value));
}
#endregion
sql.Append(string.Join(",", updateColumns.ToArray())).Append(" WHERE ").Append(string.Join(" AND ", WhereColumn.ToArray()));
if (!string.IsNullOrEmpty(Md.WhereSql))
{//���Զ����ѯ������ʱ��
if (Md.WhereSql.Trim().Length > 0) WhereColumn.Add(Md.WhereSql);
}
if (Predicate.IfNotNull())
{
var FlieldWhere = new FieldValueCollection();
strwhere = Predicate.GetSql(FlieldWhere, qc);//ƴ��SQL���
if (!strwhere.IsEmpty())
{
sql.AppendFormat(" and {0} ", strwhere);
foreach (string key in FlieldWhere.Keys)
{
strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
}
sqlstr.AppendFormat(" and {0} ", strwhere);
}
}
qc.CommandText = sql.ToString();
qc.SqlString = string.Format(ConstSql.SQL_UPDATE_TABLE, Md.FiledMeta.Table.Name, string.Join(",", StrupdateColumns.ToArray()), string.Join(" AND ", StrWhereColumn.ToArray()) + (strwhere.IsEmpty() ? strwhere : " and " + strwhere));
}
return qc;
}
private QueryCommand ParserDeleteCommand()
{
QueryCommand qc = new QueryCommand();
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
List<string> WhereColumn = new List<string>();//�������
List<string> WhereColumnStr = new List<string>();
if (!Md.FieldsColumns.IsNull())
{
foreach (string key in Md.FieldsColumns.Keys)//��ѯ����
{
//if (!Object.Equals(Md.Fields[key], null) || !Object.Equals(Md.Fields[key], DBNull.Value))
if (!(Md.FieldsColumns[key].IsNull()))
{
if (!Md.FieldsColumns[key].Value.ToString().IsEmpty())
{
WhereColumn.Add(string.Format(" [{0}].{1}=:{2} ", Md.FiledMeta.Table.Name, Md.FieldsColumns[key].Name, Md.FieldsColumns[key].Name));
qc.Parameters.Add(":" + Md.FieldsColumns[key].Name, (DbType)AntUtils.TryGetDbType(Md.FieldsColumns[key].Type), Md.FieldsColumns[key].Value);
WhereColumnStr.Add(string.Format(" [{0}].{1}={2} ", Md.FiledMeta.Table.Name, Md.FieldsColumns[key].Name, Md.FieldsColumns[key].Value));
}
}
}
}
if (!Md.WhereSql.IsNull())
{
if (Md.WhereSql.Trim().Length > 0)
{
string sqlwherestr = (Md.WhereSql.StartsWith("(") && Md.WhereSql.EndsWith(")")) ? Md.WhereSql : " (" + Md.WhereSql + ") ";
WhereColumn.Add(sqlwherestr);
WhereColumnStr.Add(sqlwherestr);
}
}
sql.AppendFormat(ConstSql.SQL_DELETE_DATA, Md.FiledMeta.Table.Name, (WhereColumn.Count > 0) ? string.Format(" and {0}", string.Join(" AND ", WhereColumn.ToArray())) : string.Empty);
sqlstr.AppendFormat(ConstSql.SQL_DELETE_DATA, Md.FiledMeta.Table.Name, (WhereColumnStr.Count > 0) ? string.Format(" and {0}", string.Join(" AND ", WhereColumnStr.ToArray())) : string.Empty);
if (Predicate.IfNotNull())
{
var FlieldWhere = new FieldValueCollection();
string strwhere = Predicate.GetSql(FlieldWhere, qc);//ƴ��SQL���
if (!strwhere.IsEmpty())
{
sql.AppendFormat(" and {0} ", strwhere);
foreach (string key in FlieldWhere.Keys)
{
strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
}
sqlstr.AppendFormat(" and {0} ", strwhere);
}
}
qc.CommandText = sql.ToString();
qc.SqlString = sqlstr.ToString();
return qc;
}
#region ���� Select SQL���
/// <summary>
/// ���� Select SQL���
/// </summary>
/// <returns></returns>
private QueryCommand ParserSelectCommand()
{
QueryCommand qc = new QueryCommand() { DbParmChar = DbParmChar };
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
switch (SqlType)
{
case SqlEnum.MainSql:
{
string topnum = Top.IsEmpty() ? string.Empty : " limit " + Top;
sql.AppendFormat(ConstSql.MYSQL_GET_VALUE, topnum, FieldColumn(), Md.FiledMeta.Table.Name);//��ʾ�Զ�����ֶ�
List<string> WhereColumn = new List<string>();//�������
List<string> WhereColumnStr = new List<string>();//�������
if (Md.FieldsColumns.IfNotNull())//��ѯ����
{
foreach (string key in Md.FieldsColumns.Keys)
{
var field = Md.FieldsColumns[key];
if (!(field.Value.IsNull()))
{
if (!field.Value.ToString().IsEmpty())
{
WhereColumn.Add(string.Format(" [{0}].{1}=:{2} ", Md.FiledMeta.Table.Name, field.Name,
field.Name));
qc.Parameters.Add(":" + field.Name, (DbType)AntUtils.TryGetDbType(field.Type), field.Value);
WhereColumnStr.Add(string.Format(" [{0}].{1}='{2}' ", Md.FiledMeta.Table.Name, field.Name, field.Value));
}
}
}
}
else
{
if (!Md.WhereSql.IsEmpty()) sql.AppendFormat(" and {0} ", Md.WhereSql);
}
sqlstr.Append(sql.ToString());
if (WhereColumn.Count > 0) sql.AppendFormat(" and {0} ", string.Join(" AND ", WhereColumn.ToArray()));
if (WhereColumnStr.Count > 0) sqlstr.AppendFormat(" and {0} ", string.Join(" AND ", WhereColumnStr.ToArray()));
if (Predicate.IfNotNull())
{
var FlieldWhere = new FieldValueCollection();
string strwhere = Predicate.GetSql(FlieldWhere, qc);//ƴ��SQL���
if (!strwhere.IsEmpty())
{
sql.AppendFormat(" and {0} ", strwhere);
foreach (string key in FlieldWhere.Keys)
{
strwhere = strwhere.Replace(key.ToString(), "'" + FlieldWhere[key].ToString() + "'");
}
sqlstr.AppendFormat(" and {0} ", strwhere);
}
}
string orderstr = GetOrderBy();
sql.Append(orderstr);
sqlstr.Append(orderstr);
WhereColumn = null; WhereColumnStr = null;
}
break;
}
qc.CommandText = sql.ToString();
qc.SqlString = sqlstr.ToString();
sqlstr = null; sql = null;
return qc;
}
#region ���� ��ѯ��ҳ SQL���
/// <summary>
/// ���� Select SQL���
/// </summary>
/// <returns></returns>
private QueryCommand ParserQueryPageCommand()
{
List<string> WhereColumn = new List<string>();
List<string> WhereColumnStr = new List<string>();//�������
QueryCommand qc = new QueryCommand() { DbParmChar = DbParmChar };
StringBuilder sql = new StringBuilder();
if (Md.FieldsColumns.IfNotNull())//��ѯ����
{
foreach (string key in Md.FieldsColumns.Keys)
{
var field = Md.FieldsColumns[key];
if (!(field.Value.IsNull()))
{
if (!field.Value.ToString().IsEmpty())
{
string parmname = string.Format("@{0}_{1}", field.Name, qc.Parameters.Count);
WhereColumn.Add(string.Format(" [{0}].{1}=:{2} ", Md.FiledMeta.Table.Name, field.Name,
parmname));
qc.Parameters.Add(":" + parmname, (DbType)AntUtils.TryGetDbType(field.Type), field.Value);
WhereColumnStr.Add(string.Format(" [{0}].{1}='{2}' ", Md.FiledMeta.Table.Name, field.Name, field.Value));
}
}
}
}
string strsqlwhere = string.Join(" AND ", WhereColumn.ToArray());
if (Md.WhereSql.Length > 0) strsqlwhere = strsqlwhere + " and " + Md.WhereSql;
if (Predicate.IfNotNull())
{
var FlieldWhere = new FieldValueCollection();
string strwhere = Predicate.GetSql(FlieldWhere, qc);//ƴ��SQL���
if (!strwhere.IsEmpty())
{
strsqlwhere = strsqlwhere + " and " + strwhere;
}
}
sql.AppendFormat(ConstSql.MYSQL_PAGE_VALUE, FieldColumn(), Md.FiledMeta.Table.Name, strsqlwhere, GetOrderBy(), (((PageNo - 1) * PageSize) + 1), (PageNo * PageSize));
if (!qc.SqlNotes.IsNull())
qc.CommandText = sql.ToString();
else
qc.CommandText = sql.ToString();
string sqlstr = sql.ToString();
qc.SqlString = sqlstr;
return qc;
}
#endregion
/// <summary>
/// ��ȡ�Ӷ���
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="M"></typeparam>
/// <returns></returns>
public QueryCommand GetChildData<T, M>()
{
QueryCommandBuilders sqls = new QueryCommandBuilders();
//Type entityType = typeof(M);
////MetaData tableMataData = MetaDataManager.GetMetaData(entityType);
//int sqlCount = Md.FiledMeta.ChildObjects.Count;
////List<string> sKeys = tableMataData.ChildObjects.Keys.ToList<string>();
QueryCommand sql = new QueryCommand();
//foreach (ChildMataData mod in Md.FiledMeta.ChildObjects.Values)
//{
// sql.SqlString = new StringBuilder("select * from " + mod.ChildTable).ToString();
// sql.SqlType = SqlEnum.ChildSql;
// sql.Key = mod.Key;
//}
return sql;
}
#endregion
/// <summary>
/// ͨ���ת��
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
protected static string GetEscape(string str)
{
return str.Replace("!", "!!").Replace("%", "!%").Replace("_", "!_");
}
/// <summary>
/// ��ȡ����
/// </summary>
/// <returns></returns>
public string GetOrderBy()
{
string orderbystr = string.Empty;
if (Sort.IsNull())
{
orderbystr = (orderbystr.Trim().IndexOf("order by", StringComparison.InvariantCultureIgnoreCase) != -1) ? string.Format(" {0} ", Md.FiledMeta.PrimaryKey.MemberInfo.Name) : string.Format(" order by {0}", Md.FiledMeta.PrimaryKey.MemberInfo.Name);
}
else
{
string orderby = string.Empty;
foreach (ISort sort in Sort)
{
if (orderby.IsEmpty())
{
orderby = sort.PropertyName + (sort.Ascending ? " ASC" : " DESC");
}
else
{
orderby += " ," + sort.PropertyName + (sort.Ascending ? " ASC" : " DESC");
}
}
orderbystr = string.Format(" ORDER BY {0} ", orderby);
}
return orderbystr;
}
/// <summary>
/// ��ȡ��ʾ�ֶ���
/// </summary>
/// <returns></returns>
private string FieldColumn()
{
List<string> columns = new List<string>();
string fieldcolumn = string.Empty;
if (!ShowField.IsEmpty())
{
fieldcolumn = ShowField;
}
else
{
foreach (MemberInfo df in Md.FiledMeta.MemberColumnMap.Keys)//��ʾ�ֶ�
{
columns.Add(string.Format("{0}", "[" + Md.FiledMeta.Table.Name + "]." + df.Name));
}
fieldcolumn = string.Join(",", columns.ToArray());
}
fieldcolumn = fieldcolumn.IsEmpty() ? " * " : fieldcolumn;
return fieldcolumn;
}
/// <summary>
/// ������
/// </summary>
/// <returns></returns>
public string GetLastInsertID()
{
return "select emp_sequence.nextval from dual;";
}
/// <summary>
/// ��ȡĬ��ֵ
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
private object SetDefaultValue(DbType ColumnType)
{
object value = null;
switch (ColumnType)
{
case DbType.String:
{
value = "";
}
break;
case DbType.Boolean:
{
value = false;
}
break;
case DbType.Decimal:
case DbType.Double:
case DbType.Int32:
case DbType.Int16:
case DbType.Int64:
{
value = 0;
}
break;
case DbType.DateTime:
{
value = DateTime.Now;
}
break;
}
return value;
}
private QueryCommand ParserSumCommand()
{
QueryCommand qc = new QueryCommand();
StringBuilder sql = new StringBuilder();
sql.AppendFormat("SELECT COUNT(*) FROM {0}", Md.FiledMeta.Table.Name);
if (!string.IsNullOrEmpty(Md.WhereSql))
{
sql.AppendFormat(" WHERE {0}", Md.WhereSql);
}
qc.CommandText = sql.ToString();
return qc;
}
}
}