Beispiel #1
0
        /// <summary>
        /// 生成更新SQL语句
        /// </summary>
        /// <param name="entityType"></param>
        /// <returns>返回</returns>
        public static string ColumnSqlForUpdate(object entity, PropertyInfo propertyInfo, List <SqlParameter> parameter)
        {
            var config        = EntityParser.GetColumnConfig(propertyInfo);       //获取对属性/列的配置信息
            var propertyValue = propertyInfo.GetValue(entity, null);

            if (config.UsePropertyForUpdate || propertyValue == null)               //如果使用属性的值更新数据库中的值
            {
                var columnName      = EntityParser.GetColumnName(propertyInfo);
                var valueExpression = "@" + columnName;

                if (config.DBType == DBType.PropertyType)                  //如果数据库中的类型 和 属性的类型相同
                {
                    parameter.Add(new SqlParameter(valueExpression, propertyValue));
                }
                else                   //如果要转换类型
                {
                    propertyValue = EntityParser.ConvertPropertyValue(propertyInfo, propertyValue, config.DBType);
                    parameter.Add(new SqlParameter(valueExpression, propertyValue));
                }
                return(valueExpression);
            }
            else
            {
                if (!string.IsNullOrEmpty(config.UpdateSQL))                  //如果不用属性,就使用特定的SQL语句指定列的值
                {
                    return(config.UpdateSQL);
                }
            }

            return(string.Empty);
        }
Beispiel #2
0
        public static string SelectSQL(Type entityType, string where = "")
        {
            string selectSQL;

            if (!selectSQLCache.TryGetValue(entityType, out selectSQL))               //如果从缓存里面没有找到
            {
                var columns = entityType.GetProperties().Where(item =>
                {
                    return(!EntityParser.IsIgnore(item));
                }).Select(item => string.Format(" [{0}]", EntityParser.GetColumnName(item)));

                if (!columns.Any())
                {
                    throw new Exception(string.Format("类型{0}没有任何可以SELECT的列", entityType.FullName));
                }

                selectSQL = string.Format("SELECT{0} FROM {1}",
                                          string.Join(",", columns),
                                          EntityParser.GetTableName(entityType)
                                          );
                selectSQLCache.Add(entityType, selectSQL);
            }

            if (!string.IsNullOrEmpty(where))
            {
                where = " WHERE " + where;
            }

            return(selectSQL + where);
        }
Beispiel #3
0
        /// <summary>
        /// 返回null或空字符串表示不生成SQL语句
        /// </summary>
        /// <returns></returns>
        public static string ColumnSqlForInsert(object entity, PropertyInfo propertyInfo, List <SqlParameter> parameter)
        {
            var config = EntityParser.GetColumnConfig(propertyInfo);

            if (config.UsePropertyForInsert)
            {
                var columnName      = EntityParser.GetColumnName(propertyInfo);
                var valueExpression = "@" + columnName;
                var propertyValue   = propertyInfo.GetValue(entity, null);
                if (config.DBType == DBType.PropertyType)                  //如果直接用属性
                {
                    parameter.Add(new SqlParameter(valueExpression, propertyValue));
                }
                else                   //如果要转换类型
                {
                    propertyValue = EntityParser.ConvertPropertyValue(propertyInfo, propertyValue, config.DBType);
                    parameter.Add(new SqlParameter(valueExpression, propertyValue));
                }
                return(valueExpression);
            }
            else
            {
                if (!string.IsNullOrEmpty(config.InsertSQL))
                {
                    return(config.InsertSQL);
                }
            }

            return(string.Empty);
        }
Beispiel #4
0
        /// <summary>
        /// 根据主键删除一个实体
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="key">主键的值</param>
        public static void DeleteByKey <TEntity>(object key) where TEntity : SQLEntity
        {
            var keyInfo = EntityParser.GetKey(typeof(TEntity));

            var keyName = keyInfo.KeyName;

            var sql = SqlBuilder.DeleteSQL(EntityParser.GetTableName(typeof(TEntity)), string.Format("{0} = @{0}", keyName));

            SQLServerHelper.ExecuteNonQuery(sql, new SqlParameter("@" + keyName, key));
        }
Beispiel #5
0
        /// <summary>
        /// 获取一个实体,只有主键的值,并且状态为NotChanged
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="keyValue">主键的值</param>
        /// <param name="isFill">是否从数据库中填充所有属性的值</param>
        /// <returns></returns>
        public static TEntity GetEntityForUpdate <TEntity>(object keyValue, bool isFill = false) where TEntity : SQLEntity
        {
            var key = EntityParser.GetKey(typeof(TEntity));

            var se = Activator.CreateInstance <TEntity>();        //创建实体对象

            se.EntityState = EntityState.NotInitialized;          //未初始化
            key.Key.SetValue(se, keyValue, null);                 //设置主键的值
            se.EntityState = EntityState.NotChanged;              //未更改
            return(se);
        }
Beispiel #6
0
        /// <summary>
        /// 刷新一个已存在的实体(如果一个实体类的属性的值是旧的,可以使用此方法从数据库中刷新值)
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static TEntity RefreshEntity <TEntity>(TEntity entity) where TEntity : SQLEntity
        {
            var keyContext = EntityParser.GetKey(typeof(TEntity));
            var keyName    = keyContext.KeyName;

            var sql = SqlBuilder.SelectSQL(typeof(TEntity), string.Format("{0} = @{0}", keyName));

            return(SQLServerHelper.FirstOrDefault((reader) =>
            {
                return FilldEntity <TEntity>(reader, entity);
            }, sql, new SqlParameter("@" + keyName, keyContext.Key.GetValue(entity, null))));
        }
Beispiel #7
0
        /// <summary>
        /// 填充一个实体类的属性
        /// </summary>
        /// <param name="entity">实体类的实例</param>
        /// <param name="propertyInfo">要填充的属性的信息</param>
        /// <param name="sourceValue">从数据源获取到的属性的只</param>
        private static void FillProperty(object entity, PropertyInfo propertyInfo, object sourceValue)
        {
            var result = EntityParser.GetTypeConverter(propertyInfo);

            if (result == null)
            {
                sourceValue = sourceValue.To(propertyInfo.PropertyType);
                propertyInfo.SetValue(entity, sourceValue, null);
            }
            else
            {
                var propertyValue = result.ConvertFrom(sourceValue);
                propertyInfo.SetValue(entity, propertyValue, null);
            }
        }
Beispiel #8
0
        public static string InsertSQL(Type entityType)
        {
            string sql;

            if (!insertSQLCache.TryGetValue(entityType, out sql))
            {
                StringBuilder insert = new StringBuilder();

                var key = EntityParser.GetKey(entityType);
                if (!key.KeyConfig.UsePropertyForInsert)
                {
                    insert.AppendLine(key.KeyConfig.KeyBeforeInsertSQL);
                }


                string tableName = EntityParser.GetTableName(entityType);
                insert.AppendFormat("INSERT INTO [{0}]", tableName);

                var properties = entityType.GetProperties().Where(item => !EntityParser.IsIgnore(item)).Select(item =>
                {
                    string columnSQL = EntityParser.GetColumnName(item);
                    string valueSQL  = "";
                    var config       = EntityParser.GetColumnConfig(item);
                    if (config.UsePropertyForInsert)
                    {
                        valueSQL = " @" + item.Name;
                    }
                    else
                    {
                        valueSQL = config.InsertSQL;
                    }

                    return(new KeyValuePair <string, string>(columnSQL, valueSQL));
                }).Where(item => !string.IsNullOrEmpty(item.Value));

                insert.AppendFormat("({0})", string.Join(",", properties.Select(item => item.Key))).AppendLine();
                insert.AppendFormat("VALUES({0})", string.Join(",", properties.Select(item => item.Value))).AppendLine();

                if (!key.KeyConfig.UsePropertyForInsert)
                {
                    insert.Append("SELECT ").AppendLine(key.KeyConfig.KeyAfterInsertSQL);
                }

                insertSQLCache.Add(entityType, insert.ToString());
                return(insert.ToString());
            }
            return(sql);
        }
Beispiel #9
0
        /// <summary>
        /// 动态的填充一个实体
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="reader">只接收DataRow和SqlDataReader作为参数,使用其他类型的对象会发生错误</param>
        /// <param name="entity">实体类的实例,如果不使用此参数,就回创建一个新实例</param>
        /// <returns></returns>
        private static TEntity DynamicFillEntity <TEntity>(dynamic reader, TEntity entity = default(TEntity)) where TEntity : SQLEntity
        {
            List <ColumnContext> contexts;

            var entityType = typeof(TEntity);

            if (!fillEntityCache.TryGetValue(entityType, out contexts))
            {
                contexts = new List <ColumnContext>();
                foreach (var propertyInfo in entityType.GetProperties())
                {
                    if (EntityParser.IsIgnore(propertyInfo))
                    {
                        continue;
                    }

                    var config  = EntityParser.GetColumnConfig(propertyInfo);
                    var context = new ColumnContext()
                    {
                        Config       = config,
                        PropertyInfo = propertyInfo,
                        PropertyName = propertyInfo.Name,
                        ColumnName   = EntityParser.GetColumnName(propertyInfo)
                    };
                    contexts.Add(context);
                }
                fillEntityCache.Add(entityType, contexts);
            }

            if (entity == null)
            {
                entity = Activator.CreateInstance <TEntity>();
            }
            foreach (var context in contexts)
            {
                var value = reader[context.ColumnName];
                FillProperty(entity, context.PropertyInfo, value);
            }

            entity.EntityState = EntityState.NotChanged;

            return(entity);
        }
Beispiel #10
0
        /// <summary>
        /// 转换属性的值
        /// </summary>
        /// <param name="propertyInfo">属性信息</param>
        /// <param name="source">从数据源获取到的值</param>
        /// <param name="destination">目标类型</param>
        /// <returns></returns>
        public static object ConvertPropertyValue(PropertyInfo propertyInfo, object source, DBType destination)
        {
            Type destinationType = null;

            switch (destination)
            {
            case DBType.String:
                destinationType = typeof(string);
                break;

            case DBType.Int:
                destinationType = typeof(int);
                break;

            case DBType.Decimal:
                destinationType = typeof(decimal);
                break;

            case DBType.DateTime:
                destinationType = typeof(DateTime);
                break;

            case DBType.BigInt:
                destinationType = typeof(long);
                break;
            }

            if (destinationType == null)
            {
                return(null);
            }

            var converter = EntityParser.GetTypeConverter(propertyInfo);              //获取类型转换器

            if (converter != null)
            {
                return(converter.ConvertTo(source, destinationType));                 //使用自定义类型转换器将值转换为另一种类型
            }

            return(source.To(destinationType));
        }
Beispiel #11
0
        /// <summary>
        /// 将实体实例的属性的值更新到数据库中,必须指定主键
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity"></param>
        /// <param name="updateToEntity"></param>
        /// <returns></returns>
        public static TEntity Update <TEntity>(TEntity entity, bool updateToEntity = true) where TEntity : SQLEntity
        {
            if (entity.EntityState == EntityState.NotInitialized)
            {
                throw new Exception("要更新的实体尚未初始化");
            }
            else if (entity.EntityState == EntityState.NotChanged)
            {
                return(entity);
            }
            else
            {
                var entityType = typeof(TEntity);

                var key = EntityParser.GetKey(entityType);                  //找到主键属性
                if (key == null)
                {
                    throw new Exception("在实体类中没有找主键属性");
                }

                List <SqlParameter> parameters = new List <SqlParameter>();

                var           tableName = EntityParser.GetTableName(entityType);
                StringBuilder update    = new StringBuilder();
                update.AppendFormat("UPDATE [{0}] SET ", tableName);
                var flags = false;
                foreach (var propertyName in entity)                  //遍历更改的属性
                {
                    if (propertyName == key.PropertyName)
                    {
                        continue;
                    }
                    var propertyInfo = entityType.GetProperty(propertyName);
                    if (propertyInfo == null || EntityParser.IsIgnore(propertyInfo))                      //如果找不到属性或不映射到数据库
                    {
                        continue;
                    }

                    var sqlPair = SqlBuilder.ColumnSqlForUpdate(entity, propertyInfo, parameters);                      //获取更新列值的sql语句
                    if (string.IsNullOrEmpty(sqlPair))
                    {
                        continue;
                    }
                    else
                    {
                        string columnName = EntityParser.GetColumnName(propertyInfo);
                        update.AppendFormat("{0} = {1}, ", columnName, sqlPair);
                        flags = true;
                    }
                }

                if (flags)
                {
                    update.Remove(update.Length - 2, 2).AppendFormat(" ");
                }
                else
                {
                    return(entity);
                }

                update.AppendFormat(" WHERE {0} = @{0}", key.KeyName);

                var keyValue = key.Key.GetValue(entity, null);                  //获取主键的值

                parameters.Add(new SqlParameter("@" + key.PropertyName, keyValue));

                SQLServerHelper.ExecuteNonQuery(update.ToString(), parameters.ToArray()); //更新到数据库
                entity.EntityState = EntityState.NotChanged;                              //未更改
                if (updateToEntity)
                {
                    //SingleByKey(keyValue, entity);  //从数据库刷新属性
                    RefreshEntity(entity);
                }
            }
            return(entity);
        }
Beispiel #12
0
        /// <summary>
        /// 向数据库中插入一个实体并返回实体类的主键的值,并从数据库中刷新实体的所有值
        /// </summary>
        /// <typeparam name="TKey"></typeparam>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="entity">要插入到数据库的实体类的实例</param>
        /// <returns></returns>
        public static TEntity Insert <TEntity>(TEntity entity) where TEntity : SQLEntity
        {
            var entityType = typeof(TEntity);
            List <ColumnContext> contexts;

            if (!insertCache.TryGetValue(entityType, out contexts))
            {
                contexts = new List <ColumnContext>();

                foreach (var propertyInfo in entityType.GetProperties())
                {
                    if (EntityParser.IsIgnore(propertyInfo))
                    {
                        continue;
                    }
                    var config  = EntityParser.GetColumnConfig(propertyInfo);
                    var context = new ColumnContext()
                    {
                        Config       = config,
                        PropertyInfo = propertyInfo,
                        PropertyName = propertyInfo.Name,
                        ColumnName   = EntityParser.GetColumnName(propertyInfo)
                    };
                    contexts.Add(context);
                }
                insertCache.Add(entityType, contexts);
            }


            List <SqlParameter> parameters = new List <SqlParameter>();

            foreach (var context in contexts)
            {
                if (context.Config.UsePropertyForInsert)
                {
                    var valueExpression = "@" + context.PropertyName;
                    var propertyValue   = context.PropertyInfo.GetValue(entity, null);
                    if (context.Config.DBType == DBType.PropertyType)                      //如果直接用属性
                    {
                        parameters.Add(new SqlParameter(valueExpression, propertyValue));
                    }
                    else                       //如果要转换类型
                    {
                        propertyValue = EntityParser.ConvertPropertyValue(context.PropertyInfo, propertyValue, context.Config.DBType);
                        parameters.Add(new SqlParameter(valueExpression, propertyValue));
                    }
                }
            }

            var    keyContext = EntityParser.GetKey(entityType);            //寻找主键特性
            object key;

            string sql = SqlBuilder.InsertSQL(entityType);

            if (keyContext.KeyConfig.UsePropertyForInsert)               //如果使用属性的值插入到数据库
            {
                SQLServerHelper.ExecuteNonQuery(sql, parameters.ToArray());
                key = keyContext.Key.GetValue(entity, null);
            }
            else
            {
                key = SQLServerHelper.GetSingle(sql, parameters.ToArray());                   //插入并获取主键的值
            }


            var se = entity as SQLEntity;

            if (se != null)
            {
                se.EntityState = EntityState.NotInitialized;                   //未初始化
            }

            string selectSQL = SqlBuilder.SelectSQL(typeof(TEntity), string.Format("{0} = @{0}", keyContext.PropertyName));

            SQLServerHelper.FirstOrDefault((reader) =>
            {
                return(FilldEntity <TEntity>(reader, entity));
            }, selectSQL, new SqlParameter("@" + keyContext.PropertyName, key));              //从数据库中刷新实体

            if (se != null)
            {
                se.EntityState = EntityState.NotChanged;                  //未修改
            }
            return(entity);
        }
Beispiel #13
0
        /// <summary>
        /// 根据指定的条件删除多个实体
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="where">条件</param>
        /// <param name="pms">参数</param>
        public static void Delete <TEntity>(string where, params SqlParameter[] pms) where TEntity : SQLEntity
        {
            var sql = SqlBuilder.DeleteSQL(EntityParser.GetTableName(typeof(TEntity)), where);

            SQLServerHelper.ExecuteNonQuery(sql, pms);
        }