MESProductionWeb/AntORM/Ant.ORM/DataBaise/HelpSQL/PersistTran.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using System.Data.Common;
using System.Reflection;
using Ant.Data;
using Ant.ORM;
using Ant.Frame;
namespace Ant.ORM
{
    /// <summary>
    /// 带事务的数据持久类
    /// <remarks>要求配置Ant.Data配置节</remarks>
    /// </summary>
    public class PersistTran
    {

        private DataAccess DAO = DataAccessFactory.GetWriteDataDefault;
        /// <summary>
        /// 带事务执行
        /// </summary>
        /// <param name="entity"></param>
        public void OperteTransactionRun(object addentity, object updateentity, object delentity)
        {
            try
            {
                DAO.BeginTransaction();


                SaveEntity(addentity);
                UpdateEntityWhere(updateentity, "UsID='ffff'");
                DeleteEntity(delentity);


                DAO.Commit();
            }
            catch (Exception ex)
            {
               
                DAO.RollBack();
                DAO.Close();
                DAO = null;
            }

        }
        #region 将实体转成SQL语句


        #region 插入INTSET方法
        /// <summary>
        /// 保存实体对象
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public bool SaveEntity(object entity)
        {
            MetaData md = new MetaData();
            md = MetaDataManager.GetMetaData(entity);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Save;
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }
        /// <summary>
        /// 保存实体对象
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="fields">实体属性列表</param>
        /// <returns></returns>
        public bool SaveFieldValue<T>(FieldValueCollection fields)
        {
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;//实体类型
            qcBuilder.PersistType = EntityPersistType.Save;//操作类型
            qcBuilder.Fields = fields;//实体属性集合
            QueryCommand cmd = qcBuilder.GetQueryCommand();//转成SQL语句
            int num = DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters);
            DAO = null;
            return num > 0;

        }

        public bool SaveFieldValueEntity<T>(object entity)
        {
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;//实体类型
            qcBuilder.PersistType = EntityPersistType.Save;//操作类型
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            QueryCommand cmd = qcBuilder.GetQueryCommand();//转成SQL语句
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }
        #endregion

        #region 更新UPDATE方法
        /// <summary>
        /// 更新实体对象在没有filter条件下，以实体中为主键的字段为where条件更新数据库
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <returns></returns>
        public bool UpdateEntity(object entity)
        {
            MetaData md = MetaDataManager.GetMetaData(entity);
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Update; ;
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }



        /// <summary>
        /// 更新实体对象在有filter（where）条件来更新数据库
        /// </summary>
        /// <param name="entity">实体对象</param>
        /// <param name="filter">更新条件表达式</param>
        /// <returns></returns>
        public bool UpdateEntityWhere(object entity, string filter)
        {
            MetaData md = MetaDataManager.GetMetaData(entity);
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Update; ;
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            // qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }


        public bool UpdateEntityFieldValue<T>(object entity, string filter)
        {
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Update; ;
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            //qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }

        /// <summary>
        /// 更新实体对象在没有filter条件下，以实体中为主键的字段为where条件更新数据库
        /// </summary>
        /// <typeparam name="T">实体属性集合</typeparam>
        /// <returns></returns>
        public bool UpdateFieldValue<T>(FieldValueCollection fields)
        {
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Update;
            qcBuilder.Fields = fields;//实体属性集合
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }

        /// <summary>
        /// 更新实体对象在没有filter条件下，以实体中为主键的字段为where条件更新数据库
        /// </summary>
        /// <typeparam name="T">实体属性集合</typeparam>
        /// <param name="fields">实体属性列表</param>
        /// <param name="filter">更新条件表达式</param>
        /// <returns></returns>
        public bool UpdateFieldValueWhere<T>(FieldValueCollection fields, string filter)
        {
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Update;
            qcBuilder.Fields = fields;//实体属性集合
            //qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }

        #endregion

        #region 删除DELETE方法
        public bool DeleteEntity(object entity)
        {
            MetaData md = new MetaData();
            md = MetaDataManager.GetMetaData(entity);
            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Delete;
           // qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }
        public bool DeleteEntityWhere(object entity, string filter)
        {
            MetaData md = new MetaData();
            md = MetaDataManager.GetMetaData(entity);

            //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Delete;
            //qcBuilder.Fields = MetaDataManager.GetEntityFields(entity);
            // qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
        }

        public bool DeleteFieldValue<T>(FieldValueCollection keyFields)
        {
            try
            {
                MetaData md = new MetaData();
                md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));
                //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
                MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
                qcBuilder.EntityType = md.FiledMeta.EntityType;
                qcBuilder.PersistType = EntityPersistType.Delete;
                qcBuilder.Fields = keyFields;
                QueryCommand cmd = qcBuilder.GetQueryCommand();
                return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
            }
            catch (Exception ex)
            {
               
                return false;
            }
        }
        public bool DeleteFieldValueWhere<T>(FieldValueCollection keyFields, string filter)
        {
            try
            {
                MetaData md = new MetaData();
                md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));
                //QueryCommandBuilder qcBuilder = QueryCommandBuilder.Instance(DAO.DatabaseType);
                MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
                qcBuilder.EntityType = md.FiledMeta.EntityType;
                qcBuilder.PersistType = EntityPersistType.Delete;
                qcBuilder.Fields = keyFields;
                // qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
                QueryCommand cmd = qcBuilder.GetQueryCommand();
                return DAO.ExecuteNonQuery(cmd.CommandText, cmd.Parameters) > 0;
            }
            catch (Exception ex)
            {
               
                return false;
            }
        }
        #endregion

        #region 查询SELECT方法
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns></returns>
        public object Query<T>()
        {

            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Query;
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            DataTable dt = DAO.ExecuteDataTable(cmd.CommandText, cmd.Parameters);
            if (!object.Equals(dt, null))
            {
                return DataTableToList<T>(dt);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="filter">查询条件表达式</param>
        /// <returns></returns>
        public object Query<T>(string filter)
        {

            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Query;
            //qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            DataTable dt = DAO.ExecuteDataTable(cmd.CommandText, cmd.Parameters);
            if (!object.Equals(dt, null))
            {
                return DataTableToList<T>(dt);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="filter">查询条件表达式</param>
        /// <returns></returns>
        public object Query<T>(string filter, string orderby)
        {

            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Query;
            //qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
            //qcBuilder.Ordering = orderby;
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            DataTable dt = DAO.ExecuteDataTable(cmd.CommandText, cmd.Parameters);
            if (!object.Equals(dt, null))
            {
                return DataTableToList<T>(dt);
            }
            else
            {
                return null;
            }



        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="pageSize">分页大小</param>
        /// <param name="pageNo">查询第多少页</param>
        /// <returns></returns>
        public object Query<T>(int pageSize, int pageNo, string orderby)
        {

            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Query;
            qcBuilder.PageNo = pageNo;
            qcBuilder.PageSize = pageSize;
            //qcBuilder.Ordering = orderby;
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            DataTable dt = DAO.ExecuteDataTable(cmd.CommandText, cmd.Parameters, cmd.StartRecord, cmd.MaxRecords);
            if (!object.Equals(dt, null))
            {
                return DataTableToList<T>(dt);
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="filter">条件表达式</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="pageNo">查询第多少页</param>
        /// <returns></returns>
        public object Query<T>(string filter, int pageSize, int pageNo, string orderby)
        {

            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
            qcBuilder.EntityType = md.FiledMeta.EntityType;
            qcBuilder.PersistType = EntityPersistType.Query;
            qcBuilder.Md.WhereSql = ModiFilter(qcBuilder.EntityType, filter);
            qcBuilder.PageNo = pageNo;
            qcBuilder.PageSize = pageSize;
            //qcBuilder.Md.OrderBy = orderby;
            QueryCommand cmd = qcBuilder.GetQueryCommand();
            DataTable dt = DAO.ExecuteDataTable(cmd.CommandText, cmd.Parameters, cmd.StartRecord, cmd.MaxRecords);
            if (!object.Equals(dt, null))
            {
                return DataTableToList<T>(dt);
            }
            else
            {
                return null;
            }

        }
        #endregion

        #region 统计表记录
        /// <summary>
        /// 统计表记录
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <returns></returns>
        public int Sum<T>()
        {
            try
            {
                MetaData md =new MetaData();
                md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));
                MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
                qcBuilder.EntityType = md.FiledMeta.EntityType;
                qcBuilder.PersistType = EntityPersistType.Sum;
                QueryCommand cmd = qcBuilder.GetQueryCommand();
                return (int)DAO.ExecuteScalar(cmd.CommandText);
            }
            catch (Exception ex)
            {
               
                return -1;
            }
        }
        /// <summary>
        /// 统计符合条件表的记录
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="filter">查询条件表达式</param>
        /// <returns></returns>
        public int Sum<T>(string filter)
        {

            try
            {

                MetaData md = new MetaData();
                md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));
                MSSqlQueryCommandBuilder qcBuilder = new MSSqlQueryCommandBuilder();
                qcBuilder.EntityType = md.FiledMeta.EntityType;
                qcBuilder.PersistType = EntityPersistType.Sum;
                //qcBuilder.Filter = ModiFilter(qcBuilder.EntityType, filter);
                QueryCommand cmd = qcBuilder.GetQueryCommand();

                return (int)DAO.ExecuteScalar(cmd.CommandText);

            }
            catch (Exception ex)
            {
               
                return -1;
            }
        }
        #endregion

        #endregion

        bool ExecuteSql(string sql)
        {
            return DAO.ExecuteNonQuery(sql) > 0;
        }

        #region 将DataTable转换成List列表
        /// <summary>
        /// 将DataTable转换成List列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private object DataTableToList<T>(DataTable dataTable)
        {
            List<T> list = new List<T>();
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(typeof(T));//获取实体的元数据
            foreach (DataRow row in dataTable.Rows)
            {
                T obj = Activator.CreateInstance<T>();
                foreach (PropertyInfo info in obj.GetType().GetProperties())
                {
                    //if (!Convert.IsDBNull(row[md.FiledMeta.FieldMeteDatas[info.Name].ColumnName]))
                    //{
                    //    info.SetValue(obj, row[md.FiledMeta.FieldMeteDatas[info.Name].ColumnName], null);
                    //}
                }
                list.Add(obj);
            }
            return list;
        }
        /// <summary>
        /// 查询条件表达式
        /// </summary>
        /// <param name="type"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        private string ModiFilter(Type type, string filter)
        {
            string tmpFilter = filter;
            if (!tmpFilter.StartsWith(" "))
                tmpFilter = " " + tmpFilter;
            MetaData md = new MetaData();
            md.FiledMeta = MetaDataManager.GetMetaData(type);//获取实体的元数据
            //foreach (string s in md.FiledMeta.FieldMeteDatas.Keys)
            //{
            //    string pattern = new StringBuilder(@"\s").Append(s).Append(@"[\s|+|-|*|/|=|&|\^|!|>|<|~|(|)]").ToString();
            //    if (Regex.IsMatch(tmpFilter, pattern))
            //    {
            //        MatchCollection matches = Regex.Matches(tmpFilter, pattern);
            //        foreach (Match match in matches)
            //        {
            //            tmpFilter = Regex.Replace(tmpFilter, match.Value, match.Value.Replace(s, md.FiledMeta.FieldMeteDatas[s].ColumnName));
            //        }
            //    }
            //}
            return tmpFilter;
        }
        #endregion

    }
}