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