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; }
/// <summary> /// Marks orphaned file records (the ones that do not have a referencing binary record anymore) as Deleted. /// </summary> public void CleanupFilesSetDeleteFlag() { var isLocalTransaction = false; if (!TransactionScope.IsActive) { TransactionScope.Begin(); isLocalTransaction = true; } try { using (var proc = new SqlProcedure { CommandText = CleanupFileSetIsdeletedScript, CommandType = CommandType.Text }) { proc.CommandType = CommandType.Text; proc.ExecuteNonQuery(); } if (isLocalTransaction && TransactionScope.IsActive) { TransactionScope.Commit(); } } catch (Exception ex) { if (isLocalTransaction && TransactionScope.IsActive) { TransactionScope.Rollback(); } throw new DataException("Error during setting deleted flag on files.", ex); } }
private static SqlProcedure GetBlobContextProcedure(int fileId, bool clearStream, int versionId, int propertyTypeId) { // this is a helper method to aid both the sync and // async version of the GetBlobContext operation var sql = GetBlobContextDataScript; // add clear stream prefix of necessary if (clearStream) { sql = ClearStreamByFileIdScript + sql; } var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }; cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId; // security check: the given fileid must belong to the given version id and propertytypeid if (clearStream) { cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; } return(cmd); }
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 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 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(); } }
private SqlProcedure CreateInsertCommand(KeyValuePair <int, string>[] values) { var sql = new StringBuilder(); var parameters = new List <SqlParameter>(); var paramIndex = 0; foreach (var item in _values) { var value = item.Value; if (value == null) { continue; } string tableName; if (!DataProvider.Current.IsCacheableText(value)) { tableName = "TextPropertiesNText"; parameters.Add(new SqlParameter(VALUE_PARAMPREFIX + paramIndex, SqlDbType.NText) { Value = value }); } else { tableName = "TextPropertiesNVarchar"; parameters.Add(new SqlParameter(VALUE_PARAMPREFIX + paramIndex, SqlDbType.NVarChar, SqlProvider.TextAlternationSizeLimit) { Value = value }); } sql.AppendFormat(INSERTSQL, tableName, PROPERTYTYPE_PARAMPREFIX + paramIndex, VALUE_PARAMPREFIX + paramIndex).AppendLine(); parameters.Add(new SqlParameter(PROPERTYTYPE_PARAMPREFIX + paramIndex, SqlDbType.Int) { Value = item.Key }); paramIndex++; } if (sql.Length == 0) { return(null); } parameters.Add(new SqlParameter("@VersionId", SqlDbType.Int) { Value = _versionId }); var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text }; cmd.Parameters.AddRange(parameters.ToArray()); return(cmd); }
// ============================================================================ "less roundtrip methods" public virtual void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId) { using (var cmd = new SqlProcedure { CommandText = "proc_NodeAndVersion_Insert" }) { cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId; cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value; cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value; cmd.Parameters.Add("@CreatingInProgress", SqlDbType.TinyInt).Value = nodeData.CreatingInProgress; cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0; cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = 0; cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value; cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name; cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value; cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path; cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index; cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0; cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value; cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty; cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType; cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout; cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate; cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty; cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate; cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate; cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value = nodeData.CreatedById; cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate; cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById; cmd.Parameters.Add("@IsSystem", SqlDbType.TinyInt).Value = nodeData.IsSystem ? 1 : 0; cmd.Parameters.Add("@OwnerId", SqlDbType.Int).Value = nodeData.OwnerId; cmd.Parameters.Add("@SavingState", SqlDbType.Int).Value = (int)nodeData.SavingState; cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData); cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major; cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor; cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status; cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate; cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById; cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate; cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById; using (var reader = cmd.ExecuteReader()) { reader.Read(); nodeData.Id = Convert.ToInt32(reader[0]); nodeData.VersionId = Convert.ToInt32(reader[1]); nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]); nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]); lastMajorVersionId = reader.GetSafeInt32(4); lastMinorVersionId = reader.GetSafeInt32(5); nodeData.Path = reader.GetSafeString(6); } } }
/// <summary> /// Loads a cache item into memory that either contains the raw binary (if its size fits into the limit) or /// just the blob metadata pointing to the blob storage. /// </summary> /// <param name="versionId">Content version id.</param> /// <param name="propertyTypeId">Binary property type id.</param> public BinaryCacheEntity LoadBinaryCacheEntity(int versionId, int propertyTypeId) { var commandText = string.Format(LoadBinaryCacheEntityFormatScript, BlobStorage.BinaryCacheSize); using (var cmd = new SqlProcedure { CommandText = commandText }) { cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; cmd.CommandType = CommandType.Text; using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (!reader.HasRows || !reader.Read()) { return(null); } var length = reader.GetInt64(0); var binaryPropertyId = reader.GetInt32(1); var fileId = reader.GetInt32(2); var providerName = reader.GetSafeString(3); var providerTextData = reader.GetSafeString(4); byte[] rawData; if (reader.IsDBNull(5)) { rawData = null; } else { rawData = (byte[])reader.GetValue(5); } var provider = BlobStorageBase.GetProvider(providerName); var context = new BlobStorageContext(provider, providerTextData) { VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = fileId, Length = length }; if (provider == BlobStorageBase.BuiltInProvider) { context.BlobProviderData = new BuiltinBlobProviderData(); } return(new BinaryCacheEntity { Length = length, RawData = rawData, BinaryPropertyId = binaryPropertyId, FileId = fileId, Context = context }); } } }
/// <summary> /// Finalizes a chunked save operation. /// </summary> /// <param name="versionId">Content version id.</param> /// <param name="propertyTypeId">Binary property type id.</param> /// <param name="fileId">File identifier.</param> /// <param name="fullSize">Full size (stream length) of the binary value.</param> /// <param name="source">Binary data containing metadata (e.g. content type).</param> public void CommitChunk(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source = null) { // start a new transaction here if needed var isLocalTransaction = !TransactionScope.IsActive; if (isLocalTransaction) { TransactionScope.Begin(); } try { // commit the process: set the final full size and checksum using (var cmd = new SqlProcedure { CommandText = CommitChunkScript, CommandType = CommandType.Text }) { cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = fullSize; cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = DBNull.Value; cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = source != null ? source.ContentType : string.Empty; cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = source != null ? source.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)source.FileName.FileNameWithoutExtension : DBNull.Value; cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = source != null?ValidateExtension(source.FileName.Extension) : string.Empty; cmd.ExecuteNonQuery(); } } catch (Exception ex) { // rollback the transaction if it was opened locally if (isLocalTransaction && TransactionScope.IsActive) { TransactionScope.Rollback(); } throw new DataException("Error during committing binary chunk to file stream.", ex); } finally { // commit the transaction if it was opened locally if (isLocalTransaction && TransactionScope.IsActive) { TransactionScope.Commit(); } } }
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; } } } }
/// <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 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 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(); } }
//============================================================================ "less roundtrip methods" public void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId) { using (var cmd = new SqlProcedure { CommandText = INSERT_NODE_AND_VERSION_ROWS, CommandType = CommandType.Text }) { cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId; cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value; cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value; cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0; cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = nodeData.IsInherited ? 1 : 0; cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value; cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name; cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value; cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path; cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index; cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0; cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value; cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty; cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType; cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout; cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate; cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty; cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate; cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value = nodeData.NodeCreationDate; cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value = nodeData.NodeCreatedById; cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate; cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value = nodeData.NodeModifiedById; cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major; cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor; cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status; cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate; cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.CreatedById; cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate; cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById; using (var reader = cmd.ExecuteReader()) { reader.Read(); nodeData.Id = Convert.ToInt32(reader[0]); nodeData.VersionId = Convert.ToInt32(reader[1]); nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]); nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]); lastMajorVersionId = reader.GetSafeInt32(4); lastMinorVersionId = reader.GetSafeInt32(5); } } }
private static void WriteSqlFileStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null) { SqlProcedure cmd = null; try { //If we did not receive a path and transaction context, retrieve it now from the database. if (fileStreamData == null) { cmd = new SqlProcedure { CommandText = UPDATE_BINARY_PROPERTY_FILESTREAM, CommandType = CommandType.Text }; cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId; string path; byte[] transactionContext; //Set Stream column to NULL and retrieve file path and //transaction context for the Filestream column using (var reader = cmd.ExecuteReader()) { reader.Read(); path = reader.GetString(0); transactionContext = reader.GetSqlBytes(1).Buffer; } fileStreamData = new FileStreamData { Path = path, TransactionContext = transactionContext }; } stream.Seek(0, SeekOrigin.Begin); //Write data using SqlFileStream using (var fs = new SqlFileStream(fileStreamData.Path, fileStreamData.TransactionContext, FileAccess.Write)) { //default buffer size is 4096 stream.CopyTo(fs); } } finally { if (cmd != null) { cmd.Dispose(); } } }
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); }
private SqlProcedure CreateDeleteCommand(KeyValuePair <int, string>[] values) { var sql = String.Format(DELETESQL, String.Join(", ", Enumerable.Range(0, _values.Count).Select(x => PROPERTYTYPE_PARAMPREFIX + x))); var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = _versionId; for (int i = 0; i < values.Length; i++) { cmd.Parameters.Add(PROPERTYTYPE_PARAMPREFIX + i, SqlDbType.Int).Value = values[i].Key; } return(cmd); }
// ReSharper disable once SuggestBaseTypeForParameter private static SqlProcedure GetWriteChunkToSqlProcedure(BlobStorageContext context, long offset, byte[] buffer) { // This is a helper method to aid both the sync and async version of the write chunk operation. var cmd = new SqlProcedure { CommandText = UpdateStreamWriteChunkScript, CommandType = CommandType.Text }; cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = context.FileId; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = context.VersionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = context.PropertyTypeId; cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value = buffer; cmd.Parameters.Add("@Offset", SqlDbType.BigInt).Value = offset; return(cmd); }
/*============================================================================ Version Insert/Update */ //public int InsertVersionRow(NodeData nodeData) //{ // SqlProcedure cmd = null; // SqlDataReader reader = null; // int result = 0; // try // { // cmd = new SqlProcedure { CommandText = "proc_Version_Insert" }; // cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id; // cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major; // cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor; // cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status; // cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate; // cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.CreatedById; // cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate; // cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById; // reader = cmd.ExecuteReader(); // while (reader.Read()) // { // // SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @@IDENTITY // result = Convert.ToInt32(reader[0]); // nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]); // } // } // finally // { // if (reader != null && !reader.IsClosed) // reader.Close(); // cmd.Dispose(); // } // return result; //} public void UpdateVersionRow(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId) { SqlProcedure cmd = null; SqlDataReader reader = null; lastMajorVersionId = 0; lastMinorVersionId = 0; try { cmd = new SqlProcedure { CommandText = "proc_Version_Update" }; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeData.VersionId; cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id; if (nodeData.IsPropertyChanged("Version")) { cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major; cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor; cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status; } cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.VersionCreationDate; cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.VersionCreatedById; cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate; cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.VersionModifiedById; cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData); reader = cmd.ExecuteReader(); while (reader.Read()) { // SELECT [Timestamp] FROM Versions WHERE VersionId = @VersionId nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[0]); nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]); lastMajorVersionId = reader.GetSafeInt32(2); lastMinorVersionId = reader.GetSafeInt32(3); } } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } cmd.Dispose(); } }
/// <summary> /// Deletes file records that are marked as deleted from the metadata database and also from the blob storage. /// </summary> /// <returns>Whether there was at least one row that was deleted.</returns> public bool CleanupFiles() { using (var proc = new SqlProcedure { CommandText = CleanupFileScript, CommandType = CommandType.Text }) { proc.CommandType = CommandType.Text; try { var deleted = false; using (var reader = proc.ExecuteReader()) { var fileId = 0; var size = 0L; string providerName = null; string providerData = null; // We do not care about the number of deleted rows, // we only want to know if a row was deleted or not. if (reader.Read()) { deleted = true; fileId = reader.GetSafeInt32(reader.GetOrdinal("FileId")); size = reader.GetSafeInt64(reader.GetOrdinal("Size")); providerName = reader.GetSafeString(reader.GetOrdinal("BlobProvider")); providerData = reader.GetSafeString(reader.GetOrdinal("BlobProviderData")); } // delete bytes from the blob storage var provider = BlobStorageBase.GetProvider(providerName); var ctx = new BlobStorageContext(provider, providerData) { VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size }; ctx.Provider.Delete(ctx); } return(deleted); } catch (Exception ex) { throw new DataException("Error during binary cleanup.", ex); } } }
/// <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); }
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(); } } } }
internal static byte[] ReadRandom(BlobStorageContext context, long offset, int count) { var commandText = LoadBinaryFragmentScript; byte[] result; using (var cmd = new SqlProcedure { CommandText = commandText }) { cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = context.FileId; cmd.Parameters.Add("@Position", SqlDbType.BigInt).Value = offset + 1; cmd.Parameters.Add("@Count", SqlDbType.Int).Value = count; cmd.CommandType = CommandType.Text; result = (byte[])cmd.ExecuteScalar(); } return(result); }
/// <summary> /// Returns whether the FileStream feature is enabled in the blob provider or not. /// </summary> /// <returns></returns> public bool IsFilestreamEnabled() { bool fsEnabled; const string sql = "SELECT COUNT(name) FROM sys.columns WHERE Name = N'FileStream' and Object_ID = Object_ID(N'Files')"; using (var pro = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { try { fsEnabled = Convert.ToInt32(pro.ExecuteScalar()) > 0; } catch (Exception ex) { SnLog.WriteException(ex); fsEnabled = false; } } return(fsEnabled); }
// ReSharper disable once SuggestBaseTypeForParameter private static SqlProcedure GetWriteChunkToSqlProcedure(BlobStorageContext context, long offset, byte[] buffer) { // This is a helper method to aid both the sync and async version of the write chunk operation. // If Filestream is enabled but not used, we need to set it NULL // when inserting the chunk to the regular Stream column var cmdText = BlobStorage.FileStreamEnabled ? UpdateStreamWriteChunkFsScript : UpdateStreamWriteChunkScript; var cmd = new SqlProcedure { CommandText = cmdText, CommandType = CommandType.Text }; cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = context.FileId; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = context.VersionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = context.PropertyTypeId; cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value = buffer; cmd.Parameters.Add("@Offset", SqlDbType.BigInt).Value = offset; return(cmd); }
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(); } }
//protected internal override void StoreBackupStream(string backupFilePath, IndexBackup2 backup, BackupProgress progress) //{ // var fileLength = new FileInfo(backupFilePath).Length; // using (var stream = new FileStream(backupFilePath, FileMode.Open)) // { // using (var reader = new BinaryReader(stream)) // { // InitializeNewStream(backup); // progress.Type = BackupProgressType.Storing; // progress.Message = "Storing backup"; // progress.MaxValue = fileLength; // var offset = 0L; // while (offset < fileLength) // { // using (var writeCommand = CreateWriteCommand(backup)) // { // progress.Value = offset; // progress.NotifyChanged(); // var remnant = fileLength - offset; // var length = remnant < BUFFERSIZE ? Convert.ToInt32(remnant) : BUFFERSIZE; // var buffer = reader.ReadBytes(length); // writeCommand.Parameters["@Buffer"].Value = buffer; // writeCommand.Parameters["@Offset"].Value = offset; // writeCommand.Parameters["@Length"].Value = length; // writeCommand.ExecuteNonQuery(); // offset += BUFFERSIZE; // } // } // progress.Value = fileLength; // progress.NotifyChanged(); // } // } //} private SqlProcedure CreateWriteCommand(IndexBackup backup) { var sql = String.Format("UPDATE {0} SET [BackupFile].WRITE(@Buffer, @Offset, @Length) WHERE BackupNumber = @BackupNumber", backup.TableName); var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }; cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BackupNumber", SqlDbType.Int)).Value = backup.BackupNumber; cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Offset", SqlDbType.BigInt)); cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", SqlDbType.BigInt)); cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Buffer", SqlDbType.VarBinary)); return cmd; }
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 internal override IndexBackup CreateBackup(int backupNumber) { var backup = new IndexBackup { BackupNumber = backupNumber, AppDomainName = AppDomain.CurrentDomain.FriendlyName, BackupDate = DateTime.Now, ComputerName = Environment.MachineName, }; var sql = String.Format(@"INSERT INTO {0} (BackupNumber, IsActive, BackupDate, ComputerName, [AppDomain]) VALUES (@BackupNumber, 0, @BackupDate, @ComputerName, @AppDomain)", backup.TableName); using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add("@BackupNumber", SqlDbType.Int).Value = backup.BackupNumber; cmd.Parameters.Add("@BackupDate", SqlDbType.DateTime).Value = backup.BackupDate; cmd.Parameters.Add("@ComputerName", SqlDbType.NVarChar, 100).Value = backup.ComputerName; cmd.Parameters.Add("@AppDomain", SqlDbType.NVarChar, 500).Value = backup.AppDomainName; cmd.ExecuteNonQuery(); } return backup; }
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 void SetActiveBackup(IndexBackup backup, IndexBackup lastBackup) { var sql = (lastBackup == null) ? String.Format("UPDATE {0} SET IsActive = 1 WHERE BackupNumber = @ActiveBackupNumber", backup.TableName) : String.Format(@"UPDATE {0} SET IsActive = 1 WHERE BackupNumber = @ActiveBackupNumber UPDATE {1} SET IsActive = 0 WHERE BackupNumber = @InactiveBackupNumber", backup.TableName, lastBackup.TableName); using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add(new SqlParameter("@ActiveBackupNumber", SqlDbType.Int)).Value = backup.BackupNumber; if(lastBackup!=null) cmd.Parameters.Add(new SqlParameter("@InactiveBackupNumber", SqlDbType.Int)).Value = lastBackup.BackupNumber; cmd.ExecuteNonQuery(); } }
protected override void KeepOnlyLastIndexBackup() { var backup = LoadLastBackup(); if (backup == null) return; backup = new IndexBackup { BackupNumber = backup.BackupNumber - 1 }; var sql = "TRUNCATE TABLE " + backup.TableName; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) cmd.ExecuteNonQuery(); }
//====================================================== Custom database script support protected internal override IDataProcedure CreateDataProcedureInternal(string commandText) { var proc = new SqlProcedure(); proc.CommandText = commandText; return proc; }
private IDataProcedure CreateReadCommand(IndexBackup backup) { var sql = String.Format("SELECT SUBSTRING([BackupFile], @Offset, @Length) FROM {0} WHERE BackupNumber = @BackupNumber", backup.TableName); var cmd = new SqlProcedure { CommandText = sql, CommandType = System.Data.CommandType.Text }; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@BackupNumber", SqlDbType.Int)).Value = backup.BackupNumber; cmd.Parameters.Add(new SqlParameter("@Offset", SqlDbType.BigInt)); cmd.Parameters.Add(new SqlParameter("@Length", SqlDbType.BigInt)); return cmd; }
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; }
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 void DeleteStagingBinaryData(int stagingBinaryDataId) { var sql = "DELETE from StagingBinaryProperties WHERE Id = @Id"; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = stagingBinaryDataId; cmd.ExecuteNonQuery(); } }
protected internal override void CopyStagingToBinaryData(int versionId, int propertyTypeId, int stagingBinaryDataId, string checksum) { var sql = @" UPDATE BinaryProperties SET ContentType = S.ContentType, FileNameWithoutExtension = S.FileNameWithoutExtension, Extension = S.Extension, Size = S.Size, [Checksum] = @Checksum, Stream = S.Stream FROM BinaryProperties B JOIN StagingBinaryProperties S ON S.VersionId = B.VersionId AND S.PropertyTypeId = B.PropertyTypeId WHERE B.VersionId = @VersionId AND B.PropertyTypeId = @PropertyTypeId "; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add(new SqlParameter("@VersionId", SqlDbType.Int)).Value = versionId; cmd.Parameters.Add(new SqlParameter("@PropertyTypeId", SqlDbType.Int)).Value = propertyTypeId; cmd.Parameters.Add(new SqlParameter("@Checksum", SqlDbType.VarChar, 200)).Value = checksum; cmd.ExecuteNonQuery(); } }
protected internal override void SaveChunk(int stagingBinaryDataId, byte[] bytes, int offset) { var sql = "UPDATE StagingBinaryProperties SET [Stream].WRITE(@Buffer, @Offset, @Length) WHERE Id = @Id"; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", SqlDbType.Int)).Value = stagingBinaryDataId; cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Buffer", SqlDbType.VarBinary)).Value = bytes; cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Offset", SqlDbType.BigInt)).Value = offset; cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", SqlDbType.BigInt)).Value = bytes.LongLength; cmd.ExecuteNonQuery(); } }
//====================================================== Powershell provider protected internal override int InitializeStagingBinaryData(int versionId, int propertyTypeId, string fileName, long fileSize) { var sql = @" INSERT INTO StagingBinaryProperties ( VersionId, PropertyTypeId, ContentType, FileNameWithoutExtension, Extension, Size, Stream) VALUES (@VersionId, @PropertyTypeId, @ContentType, @FileNameWithoutExtension, @Extension, @Size, 0x00) SELECT @@IDENTITY"; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { var fName = Path.GetFileNameWithoutExtension(fileName); var ext = Path.GetExtension(fileName); var mime = MimeTable.GetMimeType(ext.ToLower(CultureInfo.InvariantCulture)); cmd.Parameters.Add(new SqlParameter("@VersionId", SqlDbType.Int)).Value = versionId; cmd.Parameters.Add(new SqlParameter("@PropertyTypeId", SqlDbType.Int)).Value = propertyTypeId; cmd.Parameters.Add(new SqlParameter("@ContentType", SqlDbType.VarChar, 50)).Value = mime; cmd.Parameters.Add(new SqlParameter("@FileNameWithoutExtension", SqlDbType.NVarChar, 450)).Value = fName; cmd.Parameters.Add(new SqlParameter("@Extension", SqlDbType.NVarChar, 450)).Value = ext; cmd.Parameters.Add(new SqlParameter("@Size", SqlDbType.BigInt)).Value = fileSize; var result = cmd.ExecuteScalar(); return Convert.ToInt32(result); } }
const int BUFFERSIZE = 1024 * 128; // * 512; // * 64; // * 8; protected internal override IndexBackup LoadLastBackup() { var sql = @" SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain], DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp] FROM [IndexBackup] WHERE IsActive != 0 SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain], DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp] FROM [IndexBackup2] WHERE IsActive != 0 "; IndexBackup result = null; using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { using (var reader = cmd.ExecuteReader()) { do { while (reader.Read()) result = GetBackupFromReader(reader); } while (reader.NextResult()); } } return result; }
protected internal override byte[] LoadBinaryFragment(int binaryPropertyId, long position, int count) { var commandText = @" SELECT SUBSTRING(Stream, @Position, @Count) FROM dbo.BinaryProperties WHERE BinaryPropertyId = @BinaryPropertyId "; byte[] result; using (var cmd = new SqlProcedure { CommandText = commandText }) { cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryPropertyId; cmd.Parameters.Add("@Position", SqlDbType.BigInt).Value = position + 1; cmd.Parameters.Add("@Count", SqlDbType.Int).Value = count; cmd.CommandType = CommandType.Text; result = (byte[])cmd.ExecuteScalar(); } return result; }
protected internal override IndexDocumentData LoadIndexDocumentByVersionId(int versionId) { using (var cmd = new SqlProcedure { CommandText = LOADINDEXDOCUMENTSCRIPT + "WHERE V.VersionId = @VersionId" }) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId; using (var reader = cmd.ExecuteReader()) { if (reader.Read()) return GetIndexDocumentDataFromReader(reader); return null; } } }
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(); }
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 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 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(); } }
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(); } }
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(); } }
private void InitializeNewStream(IndexBackup backup) { var sql = String.Format("UPDATE {0} SET [BackupFile] = @InitialStream WHERE BackupNumber = @BackupNumber", backup.TableName); using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@BackupNumber", SqlDbType.Int)); cmd.Parameters["@BackupNumber"].Value = backup.BackupNumber; cmd.Parameters.Add(new SqlParameter("@InitialStream", SqlDbType.VarBinary)); cmd.Parameters["@InitialStream"].Value = new byte[0]; cmd.ExecuteNonQuery(); } }
protected internal override IEnumerable<int> QueryNodesByReferenceAndType(string referenceName, int referredNodeId, int[] allowedTypeIds) { if (referenceName == null) throw new ArgumentNullException("referenceName"); if (referenceName.Length == 0) throw new ArgumentException("Argument referenceName cannot be empty.", "referenceName"); var referenceProperty = ActiveSchema.PropertyTypes[referenceName]; if (referenceProperty == null) throw new ArgumentException("PropertyType is not found: " + referenceName, "referenceName"); var referencePropertyId = referenceProperty.Id; string sql; if (allowedTypeIds == null || allowedTypeIds.Length == 0) { sql = @"SELECT V.NodeId FROM ReferenceProperties R JOIN Versions V ON R.VersionId = V.VersionId JOIN Nodes N ON V.VersionId = N.LastMinorVersionId WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId"; } else { sql = String.Format(@"SELECT N.NodeId FROM ReferenceProperties R JOIN Versions V ON R.VersionId = V.VersionId JOIN Nodes N ON V.VersionId = N.LastMinorVersionId WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId AND N.NodeTypeId IN ({0})", String.Join(", ", allowedTypeIds)); } using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text }) { cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = referencePropertyId; cmd.Parameters.Add("@ReferredNodeId", SqlDbType.Int).Value = referredNodeId; var result = new List<int>(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) result.Add(reader.GetSafeInt32(0)); return result; } } }
protected internal override 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(); } }
public override int GetLastActivityId() { using (var cmd = new SqlProcedure { CommandText = GETLASTACTIVITYIDSCRIPT, CommandType = CommandType.Text }) { var x = cmd.ExecuteScalar(); if (x == DBNull.Value) return 0; return Convert.ToInt32(x); } }
protected internal override BinaryCacheEntity LoadBinaryCacheEntity(int nodeVersionId, int propertyTypeId) { var commandText = string.Format(@" SELECT Size, CASE WHEN Size < {0} THEN Stream ELSE null END AS Stream, BinaryPropertyId FROM dbo.BinaryProperties WHERE VersionId = @VersionId AND PropertyTypeId = @PropertyTypeId ", RepositoryConfiguration.CachedBinarySize); using (var cmd = new SqlProcedure { CommandText = commandText }) { cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeVersionId; cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId; cmd.CommandType = CommandType.Text; var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult); if (reader.HasRows && reader.Read()) { long length = reader.GetInt64(0); byte[] rawData; if (reader.IsDBNull(1)) rawData = null; else rawData = (byte[])reader.GetValue(1); int binaryPropertyId = reader.GetInt32(2); reader.Close(); return new BinaryCacheEntity() { Length = length, RawData = rawData, BinaryPropertyId = binaryPropertyId }; } else { reader.Close(); return null; } } }