Exemple #1
0
        /// <summary>
        /// 生成用于更新的Sql命令
        /// </summary>
        public static DbCommand CreateUpdateMemberToNullCommand <T>(Database db, GenericWhereEntity <T> whereEntity,
                                                                    List <string> memberNames)
        {
            var dbColumnNames = EntityMappingTool.GetDbColumnNames(typeof(T), memberNames);

            //生成Sql语句
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("UPDATE {0} SET", whereEntity.TableName);
            for (int i = 0; i < dbColumnNames.Count; i++)
            {
                sqlBuilder.Append((i == 0) ? "" : ",");
                sqlBuilder.AppendFormat("{0}.[{1}]=null", whereEntity.TableName, dbColumnNames[i]);
            }

            //WHERE
            string whereSql = SqlCreator.CreateWhereSql(whereEntity);

            sqlBuilder.Append(" ").Append(whereSql);

            //参数
            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            FillSqlParameters(db, cmd, whereEntity);

            return(cmd);
        }
Exemple #2
0
        /// <summary>
        /// 从DataReader实例化一个Entity对
        /// </summary>
        /// <typeparam name="TA">实体类的类型TA</typeparam>
        /// <typeparam name="TB">实体类的类型TB</typeparam>
        /// <param name="reader">DataReader</param>
        /// <param name="entityPropertysA">已经排好序的实体类的属性集合TA</param>
        /// <param name="entityPropertysB">已经排好序的实体类的属性集合TB</param>
        /// <returns></returns>
        public static GenericPairEntity <TA, TB> FillOnePairEntity <TA, TB>(IDataReader reader)
            where TA : class, new()
            where TB : class, new()
        {
            List <PropertyInfo>        entityPropertyInfoA = EntityMappingTool.GetEntityPropertyInfos(typeof(TA));
            List <PropertyInfo>        entityPropertyInfoB = EntityMappingTool.GetEntityPropertyInfos(typeof(TB));
            GenericPairEntity <TA, TB> pair = new GenericPairEntity <TA, TB>();

            pair.EntityA = new TA();
            pair.EntityB = new TB();

            int offset = 0;

            for (int i = 0; i < entityPropertyInfoA.Count; i++)
            {
                if (reader.IsDBNull(offset + i))
                {
                    continue;
                }
                entityPropertyInfoA[i].SetValue(pair.EntityA, reader.GetValue(offset + i), null);
            }

            offset = entityPropertyInfoA.Count;
            for (int i = 0; i < entityPropertyInfoB.Count; i++)
            {
                if (reader.IsDBNull(offset + i))
                {
                    continue;
                }
                entityPropertyInfoB[i].SetValue(pair.EntityB, reader.GetValue(offset + i), null);
            }

            return(pair);
        }
Exemple #3
0
        /// <summary>
        /// 判断Entity实例中有没有设置主键值
        /// </summary>
        public static bool HasPrimaryKeyValue <T>(T entity)
        {
            if (!EntityMappingTool.HasPrimaryKey(typeof(T)))
            {
                return(false);
            }

            List <string> notNullEntityFields = GetNotNullFields(entity);

            if (notNullEntityFields == null)
            {
                return(false);
            }

            var primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(typeof(T));

            for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
            {
                if (!notNullEntityFields.Any(n => n == primaryKeyEntityFieldNames[i]))
                {
                    return(false);
                }
            }

            return(true);
        }
Exemple #4
0
        /// <summary>
        /// 创建用于删除的Sql命令
        /// </summary>
        public static DbCommand CreatDeleteCommand <T>(Database db, T entity)
        {
            Type          entityType = typeof(T);
            List <string> primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
            List <string> primaryKeyDbCloumnNames    = EntityMappingTool.GetDbColumnNames(entityType,
                                                                                          primaryKeyEntityFieldNames);
            List <DbType> primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
                                                                                       primaryKeyEntityFieldNames);
            List <PropertyInfo> primaryKeyPropertyInfos = EntityMappingTool.GetEntityPropertyInfos(entityType,
                                                                                                   primaryKeyEntityFieldNames);

            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("DELETE FROM [{0}] WHERE ", EntityMappingTool.GetDbTableName(entityType));
            for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
            {
                sqlBuilder.Append((i > 0) ? " AND " : "");
                sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
            }

            //参数
            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            for (int i = 0; i < primaryKeyEntityFieldNames.Count; i++)
            {
                db.AddInParameter(cmd, "@" + primaryKeyDbCloumnNames[i], primaryKeyDbColumnTypes[i],
                                  primaryKeyPropertyInfos[i].GetValue(entity, null));
            }

            return(cmd);
        }
Exemple #5
0
        /// <summary>
        /// 生成用于更新的Sql命令
        /// </summary>
        public static DbCommand CreateUpdateCommand <T>(Database db, T entity)
        {
            Type entityType = typeof(T);

            List <string> primaryKeyEntityFieldNames = EntityMappingTool.GetPrimaryKeyOfEntityField(entityType);
            List <string> primaryKeyDbCloumnNames    = EntityMappingTool.GetDbColumnNames(entityType,
                                                                                          primaryKeyEntityFieldNames);
            List <DbType> primaryKeyDbColumnTypes = EntityMappingTool.GetDbColumnTypes(entityType,
                                                                                       primaryKeyEntityFieldNames);

            List <string>       notNullEntityFields    = EntityInstanceTool.GetNotNullFields(entity);
            List <string>       notNullDbCloumnNames   = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
            List <DbType>       notNullDbColumnTypes   = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
            List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);

            //生成Sql语句
            List <string> parameterIndex = new List <string>();
            StringBuilder sqlBuilder     = new StringBuilder();

            sqlBuilder.Append("UPDATE [").Append(EntityMappingTool.GetDbTableName(entityType)).Append("] SET ");
            bool firstColumn = true;

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                string loopColumn = notNullDbCloumnNames[i];

                //当前模式主键不更新
                if (primaryKeyDbCloumnNames.Contains(loopColumn))
                {
                    continue;
                }

                sqlBuilder.Append(firstColumn ? "" : ",");
                firstColumn = false;
                sqlBuilder.AppendFormat("[{0}]=@{0}", loopColumn);
                parameterIndex.Add(loopColumn);
            }

            //WHERE
            sqlBuilder.Append(" WHERE ");
            for (int i = 0; i < primaryKeyDbCloumnNames.Count; i++)
            {
                sqlBuilder.Append((i > 0) ? " AND " : "");
                sqlBuilder.AppendFormat("([{0}]=@{0})", primaryKeyDbCloumnNames[i]);
                parameterIndex.Add(primaryKeyDbCloumnNames[i]);
            }

            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            for (int i = 0; i < parameterIndex.Count; i++)
            {
                int pIndex = notNullDbCloumnNames.IndexOf(parameterIndex[i]);
                db.AddInParameter(cmd, "@" + notNullDbCloumnNames[pIndex], notNullDbColumnTypes[pIndex],
                                  notNullEntityPropertys[pIndex].GetValue(entity, null));
            }

            return(cmd);
        }
Exemple #6
0
        /// <summary>
        /// 创建查询条件
        /// </summary>
        public static string CreateWhereSql <T>(GenericWhereEntity <T> theWhereEntity)
        {
            string dbTableName = EntityMappingTool.GetDbTableName(theWhereEntity.EntityType);

            if (string.IsNullOrEmpty(dbTableName))
            {
                throw new EntitySqlException(string.Format("未给类型{0}设置数据表信息!", theWhereEntity.EntityType.FullName));
            }

            StringBuilder tsqlBuffer = new StringBuilder(2048);

            if (theWhereEntity.DisableTableAlias)
            {
                tsqlBuffer.Append(" FROM [").Append(dbTableName).Append("]");
            }
            else
            {
                tsqlBuffer.Append(" FROM [").Append(dbTableName).Append("] AS ").Append(theWhereEntity.TableName);
            }

            if (theWhereEntity.WhereExpressions.Count > 0)
            {
                tsqlBuffer.Append(" WHERE ");
                //逐个语句查询,并合并参数
                for (int i = 0; i < theWhereEntity.WhereExpressions.Count; i++)
                {
                    ConditionBuilderGeneric <T> conditionBuilder =
                        new ConditionBuilderGeneric <T>(
                            (theWhereEntity.DisableTableAlias ? dbTableName : theWhereEntity.TableName), theWhereEntity);
                    conditionBuilder.Build(theWhereEntity.WhereExpressions[i]);

                    if (i > 0)
                    {
                        tsqlBuffer.Append(" AND ");
                    }

                    tsqlBuffer.Append(conditionBuilder.Condition);

                    if (conditionBuilder.Arguments != null && conditionBuilder.Arguments.Length > 0)
                    {
                        theWhereEntity.WhereParameterValues.AddRange(conditionBuilder.Arguments);
                    }
                    if (conditionBuilder.ParameterNames != null && conditionBuilder.ParameterNames.Length > 0)
                    {
                        theWhereEntity.WhereParameterNames.AddRange(conditionBuilder.ParameterNames);
                    }
                    if (conditionBuilder.DbTypes != null && conditionBuilder.DbTypes.Length > 0)
                    {
                        theWhereEntity.WhereParameterTypes.AddRange(conditionBuilder.DbTypes);
                    }
                }
            }

            return(tsqlBuffer.ToString());
        }
Exemple #7
0
        /// <summary>
        /// 创建用于删除的Sql命令
        /// </summary>
        public static DbCommand CreatDeleteCommand <T>(Database db, GenericWhereEntity <T> whereEntity)
        {
            //生成Sql语句
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("DELETE ", EntityMappingTool.GetDbTableName(typeof(T)));
            sqlBuilder.Append(SqlCreator.CreateWhereSql(whereEntity));
            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            FillSqlParameters(db, cmd, whereEntity);
            return(cmd);
        }
Exemple #8
0
        /// <summary>
        /// 检索出Entity实例中有设置值的属性
        /// </summary>
        /// <typeparam name="T">实体类的类型</typeparam>
        /// <param name="entity">实体类的实例</param>
        /// <param name="entityPropertyInfos">实体类对应的属性</param>
        /// <returns>Entity实例中有设置值的属性</returns>
        public static List <PropertyInfo> GetNotNullEntityPropertys <T>(T entity)
        {
            List <PropertyInfo> entityPropertyInfos    = EntityMappingTool.GetEntityPropertyInfos((typeof(T)));
            List <PropertyInfo> notNullEntityPropertys = new List <PropertyInfo>(entityPropertyInfos.Count);

            for (int i = 0; i < entityPropertyInfos.Count; i++)
            {
                if (entityPropertyInfos[i].GetValue(entity, null) != null)
                {
                    notNullEntityPropertys.Add(entityPropertyInfos[i]);
                }
            }

            return(notNullEntityPropertys);
        }
Exemple #9
0
        /// <summary>
        /// 从DataReader实例化一个Entity
        /// </summary>
        /// <typeparam name="T">实体类的类型</typeparam>
        /// <param name="reader">DataReader</param>
        /// <param name="entityPropertyInfos">已经排好序的实体类的属性集合</param>
        /// <returns></returns>
        public static T FillOneEntity <T>(IDataReader reader) where T : class, new()
        {
            List <PropertyInfo> entityPropertyInfo = EntityMappingTool.GetEntityPropertyInfos(typeof(T));
            T entity = new T();

            for (int i = 0; i < entityPropertyInfo.Count; i++)
            {
                if (reader.IsDBNull(i))
                {
                    continue;
                }
                entityPropertyInfo[i].SetValue(entity, reader.GetValue(i), null);
            }

            return(entity);
        }
Exemple #10
0
        /// <summary>
        /// 生成用于插入的Sql命令
        /// </summary>
        public static DbCommand CreateInsertCommand <T>(Database db, T entity)
        {
            var entityType = typeof(T);

            List <string>       notNullEntityFields    = EntityInstanceTool.GetNotNullFields(entity);
            List <string>       notNullDbCloumnNames   = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
            List <DbType>       notNullDbColumnTypes   = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
            List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);

            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append("INSERT INTO [").Append(EntityMappingTool.GetDbTableName(entity.GetType())).Append("] (");

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                if (i > 0)
                {
                    sqlBuilder.Append(", ");
                }
                sqlBuilder.Append("[").Append(notNullDbCloumnNames[i]).Append("]");
            }

            sqlBuilder.Append(") VALUES (");

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                if (i > 0)
                {
                    sqlBuilder.Append(", ");
                }
                sqlBuilder.Append("@").Append(notNullDbCloumnNames[i]);
            }

            sqlBuilder.Append(")");

            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
                                  notNullEntityPropertys[i].GetValue(entity, null));
            }

            return(cmd);
        }
Exemple #11
0
        /// <summary>
        /// 创建成员查询的Sql语句
        /// </summary>
        public static string CreateSelectSql <T>(GenericWhereEntity <T> theWhereEntity, List <string> dbColumnNames = null,
                                                 int topCount = 0)
        {
            string dbTableName = EntityMappingTool.GetDbTableName(theWhereEntity.EntityType);

            if (string.IsNullOrEmpty(dbTableName))
            {
                throw new EntitySqlException(string.Format("未给类型{0}设置数据表信息!", theWhereEntity.EntityType.FullName));
            }

            if (dbColumnNames == null)
            {
                dbColumnNames = EntityMappingTool.GetDbColumnNames(typeof(T));
            }

            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append("SELECT ");

            if (topCount > 0)
            {
                sqlBuilder.AppendFormat("TOP {0} ", topCount);
            }

            for (int i = 0; i < dbColumnNames.Count; i++)
            {
                if (i > 0)
                {
                    sqlBuilder.Append(", ");
                }

                if (theWhereEntity.DisableTableAlias)
                {
                    sqlBuilder.Append(string.Format("{0}.[{1}]", dbTableName, dbColumnNames[i]));
                }
                else
                {
                    sqlBuilder.Append(string.Format("{0}.[{1}]", theWhereEntity.TableName, dbColumnNames[i]));
                }
            }

            return(sqlBuilder.ToString());
        }
Exemple #12
0
        /// <summary>
        /// 生成用于更新的Sql命令
        /// </summary>
        public static DbCommand CreateUpdateCommand <T>(Database db, T entity, GenericWhereEntity <T> whereEntity)
        {
            Type entityType = typeof(T);

            List <string>       notNullEntityFields    = EntityInstanceTool.GetNotNullFields(entity);
            List <string>       notNullDbCloumnNames   = EntityMappingTool.GetDbColumnNames(entityType, notNullEntityFields);
            List <DbType>       notNullDbColumnTypes   = EntityMappingTool.GetDbColumnTypes(entityType, notNullEntityFields);
            List <PropertyInfo> notNullEntityPropertys = EntityInstanceTool.GetNotNullEntityPropertys(entity);

            //生成Sql语句
            List <string> parameterIndex = new List <string>();
            StringBuilder sqlBuilder     = new StringBuilder();

            sqlBuilder.AppendFormat("UPDATE {0} SET ", whereEntity.TableName);
            bool firstColumn = true;

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                string loopColumn = notNullDbCloumnNames[i];
                sqlBuilder.Append(firstColumn ? "" : ",");
                firstColumn = false;
                sqlBuilder.AppendFormat("{0}.[{1}]=@{1}", whereEntity.TableName, loopColumn);
                parameterIndex.Add(loopColumn);
            }

            //WHERE
            string whereSql = SqlCreator.CreateWhereSql(whereEntity);

            sqlBuilder.Append(" ").Append(whereSql);

            //参数
            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            for (int i = 0; i < notNullDbCloumnNames.Count; i++)
            {
                db.AddInParameter(cmd, "@" + notNullDbCloumnNames[i], notNullDbColumnTypes[i],
                                  notNullEntityPropertys[i].GetValue(entity, null));
            }
            FillSqlParameters(db, cmd, whereEntity);

            return(cmd);
        }
Exemple #13
0
        /// <summary>
        /// 生成用于更新的Sql命令
        /// </summary>
        public static DbCommand CreateUpdateMemberToNullCommand <T>(Database db, GenericWhereEntity <T> whereEntity,
                                                                    string memberName)
        {
            string dbColumnName = EntityMappingTool.GetDbColumnName(typeof(T), memberName);

            //生成Sql语句
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.AppendFormat("UPDATE {0} SET  {0}.[{1}]=null", whereEntity.TableName, dbColumnName);

            //WHERE
            string whereSql = SqlCreator.CreateWhereSql(whereEntity);

            sqlBuilder.Append(" ").Append(whereSql);

            //参数
            DbCommand cmd = db.GetSqlStringCommand(sqlBuilder.ToString());

            FillSqlParameters(db, cmd, whereEntity);

            return(cmd);
        }