//...
//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);
}
//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);
}