/// <summary>
/// 删除
/// </summary>
/// <param name="mr">成员关系</param>
public void Delete(SCMemberRelation mr)
{
ItemAndContainerSnapshot existedObj = this.GetExistedObject(mr);
string sql = string.Empty;
ItemAndContainerSnapshot obj = new ItemAndContainerSnapshot(mr);
obj.Status = SchemaObjectStatus.Deleted;
if (existedObj != null)
{
//如果已经存在，且状态是正常的
obj.VersionStartTime = existedObj.VersionStartTime;
if (existedObj.Status == SchemaObjectStatus.Normal)
{
sql = this.PrepareUpdateSql(new ItemAndContainerSnapshot[] { obj });
}
}
if (sql.IsNotEmpty())
{
using (TransactionScope scope = TransactionScopeFactory.Create())
{
DateTime dt = (DateTime)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
SCActionContext.Current.TimePoint.IsMinValue(() => SCActionContext.Current.TimePoint = dt);
scope.Complete();
}
}
}
private static void FillOneUpdateSql(StringBuilder strB, ItemAndContainerSnapshot obj, ORMappingItemCollection mapping)
{
if (obj.VersionStartTime != DateTime.MinValue)
{
WhereSqlClauseBuilder primaryKeyBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(obj, mapping);
strB.AppendFormat("UPDATE {0} SET VersionEndTime = @currentTime WHERE {1} AND VersionStartTime = {2}",
mapping.TableName,
primaryKeyBuilder.ToSqlString(TSqlBuilder.Instance),
TSqlBuilder.Instance.FormatDateTime(obj.VersionStartTime));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("IF @@ROWCOUNT > 0\n");
strB.AppendFormat("BEGIN\n");
strB.AppendFormat("\t{0}\n", PrepareInsertSql(obj, mapping));
strB.AppendFormat("END\n");
strB.AppendFormat("ELSE\n");
strB.AppendFormat("\tRAISERROR ({0}, 16, 1)",
TSqlBuilder.Instance.CheckUnicodeQuotationMark(string.Format("对象\"{0}\"和\"{1}\"的版本不是最新的，不能更新",
obj.ItemID, obj.ContainerID)));
}
else
{
strB.AppendFormat(PrepareInsertSql(obj, mapping));
}
}
private static string PrepareInsertSql(ItemAndContainerSnapshot obj, ORMappingItemCollection mapping)
{
InsertSqlClauseBuilder builder = ORMapping.GetInsertSqlClauseBuilder(obj);
builder.AppendItem("VersionStartTime", "@currentTime", "=", true);
builder.AppendItem("VersionEndTime", SCConnectionDefine.MaxVersionEndTime);
return(string.Format("INSERT INTO {0}{1}", mapping.TableName, builder.ToSqlString(TSqlBuilder.Instance)));
}
private static SameContainerItemAndContainerSnapshotCollection GetInsertData(string containerID, string containerSchemaType, SameContainerItemAndContainerSnapshotCollection existedData, SchemaObjectCollection items)
{
SameContainerItemAndContainerSnapshotCollection newInfo = new SameContainerItemAndContainerSnapshotCollection();
foreach (SchemaObjectBase item in items)
{
if (existedData.ContainsKey(item.ID) == false && newInfo.ContainsKey(item.ID) == false)
{
ItemAndContainerSnapshot uacs = new ItemAndContainerSnapshot();
uacs.ContainerID = containerID;
uacs.ContainerSchemaType = containerSchemaType;
uacs.ItemID = item.ID;
uacs.ItemSchemaType = item.SchemaType;
uacs.Status = SchemaObjectStatus.Normal;
newInfo.Add(uacs);
}
}
return(newInfo);
}
public string PrepareOneUpdateSql(ItemAndContainerSnapshot obj)
{
ORMappingItemCollection mapping = this.GetMappingInfo();
StringBuilder strB = new StringBuilder();
string currentTimeSql = TSqlBuilder.Instance.DBCurrentTimeFunction;
SCActionContext.Current.TimePoint.IsNotMinValue(tp => currentTimeSql = TSqlBuilder.Instance.FormatDateTime(tp));
strB.Append("DECLARE @currentTime DATETIME");
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("SET @currentTime = {0}", currentTimeSql);
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
FillOneUpdateSql(strB, obj, mapping);
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append("SELECT @currentTime");
return(strB.ToString());
}
private ItemAndContainerSnapshot GetExistedObject(SCMemberRelation mr)
{
ItemAndContainerSnapshot condition = new ItemAndContainerSnapshot(mr);
WhereSqlClauseBuilder keyBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(condition, this.GetMappingInfo());
string sql = string.Format("SELECT TOP 1 {0} FROM {1} WHERE {2} ORDER BY VersionStartTime DESC",
string.Join(",", ORMapping.GetSelectFieldsName(this.GetMappingInfo())),
this.GetLoadingTableName(DateTime.MinValue),
keyBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
ItemAndContainerSnapshot result = null;
if (table.Rows.Count > 0)
{
result = new ItemAndContainerSnapshot();
ORMapping.DataRowToObject(table.Rows[0], result);
}
return(result);
}
/// <summary>
/// 根据Container来加载ItemAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SameContainerItemAndContainerSnapshotCollection LoadByContainerID(string containerID, DateTime timePoint)
{
containerID.CheckStringIsNullOrEmpty("containerID");
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ContainerID", containerID);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(whereBuilder, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
SameContainerItemAndContainerSnapshotCollection result = new SameContainerItemAndContainerSnapshotCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
ItemAndContainerSnapshot item = new ItemAndContainerSnapshot();
ORMapping.DataReaderToObject(reader, item);
if (result.ContainsKey(item.ItemID) == false)
{
result.Add(item);
}
}
}
}
return(result);
}
private ItemAndContainerSnapshot GetExistedObject(SCMemberRelation mr)
{
ItemAndContainerSnapshot condition = new ItemAndContainerSnapshot(mr);
WhereSqlClauseBuilder keyBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(condition, this.GetMappingInfo());
string sql = string.Format("SELECT TOP 1 {0} FROM {1} WHERE {2} ORDER BY VersionStartTime DESC",
string.Join(",", ORMapping.GetSelectFieldsName(this.GetMappingInfo())),
this.GetLoadingTableName(DateTime.MinValue),
keyBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
ItemAndContainerSnapshot result = null;
if (table.Rows.Count > 0)
{
result = new ItemAndContainerSnapshot();
ORMapping.DataRowToObject(table.Rows[0], result);
}
return result;
}
private static SameContainerItemAndContainerSnapshotCollection GetInsertData(string containerID, string containerSchemaType, SameContainerItemAndContainerSnapshotCollection existedData, SchemaObjectCollection items)
{
SameContainerItemAndContainerSnapshotCollection newInfo = new SameContainerItemAndContainerSnapshotCollection();
foreach (SchemaObjectBase item in items)
{
if (existedData.ContainsKey(item.ID) == false && newInfo.ContainsKey(item.ID) == false)
{
ItemAndContainerSnapshot uacs = new ItemAndContainerSnapshot();
uacs.ContainerID = containerID;
uacs.ContainerSchemaType = containerSchemaType;
uacs.ItemID = item.ID;
uacs.ItemSchemaType = item.SchemaType;
uacs.Status = SchemaObjectStatus.Normal;
newInfo.Add(uacs);
}
}
return newInfo;
}
private static string PrepareInsertSql(ItemAndContainerSnapshot obj, ORMappingItemCollection mapping)
{
InsertSqlClauseBuilder builder = ORMapping.GetInsertSqlClauseBuilder(obj);
builder.AppendItem("VersionStartTime", "@currentTime", "=", true);
builder.AppendItem("VersionEndTime", SCConnectionDefine.MaxVersionEndTime);
return string.Format("INSERT INTO {0}{1}", mapping.TableName, builder.ToSqlString(TSqlBuilder.Instance));
}
private static void FillOneUpdateSql(StringBuilder strB, ItemAndContainerSnapshot obj, ORMappingItemCollection mapping)
{
if (obj.VersionStartTime != DateTime.MinValue)
{
WhereSqlClauseBuilder primaryKeyBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(obj, mapping);
strB.AppendFormat("UPDATE {0} SET VersionEndTime = @currentTime WHERE {1} AND VersionStartTime = {2}",
mapping.TableName,
primaryKeyBuilder.ToSqlString(TSqlBuilder.Instance),
TSqlBuilder.Instance.FormatDateTime(obj.VersionStartTime));
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("IF @@ROWCOUNT > 0\n");
strB.AppendFormat("BEGIN\n");
strB.AppendFormat("\t{0}\n", PrepareInsertSql(obj, mapping));
strB.AppendFormat("END\n");
strB.AppendFormat("ELSE\n");
strB.AppendFormat("\tRAISERROR ({0}, 16, 1)",
TSqlBuilder.Instance.CheckUnicodeQuotationMark(string.Format("对象\"{0}\"和\"{1}\"的版本不是最新的，不能更新",
obj.ItemID, obj.ContainerID)));
}
else
{
strB.AppendFormat(PrepareInsertSql(obj, mapping));
}
}
public string PrepareOneUpdateSql(ItemAndContainerSnapshot obj)
{
ORMappingItemCollection mapping = this.GetMappingInfo();
StringBuilder strB = new StringBuilder();
string currentTimeSql = TSqlBuilder.Instance.DBCurrentTimeFunction;
SCActionContext.Current.TimePoint.IsNotMinValue(tp => currentTimeSql = TSqlBuilder.Instance.FormatDateTime(tp));
strB.Append("DECLARE @currentTime DATETIME");
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.AppendFormat("SET @currentTime = {0}", currentTimeSql);
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
FillOneUpdateSql(strB, obj, mapping);
strB.Append(TSqlBuilder.Instance.DBStatementSeperator);
strB.Append("SELECT @currentTime");
return strB.ToString();
}
/// <summary>
/// 删除
/// </summary>
/// <param name="mr">成员关系</param>
public void Delete(SCMemberRelation mr)
{
ItemAndContainerSnapshot existedObj = this.GetExistedObject(mr);
string sql = string.Empty;
ItemAndContainerSnapshot obj = new ItemAndContainerSnapshot(mr);
obj.Status = SchemaObjectStatus.Deleted;
if (existedObj != null)
{
//如果已经存在，且状态是正常的
obj.VersionStartTime = existedObj.VersionStartTime;
if (existedObj.Status == SchemaObjectStatus.Normal)
{
sql = this.PrepareUpdateSql(new ItemAndContainerSnapshot[] { obj });
}
}
if (sql.IsNotEmpty())
{
using (TransactionScope scope = TransactionScopeFactory.Create())
{
DateTime dt = (DateTime)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName());
SCActionContext.Current.TimePoint.IsMinValue(() => SCActionContext.Current.TimePoint = dt);
scope.Complete();
}
}
}
/// <summary>
/// 根据Container来加载ItemAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SameContainerItemAndContainerSnapshotCollection LoadByContainerID(string containerID, DateTime timePoint)
{
containerID.CheckStringIsNullOrEmpty("containerID");
ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint);
WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder();
whereBuilder.AppendItem("ContainerID", containerID);
ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(whereBuilder, timePointBuilder);
string sql = string.Format("SELECT * FROM {0} WHERE {1}",
this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance));
SameContainerItemAndContainerSnapshotCollection result = new SameContainerItemAndContainerSnapshotCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
ItemAndContainerSnapshot item = new ItemAndContainerSnapshot();
ORMapping.DataReaderToObject(reader, item);
if (result.ContainsKey(item.ItemID) == false)
result.Add(item);
}
}
}
return result;
}