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 CopyAndUpdateVersion(NodeData nodeData, int previousVersionId, int destinationVersionId, out int lastMajorVersionId, out int lastMinorVersionId) { SqlProcedure cmd = null; SqlDataReader reader = null; lastMajorVersionId = 0; lastMinorVersionId = 0; try { cmd = new SqlProcedure { CommandText = "proc_Version_CopyAndUpdate" }; cmd.Parameters.Add("@PreviousVersionId", SqlDbType.Int).Value = previousVersionId; cmd.Parameters.Add("@DestinationVersionId", SqlDbType.Int).Value = (destinationVersionId != 0) ? (object)destinationVersionId : DBNull.Value; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id; cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major; cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor; cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status; cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate; cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById; cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate; cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById; cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData); reader = cmd.ExecuteReader(); while (reader.Read()) { // SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @NewVersionId nodeData.VersionId = Convert.ToInt32(reader[0]); nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]); nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]); lastMajorVersionId = reader.GetSafeInt32(3); lastMinorVersionId = reader.GetSafeInt32(4); } if (reader.NextResult()) { // SELECT BinaryPropertyId, PropertyTypeId FROM BinaryProperties WHERE VersionId = @NewVersionId while (reader.Read()) { var binId = Convert.ToInt32(reader[0]); var propId = Convert.ToInt32(reader[1]); var binaryData = (BinaryDataValue)nodeData.GetDynamicRawData(propId); binaryData.Id = binId; } } } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } cmd.Dispose(); } }
//============================================================================ "less roundtrip methods" public 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 = 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.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("@ClosestSecurityNodeId", SqlDbType.Int).Value = nodeData.ClosestSecurityNodeId; 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); } } }
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 System.Data.SqlTypes.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(); } }
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 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 System.Data.SqlTypes.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); }
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("@CreatingInProgress", SqlDbType.TinyInt).Value = nodeData.CreatingInProgress ? 1 : 0; 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("@IsSystem", SqlDbType.TinyInt).Value = nodeData.IsSystem ? 1 : 0; cmd.Parameters.Add("@ClosestSecurityNodeId", SqlDbType.Int).Value = nodeData.ClosestSecurityNodeId; cmd.Parameters.Add("@SavingState", SqlDbType.Int).Value = (int)nodeData.SavingState; //cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData); 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; 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(); } }