//public int InsertNodeRow(NodeData nodeData)
//{
// SqlProcedure cmd = null;
// SqlDataReader reader = null;
// var result = 0;
// try
// {
// cmd = new SqlProcedure { CommandText = "proc_Node_Insert" };
// cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId;
// cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
// cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
// cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0;
// cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = nodeData.IsInherited ? 1 : 0;
// cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
// cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name;
// cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
// cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path;
// cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index;
// cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0;
// cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
// cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty;
// cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType;
// cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout;
// cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate;
// cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty;
// cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate;
// cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.NodeCreationDate;
// cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.NodeCreatedById;
// cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
// cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.NodeModifiedById;
// reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// // SELECT [NodeId], [Timestamp] FROM Nodes WHERE NodeId = @@IDENTITY
// result = Convert.ToInt32(reader[0]);
// nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
// }
// }
// catch (SqlException e) //rethrow
// {
// throw new DataException(e.Message, e);
// }
// finally
// {
// if (reader != null && !reader.IsClosed)
// reader.Close();
// cmd.Dispose();
// }
// return result;
//}
public void UpdateNodeRow(NodeData nodeData)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_Node_Update"
};
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id;
cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId;
cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0;
cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = nodeData.IsInherited ? 1 : 0;
cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name;
cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path;
cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index;
cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0;
cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty;
cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType;
cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout;
cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate;
cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty;
cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate;
cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.NodeCreationDate;
cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.NodeCreatedById;
cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.NodeModifiedById;
cmd.Parameters.Add("@NodeTimestamp", SqlDbType.Timestamp).Value = SqlProvider.GetBytesFromLong(nodeData.NodeTimestamp);
reader = cmd.ExecuteReader();
while (reader.Read())
{
// SELECT [Timestamp] FROM Nodes WHERE NodeId = @NodeId
nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[0]);
}
}
catch (SqlException sex) //rethrow
{
if (sex.Message.StartsWith("Node is out of date"))
{
throw new NodeIsOutOfDateException(nodeData.Id, nodeData.Path, nodeData.VersionId, nodeData.Version, sex, nodeData.NodeTimestamp);
}
throw new DataException(sex.Message, sex);
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
cmd.Dispose();
}
}
public void CopyAndUpdateVersion(NodeData nodeData, int previousVersionId, int destinationVersionId, out int lastMajorVersionId, out int lastMinorVersionId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
lastMajorVersionId = 0;
lastMinorVersionId = 0;
try
{
cmd = new SqlProcedure {
CommandText = "proc_Version_CopyAndUpdate"
};
cmd.Parameters.Add("@PreviousVersionId", SqlDbType.Int).Value = previousVersionId;
cmd.Parameters.Add("@DestinationVersionId", SqlDbType.Int).Value = (destinationVersionId != 0) ? (object)destinationVersionId : DBNull.Value;
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id;
cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate;
cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById;
cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById;
cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData);
reader = cmd.ExecuteReader();
while (reader.Read())
{
// SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @NewVersionId
nodeData.VersionId = Convert.ToInt32(reader[0]);
nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]);
lastMajorVersionId = reader.GetSafeInt32(3);
lastMinorVersionId = reader.GetSafeInt32(4);
}
if (reader.NextResult())
{
// SELECT BinaryPropertyId, PropertyTypeId FROM BinaryProperties WHERE VersionId = @NewVersionId
while (reader.Read())
{
var binId = Convert.ToInt32(reader[0]);
var propId = Convert.ToInt32(reader[1]);
var binaryData = (BinaryDataValue)nodeData.GetDynamicRawData(propId);
binaryData.Id = binId;
}
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
cmd.Dispose();
}
}
// ============================================================================ "less roundtrip methods"
public virtual void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
{
using (var cmd = new SqlProcedure {
CommandText = "proc_NodeAndVersion_Insert"
})
{
cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId;
cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
cmd.Parameters.Add("@CreatingInProgress", SqlDbType.TinyInt).Value = nodeData.CreatingInProgress;
cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0;
cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = 0;
cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name;
cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path;
cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index;
cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0;
cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty;
cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType;
cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout;
cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate;
cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty;
cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate;
cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate;
cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value = nodeData.CreatedById;
cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById;
cmd.Parameters.Add("@IsSystem", SqlDbType.TinyInt).Value = nodeData.IsSystem ? 1 : 0;
cmd.Parameters.Add("@OwnerId", SqlDbType.Int).Value = nodeData.OwnerId;
cmd.Parameters.Add("@SavingState", SqlDbType.Int).Value = (int)nodeData.SavingState;
cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData);
cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate;
cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById;
cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
nodeData.Id = Convert.ToInt32(reader[0]);
nodeData.VersionId = Convert.ToInt32(reader[1]);
nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]);
nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]);
lastMajorVersionId = reader.GetSafeInt32(4);
lastMinorVersionId = reader.GetSafeInt32(5);
nodeData.Path = reader.GetSafeString(6);
}
}
}
//============================================================================ "less roundtrip methods"
public void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
{
using (var cmd = new SqlProcedure {
CommandText = INSERT_NODE_AND_VERSION_ROWS, CommandType = CommandType.Text
})
{
cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId;
cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0;
cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = nodeData.IsInherited ? 1 : 0;
cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name;
cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path;
cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index;
cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0;
cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty;
cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType;
cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout;
cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate;
cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty;
cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate;
cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value = nodeData.NodeCreationDate;
cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value = nodeData.NodeCreatedById;
cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value = nodeData.NodeModifiedById;
cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate;
cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.CreatedById;
cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
nodeData.Id = Convert.ToInt32(reader[0]);
nodeData.VersionId = Convert.ToInt32(reader[1]);
nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]);
nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]);
lastMajorVersionId = reader.GetSafeInt32(4);
lastMinorVersionId = reader.GetSafeInt32(5);
}
}
}
/*============================================================================ Version Insert/Update */
//public int InsertVersionRow(NodeData nodeData)
//{
// SqlProcedure cmd = null;
// SqlDataReader reader = null;
// int result = 0;
// try
// {
// cmd = new SqlProcedure { CommandText = "proc_Version_Insert" };
// cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id;
// cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
// cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
// cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
// cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate;
// cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.CreatedById;
// cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
// cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById;
// reader = cmd.ExecuteReader();
// while (reader.Read())
// {
// // SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @@IDENTITY
// result = Convert.ToInt32(reader[0]);
// nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
// }
// }
// finally
// {
// if (reader != null && !reader.IsClosed)
// reader.Close();
// cmd.Dispose();
// }
// return result;
//}
public void UpdateVersionRow(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
lastMajorVersionId = 0;
lastMinorVersionId = 0;
try
{
cmd = new SqlProcedure {
CommandText = "proc_Version_Update"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeData.VersionId;
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id;
if (nodeData.IsPropertyChanged("Version"))
{
cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
}
cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate;
cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById;
cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById;
cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData);
reader = cmd.ExecuteReader();
while (reader.Read())
{
// SELECT [Timestamp] FROM Versions WHERE VersionId = @VersionId
nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[0]);
nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
lastMajorVersionId = reader.GetSafeInt32(2);
lastMinorVersionId = reader.GetSafeInt32(3);
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
cmd.Dispose();
}
}
