コード例 #1
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);
        }
コード例 #2
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);
        }
コード例 #3
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);
        }