/// <summary> /// Add the input entities parameter /// </summary> /// <param name="command"></param> /// <returns></returns> private bool AddInputEntitiesParameter(IDbCommand command) { if (_inputEntitiesTable == null || _inputEntitiesTable.Rows.Count == 0) { return(false); } command.AddTableValuedParameter("@inputEntities", _inputEntitiesTable); return(true); }
/// <summary> /// Adds any entity clone parameters. /// </summary> /// <param name="command">The command.</param> private bool AddEntityClonesParameters(IDbCommand command) { bool haveClones = false; if (EntityClones.Count > 0) { DataTable dt; if (EntityClones.TryGetValue(CloneOption.Shallow, out dt)) { command.AddTableValuedParameter("@shallow", dt); haveClones = true; } if (EntityClones.TryGetValue(CloneOption.Deep, out dt)) { command.AddTableValuedParameter("@deep", dt); haveClones = true; } } return(haveClones); }
/// <summary> /// Adds any deletion parameters. /// </summary> /// <param name="command">The command.</param> private bool AddDeletionParameters(IDbCommand command) { bool haveDeletions = Deletions.Count != 0; if (haveDeletions) { foreach (var pair in Deletions.OrderBy(kvp => kvp.Key)) { string typeName = GetTypeName(pair.Key); command.AddTableValuedParameter(string.Format("@delete{0}", typeName), pair.Value); } } return(haveDeletions); }
/// <summary> /// Adds a parameter to a command that can accept a list of some arbitrary object type. /// </summary> /// <param name="command">The command object to update.</param> /// <param name="name">A string containing the name of the parameter.</param> /// <param name="listType">The type of data.</param> /// <param name="dataList">The data list.</param> /// <returns></returns> public static IDbDataParameter AddListParameter(this IDbCommand command, string name, TableValuedParameterType listType, IEnumerable <object> dataList) { ///// // Define data table ///// DataTable dataTable = command.CreateTableValuedParameter(listType); ///// // Fill table ///// foreach (object value in dataList) { dataTable.Rows.Add(value); } return(command.AddTableValuedParameter(name, dataTable)); }
/// <summary> /// Adds a parameter to a command that can accept a list of IDs. /// </summary> /// <param name="command">The command object to update.</param> /// <param name="name">A string containing the name of the parameter.</param> /// <param name="stringList">The string list.</param> /// <returns></returns> public static IDbDataParameter AddStringListParameter(this IDbCommand command, string name, IEnumerable <string> stringList) { ///// // Define data table ///// DataTable dataTable = command.CreateTableValuedParameter(TableValuedParameterType.NVarCharMaxListType); ///// // Fill table ///// foreach (string value in stringList) { dataTable.Rows.Add(value); } return(command.AddTableValuedParameter(name, dataTable)); }
/// <summary> /// Adds a parameter to a command that can accept a list of IDs. /// </summary> /// <param name="command"> /// The command object to update. /// </param> /// <param name="name"> /// A string containing the name of the parameter. /// </param> /// <param name="idList"> /// The list of IDs, which must be unique. /// </param> public static IDbDataParameter AddIdListParameter(this IDbCommand command, string name, IEnumerable <long> idList) { ///// // Define data table ///// DataTable dataTable = command.CreateTableValuedParameter(TableValuedParameterType.BigInt); ///// // Fill table ///// foreach (long id in idList.Distinct( )) { dataTable.Rows.Add(id); } return(command.AddTableValuedParameter(name, dataTable)); }
public HttpResponseMessage <IDictionary <long, Guid> > GetUpgradeIdsByEntityIds([FromBody] List <long> entityIds) { if (entityIds == null) { throw new WebArgumentNullException("entityIds"); } IDictionary <long, Guid> dict = new Dictionary <long, Guid>(); DataTable dt = TableValuedParameter.Create(TableValuedParameterType.BigInt); foreach (var entityId in entityIds) { dt.Rows.Add(entityId); } using (DatabaseContext context = DatabaseContext.GetContext(false)) { if (context == null) { throw new ArgumentNullException("context"); } using (IDbCommand command = context.CreateCommand()) { command.CommandText = "dbo.spGetUpgradeIdsByEntityIds"; command.CommandType = CommandType.StoredProcedure; command.AddParameter("@tenantId", DbType.Int64, EDC.ReadiNow.IO.RequestContext.TenantId); command.AddTableValuedParameter("@data", dt); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { var id = reader.GetInt64(0); var guid = reader.GetGuid(1); dict[id] = guid; } } } } return(new HttpResponseMessage <IDictionary <long, Guid> >(dict, HttpStatusCode.OK)); }
/// <summary> /// Add all database query parameters. /// </summary> private void AddAllParameters(QuerySettings settings, QueryBuild result, IDatabaseContext context, IDbDataAdapter adapter) { IDbCommand selectCommand = adapter.SelectCommand; selectCommand.AddParameter(TenantParameterName, DatabaseType.IdentifierType, RequestContext.TenantId); if (result.DataReliesOnCurrentUser) { selectCommand.AddParameter(UserParameterName, DatabaseType.IdentifierType, settings.RunAsUser); } if (result.EntityBatchDataTable != null) { selectCommand.AddTableValuedParameter(EntityBatchParameterName, result.EntityBatchDataTable); } AddQuickSearchParameter(settings, selectCommand); AddFauxRelationshipParameters(settings, selectCommand); AddRootIdFilterParameter(settings, selectCommand); if (settings.ValueList != null) { selectCommand.AddStringListParameter("@valueList", settings.ValueList); } if (settings.SupportPaging) { SetPage(settings.FirstRow, settings.FirstRow + settings.PageSize, selectCommand); } if (result.SharedParameters != null) { foreach (KeyValuePair <ParameterValue, string> parameter in result.SharedParameters) { selectCommand.AddParameter(parameter.Value, parameter.Key.Type, parameter.Key.Value); } } }
/// <summary> /// Gets the entities that should not be removed as part of an upgrade operation. /// </summary> /// <param name="context">The context.</param> /// <returns></returns> public IEnumerable <Guid> GetDoNotRemove(IProcessingContext context) { // Namely, get the entities that: // 1. used to be part of the application, // 2. which are no longer part of the application, // 3. which are still present in the tenant (i.e. the app developer removed them from the app, but didn't delete them altogether). // 4. we also need to carry over any entities marked as 'do not remove' in previous versions // Step 0: Determine what the original version of this package was Guid?packageId = OriginalPackageId; if (packageId == null) { context.WriteInfo("Tenant application does not have a package Id"); return(Enumerable.Empty <Guid>( )); } // Step 1: Get entities in the previous version LibraryAppSource appLibrarySource = new LibraryAppSource { AppVerId = packageId.Value }; IEnumerable <Guid> entitiesInPrevVersion = appLibrarySource .GetEntities(context) .Select(e => e.EntityId) .ToList( ); // Step 2: Disregard entities that are still in the application IEnumerable <Guid> entitiesInTenantApp = ((IDataSource)this) .GetEntities(context) .Select(e => e.EntityId); IEnumerable <Guid> missingEntities = entitiesInPrevVersion.Except(entitiesInTenantApp).ToList(); // Step 3: Check database to see if the entities are still present ISet <Guid> doNotRemove = new HashSet <Guid>( ); DataTable dt = TableValuedParameter.Create(TableValuedParameterType.Guid); foreach (var guid in missingEntities) { dt.Rows.Add(guid); } using (IDbCommand command = CreateCommand( )) { command.CommandText = "dbo.spGetEntityIdsByUpgradeIds"; command.CommandType = CommandType.StoredProcedure; command.AddParameter("@tenantId", DbType.Int64, TenantId); command.AddTableValuedParameter("@data", dt); using (IDataReader reader = command.ExecuteReader( )) { while (reader.Read( )) { var guid = reader.GetGuid(0); doNotRemove.Add(guid); // entities still in the tenant should be marked as 'do not remove' } } } // Step 4: Include entities marked as 'do not remove' in previous tenants var carriedOver = appLibrarySource.GetDoNotRemove(context); foreach (Guid guid in carriedOver) { doNotRemove.Add(guid); } // Entities that have been removed from the application, but still present in the tenant, // should get marked as 'do not delete' to indicate that when the application is upgraded it should not delete the entities. return(doNotRemove); }
/// <summary> /// Execute the query held in the BulkSqlQuery object for the specified entities, and capture the results in a fairly raw format. /// </summary> /// <param name="query">The query object.</param> /// <param name="entities">IDs of root-level entities to load. Must not contain duplicates.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException"> /// query /// or /// entities /// </exception> /// <exception cref="System.ArgumentException">No entities were loaded;entities</exception> public static BulkRequestResult RunQuery(BulkSqlQuery query, IEnumerable <long> entities) { if (query == null) { throw new ArgumentNullException("query"); } if (entities == null) { throw new ArgumentNullException("entities"); } var entitiesList = entities.ToList( ); if (entitiesList.Count <= 0) { throw new ArgumentException("No entities were loaded", "entities"); } var result = new BulkRequestResult(); result.BulkSqlQuery = query; ///// // HACK:TODO: 'LastLogin' is handled differently to all other fields on an entity. See UserAccountValidator ///// long lastLogonId = WellKnownAliases.CurrentTenant.LastLogon; if (query.FieldTypes.ContainsKey(lastLogonId)) { using (CacheContext cacheContext = new CacheContext( )) { cacheContext.Entities.Add(lastLogonId); } } using (DatabaseContext ctx = DatabaseContext.GetContext()) using (IDbCommand command = ctx.CreateCommand()) { // If single entity, then pass via parameter (to allow SQL to cache execution plan) if (entitiesList.Count == 1) { ctx.AddParameter(command, "@entityId", DbType.Int64, entitiesList[0]); } else { command.AddIdListParameter("@entityIds", entitiesList); } ctx.AddParameter(command, "@tenantId", DbType.Int64, RequestContext.TenantId); foreach (KeyValuePair <string, DataTable> tvp in query.TableValuedParameters) { command.AddTableValuedParameter(tvp.Key, tvp.Value); } command.CommandText = "dbo.spExecBulkRequest"; command.CommandType = CommandType.StoredProcedure; using (IDataReader reader = command.ExecuteReader()) { if (reader != null) { ReadRelationships(reader, query, result); while (reader.NextResult()) { ReadFieldValues(reader, query, result); } } } } return(result); }