/// <summary>
/// 删除
/// </summary>
/// <param name="mr">成员关系</param>
public void Delete(SCMemberRelation mr)
{
UserAndContainerSnapshot existedObj = this.GetExistedObject(mr);
string sql = string.Empty;
UserAndContainerSnapshot obj = new UserAndContainerSnapshot(mr);
obj.Status = SchemaObjectStatus.Deleted;
if (existedObj != null)
{
//如果已经存在，且状态是正常的
obj.VersionStartTime = existedObj.VersionStartTime;
if (existedObj.Status == SchemaObjectStatus.Normal)
{
sql = this.PrepareUpdateSql(new UserAndContainerSnapshot[] { 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, UserAndContainerSnapshot 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.UserID, obj.ContainerID)));
}
else
{
strB.AppendFormat(PrepareInsertSql(obj, mapping));
}
}
private static string PrepareInsertSql(UserAndContainerSnapshot 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 SameContainerUserAndContainerSnapshotCollection GetInsertData(string containerID, string containerSchemaType, SameContainerUserAndContainerSnapshotCollection existedData, SchemaObjectCollection users)
{
SameContainerUserAndContainerSnapshotCollection newInfo = new SameContainerUserAndContainerSnapshotCollection();
foreach (SchemaObjectBase user in users)
{
if (existedData.ContainsKey(user.ID) == false && newInfo.ContainsKey(user.ID) == false)
{
UserAndContainerSnapshot uacs = new UserAndContainerSnapshot();
uacs.ContainerID = containerID;
uacs.ContainerSchemaType = containerSchemaType;
uacs.UserID = user.ID;
uacs.UserSchemaType = "Users";
uacs.Status = SchemaObjectStatus.Normal;
newInfo.Add(uacs);
}
}
return(newInfo);
}
public string PrepareOneUpdateSql(UserAndContainerSnapshot obj)
{
ORMappingItemCollection mapping = this.GetMappingInfo();
StringBuilder strB = new StringBuilder();
string currentTimeSql = TimePointContext.Current.DBCurrentTimeFunction(TSqlBuilder.Instance);
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 UserAndContainerSnapshot GetExistedObject(SCMemberRelation mr)
{
UserAndContainerSnapshot condition = new UserAndContainerSnapshot(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];
UserAndContainerSnapshot result = null;
if (table.Rows.Count > 0)
{
result = new UserAndContainerSnapshot();
ORMapping.DataRowToObject(table.Rows[0], result);
}
return(result);
}
/// <summary>
/// 根据Container来加载UserAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SameContainerUserAndContainerSnapshotCollection 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));
SameContainerUserAndContainerSnapshotCollection result = new SameContainerUserAndContainerSnapshotCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
UserAndContainerSnapshot item = new UserAndContainerSnapshot();
ORMapping.DataReaderToObject(reader, item);
if (result.ContainsKey(item.UserID) == false)
{
result.Add(item);
}
}
}
}
return(result);
}
private UserAndContainerSnapshot GetExistedObject(SCMemberRelation mr)
{
UserAndContainerSnapshot condition = new UserAndContainerSnapshot(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];
UserAndContainerSnapshot result = null;
if (table.Rows.Count > 0)
{
result = new UserAndContainerSnapshot();
ORMapping.DataRowToObject(table.Rows[0], result);
}
return result;
}
private static SameContainerUserAndContainerSnapshotCollection GetInsertData(string containerID, string containerSchemaType, SameContainerUserAndContainerSnapshotCollection existedData, SchemaObjectCollection users)
{
SameContainerUserAndContainerSnapshotCollection newInfo = new SameContainerUserAndContainerSnapshotCollection();
foreach (SchemaObjectBase user in users)
{
if (existedData.ContainsKey(user.ID) == false && newInfo.ContainsKey(user.ID) == false)
{
UserAndContainerSnapshot uacs = new UserAndContainerSnapshot();
uacs.ContainerID = containerID;
uacs.ContainerSchemaType = containerSchemaType;
uacs.UserID = user.ID;
uacs.UserSchemaType = "Users";
uacs.Status = SchemaObjectStatus.Normal;
newInfo.Add(uacs);
}
}
return newInfo;
}
private static string PrepareInsertSql(UserAndContainerSnapshot 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, UserAndContainerSnapshot 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.UserID, obj.ContainerID)));
}
else
{
strB.AppendFormat(PrepareInsertSql(obj, mapping));
}
}
public string PrepareOneUpdateSql(UserAndContainerSnapshot 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)
{
UserAndContainerSnapshot existedObj = this.GetExistedObject(mr);
string sql = string.Empty;
UserAndContainerSnapshot obj = new UserAndContainerSnapshot(mr);
obj.Status = SchemaObjectStatus.Deleted;
if (existedObj != null)
{
//如果已经存在，且状态是正常的
obj.VersionStartTime = existedObj.VersionStartTime;
if (existedObj.Status == SchemaObjectStatus.Normal)
{
sql = this.PrepareUpdateSql(new UserAndContainerSnapshot[] { 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来加载UserAndContainerSnapshot的信息
/// </summary>
/// <param name="containerID"></param>
/// <param name="timePoint"></param>
/// <returns></returns>
public SameContainerUserAndContainerSnapshotCollection 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));
SameContainerUserAndContainerSnapshotCollection result = new SameContainerUserAndContainerSnapshotCollection();
using (DbContext context = DbContext.GetContext(this.GetConnectionName()))
{
using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName()))
{
while (reader.Read())
{
UserAndContainerSnapshot item = new UserAndContainerSnapshot();
ORMapping.DataReaderToObject(reader, item);
if (result.ContainsKey(item.UserID) == false)
result.Add(item);
}
}
}
return result;
}
