//...
        //FROM Transcripts S,
        //(
        //SELECT 1 StudentId, 1 CourseId
        //UNION ALL
        //SELECT 2 StudentId, 1 CourseId
        //...
        //) T
        //WHERE S.StudentId = T.StudentId
        //AND S.CourseId = T.CourseId  // SQL Server/MySQL
        protected BatchStatement Batch_GenerateFromSelectWhereClause(int startIndex, int endIndex,
                                                                     Dictionary <string, object>[] array, Dictionary <string, Tuple <string, Type> > keyConcPropertyColumns, string tableName)
        {
            BatchStatement statement = Batch_GenerateSelectUnionAll(startIndex, endIndex, array, keyConcPropertyColumns);

            string sql = string.Format("FROM {0} S, ({1}) T WHERE {2}", tableName, statement.Sql,
                                       string.Join(" AND ", keyConcPropertyColumns.Select(p => string.Format("S.{0} = T.{0}", p.Value.Item1))));

            return(new BatchStatement(sql, statement.Parameters, statement.StartIndex, statement.EndIndex));
        }
        protected IEnumerable <BatchStatement> Batch_GenerateFromSelectWhereClauses(Dictionary <string, object>[] array,
                                                                                    int pageSize, XElement KeyConcSchema, string tableName)
        {
            List <BatchStatement> whereClauses = new List <BatchStatement>();

            Dictionary <int, int> partitions = Batch_Paging(array.Length, pageSize);

            foreach (KeyValuePair <int, int> partition in partitions)
            {
                BatchStatement statement = Batch_GenerateFromSelectWhereClause(partition.Key, partition.Value, array,
                                                                               Batch_GetKeyConcPropertyColumns(KeyConcSchema), tableName);
                whereClauses.Add(statement);
            }

            return(whereClauses);
        }
        // SQL Server/MySQL
        //UPDATE Transcripts
        //SET Score = 90,
        //Levle='A'
        //WHERE Id IN (1, 2...)
        // or
        //UPDATE S
        //SET Score = 90,
        //Levle='A'
        //...
        public virtual IEnumerable <BatchStatement> GenerateBatchUpdateStatements(Dictionary <string, object>[] objects,
                                                                                  Dictionary <string, object> value, XElement entitySchema, XElement keySchema, XElement concurrencySchema)
        {
            int dbParamIndex = DbParamLimit;
            Dictionary <string, object> paramDict = new Dictionary <string, object>();
            List <string> valueList = new List <string>();

            foreach (string property in value.Keys)
            {
                XElement propertySchema = entitySchema.Elements(SchemaVocab.Property).First(p => p.Attribute(SchemaVocab.Name).Value == property);
                string   column         = propertySchema.Attribute(SchemaVocab.Column).Value;
                column = DecorateColumnName(column);
                Type   type      = Type.GetType(propertySchema.Attribute(SchemaVocab.DataType).Value);
                string valueExpr = Batch_GetValueExpr(value[property], type, paramDict, dbParamIndex);
                valueList.Add(string.Format("{0} = {1}", column, valueExpr));
                dbParamIndex--;
            }

            int dbParamLimit = DbParamLimit - paramDict.Count;
            IEnumerable <BatchStatement> whereClauses = Batch_GenerateWhereClauses(objects, entitySchema, keySchema, concurrencySchema, dbParamLimit, out string alias);

            string tableName = DecorateTableName(entitySchema.Attribute(SchemaVocab.Table).Value);
            string head      = string.IsNullOrWhiteSpace(alias) ? string.Format("UPDATE {0} ", tableName) : string.Format("UPDATE {0} ", alias);

            head += string.Format("SET {0} ", string.Join(",", valueList));

            List <BatchStatement> statements = new List <BatchStatement>();

            foreach (BatchStatement whereClause in whereClauses)
            {
                Dictionary <string, object> statementParams = new Dictionary <string, object>();
                foreach (KeyValuePair <string, object> param in whereClause.Parameters)
                {
                    statementParams.Add(param.Key, param.Value);
                }
                foreach (KeyValuePair <string, object> param in paramDict)
                {
                    statementParams.Add(param.Key, param.Value);
                }

                BatchStatement statement = new BatchStatement(head + whereClause.Sql, statementParams, whereClause.StartIndex, whereClause.EndIndex);
                statements.Add(statement);
            }

            return(statements);
        }
        // SQL Server/MySQL
        //UPDATE S
        //SET IsDisabled = T.IsDisabled,
        //IsLockedOut = T.IsLockedOut
        //FROM Users S,
        //(
        //SELECT 1 EmployeeId, 0 IsDisabled, 1 IsLockedOut
        //UNION ALL
        //SELECT 2 EmployeeId, 1 IsDisabled, 0 IsLockedOut
        //) T
        //WHERE S.EmployeeId = T.EmployeeId
        public virtual IEnumerable <BatchStatement> GenerateBatchUpdateStatements(Dictionary <string, object>[] objects,
                                                                                  XElement entitySchema, XElement keySchema, XElement concurrencySchema)
        {
            string tableName = DecorateTableName(entitySchema.Attribute(SchemaVocab.Table).Value);
            Dictionary <string, Tuple <string, Type> > propertyColumns = Batch_GetPropertyColumns(objects[0], entitySchema);

            //
            XElement keyConcSchema = new XElement(keySchema);

            if (concurrencySchema != null)
            {
                keyConcSchema.Add(concurrencySchema.Elements());
            }

            List <string> headList = new List <string>();
            List <string> tailList = new List <string>();

            foreach (KeyValuePair <string, Tuple <string, Type> > propertyColumn in propertyColumns)
            {
                string property = propertyColumn.Key;
                string column   = propertyColumn.Value.Item1;
                if (keyConcSchema.Elements(SchemaVocab.Property).Any(x => x.Attribute(SchemaVocab.Name).Value == property))
                {
                    tailList.Add(string.Format("S.{0} = T.{0}", column));
                }
                else
                {
                    headList.Add(string.Format("{0} = T.{0}", column));
                }
            }

            string head = string.Format("UPDATE S SET {0} FROM {1} S,", string.Join(",", headList), tableName);
            string tail = string.Format("WHERE {0}", string.Join(",", tailList));

            //
            int nonIntColCount = propertyColumns.Values.Where(v => IsInteger(v.Item2)).Count();
            int pageSize;

            if (nonIntColCount == 0)
            {
                pageSize = objects.Length;
            }
            else
            {
                pageSize = DbParamLimit / nonIntColCount;
                if (DbParamLimit % nonIntColCount != 0)
                {
                    pageSize++;
                }
            }

            List <BatchStatement> result     = new List <BatchStatement>();
            Dictionary <int, int> partitions = Batch_Paging(objects.Length, pageSize);

            foreach (KeyValuePair <int, int> partition in partitions)
            {
                BatchStatement statement = Batch_GenerateSelectUnionAll(partition.Key, partition.Value, objects, propertyColumns);
                string         sql       = string.Format("{0} ({1}) T {2}", head, statement.Sql, tail);
                result.Add(new BatchStatement(sql, statement.Parameters, statement.StartIndex, statement.EndIndex));
            }

            return(result);
        }
예제 #5
0
        //UPDATE Users S
        //SET(IsDisabled, IsLockedOut)=(SELECT T.IsDisabled, T.IsLockedOut
        //FROM
        //(
        //SELECT 1 EmployeeId, 0 IsDisabled, 1 IsLockedOut FROM DUAL
        //UNION ALL
        //SELECT 2 EmployeeId, 1 IsDisabled, 0 IsLockedOut FROM DUAL
        //) T
        //WHERE S.EmployeeId = T.EmployeeId)
        public override IEnumerable <BatchStatement> GenerateBatchUpdateStatements(Dictionary <string, object>[] objects,
                                                                                   XElement entitySchema, XElement keySchema, XElement concurrencySchema)
        {
            string tableName = DecorateTableName(entitySchema.Attribute(SchemaVocab.Table).Value);
            Dictionary <string, Tuple <string, Type> > propertyColumns = Batch_GetPropertyColumns(objects[0], entitySchema);

            //
            XElement keyConcSchema = new XElement(keySchema);

            if (concurrencySchema != null)
            {
                keyConcSchema.Add(concurrencySchema.Elements());
            }
            IEnumerable <string> keyConcCols = keyConcSchema.Elements(SchemaVocab.Property)
                                               .Select(x => DecorateColumnName(x.Attribute(SchemaVocab.Column).Value))
                                               .Select(s => string.Format("S.{0} = T.{0}", s));

            string where = string.Format("WHERE {0})", string.Join(" AND ", keyConcCols));

            //
            List <string> updateCols = new List <string>();

            foreach (KeyValuePair <string, Tuple <string, Type> > propertyColumn in propertyColumns)
            {
                string property = propertyColumn.Key;
                string column   = propertyColumn.Value.Item1;
                if (keyConcSchema.Elements(SchemaVocab.Property).Any(x => x.Attribute(SchemaVocab.Name).Value == propertyColumn.Key))
                {
                    continue;
                }
                updateCols.Add(column);
            }
            string update = string.Format("UPDATE {0} S SET(IsDisabled, IsLockedOut) = (SELECT T.IsDisabled, T.IsLockedOut FROM (",
                                          tableName, string.Join(",", updateCols), string.Join(",", updateCols.Select(s => string.Format("T.{0}", s))));

            //
            int nonIntColCount = propertyColumns.Values.Where(v => IsInteger(v.Item2)).Count();
            int pageSize;

            if (nonIntColCount == 0)
            {
                pageSize = objects.Length;
            }
            else
            {
                pageSize = DbParamLimit / nonIntColCount;
                if (DbParamLimit % nonIntColCount != 0)
                {
                    pageSize++;
                }
            }

            //
            List <BatchStatement> result     = new List <BatchStatement>();
            Dictionary <int, int> partitions = Batch_Paging(objects.Length, pageSize);

            foreach (KeyValuePair <int, int> partition in partitions)
            {
                BatchStatement statement = GenerateBatchUpdateStatement(partition.Key, partition.Value, objects, propertyColumns, update, where);
                result.Add(statement);
            }
            return(result);
        }