| public ExecuteReader ( ) : System.Data.SqlClient.SqlDataReader | ||
| return | System.Data.SqlClient.SqlDataReader | |
private static List <int> GetExistingFlatPages(int versionId)
{
List <int> pages = new List <int>();
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_FlatProperties_GetExistingPages"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
reader = cmd.ExecuteReader();
int pageIndex = reader.GetOrdinal("Page");
while (reader.Read())
{
pages.Add(reader.GetInt32(pageIndex));
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
cmd.Dispose();
}
return(pages);
}
private static List<int> GetExistingFlatPages(int versionId)
{
List<int> pages = new List<int>();
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_FlatProperties_GetExistingPages" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
reader = cmd.ExecuteReader();
int pageIndex = reader.GetOrdinal("Page");
while (reader.Read())
{
pages.Add(reader.GetInt32(pageIndex));
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
return pages;
}
//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();
}
}
// ============================================================================ "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);
}
}
}
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();
}
}
/// <summary>
/// Loads a cache item into memory that either contains the raw binary (if its size fits into the limit) or
/// just the blob metadata pointing to the blob storage.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
public BinaryCacheEntity LoadBinaryCacheEntity(int versionId, int propertyTypeId)
{
var commandText = string.Format(LoadBinaryCacheEntityFormatScript, BlobStorage.BinaryCacheSize);
using (var cmd = new SqlProcedure {
CommandText = commandText
})
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (!reader.HasRows || !reader.Read())
{
return(null);
}
var length = reader.GetInt64(0);
var binaryPropertyId = reader.GetInt32(1);
var fileId = reader.GetInt32(2);
var providerName = reader.GetSafeString(3);
var providerTextData = reader.GetSafeString(4);
byte[] rawData;
if (reader.IsDBNull(5))
{
rawData = null;
}
else
{
rawData = (byte[])reader.GetValue(5);
}
var provider = BlobStorageBase.GetProvider(providerName);
var context = new BlobStorageContext(provider, providerTextData)
{
VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = fileId, Length = length
};
if (provider == BlobStorageBase.BuiltInProvider)
{
context.BlobProviderData = new BuiltinBlobProviderData();
}
return(new BinaryCacheEntity
{
Length = length,
RawData = rawData,
BinaryPropertyId = binaryPropertyId,
FileId = fileId,
Context = context
});
}
}
}
//============================================================================ "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);
}
}
}
private static void WriteSqlFileStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null)
{
SqlProcedure cmd = null;
try
{
//If we did not receive a path and transaction context, retrieve it now from the database.
if (fileStreamData == null)
{
cmd = new SqlProcedure {
CommandText = UPDATE_BINARY_PROPERTY_FILESTREAM, CommandType = CommandType.Text
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;
string path;
byte[] transactionContext;
//Set Stream column to NULL and retrieve file path and
//transaction context for the Filestream column
using (var reader = cmd.ExecuteReader())
{
reader.Read();
path = reader.GetString(0);
transactionContext = reader.GetSqlBytes(1).Buffer;
}
fileStreamData = new FileStreamData {
Path = path, TransactionContext = transactionContext
};
}
stream.Seek(0, SeekOrigin.Begin);
//Write data using SqlFileStream
using (var fs = new SqlFileStream(fileStreamData.Path, fileStreamData.TransactionContext, FileAccess.Write))
{
//default buffer size is 4096
stream.CopyTo(fs);
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
/*============================================================================ 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();
}
}
/// <summary>
/// Deletes file records that are marked as deleted from the metadata database and also from the blob storage.
/// </summary>
/// <returns>Whether there was at least one row that was deleted.</returns>
public bool CleanupFiles()
{
using (var proc = new SqlProcedure {
CommandText = CleanupFileScript, CommandType = CommandType.Text
})
{
proc.CommandType = CommandType.Text;
try
{
var deleted = false;
using (var reader = proc.ExecuteReader())
{
var fileId = 0;
var size = 0L;
string providerName = null;
string providerData = null;
// We do not care about the number of deleted rows,
// we only want to know if a row was deleted or not.
if (reader.Read())
{
deleted = true;
fileId = reader.GetSafeInt32(reader.GetOrdinal("FileId"));
size = reader.GetSafeInt64(reader.GetOrdinal("Size"));
providerName = reader.GetSafeString(reader.GetOrdinal("BlobProvider"));
providerData = reader.GetSafeString(reader.GetOrdinal("BlobProviderData"));
}
// delete bytes from the blob storage
var provider = BlobStorageBase.GetProvider(providerName);
var ctx = new BlobStorageContext(provider, providerData)
{
VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size
};
ctx.Provider.Delete(ctx);
}
return(deleted);
}
catch (Exception ex)
{
throw new DataException("Error during binary cleanup.", ex);
}
}
}
public void UpdateBinaryProperty(int binaryDataId, BinaryDataValue value)
{
if (!RepositoryConfiguration.FileStreamEnabled)
{
// MS-SQL does not support stream size over [Int32.MaxValue],
// but check only if Filestream is not enabled
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
{
throw new NotSupportedException();
}
}
var isRepositoryStream = value.Stream is RepositoryStream || value.Stream is SenseNetSqlFileStream;
FileStreamData fileStreamData = null;
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Update"
};
cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryDataId;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = value.Size;
// Do not update the stream field in the database if it is not loaded (other change happened)
cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isRepositoryStream ? 0 : 1;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;
if (RepositoryConfiguration.FileStreamEnabled)
{
string path;
byte[] transactionContext;
//Update row and retrieve file path and
//transaction context for the Filestream column
using (var reader = cmd.ExecuteReader())
{
reader.Read();
path = reader.GetString(0);
transactionContext = reader.GetSqlBytes(1).Buffer;
}
if (!string.IsNullOrEmpty(path))
{
fileStreamData = new FileStreamData {
Path = path, TransactionContext = transactionContext
}
}
;
}
else
{
cmd.ExecuteNonQuery();
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
if (!isRepositoryStream && value.Stream != null && value.Stream.Length > 0)
{
// Stream exists and is loaded -> write it
WriteBinaryStream(value.Stream, binaryDataId, fileStreamData);
}
}
protected internal override IndexDocumentData LoadIndexDocumentByVersionId(int versionId)
{
using (var cmd = new SqlProcedure { CommandText = LOADINDEXDOCUMENTSCRIPT + "WHERE V.VersionId = @VersionId" })
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
return GetIndexDocumentDataFromReader(reader);
return null;
}
}
}
/// <summary>
/// Inserts a new binary property value into the metadata database and the blob storage,
/// removing the previous one if the content is not new.
/// </summary>
/// <param name="blobProvider">Blob storage provider.</param>
/// <param name="value">Binary data to insert.</param>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="isNewNode">Whether this value belongs to a new or an existing node.</param>
public void InsertBinaryProperty(IBlobProvider blobProvider, BinaryDataValue value, int versionId, int propertyTypeId, bool isNewNode)
{
var streamLength = value.Stream?.Length ?? 0;
var ctx = new BlobStorageContext(blobProvider)
{
VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = 0, Length = streamLength
};
// In case of an external provider allocate the place for bytes and
// write the stream beforehand and get the generated provider data.
// Note that the external provider does not need an existing record
// in the Files table to work, it just stores the bytes.
if (blobProvider != BlobStorageBase.BuiltInProvider && streamLength > 0)
{
blobProvider.Allocate(ctx);
using (var stream = blobProvider.GetStreamForWrite(ctx))
value.Stream?.CopyTo(stream);
value.BlobProviderName = ctx.Provider.GetType().FullName;
value.BlobProviderData = BlobStorageContext.SerializeBlobProviderData(ctx.BlobProviderData);
}
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = isNewNode ? InsertBinaryPropertyScript : DeleteAndInsertBinaryProperty, CommandType = CommandType.Text
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId != 0 ? (object)versionId : DBNull.Value;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId != 0 ? (object)propertyTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = Math.Max(0, value.Size);
cmd.Parameters.Add("@BlobProvider", SqlDbType.NVarChar, 450).Value = value.BlobProviderName != null ? (object)value.BlobProviderName : DBNull.Value;
cmd.Parameters.Add("@BlobProviderData", SqlDbType.NVarChar, int.MaxValue).Value = value.BlobProviderData != null ? (object)value.BlobProviderData : DBNull.Value;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = value.Checksum != null ? (object)value.Checksum : DBNull.Value;
// insert binary and file rows and retrieve new ids.
using (var reader = cmd.ExecuteReader())
{
reader.Read();
value.Id = Convert.ToInt32(reader[0]);
value.FileId = Convert.ToInt32(reader[1]);
value.Timestamp = Utility.Convert.BytesToLong((byte[])reader.GetValue(2));
}
}
finally
{
cmd.Dispose();
}
// The BuiltIn blob provider saves the stream after the record
// was saved into the Files table, because simple varbinary
// column must exist before we can write a stream into the record.
// ReSharper disable once InvertIf
if (blobProvider == BlobStorageBase.BuiltInProvider && value.Stream != null && value.Stream.Length > 0)
{
ctx.FileId = value.FileId;
ctx.BlobProviderData = new BuiltinBlobProviderData();
BuiltInBlobProvider.AddStream(ctx, value.Stream);
}
}
/// <summary>
/// Starts a chunked save operation on an existing content. It does not write any binary data
/// to the storage, it only makes prerequisite operations - e.g. allocates a new slot in the storage.
/// </summary>
/// <param name="blobProvider">Blob storage provider.</param>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="fullSize">Full size (stream length) of the binary value.</param>
/// <returns>A token containing all the information (db record ids) that identify a single entry in the blob storage.</returns>
public string StartChunk(IBlobProvider blobProvider, int versionId, int propertyTypeId, long fullSize)
{
var isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
{
TransactionScope.Begin();
}
var ctx = new BlobStorageContext(blobProvider)
{
VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = 0, Length = fullSize
};
string blobProviderName = null;
string blobProviderData = null;
if (blobProvider != BlobStorageBase.BuiltInProvider)
{
blobProvider.Allocate(ctx);
blobProviderName = blobProvider.GetType().FullName;
blobProviderData = BlobStorageContext.SerializeBlobProviderData(ctx.BlobProviderData);
}
try
{
using (var cmd = new SqlProcedure {
CommandText = InsertStagingBinaryScript, CommandType = CommandType.Text
})
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = fullSize;
cmd.Parameters.Add("@BlobProvider", SqlDbType.NVarChar, 450).Value = blobProviderName != null ? (object)blobProviderName : DBNull.Value;
cmd.Parameters.Add("@BlobProviderData", SqlDbType.NVarChar, int.MaxValue).Value = blobProviderData != null ? (object)blobProviderData : DBNull.Value;
int binaryPropertyId;
int fileId;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
binaryPropertyId = reader.GetSafeInt32(0);
fileId = reader.GetSafeInt32(1);
}
else
{
throw new DataException("File row could not be inserted.");
}
}
ctx.FileId = fileId;
return(new ChunkToken
{
VersionId = versionId,
PropertyTypeId = propertyTypeId,
BinaryPropertyId = binaryPropertyId,
FileId = fileId
}.GetToken());
}
}
catch (Exception ex)
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during saving binary chunk to SQL Server.", ex);
}
finally
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
}
protected internal override IEnumerable<int> QueryNodesByReferenceAndType(string referenceName, int referredNodeId, int[] allowedTypeIds)
{
if (referenceName == null)
throw new ArgumentNullException("referenceName");
if (referenceName.Length == 0)
throw new ArgumentException("Argument referenceName cannot be empty.", "referenceName");
var referenceProperty = ActiveSchema.PropertyTypes[referenceName];
if (referenceProperty == null)
throw new ArgumentException("PropertyType is not found: " + referenceName, "referenceName");
var referencePropertyId = referenceProperty.Id;
string sql;
if (allowedTypeIds == null || allowedTypeIds.Length == 0)
{
sql = @"SELECT V.NodeId FROM ReferenceProperties R
JOIN Versions V ON R.VersionId = V.VersionId
JOIN Nodes N ON V.VersionId = N.LastMinorVersionId
WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId";
}
else
{
sql = String.Format(@"SELECT N.NodeId FROM ReferenceProperties R
JOIN Versions V ON R.VersionId = V.VersionId
JOIN Nodes N ON V.VersionId = N.LastMinorVersionId
WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId AND N.NodeTypeId IN ({0})", String.Join(", ", allowedTypeIds));
}
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = referencePropertyId;
cmd.Parameters.Add("@ReferredNodeId", SqlDbType.Int).Value = referredNodeId;
var result = new List<int>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
result.Add(reader.GetSafeInt32(0));
return result;
}
}
}
//protected internal override INodeQueryCompiler CreateNodeQueryCompiler()
//{
// return new SqlCompiler();
//}
//protected internal override List<NodeToken> ExecuteQuery(NodeQuery query)
//{
// List<NodeToken> result = new List<NodeToken>();
// SqlCompiler compiler = new SqlCompiler();
// NodeQueryParameter[] parameters;
// string compiledCommandText = compiler.Compile(query, out parameters);
// SqlProcedure command = null;
// SqlDataReader reader = null;
// try
// {
// command = new SqlProcedure { CommandText = compiledCommandText };
// command.CommandType = CommandType.Text;
// foreach (var parameter in parameters)
// command.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
// reader = command.ExecuteReader();
// ReadNodeTokens(reader, result);
// }
// finally
// {
// if (reader != null && !reader.IsClosed)
// reader.Close();
// command.Dispose();
// }
// return result;
//}
protected internal override void LoadNodes(Dictionary<int, NodeBuilder> buildersByVersionId)
{
List<string> versionInfo = new List<string>();
versionInfo.Add(String.Concat("VersionsId[count: ", buildersByVersionId.Count, "]"));
if (buildersByVersionId.Keys.Count > 20)
{
versionInfo.AddRange(buildersByVersionId.Keys.Take(20).Select(x => x.ToString()));
versionInfo.Add("...");
}
else
versionInfo.AddRange(buildersByVersionId.Keys.Select(x => x.ToString()).ToArray());
var operationTitle = String.Join(", ", versionInfo.ToArray());
using (var traceOperation = Logger.TraceOperation("SqlProvider.LoadNodes" + operationTitle))
{
var builders = buildersByVersionId; // Shortcut
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_LoadData_Batch" };
string xmlIds = CreateIdXmlForNodeInfoBatchLoad(builders);
cmd.Parameters.Add("@IdsInXml", SqlDbType.Xml).Value = xmlIds;
reader = cmd.ExecuteReader();
//-- #1: FlatProperties
//SELECT * FROM FlatProperties
// WHERE VersionId IN (select id from @versionids)
var versionIdIndex = reader.GetOrdinal("VersionId");
var pageIndex = reader.GetOrdinal("Page");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
int page = reader.GetInt32(pageIndex);
NodeBuilder builder = builders[versionId];
foreach (PropertyType pt in builder.Token.AllPropertyTypes)
{
string mapping = PropertyMap.GetValidMapping(page, pt);
if (mapping.Length != 0)
{
// Mapped property appears in the given page
object val = reader[mapping];
builder.AddDynamicProperty(pt, (val == DBNull.Value) ? null : val);
}
}
}
reader.NextResult();
//-- #2: BinaryProperties
//SELECT BinaryPropertyId, VersionId, PropertyTypeId, ContentType, FileNameWithoutExtension,
// Extension, [Size], [Checksum], NULL AS Stream, 0 AS Loaded
//FROM dbo.BinaryProperties
//WHERE PropertyTypeId IN (select id from @binids) AND VersionId IN (select id from @versionids)
var binaryPropertyIdIndex = reader.GetOrdinal("BinaryPropertyId");
versionIdIndex = reader.GetOrdinal("VersionId");
var checksumPropertyIndex = reader.GetOrdinal("Checksum");
var propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var contentTypeIndex = reader.GetOrdinal("ContentType");
var fileNameWithoutExtensionIndex = reader.GetOrdinal("FileNameWithoutExtension");
var extensionIndex = reader.GetOrdinal("Extension");
var sizeIndex = reader.GetOrdinal("Size");
while (reader.Read())
{
string ext = reader.GetString(extensionIndex);
if (ext.Length != 0)
ext = ext.Remove(0, 1); // Remove dot from the start if extension is not empty
string fn = reader.GetSafeString(fileNameWithoutExtensionIndex); // reader.IsDBNull(fileNameWithoutExtensionIndex) ? null : reader.GetString(fileNameWithoutExtensionIndex);
var x = new BinaryDataValue
{
Id = reader.GetInt32(binaryPropertyIdIndex),
Checksum = reader.GetSafeString(checksumPropertyIndex), //reader.IsDBNull(checksumPropertyIndex) ? null : reader.GetString(checksumPropertyIndex),
FileName = new BinaryFileName(fn, ext),
ContentType = reader.GetString(contentTypeIndex),
Size = reader.GetInt64(sizeIndex)
};
var versionId = reader.GetInt32(versionIdIndex);
var propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
builders[versionId].AddDynamicProperty(propertyTypeId, x);
}
reader.NextResult();
//-- #3: ReferencePropertyInfo + Referred NodeToken
//SELECT VersionId, PropertyTypeId, ReferredNodeId
//FROM dbo.ReferenceProperties ref
//WHERE ref.VersionId IN (select id from @versionids)
versionIdIndex = reader.GetOrdinal("VersionId");
propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var nodeIdIndex = reader.GetOrdinal("ReferredNodeId");
//-- Collect references to Dictionary<versionId, Dictionary<propertyTypeId, List<referredNodeId>>>
var referenceCollector = new Dictionary<int, Dictionary<int, List<int>>>();
while (reader.Read())
{
var versionId = reader.GetInt32(versionIdIndex);
var propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
var referredNodeId = reader.GetInt32(nodeIdIndex);
if (!referenceCollector.ContainsKey(versionId))
referenceCollector.Add(versionId, new Dictionary<int, List<int>>());
var referenceCollectorPerVersion = referenceCollector[versionId];
if (!referenceCollectorPerVersion.ContainsKey(propertyTypeId))
referenceCollectorPerVersion.Add(propertyTypeId, new List<int>());
referenceCollectorPerVersion[propertyTypeId].Add(referredNodeId);
}
//-- Set references to NodeData
foreach (var versionId in referenceCollector.Keys)
{
var referenceCollectorPerVersion = referenceCollector[versionId];
foreach (var propertyTypeId in referenceCollectorPerVersion.Keys)
builders[versionId].AddDynamicProperty(propertyTypeId, referenceCollectorPerVersion[propertyTypeId]);
}
reader.NextResult();
//-- #4: TextPropertyInfo (NText:Lazy, NVarchar(4000):loaded)
//SELECT VersionId, PropertyTypeId, NULL AS Value, 0 AS Loaded
//FROM dbo.TextPropertiesNText
//WHERE VersionId IN (select id from @versionids)
//UNION ALL
//SELECT VersionId, PropertyTypeId, Value, 1 AS Loaded
//FROM dbo.TextPropertiesNVarchar
//WHERE VersionId IN (select id from @versionids)
versionIdIndex = reader.GetOrdinal("VersionID");
propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var valueIndex = reader.GetOrdinal("Value");
var loadedIndex = reader.GetOrdinal("Loaded");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
int propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
string value = reader.GetSafeString(valueIndex); // (reader[valueIndex] == DBNull.Value) ? null : reader.GetString(valueIndex);
bool loaded = Convert.ToBoolean(reader.GetInt32(loadedIndex));
if (loaded)
builders[versionId].AddDynamicProperty(propertyTypeId, value);
}
reader.NextResult();
//-- #5: BaseData
//SELECT N.NodeId, N.NodeTypeId, N.ContentListTypeId, N.ContentListId, N.IsDeleted, N.IsInherited,
// N.ParentNodeId, N.[Name], N.[Path], N.[Index], N.Locked, N.LockedById,
// N.ETag, N.LockType, N.LockTimeout, N.LockDate, N.LockToken, N.LastLockUpdate,
// N.CreationDate AS NodeCreationDate, N.CreatedById AS NodeCreatedById,
// N.ModificationDate AS NodeModificationDate, N.ModifiedById AS NodeModifiedById,
// V.VersionId, V.MajorNumber, V.MinorNumber, V.CreationDate, V.CreatedById,
// V.ModificationDate, V.ModifiedById, V.[Status]
//FROM dbo.Nodes AS N
// INNER JOIN dbo.Versions AS V ON N.NodeId = V.NodeId ON N.NodeId = V.NodeId
//WHERE V.VersionId IN (select id from @versionids)
nodeIdIndex = reader.GetOrdinal("NodeId");
var nodeTypeIdIndex = reader.GetOrdinal("NodeTypeId");
var contentListTypeIdIndex = reader.GetOrdinal("ContentListTypeId");
var contentListIdIndex = reader.GetOrdinal("ContentListId");
var isDeletedIndex = reader.GetOrdinal("IsDeleted");
var isInheritedIndex = reader.GetOrdinal("IsInherited");
var parentNodeIdIndex = reader.GetOrdinal("ParentNodeId");
var nameIndex = reader.GetOrdinal("Name");
var displayNameIndex = reader.GetOrdinal("DisplayName");
var pathIndex = reader.GetOrdinal("Path");
var indexIndex = reader.GetOrdinal("Index");
var lockedIndex = reader.GetOrdinal("Locked");
var lockedByIdIndex = reader.GetOrdinal("LockedById");
var eTagIndex = reader.GetOrdinal("ETag");
var lockTypeIndex = reader.GetOrdinal("LockType");
var lockTimeoutIndex = reader.GetOrdinal("LockTimeout");
var lockDateIndex = reader.GetOrdinal("LockDate");
var lockTokenIndex = reader.GetOrdinal("LockToken");
var lastLockUpdateIndex = reader.GetOrdinal("LastLockUpdate");
var nodeCreationDateIndex = reader.GetOrdinal("NodeCreationDate");
var nodeCreatedByIdIndex = reader.GetOrdinal("NodeCreatedById");
var nodeModificationDateIndex = reader.GetOrdinal("NodeModificationDate");
var nodeModifiedByIdIndex = reader.GetOrdinal("NodeModifiedById");
var nodeTimestampIndex = reader.GetOrdinal("NodeTimestamp");
versionIdIndex = reader.GetOrdinal("VersionId");
var majorNumberIndex = reader.GetOrdinal("MajorNumber");
var minorNumberIndex = reader.GetOrdinal("MinorNumber");
var creationDateIndex = reader.GetOrdinal("CreationDate");
var createdByIdIndex = reader.GetOrdinal("CreatedById");
var modificationDateIndex = reader.GetOrdinal("ModificationDate");
var modifiedByIdIndex = reader.GetOrdinal("ModifiedById");
var status = reader.GetOrdinal("Status");
var versionTimestampIndex = reader.GetOrdinal("VersionTimestamp");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
VersionNumber versionNumber = new VersionNumber(
reader.GetInt16(majorNumberIndex),
reader.GetInt16(minorNumberIndex),
(VersionStatus)reader.GetInt16(status));
builders[versionId].SetCoreAttributes(
reader.GetInt32(nodeIdIndex),
reader.GetInt32(nodeTypeIdIndex),
TypeConverter.ToInt32(reader.GetValue(contentListIdIndex)),
TypeConverter.ToInt32(reader.GetValue(contentListTypeIdIndex)),
Convert.ToBoolean(reader.GetByte(isDeletedIndex)),
Convert.ToBoolean(reader.GetByte(isInheritedIndex)),
reader.GetSafeInt32(parentNodeIdIndex), // reader.GetValue(parentNodeIdIndex) == DBNull.Value ? 0 : reader.GetInt32(parentNodeIdIndex), //parent,
reader.GetString(nameIndex),
reader.GetSafeString(displayNameIndex),
reader.GetString(pathIndex),
reader.GetInt32(indexIndex),
Convert.ToBoolean(reader.GetByte(lockedIndex)),
reader.GetSafeInt32(lockedByIdIndex), // reader.GetValue(lockedByIdIndex) == DBNull.Value ? 0 : reader.GetInt32(lockedByIdIndex),
reader.GetString(eTagIndex),
reader.GetInt32(lockTypeIndex),
reader.GetInt32(lockTimeoutIndex),
reader.GetDateTime(lockDateIndex),
reader.GetString(lockTokenIndex),
reader.GetDateTime(lastLockUpdateIndex),
versionId,
versionNumber,
reader.GetDateTime(creationDateIndex),
reader.GetInt32(createdByIdIndex),
reader.GetDateTime(modificationDateIndex),
reader.GetInt32(modifiedByIdIndex),
reader.GetDateTime(nodeCreationDateIndex),
reader.GetInt32(nodeCreatedByIdIndex),
reader.GetDateTime(nodeModificationDateIndex),
reader.GetInt32(nodeModifiedByIdIndex),
GetLongFromBytes((byte[])reader.GetValue(nodeTimestampIndex)),
GetLongFromBytes((byte[])reader.GetValue(versionTimestampIndex))
);
}
foreach (var builder in builders.Values)
builder.Finish();
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
traceOperation.IsSuccessful = true;
}
}
protected internal override List<ContentListType> GetContentListTypesInTree(string path)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
var result = new List<ContentListType>();
string commandString = @"SELECT ContentListTypeId FROM Nodes WHERE ContentListId IS NULL AND ContentListTypeId IS NOT NULL AND Path LIKE @Path + '/%'";
cmd = new SqlProcedure { CommandText = commandString };
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = path;
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
{
var id = reader.GetInt32(0);
var t = NodeTypeManager.Current.ContentListTypes.GetItemById(id);
result.Add(t);
}
}
finally
{
if (reader != null)
reader.Dispose();
if (cmd != null)
cmd.Dispose();
}
return result;
}
protected internal override IEnumerable<int> QueryNodesByTypeAndPathAndName(int[] nodeTypeIds, string pathStart, bool orderByPath, string name)
{
var sql = new StringBuilder("SELECT NodeId FROM Nodes WHERE");
var first = true;
if (pathStart != null)
{
sql.Append(" Path LIKE '");
sql.Append(pathStart);
if (!pathStart.EndsWith(RepositoryPath.PathSeparator))
sql.Append(RepositoryPath.PathSeparator);
sql.Append("%'");
first = false;
}
if (name != null)
{
if (!first)
sql.Append(" AND");
sql.Append(" Name = '").Append(name).Append("'");
first = false;
}
if (nodeTypeIds != null)
{
if (!first)
sql.Append(" AND");
sql.Append(" NodeTypeId");
if (nodeTypeIds.Length == 1)
sql.Append(" = ").Append(nodeTypeIds[0]);
else
sql.Append(" IN (").Append(String.Join(", ", nodeTypeIds)).Append(")");
first = false;
}
if (orderByPath)
sql.AppendLine().Append("ORDER BY Path");
var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text };
SqlDataReader reader = null;
var result = new List<int>();
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
result.Add(reader.GetSafeInt32(0));
return result;
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
}
//////////////////////////////////////// Operations ////////////////////////////////////////
protected internal override IEnumerable<NodeType> LoadChildTypesToAllow(int sourceNodeId)
{
var result = new List<NodeType>();
using (var cmd = new SqlProcedure { CommandText = "proc_LoadChildTypesToAllow" })
{
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = sourceNodeId;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var name = (string)reader[0];
var nt = ActiveSchema.NodeTypes[name];
if(nt != null)
result.Add(nt);
}
}
return result;
}
protected internal override void DeleteVersion(int versionId, NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
lastMajorVersionId = 0;
lastMinorVersionId = 0;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_DeleteVersion" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
reader = cmd.ExecuteReader();
//refresh timestamp value from the db
while (reader.Read())
{
nodeData.NodeTimestamp = DataProvider.GetLongFromBytes((byte[])reader[0]);
lastMajorVersionId = reader.GetSafeInt32(1);
lastMinorVersionId = reader.GetSafeInt32(2);
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
}
protected internal override IEnumerable<int> GetChildrenIdentfiers(int nodeId)
{
var cmd = new SqlProcedure { CommandText = "SELECT NodeId FROM Nodes WHERE ParentNodeId = @ParentNodeId" };
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = nodeId;
SqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
var ids = new List<int>();
while (reader.Read())
ids.Add(reader.GetSafeInt32(0));
return ids;
}
finally
{
if (reader != null)
reader.Dispose();
if (cmd != null)
cmd.Dispose();
}
}
/// <summary>
/// Updates an existing binary property value in the database and the blob storage.
/// </summary>
/// <param name="blobProvider">Blob storage provider.</param>
/// <param name="value">Binary data to update.</param>
public void UpdateBinaryProperty(IBlobProvider blobProvider, BinaryDataValue value)
{
var streamLength = value.Stream?.Length ?? 0;
if (blobProvider != BlobStorageBase.BuiltInProvider && streamLength > 0)
{
var ctx = new BlobStorageContext(blobProvider, value.BlobProviderData)
{
VersionId = 0,
PropertyTypeId = 0,
FileId = value.FileId,
Length = streamLength,
UseFileStream = false
};
blobProvider.Allocate(ctx);
using (var stream = blobProvider.GetStreamForWrite(ctx))
value.Stream?.CopyTo(stream);
value.BlobProviderName = ctx.Provider.GetType().FullName;
value.BlobProviderData = BlobStorageContext.SerializeBlobProviderData(ctx.BlobProviderData);
}
else
{
value.BlobProviderName = null;
value.BlobProviderData = null;
}
if (blobProvider == BlobStorageBase.BuiltInProvider && !BlobStorage.FileStreamEnabled)
{
// MS-SQL does not support stream size over [Int32.MaxValue],
// but check only if Filestream is not enabled
if (streamLength > int.MaxValue)
{
throw new NotSupportedException();
}
}
var isRepositoryStream = value.Stream is RepositoryStream || value.Stream is SenseNetSqlFileStream;
var hasStream = isRepositoryStream || value.Stream is MemoryStream;
if (!hasStream)
{
// do not do any database operation if the stream is not modified
return;
}
FileStreamData fileStreamData = null;
SqlProcedure cmd = null;
try
{
string sql;
CommandType commandType;
if (blobProvider == BlobStorageBase.BuiltInProvider)
{
commandType = CommandType.StoredProcedure;
sql = "proc_BinaryProperty_Update";
}
else
{
commandType = CommandType.Text;
sql = BlobStorage.FileStreamEnabled
? UpdateBinarypropertyNewFilerowFilestreamScript
: UpdateBinarypropertyNewFilerowScript;
}
cmd = new SqlProcedure {
CommandText = sql, CommandType = commandType
};
cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = value.Id;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = value.Size;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = value.Checksum != null ? (object)value.Checksum : DBNull.Value;
cmd.Parameters.Add("@BlobProvider", SqlDbType.NVarChar, 450).Value = value.BlobProviderName != null ? (object)value.BlobProviderName : DBNull.Value;
cmd.Parameters.Add("@BlobProviderData", SqlDbType.NVarChar, int.MaxValue).Value = value.BlobProviderData != null ? (object)value.BlobProviderData : DBNull.Value;
int fileId;
if (BlobStorage.FileStreamEnabled)
{
string path;
byte[] transactionContext;
// Update row and retrieve file path and
// transaction context for the Filestream column
using (var reader = cmd.ExecuteReader())
{
reader.Read();
fileId = reader.GetInt32(0);
path = reader.GetSafeString(1);
transactionContext = reader.IsDBNull(2) ? null : reader.GetSqlBytes(2).Buffer;
}
if (!string.IsNullOrEmpty(path))
{
fileStreamData = new FileStreamData {
Path = path, TransactionContext = transactionContext
}
}
;
}
else
{
fileId = (int)cmd.ExecuteScalar();
}
if (fileId > 0 && fileId != value.FileId)
{
value.FileId = fileId;
}
}
finally
{
cmd?.Dispose();
}
// ReSharper disable once InvertIf
if (blobProvider == BlobStorageBase.BuiltInProvider && !isRepositoryStream && streamLength > 0)
{
// Stream exists and is loaded -> write it
var ctx = new BlobStorageContext(blobProvider, value.BlobProviderData)
{
VersionId = 0,
PropertyTypeId = 0,
FileId = value.FileId,
Length = streamLength,
UseFileStream = fileStreamData != null,
BlobProviderData = new BuiltinBlobProviderData {
FileStreamData = fileStreamData
}
};
BuiltInBlobProvider.UpdateStream(ctx, value.Stream);
}
}
/// <summary>
/// Loads a cache item into memory that either contains the raw binary (if its size fits into the limit) or
/// just the blob metadata pointing to the blob storage.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
public BinaryCacheEntity LoadBinaryCacheEntity(int versionId, int propertyTypeId)
{
var columnDefinitions = BlobStorage.FileStreamEnabled
? string.Format(LoadBinaryCacheentityColumnsFormatFilestreamScript, BlobStorage.BinaryCacheSize)
: string.Format(LoadBinaryCacheentityColumnsFormatScript, BlobStorage.BinaryCacheSize);
var commandText = string.Format(LoadBinaryCacheentityFormatScript, columnDefinitions);
using (var cmd = new SqlProcedure {
CommandText = commandText
})
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (!reader.HasRows || !reader.Read())
{
return(null);
}
var length = reader.GetInt64(0);
var binaryPropertyId = reader.GetInt32(1);
var fileId = reader.GetInt32(2);
var providerName = reader.GetSafeString(3);
var providerTextData = reader.GetSafeString(4);
byte[] rawData;
if (reader.IsDBNull(5))
{
rawData = null;
}
else
{
rawData = (byte[])reader.GetValue(5);
}
var useFileStream = false;
FileStreamData fileStreamData = null;
if (BlobStorage.FileStreamEnabled)
{
useFileStream = reader.GetInt32(6) == 1;
if (useFileStream)
{
// fill Filestream info if we really need it
fileStreamData = new FileStreamData
{
Path = reader.GetSafeString(7),
TransactionContext = reader.GetSqlBytes(8).Buffer
};
}
}
var provider = BlobStorageBase.GetProvider(providerName);
var context = new BlobStorageContext(provider, providerTextData)
{
VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = fileId, Length = length, UseFileStream = useFileStream
};
if (provider == BlobStorageBase.BuiltInProvider)
{
context.BlobProviderData = new BuiltinBlobProviderData {
FileStreamData = fileStreamData
}
}
;
return(new BinaryCacheEntity
{
Length = length,
RawData = rawData,
BinaryPropertyId = binaryPropertyId,
FileId = fileId,
Context = context
});
}
}
}
protected internal override IEnumerable<IndexDocumentData> LoadIndexDocumentByVersionId(IEnumerable<int> versionId)
{
var fi = 0;
var listCount = versionId.Count();
var result = new List<IndexDocumentData>();
while (fi * DOCSFRAGMENTSIZE < listCount)
{
var docsSegment = versionId.Skip(fi * DOCSFRAGMENTSIZE).Take(DOCSFRAGMENTSIZE).ToArray();
var paramNames = docsSegment.Select((s, i) => "@vi" + i.ToString()).ToArray();
var where = String.Concat("WHERE V.VersionId IN (", string.Join(", ", paramNames), ")");
SqlProcedure cmd = null;
var retry = 0;
while (retry < 15)
{
try
{
cmd = new SqlProcedure { CommandText = LOADINDEXDOCUMENTSCRIPT + where };
cmd.CommandType = CommandType.Text;
for (var i = 0; i < paramNames.Length; i++)
{
cmd.Parameters.AddWithValue(paramNames[i], docsSegment[i]);
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
result.Add(GetIndexDocumentDataFromReader(reader));
}
break;
}
catch (Exception ex)
{
Logger.WriteException(ex);
retry++;
System.Threading.Thread.Sleep(1000);
}
finally
{
if (cmd != null)
cmd.Dispose();
}
}
fi++;
}
return result;
}
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.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 = @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();
}
}
const int BUFFERSIZE = 1024 * 128; // * 512; // * 64; // * 8;
protected internal override IndexBackup LoadLastBackup()
{
var sql = @"
SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain],
DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp]
FROM [IndexBackup] WHERE IsActive != 0
SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain],
DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp]
FROM [IndexBackup2] WHERE IsActive != 0
";
IndexBackup result = null;
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
using (var reader = cmd.ExecuteReader())
{
do
{
while (reader.Read())
result = GetBackupFromReader(reader);
} while (reader.NextResult());
}
}
return result;
}
//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();
}
}
protected internal override IEnumerable<int> QueryNodesByTypeAndPathAndProperty(int[] nodeTypeIds, string pathStart, bool orderByPath, List<QueryPropertyData> properties)
{
var sql = new StringBuilder("SELECT NodeId FROM SysSearchWithFlatsView WHERE");
var first = true;
if (pathStart != null)
{
sql.Append(" Path LIKE '");
sql.Append(pathStart);
if (!pathStart.EndsWith(RepositoryPath.PathSeparator))
sql.Append(RepositoryPath.PathSeparator);
sql.Append("%'");
first = false;
}
if (nodeTypeIds != null)
{
if (!first)
sql.Append(" AND");
sql.Append(" NodeTypeId");
if (nodeTypeIds.Length == 1)
sql.Append(" = ").Append(nodeTypeIds[0]);
else
sql.Append(" IN (").Append(String.Join(", ", nodeTypeIds)).Append(")");
first = false;
}
if (properties != null)
{
foreach (var queryPropVal in properties)
{
if (string.IsNullOrEmpty(queryPropVal.PropertyName))
continue;
var pt = PropertyType.GetByName(queryPropVal.PropertyName);
var pm = pt == null ? null : pt.GetDatabaseInfo();
var colName = pm == null ? GetNodeAttributeName(queryPropVal.PropertyName) : pm.ColumnName;
var dt = pt == null ? GetNodeAttributeType(queryPropVal.PropertyName) : pt.DataType;
if (!first)
sql.Append(" AND");
if (queryPropVal.Value != null)
{
switch (dt)
{
case DataType.DateTime:
case DataType.String:
switch (queryPropVal.QueryOperator)
{
case Operator.Equal:
sql.AppendFormat(" {0} = '{1}'", colName, queryPropVal.Value);
break;
case Operator.Contains:
sql.AppendFormat(" {0} LIKE '%{1}%'", colName, queryPropVal.Value);
break;
case Operator.StartsWith:
sql.AppendFormat(" {0} LIKE '{1}%'", colName, queryPropVal.Value);
break;
case Operator.EndsWith:
sql.AppendFormat(" {0} LIKE '%{1}'", colName, queryPropVal.Value);
break;
case Operator.GreaterThan:
sql.AppendFormat(" {0} > '{1}'", colName, queryPropVal.Value);
break;
case Operator.GreaterThanOrEqual:
sql.AppendFormat(" {0} >= '{1}'", colName, queryPropVal.Value);
break;
case Operator.LessThan:
sql.AppendFormat(" {0} < '{1}'", colName, queryPropVal.Value);
break;
case Operator.LessThanOrEqual:
sql.AppendFormat(" {0} <= '{1}'", colName, queryPropVal.Value);
break;
case Operator.NotEqual:
sql.AppendFormat(" {0} <> '{1}'", colName, queryPropVal.Value);
break;
default:
throw new InvalidOperationException(string.Format("Direct query not implemented (data type: {0}, operator: {1})", dt, queryPropVal.QueryOperator));
}
break;
case DataType.Int:
case DataType.Currency:
switch (queryPropVal.QueryOperator)
{
case Operator.Equal:
sql.AppendFormat(" {0} = {1}", colName, queryPropVal.Value);
break;
case Operator.GreaterThan:
sql.AppendFormat(" {0} > '{1}'", colName, queryPropVal.Value);
break;
case Operator.GreaterThanOrEqual:
sql.AppendFormat(" {0} >= '{1}'", colName, queryPropVal.Value);
break;
case Operator.LessThan:
sql.AppendFormat(" {0} < '{1}'", colName, queryPropVal.Value);
break;
case Operator.LessThanOrEqual:
sql.AppendFormat(" {0} <= '{1}'", colName, queryPropVal.Value);
break;
case Operator.NotEqual:
sql.AppendFormat(" {0} <> '{1}'", colName, queryPropVal.Value);
break;
default:
throw new InvalidOperationException(string.Format("Direct query not implemented (data type: {0}, operator: {1})", dt, queryPropVal.QueryOperator));
}
break;
default:
throw new NotSupportedException("Not supported direct query dataType: " + dt);
}
}
else
{
sql.Append(" IS NULL");
}
}
}
if (orderByPath)
sql.AppendLine().Append("ORDER BY Path");
var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text };
SqlDataReader reader = null;
var result = new List<int>();
try
{
reader = cmd.ExecuteReader();
while (reader.Read())
result.Add(reader.GetSafeInt32(0));
return result;
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
}
internal override int LoadLastModifiersGroupId()
{
// SELECT TOP 1 NodeId FROM Nodes WHERE NodeTypeId = 2 AND Name = 'LastModifiers'
SqlProcedure cmd = null;
SqlDataReader reader = null;
string commandString = String.Format("SELECT TOP 1 NodeId FROM Nodes WHERE NodeTypeId = {0} AND Name = 'LastModifiers'", ActiveSchema.NodeTypes["Group"].Id);
cmd = new SqlProcedure { CommandText = commandString };
cmd.CommandType = CommandType.Text;
try
{
reader = cmd.ExecuteReader();
if (!reader.Read())
return 0;
var id = reader.GetSafeInt32(0);
return id;
}
finally
{
if (reader != null)
reader.Dispose();
if (cmd != null)
cmd.Dispose();
}
}
protected internal override VersionNumber[] GetVersionNumbers(string path)
{
List<VersionNumber> versions = new List<VersionNumber>();
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_VersionNumbers_GetByPath" };
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = path;
reader = cmd.ExecuteReader();
int majorNumberIndex = reader.GetOrdinal("MajorNumber");
int minorNumberIndex = reader.GetOrdinal("MinorNumber");
int statusIndex = reader.GetOrdinal("Status");
while (reader.Read())
{
versions.Add(new VersionNumber(
reader.GetInt32(majorNumberIndex),
reader.GetInt32(minorNumberIndex),
(VersionStatus)reader.GetInt32(statusIndex)));
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
return versions.ToArray();
}
private NodeHead LoadNodeHead(int nodeId, string path, int versionId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
//command string sceleton. When using this, WHERE clause needs to be completed!
string commandString = @"
SELECT
Node.NodeId, -- 0
Node.Name, -- 1
Node.DisplayName, -- 2
Node.Path, -- 3
Node.ParentNodeId, -- 4
Node.NodeTypeId, -- 5
Node.ContentListTypeId, -- 6
Node.ContentListId, -- 7
Node.CreationDate, -- 8
Node.ModificationDate, -- 9
Node.LastMinorVersionId, -- 10
Node.LastMajorVersionId, -- 11
Node.CreatedById, -- 12
Node.ModifiedById, -- 13
Node.[Index], -- 14
Node.LockedById, -- 15
Node.Timestamp -- 16
FROM
Nodes Node
WHERE ";
if (path != null)
{
commandString = string.Concat(commandString, "Node.Path = @Path");
}
else if (versionId > 0)
{
commandString = string.Concat(@"DECLARE @NodeId int
SELECT @NodeId = NodeId FROM Versions WHERE VersionId = @VersionId
",
commandString,
"Node.NodeId = @NodeId");
}
else
{
commandString = string.Concat(commandString, "Node.NodeId = @NodeId");
}
cmd = new SqlProcedure { CommandText = commandString };
cmd.CommandType = CommandType.Text;
if (path != null)
cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = path;
else if (versionId > 0)
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
else
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId;
try
{
reader = cmd.ExecuteReader();
if (!reader.Read())
return null;
return new NodeHead(
reader.GetInt32(0), // nodeId,
reader.GetString(1), // name,
reader.GetSafeString(2), // displayName,
reader.GetString(3), // pathInDb,
reader.GetSafeInt32(4), // parentNodeId,
reader.GetInt32(5), // nodeTypeId,
reader.GetSafeInt32(6), // contentListTypeId,
reader.GetSafeInt32(7), // contentListId,
reader.GetDateTime(8), // creationDate,
reader.GetDateTime(9), // modificationDate,
reader.GetSafeInt32(10), // lastMinorVersionId,
reader.GetSafeInt32(11), // lastMajorVersionId,
reader.GetSafeInt32(12), // creatorId,
reader.GetSafeInt32(13), // modifierId,
reader.GetSafeInt32(14), // index,
reader.GetSafeInt32(15), // lockerId
GetLongFromBytes((byte[])reader.GetValue(16)) // timestamp
);
}
finally
{
if (reader != null)
reader.Dispose();
if (cmd != null)
cmd.Dispose();
}
}
protected internal override Stream LoadBinaryPropertyValue(int versionId, int propertyTypeId)
{
// Retrieve binary pointer for chunk reading
int length = 0;
int pointer = 0;
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_GetPointer" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
SqlParameter pointerOutParam = cmd.Parameters.Add("@Id", SqlDbType.Int);
pointerOutParam.Direction = ParameterDirection.Output;
SqlParameter lengthOutParam = cmd.Parameters.Add("@Length", SqlDbType.Int);
lengthOutParam.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (lengthOutParam.Value != DBNull.Value)
length = (int)lengthOutParam.Value;
if (pointerOutParam.Value != DBNull.Value)
pointer = Convert.ToInt32(pointerOutParam.Value);
}
finally
{
cmd.Dispose();
}
if (pointer == 0)
return null;
// Read the stream by segments
cmd = null;
SqlDataReader reader = null;
Stream stream = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_ReadStream" };
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = pointer;
SqlParameter offsetParam = cmd.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter sizeParam = cmd.Parameters.Add("@Size", SqlDbType.Int);
int offset = 0;
offsetParam.Value = offset;
int size = BinaryStreamBufferLength;
sizeParam.Value = size;
byte[] buffer = new byte[BinaryStreamBufferLength];
stream = new MemoryStream(length);
if (length > 0)
{
do
{
// Calculate buffer size - may be less than BinaryStreamBufferLength for last block.
if ((offset + BinaryStreamBufferLength) >= length)
{
size = length - offset;
sizeParam.Value = size + 1;
}
reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
reader.Read();
reader.GetBytes(0, 0, buffer, 0, size);
reader.Close();
stream.Write(buffer, 0, size);
// Set the new offset
offset += size;
offsetParam.Value = offset;
}
while (offset < length);
stream.Seek(0, SeekOrigin.Begin);
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
return stream;
}
protected internal override NodeHead.NodeVersion[] GetNodeVersions(int nodeId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
string commandString = @"
SELECT VersionId, MajorNumber, MinorNumber, Status
FROM Versions
WHERE NodeId = @NodeId
ORDER BY MajorNumber, MinorNumber
";
cmd = new SqlProcedure { CommandText = commandString };
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@NodeId", SqlDbType.NVarChar, 450).Value = nodeId;
reader = cmd.ExecuteReader();
List<NodeHead.NodeVersion> versionList = new List<NodeHead.NodeVersion>();
while (reader.Read())
{
var versionId = reader.GetInt32(0);
var major = reader.GetInt16(1);
var minor = reader.GetInt16(2);
var statusCode = reader.GetInt16(3);
var versionNumber = new VersionNumber(major, minor, (VersionStatus)statusCode);
versionList.Add(new NodeHead.NodeVersion(versionNumber, versionId));
}
return versionList.ToArray();
}
finally
{
if (reader != null)
reader.Dispose();
if (cmd != null)
cmd.Dispose();
}
}
/*============================================================================ 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.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 [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();
}
}
protected internal override BinaryCacheEntity LoadBinaryCacheEntity(int nodeVersionId, int propertyTypeId)
{
var commandText = string.Format(@"
SELECT
Size,
CASE
WHEN Size < {0} THEN Stream
ELSE null
END AS Stream,
BinaryPropertyId
FROM
dbo.BinaryProperties
WHERE
VersionId = @VersionId
AND
PropertyTypeId = @PropertyTypeId
", RepositoryConfiguration.CachedBinarySize);
using (var cmd = new SqlProcedure { CommandText = commandText })
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeVersionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.CommandType = CommandType.Text;
var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult);
if (reader.HasRows && reader.Read())
{
long length = reader.GetInt64(0);
byte[] rawData;
if (reader.IsDBNull(1))
rawData = null;
else
rawData = (byte[])reader.GetValue(1);
int binaryPropertyId = reader.GetInt32(2);
reader.Close();
return new BinaryCacheEntity()
{
Length = length,
RawData = rawData,
BinaryPropertyId = binaryPropertyId
};
}
else
{
reader.Close();
return null;
}
}
}
//////////////////////////////////////// Security Methods ////////////////////////////////////////
protected internal override Dictionary<int, List<int>> LoadMemberships()
{
SqlProcedure cmd = null;
Dictionary<int, List<int>> result = new Dictionary<int, List<int>>();
try
{
cmd = new SqlProcedure { CommandText = "proc_Security_LoadMemberships" };
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int groupId = (int)reader["ContainerId"];
int userId = (int)reader["UserId"];
if (!result.ContainsKey(groupId))
result.Add(groupId, new List<int>());
result[groupId].Add(userId);
}
}
finally
{
cmd.Dispose();
}
return result;
}
public int InsertBinaryProperty(int versionId, int propertyTypeId, BinaryDataValue value, bool isNewNode)
{
SqlProcedure cmd = null;
var id = 0;
try
{
var streamSize = value.Stream != null?Convert.ToInt32(value.Stream.Length) : 0;
var useFileStream = RepositoryConfiguration.FileStreamEnabled &&
streamSize > RepositoryConfiguration.MinimumSizeForFileStreamInBytes;
cmd = useFileStream
? new SqlProcedure {
CommandText = (isNewNode ? INSERT_BINARY_PROPERTY_FILESTREAM : DELETE_AND_INSERT_BINARY_PROPERTY_FILESTREAM), CommandType = CommandType.Text
}
: new SqlProcedure {
CommandText = (isNewNode ? INSERT_BINARY_PROPERTY : DELETE_AND_INSERT_BINARY_PROPERTY), CommandType = CommandType.Text
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = (versionId != 0) ? (object)versionId : DBNull.Value;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = (propertyTypeId != 0) ? (object)propertyTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = Math.Max(0, value.Size);
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;
if (value.Stream != null && value.Stream.Length > 0)
{
value.Stream.Seek(0, SeekOrigin.Begin);
//use Filstream if it is enabled and the size is big enough
if (useFileStream)
{
//set old binary to NULL
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = DBNull.Value;
string path;
byte[] transactionContext;
//insert binary row and retrieve file path and transaction context for the Filestream column
using (var reader = cmd.ExecuteReader())
{
reader.Read();
id = Convert.ToInt32(reader[0]);
path = reader.GetString(1);
transactionContext = reader.GetSqlBytes(2).Buffer;
value.Timestamp = DataProvider.GetLongFromBytes((byte[])reader.GetValue(3));
}
//Write the data using SqlFileStream
using (var fs = new SqlFileStream(path, transactionContext, FileAccess.Write))
{
//default buffer size is 4096
value.Stream.CopyTo(fs);
}
}
else
{
if (value.Stream.Length > Int32.MaxValue)
{
throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
}
//read the whole data the old way
var buffer = new byte[streamSize];
value.Stream.Read(buffer, 0, streamSize);
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = buffer;
}
}
else
{
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = DBNull.Value;
}
//if Filestream is enabled but was not used due to the small file size: set it to null
if (RepositoryConfiguration.FileStreamEnabled && !useFileStream)
{
cmd.Parameters.Add(new SqlParameter("@FileStream", SqlDbType.VarBinary)).Value = DBNull.Value;
}
//If Filestream was not involved, execute the command
//and get the new id the old way from the db.
if (!useFileStream)
{
using (var reader = cmd.ExecuteReader())
{
reader.Read();
id = Convert.ToInt32(reader[0]);
value.Timestamp = DataProvider.GetLongFromBytes((byte[])reader.GetValue(1));
}
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
return(id);
}
protected internal override List<int> LoadGroupMembership(int groupId)
{
SqlProcedure cmd = null;
SqlDataReader reader = null;
var members = new List<int>();
try
{
cmd = new SqlProcedure { CommandText = "proc_Security_LoadGroupMembership" };
cmd.Parameters.Add("@GroupId", SqlDbType.Int).Value = groupId;
reader = cmd.ExecuteReader();
while (reader.Read())
{
var memberId = (int)reader["MemberId"];
members.Add(memberId);
}
return members;
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
}
//============================================================================ "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);
}
}
}
