public void SaveReferenceProperty(int versionId, PropertyType propertyType, IEnumerable <int> value) { if (propertyType == null) { throw new ArgumentNullException("propertyType"); } for (short tryAgain = 3; tryAgain > 0; tryAgain--) { // Optimistic approach: try to save the value as is, without checking and compensate if it fails try { // Create XML var referredListXml = SqlProvider.CreateIdXmlForReferencePropertyUpdate(value); // Execute SQL using (var 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(); // Success, don't try again tryAgain = 0; } } catch (SqlException exc) { // This was the last try and it failed, throw if (tryAgain == 1) { throw; } // The value contains a node ID which no longer exists in the database, let's compensate for that if (exc.Message.Contains("The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_ReferenceProperties_Nodes\".")) { // Get node heads for the IDs var heads = DataProvider.Current.LoadNodeHeads(value); // Select the IDs of the existing node heads value = heads.Where(h => h != null).Select(h => h.Id); } else { // If the error is something else, just throw it up throw; } } } }
private void ExecuteScript(string script, int versionId, int page, IEnumerable <SqlParameter> @params) { using (var cmd = new SqlProcedure { CommandText = script }) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@Page", SqlDbType.Int).Value = page; cmd.Parameters.AddRange(@params.ToArray()); cmd.ExecuteNonQuery(); } }
private void ExecuteScripts(List <StringBuilder> scripts) { using (var traceOperation = Logger.TraceOperation("Execute storage schema scripts.")) { // Ensure transaction encapsulation bool isLocalTransaction = !TransactionScope.IsActive; if (isLocalTransaction) { TransactionScope.Begin(); } try { if (_slotsToReset.Count > 0) { StringBuilder sb = new StringBuilder(); scripts.Insert(0, sb); WriteSlotsToResetScripts(sb, _slotsToReset); } foreach (StringBuilder script in scripts) { using (var proc = new SqlProcedure { CommandText = script.ToString(), CommandType = CommandType.Text }) { proc.ExecuteNonQuery(); } } if (isLocalTransaction) { TransactionScope.Commit(); } traceOperation.IsSuccessful = true; } finally { if (isLocalTransaction && TransactionScope.IsActive) { TransactionScope.Rollback(); } } } }
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(); } }
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(); } } }
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 ContentRepository.Storage.Data.SqlFileStream; 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); } }
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(); } } }