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 void SaveReferenceProperty(int versionId, PropertyType propertyType, IEnumerable <int> value) { if (propertyType == null) { throw new ArgumentNullException("propertyType"); } // Remove/Add referredNodeIds to update the database state to the proper one SqlProcedure cmd = null; try { string referredListXml = SqlProvider.CreateIdXmlForReferencePropertyUpdate(value); cmd = new SqlProcedure { CommandText = "proc_ReferenceProperty_Update" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id; cmd.Parameters.Add("@ReferredNodeIdListXml", SqlDbType.Xml).Value = referredListXml; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } }
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(); } }
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> /// DO NOT USE DIRECTLY THIS METHOD FROM YOUR CODE. /// Updates the stream in the appropriate row of the Files table specified by the context. /// </summary> public static void UpdateStream(BlobStorageContext context, Stream stream) { var fileId = context.FileId; SqlProcedure cmd = null; try { // We have to work with an integer since SQL does not support // binary values bigger than [Int32.MaxValue]. var streamSize = Convert.ToInt32(stream.Length); cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = fileId; var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int); var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize); var offset = 0; byte[] buffer = null; stream.Seek(0, SeekOrigin.Begin); // The 'while' loop is misleading here, because we write the whole // stream at once. Bigger files should go to another blob provider. while (offset < streamSize) { // Buffer size may be less at the end os the stream than the limit var bufferSize = streamSize - offset; if (buffer == null || buffer.Length != bufferSize) { buffer = new byte[bufferSize]; } // Read bytes from the source stream.Read(buffer, 0, bufferSize); offsetParameter.Value = offset; valueParameter.Value = buffer; // Write full stream cmd.ExecuteNonQuery(); offset += bufferSize; } } finally { cmd?.Dispose(); } }
private static void WriteBinaryStream(Stream stream, int binaryPropertyId) { SqlProcedure cmd = null; try { int streamSize = Convert.ToInt32(stream.Length); cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId; SqlParameter offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int); SqlParameter valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize); int offset = 0; byte[] buffer = null; stream.Seek(0, SeekOrigin.Begin); while (offset < streamSize) { // Buffer size may be less at the end os the stream than the limit int bufferSize = streamSize - offset; //partial update is not supported on FILESTREAM columns //if(bufferSize > SqlProvider.BinaryStreamBufferLength) // bufferSize = SqlProvider.BinaryStreamBufferLength; if (buffer == null || buffer.Length != bufferSize) { buffer = new byte[bufferSize]; } // Write buffered stream segment stream.Read(buffer, 0, bufferSize); offsetParameter.Value = offset; valueParameter.Value = buffer; cmd.ExecuteNonQuery(); offset += bufferSize; } } finally { cmd.Dispose(); } }
public int InsertBinaryProperty(int versionId, int propertyTypeId, BinaryDataValue value, bool isNewNode) { if (value.Stream != null && value.Stream.Length > Int32.MaxValue) { throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue] } SqlProcedure cmd = null; int id; try { cmd = 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.VarChar, 50).Value = value.ContentType; cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)value.FileName.FileNameWithoutExtension; cmd.Parameters.Add("@Extension", SqlDbType.VarChar, 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; if (value.Stream != null && value.Stream.Length > 0) { var streamSize = Convert.ToInt32(value.Stream.Length); var buffer = new byte[streamSize]; value.Stream.Seek(0, SeekOrigin.Begin); 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; } id = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.CurrentCulture); } finally { if (cmd != null) { cmd.Dispose(); } } return(id); }
/*============================================================================ 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 a binary property value from the metadata database, making the corresponding blbo storage entry orphaned. /// </summary> /// <param name="versionId">Content version id.</param> /// <param name="propertyTypeId">Binary property type id.</param> public void DeleteBinaryProperty(int versionId, int propertyTypeId) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Delete" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; cmd.ExecuteNonQuery(); } finally { cmd?.Dispose(); } }
public void UpdateBinaryProperty(int binaryDataId, BinaryDataValue value) { if (value.Stream != null && value.Stream.Length > Int32.MaxValue) { throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue] } bool isRepositoryStream = value.Stream is RepositoryStream; SqlProcedure cmd = null; //object pointer; try { cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Update" }; cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryDataId; cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50).Value = value.ContentType; cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension; cmd.Parameters.Add("@Extension", SqlDbType.VarChar, 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 that is not loaded (other change happened) cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isRepositoryStream ? 0 : 1; //cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isLoaded ? 1 : 0; cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;; //SqlParameter pointerParameter = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16); //pointerParameter.Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); //pointer = pointerParameter.Value; } finally { cmd.Dispose(); } if (!isRepositoryStream && value.Stream != null && value.Stream.Length > 0) //if(isLoaded && stream != null && stream.Length > 0) { // Stream isloaded, exists -> write it WriteBinaryStream(value.Stream, binaryDataId); } }
public int InsertBinaryPropertyOld(int versionId, int propertyTypeId, BinaryDataValue value) { if (value.Stream != null && value.Stream.Length > Int32.MaxValue) { throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue] } SqlProcedure cmd = null; //object pointer; int id = 0; try { cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Insert" }; 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.VarChar, 50).Value = value.ContentType; cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension; cmd.Parameters.Add("@Extension", SqlDbType.VarChar, 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;; //SqlParameter pointerParameter = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16); //pointerParameter.Direction = ParameterDirection.Output; id = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.CurrentCulture); //pointer = pointerParameter.Value; } finally { cmd.Dispose(); } if (value.Stream != null && value.Stream.Length > 0) { // Stream exists -> write it WriteBinaryStream(value.Stream, id); } return(id); }
public void DeleteBinaryProperty(int versionId, PropertyType propertyType) { if (propertyType == null) { throw new ArgumentNullException("propertyType"); } SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Delete" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } }
/*============================================================================ Node Insert/Update */ public void UpdateSubTreePath(string oldPath, string newPath) { if (oldPath == null) { throw new ArgumentNullException("oldPath"); } if (newPath == null) { throw new ArgumentNullException("newPath"); } if (oldPath.Length == 0) { throw new ArgumentException("Old path cannot be empty.", "oldPath"); } if (newPath.Length == 0) { throw new ArgumentException("New path cannot be empty.", "newPath"); } SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Node_UpdateSubTreePath" }; cmd.Parameters.Add("@OldPath", SqlDbType.NVarChar, 450).Value = oldPath; cmd.Parameters.Add("@NewPath", SqlDbType.NVarChar, 450).Value = newPath; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } }
/// <summary> /// Inserts a new binary record into the metadata database containing an already exising file id, /// removing the previous record if the content is not new. /// </summary> /// <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 InsertBinaryPropertyWithFileId(BinaryDataValue value, int versionId, int propertyTypeId, bool isNewNode) { SqlProcedure cmd = null; int id; try { cmd = new SqlProcedure { CommandText = isNewNode ? InsertBinaryPropertyWithKnownFileIdScript : DeleteAndInsertBinaryPropertyWithKnownFileId, 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("@FileId", SqlDbType.Int).Value = value.FileId; id = (int)cmd.ExecuteScalar(); } finally { cmd?.Dispose(); } value.Id = id; }
protected internal override bool HasChild(int nodeId) { SqlProcedure cmd = null; int result; try { cmd = new SqlProcedure { CommandText = "proc_Node_HasChild" }; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId; result = (int)cmd.ExecuteScalar(); } finally { cmd.Dispose(); } if (result == -1) throw new ApplicationException(); return result > 0; }
internal static void AddStream(BlobStorageContext context, Stream stream) { FileStreamData fileStreamData = null; var providerData = context.BlobProviderData as BuiltinBlobProviderData; if (providerData != null) { fileStreamData = providerData.FileStreamData; } SqlProcedure cmd = null; try { // if possible, write the stream using the special Filestream technology if (BlobStorageBase.UseFileStream(stream.Length)) { WriteSqlFileStream(stream, context.FileId, fileStreamData); return; } // We have to work with an integer since SQL does not support // binary values bigger than [Int32.MaxValue]. var streamSize = Convert.ToInt32(stream.Length); cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = context.FileId; var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int); var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize); if (BlobStorage.FileStreamEnabled) { var useFileStreamParameter = cmd.Parameters.Add("@UseFileStream", SqlDbType.TinyInt); useFileStreamParameter.Value = false; } var offset = 0; byte[] buffer = null; stream.Seek(0, SeekOrigin.Begin); // The 'while' loop is misleading here, because we write the whole // stream at once. Bigger files should go to the Filestream // column anyway. while (offset < streamSize) { // Buffer size may be less at the end os the stream than the limit var bufferSize = streamSize - offset; if (buffer == null || buffer.Length != bufferSize) { buffer = new byte[bufferSize]; } // Read bytes from the source stream.Read(buffer, 0, bufferSize); offsetParameter.Value = offset; valueParameter.Value = buffer; // Write full stream cmd.ExecuteNonQuery(); offset += bufferSize; } } finally { cmd?.Dispose(); } }
//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 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 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(); } }
protected internal override int InstanceCount(int[] nodeTypeIds) { var sql = new StringBuilder("SELECT COUNT(*) FROM Nodes WHERE NodeTypeId"); if (nodeTypeIds.Length == 1) sql.Append(" = ").Append(nodeTypeIds[0]); else sql.Append(" IN (").Append(String.Join(", ", nodeTypeIds)).Append(")"); var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text }; ; try { var count = (int)cmd.ExecuteScalar(); return count; } finally { cmd.Dispose(); } }
protected internal override void ExplicateGroupMemberships() { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Security_ExplicateGroupMemberships" }; cmd.ExecuteNonQuery(); } finally { if (cmd != null) cmd.Dispose(); } }
protected internal override void ExplicateOrganizationUnitMemberships(IUser user) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Security_ExplicateOrgUnitMemberships" }; cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = user.Id; cmd.ExecuteNonQuery(); } finally { if (cmd != null) cmd.Dispose(); } }
protected internal override void SetPermission(int principalId, int nodeId, PermissionType permissionType, bool isInheritable, PermissionValue permissionValue) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Security_SetPermission" }; cmd.Parameters.Add("@PrincipalId", SqlDbType.Int).Value = principalId; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId; cmd.Parameters.Add("@PermissionTypeId", SqlDbType.Int).Value = permissionType.Id; cmd.Parameters.Add("@IsInheritable", SqlDbType.TinyInt).Value = isInheritable ? (byte)1 : (byte)0; cmd.Parameters.Add("@PermissionValue", SqlDbType.TinyInt).Value = (byte)permissionValue; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } }
protected internal override void SetPermission(SecurityEntry entry) { var sql = new StringBuilder(); if (entry.AllowBits + entry.DenyBits == 0) { sql.Append("DELETE FROM SecurityEntries WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable"); } else { sql.AppendLine("IF NOT EXISTS (SELECT SecurityEntryId FROM dbo.SecurityEntries WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable)"); sql.AppendLine(" INSERT INTO dbo.SecurityEntries (DefinedOnNodeId, PrincipalId, IsInheritable) VALUES (@NodeId, @PrincipalId, @IsInheritable)"); sql.AppendLine("UPDATE SecurityEntries SET"); for (int i = 0; i < ActiveSchema.PermissionTypes.Count; i++) { var value = (byte)0; var mask = 1<<i; if((entry.DenyBits & mask)!=0) value = (byte)2; else if((entry.AllowBits & mask)!=0) value = (byte)1; if (i > 0) sql.AppendLine(","); sql.Append(" PermissionValue").Append(i + 1).Append(" = ").Append(value); } sql.AppendLine(); sql.AppendLine("WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable"); } SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text }; cmd.Parameters.Add("@PrincipalId", SqlDbType.Int).Value = entry.PrincipalId; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = entry.DefinedOnNodeId; cmd.Parameters.Add("@IsInheritable", SqlDbType.TinyInt).Value = entry.Propagates ? (byte)1 : (byte)0; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } }
//////////////////////////////////////// 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; }
protected internal override long GetTreeSize(string path, bool includeChildren) { SqlProcedure cmd = null; long result; try { cmd = new SqlProcedure { CommandText = "proc_Node_GetTreeSize" }; cmd.Parameters.Add("@NodePath", SqlDbType.NVarChar, 450).Value = path; cmd.Parameters.Add("@IncludeChildren", SqlDbType.TinyInt).Value = includeChildren ? 1 : 0; var obj = cmd.ExecuteScalar(); result = (obj == DBNull.Value) ? 0 : (long)obj; } catch (Exception ex) { throw ex; } finally { if (cmd != null) cmd.Dispose(); } if (result == -1) throw new ApplicationException(); return result; }
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 DataOperationResult MoveNodeTree(int sourceNodeId, int targetNodeId) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Node_Move" }; cmd.Parameters.Add("@SourceNodeId", SqlDbType.Int).Value = sourceNodeId; cmd.Parameters.Add("@TargetNodeId", SqlDbType.Int).Value = targetNodeId; cmd.ExecuteNonQuery(); } catch (SqlException e) //logged //rethrow { switch (e.State) { case 1: //'Invalid operation: moving a contentList / a subtree that contains a contentList under an another contentList.' Logger.WriteException(e); return DataOperationResult.Move_TargetContainsSameName; case 2: default: throw; } } finally { cmd.Dispose(); } return 0; }
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(); } }
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; }
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 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(); } }
protected internal override DataOperationResult DeleteNodeTreePsychical(int nodeId) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_Node_DeletePhysical" }; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } return DataOperationResult.Successful; }
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(); }
/// <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); } }
protected internal override string LoadTextPropertyValue(int versionId, int propertyTypeId) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_TextProperty_LoadValue" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; var s = (string)cmd.ExecuteScalar(); return s; } finally { cmd.Dispose(); } }
private static void WriteBinaryStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null) { SqlProcedure cmd = null; try { var longStreamSize = stream.Length; var useFileStream = RepositoryConfiguration.FileStreamEnabled && longStreamSize > RepositoryConfiguration.MinimumSizeForFileStreamInBytes; //If possible, write the stream using the special Filestream technology if (useFileStream) { WriteSqlFileStream(stream, binaryPropertyId, fileStreamData); return; } //We have to work with an integer since SQL does not support //binary values bigger than [Int32.MaxValue]. var streamSize = Convert.ToInt32(stream.Length); cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId; var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int); var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize); if (RepositoryConfiguration.FileStreamEnabled) { var useFileStreamParameter = cmd.Parameters.Add("@UseFileStream", SqlDbType.TinyInt); useFileStreamParameter.Value = useFileStream; } int offset = 0; byte[] buffer = null; stream.Seek(0, SeekOrigin.Begin); //The 'while' loop is misleading here, because we write the whole //stream at once. Bigger files should go to the Filestream //column anyway. while (offset < streamSize) { // Buffer size may be less at the end os the stream than the limit int bufferSize = streamSize - offset; if (buffer == null || buffer.Length != bufferSize) { buffer = new byte[bufferSize]; } // Read bytes from the source stream.Read(buffer, 0, bufferSize); offsetParameter.Value = offset; valueParameter.Value = buffer; // Write full stream cmd.ExecuteNonQuery(); offset += bufferSize; } } finally { if (cmd != null) { cmd.Dispose(); } } }
/// <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); } }
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); } }
/// <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, }; 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) { // MS-SQL does not support stream size over [Int32.MaxValue]. if (streamLength > int.MaxValue) { throw new NotSupportedException(); } } var isRepositoryStream = value.Stream is RepositoryStream; var hasStream = isRepositoryStream || value.Stream is MemoryStream; if (!hasStream) { // do not do any database operation if the stream is not modified return; } SqlProcedure cmd = null; try { string sql; CommandType commandType; if (blobProvider == BlobStorageBase.BuiltInProvider) { commandType = CommandType.StoredProcedure; sql = "proc_BinaryProperty_Update"; } else { commandType = CommandType.Text; sql = 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; var 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, BlobProviderData = new BuiltinBlobProviderData() }; BuiltInBlobProvider.UpdateStream(ctx, value.Stream); } }
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); }
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 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(); } }
public void SaveTextProperty(int versionId, PropertyType propertyType, bool isLoaded, string value) { //if (propertyType.Name == "TextExtract" && !isLoaded) // return; if (propertyType == null) { throw new ArgumentNullException("propertyType"); } if (!isLoaded) { throw new ApplicationException(); // There is no other data that could be 'IsModified'... } SqlProcedure cmd = null; // Delete existing values (otherwise have to check which table used and then switch between // the Insert or Update of the specific table. This way only insert is necessary.) try { cmd = new SqlProcedure { CommandText = "proc_TextProperty_Delete" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } if (value == null) { return; } if (value.Length > SqlProvider.TextAlternationSizeLimit) { // NText try { cmd = new SqlProcedure { CommandText = "proc_TextProperty_InsertNText" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id; cmd.Parameters.Add("@Value", SqlDbType.NText).Value = value; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } else { // NVarchar try { cmd = new SqlProcedure { CommandText = "proc_TextProperty_InsertNVarchar" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id; cmd.Parameters.Add("@Value", SqlDbType.NVarChar, SqlProvider.TextAlternationSizeLimit).Value = value == null ? (object)DBNull.Value : (object)value;; cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } }
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(); } }
protected override bool NodeExistsInDatabase(string path) { var cmd = new SqlProcedure { CommandText = "SELECT COUNT(*) FROM Nodes WHERE Path = @Path", CommandType = CommandType.Text }; cmd.Parameters.Add("@Path", SqlDbType.NVarChar, PathMaxLength).Value = path; try { var count = (int)cmd.ExecuteScalar(); return count > 0; } finally { if (cmd != null) cmd.Dispose(); } }
public override string GetNameOfLastNodeWithNameBase(int parentId, string namebase, string extension) { var cmd = new SqlProcedure { CommandText = "SELECT TOP 1 Name FROM Nodes WHERE ParentNodeId=@ParentId AND Name LIKE @Name+'(%)' + @Extension ORDER BY LEN(Name) DESC, Name DESC", CommandType = CommandType.Text }; cmd.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId; cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = namebase; cmd.Parameters.Add("@Extension", SqlDbType.NVarChar).Value = extension; try { var lastName = (string)cmd.ExecuteScalar(); return lastName; } finally { if (cmd != null) cmd.Dispose(); } }
//====================================================== protected internal override void PersistUploadToken(UploadToken value) { SqlProcedure cmd = null; try { cmd = new SqlProcedure { CommandText = "proc_ApplicationMessaging_PersistUploadToken" }; cmd.Parameters.Add("@Token", SqlDbType.UniqueIdentifier).Value = value.UploadGuid; cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = value.UserId; cmd.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = DateTime.Now; cmd.ExecuteNonQuery(); } finally { 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(); } }
protected internal override int GetUserIdByUploadGuid(Guid uploadGuid) { SqlProcedure cmd = null; int result; try { cmd = new SqlProcedure { CommandText = "proc_ApplicationMessaging_GetUserIdByUploadGuid" }; cmd.Parameters.Add("@Token", SqlDbType.UniqueIdentifier).Value = uploadGuid; result = (int)cmd.ExecuteScalar(); } finally { cmd.Dispose(); } return result; }
private static void WriteBinaryStream(Stream stream, int binaryPropertyId) { SqlProcedure cmd = null; try { int streamSize = Convert.ToInt32(stream.Length); cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId; SqlParameter offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int); SqlParameter valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize); int offset = 0; byte[] buffer = null; stream.Seek(0, SeekOrigin.Begin); while (offset < streamSize) { // Buffer size may be less at the end os the stream than the limit int bufferSize = streamSize - offset; //partial update is not supported on FILESTREAM columns //if(bufferSize > SqlProvider.BinaryStreamBufferLength) // bufferSize = SqlProvider.BinaryStreamBufferLength; if (buffer == null || buffer.Length != bufferSize) buffer = new byte[bufferSize]; // Write buffered stream segment stream.Read(buffer, 0, bufferSize); offsetParameter.Value = offset; valueParameter.Value = buffer; cmd.ExecuteNonQuery(); offset += bufferSize; } } finally { cmd.Dispose(); } }