public static object ExecuteScalar(this NpgsqlConnection conn, string sql, NpgsqlTransaction tx = null) { var cmd = tx == null ? new NpgsqlCommand(sql, conn) : new NpgsqlCommand(sql, conn, tx); using (cmd) return cmd.ExecuteScalar(); }
public PostgresUnitOfWork(string connectionString) { _connection = new NpgsqlConnection(connectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); }
public override void AfterUpdate(ParticipantPermission thing, Thing container, ParticipantPermission originalThing, NpgsqlTransaction transaction, string partition, ISecurityContext securityContext) { this.ValidateAccessRightKind(thing); }
public static async Task <bool> SchemaExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string schemaName) { bool schemaExist; using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = "SELECT count(*) FROM information_schema.schemata where schema_name = @schemaName"; var sqlParameter = new NpgsqlParameter() { ParameterName = "@schemaName", Value = schemaName }; dbCommand.Parameters.Add(sqlParameter); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { dbCommand.Transaction = transaction; } var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false); schemaExist = (long)result != 0; if (!alreadyOpened) { connection.Close(); } } return(schemaExist); }
public static Task <bool> TypeExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTypeName) { throw new NotImplementedException(); }
public static Task DropTypeIfExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, int commandTimeout, string quotedTypeName) { throw new NotImplementedException(); }
public static async Task <bool> DatabaseExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction) { bool tableExist; using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = "SELECT count(*) FROM pg_database where pg_database.datname = @databaseName"; var sqlParameter = new NpgsqlParameter() { ParameterName = "@databaseName", Value = connection.Database }; dbCommand.Parameters.Add(sqlParameter); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { dbCommand.Transaction = transaction; } var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false); tableExist = (long)result != 0; if (!alreadyOpened) { connection.Close(); } } return(tableExist); }
/// <summary> /// Remove the supplied value from the association property as indicated by the supplied property name. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) from where the requested resource will be removed. /// </param> /// <param name="propertyName"> /// The association property from where the supplied value will be removed. /// </param> /// <param name="iid"> /// The <see cref="UnitPrefix"/> id that is the source of the link table records. /// </param> /// <param name="value"> /// A value for which the link table record will be removed. /// </param> /// <returns> /// True if the link was removed. /// </returns> public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value) { return(this.UnitPrefixDao.DeleteFromCollectionProperty(transaction, partition, propertyName, iid, value)); }
/// <summary> /// Reorder the supplied value collection of the association link table indicated by the supplied property name. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) where the requested resource order will be updated. /// </param> /// <param name="propertyName"> /// The association property name that will be reordered. /// </param> /// <param name="iid"> /// The <see cref="UnitPrefix"/> id that is the source for the reordered link table record. /// </param> /// <param name="orderUpdate"> /// The order update information containing the new order key. /// </param> /// <returns> /// True if the link was created. /// </returns> public bool ReorderCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, CDP4Common.Types.OrderedItem orderUpdate) { return(this.UnitPrefixDao.ReorderCollectionProperty(transaction, partition, propertyName, iid, orderUpdate)); }
/// <summary> /// Remove the supplied value from the association property as indicated by the supplied property name. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) from where the requested resource will be removed. /// </param> /// <param name="propertyName"> /// The association property from where the supplied value will be removed. /// </param> /// <param name="iid"> /// The <see cref="RuleVerificationList"/> id that is the source of the link table records. /// </param> /// <param name="value"> /// A value for which the link table record will be removed. /// </param> /// <returns> /// True if the link was removed. /// </returns> public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value) { return(this.RuleVerificationListDao.DeleteFromCollectionProperty(transaction, partition, propertyName, iid, value)); }
/// <summary> /// Read the data from the database. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) where the requested resource is stored. /// </param> /// <param name="ids"> /// Ids to retrieve from the database. /// </param> /// <param name="isCachedDtoReadEnabledAndInstant"> /// The value indicating whether to get cached last state of Dto from revision history. /// </param> /// <returns> /// List of instances of <see cref="CDP4Common.DTO.DiagramCanvas"/>. /// </returns> public virtual IEnumerable <CDP4Common.DTO.DiagramCanvas> Read(NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids = null, bool isCachedDtoReadEnabledAndInstant = false) { using (var command = new NpgsqlCommand()) { var sqlBuilder = new System.Text.StringBuilder(); if (isCachedDtoReadEnabledAndInstant) { sqlBuilder.AppendFormat("SELECT \"Jsonb\" FROM \"{0}\".\"DiagramCanvas_Cache\"", partition); if (ids != null && ids.Any()) { sqlBuilder.Append(" WHERE \"Iid\" = ANY(:ids)"); command.Parameters.Add("ids", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = ids; } sqlBuilder.Append(";"); command.Connection = transaction.Connection; command.Transaction = transaction; command.CommandText = sqlBuilder.ToString(); // log the sql command this.LogCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var thing = this.MapJsonbToDto(reader); if (thing != null) { yield return(thing as DiagramCanvas); } } } } else { sqlBuilder.AppendFormat("SELECT * FROM \"{0}\".\"DiagramCanvas_View\"", partition); if (ids != null && ids.Any()) { sqlBuilder.Append(" WHERE \"Iid\" = ANY(:ids)"); command.Parameters.Add("ids", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = ids; } sqlBuilder.Append(";"); command.Connection = transaction.Connection; command.Transaction = transaction; command.CommandText = sqlBuilder.ToString(); // log the sql command this.LogCommand(command); using (var reader = command.ExecuteReader()) { while (reader.Read()) { yield return(this.MapToDto(reader)); } } } } }
/// <summary> /// Gets a unique revision number for this transaction by reading it from the RevisionRegistry table, or adding it there if it does not exist yet /// This ensures that there is only /// </summary> /// <param name="transaction"> /// The current transaction /// </param> /// <param name="partition"> /// The partition /// </param> /// <returns> /// The current or next available revision number /// </returns> public int GetRevisionForTransaction(NpgsqlTransaction transaction, string partition) { return(this.RevisionDao.GetRevisionForTransaction(transaction, partition)); }
/// <summary> /// Insert new values into the IterationRevisionLog table /// </summary> /// <param name="transaction"> /// The current transaction /// </param> /// <param name="partition"> /// The partition /// </param> /// <param name="iteration"> /// The iteration associated to the revision /// </param> /// <param name="fromRevision"> /// The starting revision number for the iteration. If null the current revision is used. /// </param> /// <param name="toRevision"> /// The to Revision. /// </param> public void InsertIterationRevisionLog(NpgsqlTransaction transaction, string partition, Guid iteration, int?fromRevision, int?toRevision) { this.RevisionDao.InsertIterationRevisionLog(transaction, partition, iteration, fromRevision, toRevision); }
public ContaPagarParcelaRepositorio(NpgsqlConnection context, NpgsqlTransaction transaction) { this._context = context; this._transaction = transaction; }
/// <summary> /// Updates sitemap area node record /// </summary> /// <param name="id"></param> /// <param name="areaId"></param> /// <param name="name"></param> /// <param name="label"></param> /// <param name="labelTranslations"></param> /// <param name="iconClass"></param> /// <param name="url"></param> /// <param name="type"></param> /// <param name="entityId"></param> /// <param name="weight"></param> /// <param name="accessRoles"></param> /// <param name="transaction"></param> public void Update(Guid id, Guid areaId, string name, string label, string labelTranslations, string iconClass, string url, int type, Guid?entityId, int weight, List <Guid> accessRoles, List <Guid> entityListPages = null, List <Guid> entityCreatePages = null, List <Guid> entityDetailsPages = null, List <Guid> entityManagePages = null, NpgsqlTransaction transaction = null, Guid?parentId = null) { NpgsqlCommand command = new NpgsqlCommand( "UPDATE public.app_sitemap_area_node SET " + "parent_id = @parent_id, area_id = @area_id, name = @name, label = @label, label_translations = @label_translations, " + "weight = @weight, type = @type, icon_class = @icon_class, url = @url, entity_id = @entity_id, " + "access_roles = @access_roles, entity_list_pages = @entity_list_pages, entity_create_pages = @entity_create_pages, " + "entity_details_pages = @entity_details_pages, entity_manage_pages = @entity_manage_pages " + "WHERE id = @id"); command.Parameters.Add(new NpgsqlParameter("@id", id)); command.Parameters.Add(new NpgsqlParameter("@parent_id", (object)parentId ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@area_id", areaId)); command.Parameters.Add(new NpgsqlParameter("@name", name)); command.Parameters.Add(new NpgsqlParameter("@label", (object)label ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@label_translations", (object)labelTranslations ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@icon_class", (object)iconClass ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@url", (object)url ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@entity_id", (object)entityId ?? DBNull.Value)); command.Parameters.Add(new NpgsqlParameter("@weight", weight)); command.Parameters.Add(new NpgsqlParameter("@type", type)); if (accessRoles != null && accessRoles.Count > 0) { command.Parameters.Add("@access_roles", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = accessRoles.ToArray(); } else { command.Parameters.Add("@access_roles", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray(); } if (entityListPages != null && entityListPages.Count > 0) { command.Parameters.Add("@entity_list_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityListPages.ToArray(); } else { command.Parameters.Add("@entity_list_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray(); } if (entityCreatePages != null && entityCreatePages.Count > 0) { command.Parameters.Add("@entity_create_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityCreatePages.ToArray(); } else { command.Parameters.Add("@entity_create_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray(); } if (entityDetailsPages != null && entityDetailsPages.Count > 0) { command.Parameters.Add("@entity_details_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityDetailsPages.ToArray(); } else { command.Parameters.Add("@entity_details_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray(); } if (entityManagePages != null && entityManagePages.Count > 0) { command.Parameters.Add("@entity_manage_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = entityManagePages.ToArray(); } else { command.Parameters.Add("@entity_manage_pages", NpgsqlDbType.Array | NpgsqlDbType.Uuid).Value = new List <Guid>().ToArray(); } if (transaction != null) { ExecuteSqlNonQueryCommands(transaction, command); } else { ExecuteSqlNonQueryCommands(command); } }
/// <summary> /// Update the containment order as indicated by the supplied orderedItem. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) where the requested resource order will be updated. /// </param> /// <param name="orderedItem"> /// The order update information containing the new order key. /// </param> /// <returns> /// True if the contained item was successfully reordered. /// </returns> public bool ReorderContainment(NpgsqlTransaction transaction, string partition, CDP4Common.Types.OrderedItem orderedItem) { throw new NotSupportedException(); }
public static async Task DropTriggerIfExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, int commandTimeout, string quotedTriggerName, string quotedTableName) { var triggerName = ParserName.Parse(quotedTriggerName).ToString(); using (var sqlCommand = new NpgsqlCommand(string.Format(CultureInfo.InvariantCulture, "DROP TRIGGER IF EXISTS {0} ON TABLE {1}", quotedTriggerName, quotedTableName), connection)) { bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { sqlCommand.Transaction = transaction; } await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false); if (!alreadyOpened) { connection.Close(); } } }
/// <summary> /// Execute additional logic after each get function call. /// </summary> /// <param name="resultCollection"> /// An instance collection that was retrieved from the persistence layer. /// </param> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) from which the requested resource is to be retrieved. /// </param> /// <param name="ids"> /// Ids to retrieve from the database. /// </param> /// <param name="includeReferenceData"> /// Control flag to indicate if reference library data should be retrieved extent=deep or extent=shallow. /// </param> /// <returns> /// A post filtered instance of the passed in resultCollection. /// </returns> public override IEnumerable <Thing> AfterGet(IEnumerable <Thing> resultCollection, NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids, bool includeReferenceData = false) { var filteredCollection = new List <Thing>(); foreach (var thing in resultCollection) { if (this.IsInstanceReadAllowed(transaction, thing, partition)) { filteredCollection.Add(thing); } else { Logger.Info("The person " + this.PermissionService.Credentials.Person.UserName + " does not have a read permission for " + thing.GetType().Name + "."); } } return(filteredCollection); }
public static async Task <(string DatabaseName, string EngineVersion)> GetHelloAsync(NpgsqlConnection connection, NpgsqlTransaction transaction) { string dbName = null; string dbVersion = null; using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = "SELECT version(), pg_database.datname FROM pg_database WHERE datname = @databaseName;"; var sqlParameter = new NpgsqlParameter() { ParameterName = "@databaseName", Value = connection.Database }; dbCommand.Parameters.Add(sqlParameter); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { dbCommand.Transaction = transaction; } using (var reader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false)) { if (reader.HasRows) { reader.Read(); dbVersion = reader.GetString(0); dbName = reader.GetString(1); } } if (!alreadyOpened) { connection.Close(); } } return(dbName, dbVersion); }
/// <summary> /// Get the requested data from the ORM layer. /// </summary> /// <param name="transaction"> /// The transaction object. /// </param> /// <param name="partition"> /// The database partition (schema) where the requested resource is stored. /// </param> /// <param name="ids"> /// Ids to retrieve from the database. /// </param> /// <param name="containerSecurityContext"> /// The security context of the container instance. /// </param> /// <returns> /// List of instances of <see cref="UnitPrefix"/>. /// </returns> public IEnumerable <Thing> Get(NpgsqlTransaction transaction, string partition, IEnumerable <Guid> ids, ISecurityContext containerSecurityContext) { return(this.RequestUtils.QueryParameters.ExtentDeep ? this.GetDeep(transaction, partition, ids, containerSecurityContext) : this.GetShallow(transaction, partition, ids, containerSecurityContext)); }
public static async Task <bool> TableExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTableName) { bool tableExist; var tableName = ParserName.Parse(quotedTableName).ObjectName; using (DbCommand dbCommand = connection.CreateCommand()) { dbCommand.CommandText = "SELECT count(*) FROM information_schema.tables " + "WHERE table_type = 'BASE TABLE' AND table_schema != 'pg_catalog' AND table_schema != 'information_schema' " + "AND table_name = @tableName AND table_schema = @schemaName"; NpgsqlParameter sqlParameter = new NpgsqlParameter() { ParameterName = "@tableName", Value = tableName }; dbCommand.Parameters.Add(sqlParameter); sqlParameter = new NpgsqlParameter() { ParameterName = "@schemaName", Value = GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName)) }; dbCommand.Parameters.Add(sqlParameter); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { dbCommand.Transaction = transaction; } var result = await dbCommand.ExecuteScalarAsync().ConfigureAwait(false); tableExist = (long)result != 0; if (!alreadyOpened) { connection.Close(); } } return(tableExist); }
public SqlCommand(string sql, NpgsqlConnection connection = null, NpgsqlTransaction transaction = null) { _command = new NpgsqlCommand(sql, connection ?? Database.CreateConnection(), transaction); }
public static async Task <bool> TriggerExistsAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string quotedTriggerName) { bool triggerExist; var triggerName = ParserName.Parse(quotedTriggerName).ToString(); var commandText = "Select count(*) from Information_schema.triggers " + "Where trigger_name = @triggerName and trigger_schema = @schemaName"; using (var sqlCommand = new NpgsqlCommand(commandText, connection)) { sqlCommand.Parameters.AddWithValue("@triggerName", triggerName); sqlCommand.Parameters.AddWithValue("@schemaName", NpgsqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName))); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { sqlCommand.Transaction = transaction; } var result = await sqlCommand.ExecuteScalarAsync().ConfigureAwait(false); triggerExist = (long)result != 0; if (!alreadyOpened) { connection.Close(); } } return(triggerExist); }
/// <summary> /// Get columns for table /// </summary> public static async Task <SyncTable> GetColumnsForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName) { var commandColumn = $"Select column_name, " + $"ordinal_position, " + $"data_type, " + $"character_maximum_length, " + $"numeric_precision, " + $"numeric_scale, " + $"is_nullable, " + $"is_generated, " + $"is_identity, " + $"ind.is_unique, " + $"identity_start, " + $"identity_increment " + $"from information_schema.columns " + $"where table_name = @tableName and table_schema = @schemaName "; var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString(); var tableNameString = ParserName.Parse(tableName).ToString(); var schemaNameString = "public"; if (!string.IsNullOrEmpty(schemaName)) { schemaNameString = ParserName.Parse(schemaName).ToString(); schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString; } var syncTable = new SyncTable(tableNameNormalized); using (var sqlCommand = new NpgsqlCommand(commandColumn, connection)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameString); sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { sqlCommand.Transaction = transaction; } using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false)) syncTable.Load(reader); if (!alreadyOpened) { connection.Close(); } } return(syncTable); }
/// <summary> /// Get Table /// </summary> public static async Task <SyncTable> GetTriggerAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string triggerName, string schemaName) { var command = $"Select trigger_name from Information_schema.triggers " + "Where trigger_name = @triggerName and trigger_schema = @schemaName"; var triggerNameNormalized = ParserName.Parse(triggerName).Unquoted().Normalized().ToString(); var triggerNameString = ParserName.Parse(triggerName).ToString(); var schemaNameString = "public"; if (!string.IsNullOrEmpty(schemaName)) { schemaNameString = ParserName.Parse(schemaName).ToString(); schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString; } var syncTable = new SyncTable(triggerNameNormalized, schemaNameString); using (var npgsqlCommand = new NpgsqlCommand(command, connection)) { npgsqlCommand.Parameters.AddWithValue("@triggerName", triggerNameString); npgsqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { npgsqlCommand.Transaction = transaction; } using (var reader = await npgsqlCommand.ExecuteReaderAsync().ConfigureAwait(false)) syncTable.Load(reader); if (!alreadyOpened) { connection.Close(); } } return(syncTable); }
/// <summary> /// Get Table /// </summary> public static async Task <SyncTable> GetTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName) { var command = $"SELECT * " + " FROM information_schema.tables " + " WHERE table_type = 'BASE TABLE' " + " AND table_schema != 'pg_catalog' AND table_schema != 'information_schema' " + " AND table_name=@tableName AND table_schema=@schemaName " + " ORDER BY table_schema, table_name " + " LIMIT 1"; var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString(); var tableNameString = ParserName.Parse(tableName).ToString(); var schemaNameString = "public"; if (!string.IsNullOrEmpty(schemaName)) { schemaNameString = ParserName.Parse(schemaName).ToString(); schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString; } var syncTable = new SyncTable(tableNameNormalized, schemaNameString); using (var sqlCommand = new NpgsqlCommand(command, connection)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameString); sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { sqlCommand.Transaction = transaction; } using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false)) syncTable.Load(reader); if (!alreadyOpened) { connection.Close(); } } return(syncTable); }
public static int DeleteByProcessId(NpgsqlConnection connection, Guid processId, NpgsqlTransaction transaction = null) { var pProcessId = new NpgsqlParameter("processid", NpgsqlDbType.Uuid) { Value = processId }; return(ExecuteCommand(connection, string.Format("DELETE FROM {0} WHERE \"ProcessId\" = @processid", ObjectName), transaction, pProcessId)); }
/// <summary> /// Get primary keys columns /// </summary> public static async Task <SyncTable> GetPrimaryKeysForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName) { var commandColumn = @"SELECT ccu.column_name, tc.constraint_name, c.ordinal_position FROM information_schema.table_constraints tc JOIN information_schema.constraint_column_usage AS ccu on ccu.constraint_schema = tc.constraint_schema AND ccu.constraint_name = tc.constraint_name JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name WHERE tc.constraint_type = 'PRIMARY KEY' and tc.table_name = @tableName and tc.table_schema=@schemaName;"; var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString(); var tableNameString = ParserName.Parse(tableName).ToString(); var schemaNameString = "public"; if (!string.IsNullOrEmpty(schemaName)) { schemaNameString = ParserName.Parse(schemaName).ToString(); schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "public" : schemaNameString; } var syncTable = new SyncTable(tableNameNormalized); using (var command = new NpgsqlCommand(commandColumn, connection)) { command.Parameters.AddWithValue("@tableName", tableNameString); command.Parameters.AddWithValue("@schemaName", schemaNameString); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { command.Transaction = transaction; } using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false)) syncTable.Load(reader); if (!alreadyOpened) { connection.Close(); } } return(syncTable); }
/// <summary> /// Remove the supplied value from the association property as indicated by the supplied property name. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) from where the requested resource will be removed. /// </param> /// <param name="propertyName"> /// The association property from where the supplied value will be removed. /// </param> /// <param name="iid"> /// The <see cref="MeasurementScale"/> id that is the source of the link table records. /// </param> /// <param name="value"> /// A value for which the link table record will be removed. /// </param> /// <returns> /// True if the link was removed. /// </returns> public bool DeleteFromCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value) { throw new NotSupportedException(); }
public static async Task <SyncTable> GetRelationsForTableAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, string tableName, string schemaName) { var commandRelations = @" SELECT tc.constraint_name AS ForeignKey, tc.table_schema AS SchemaName, tc.table_name AS TableName, kcu.column_name AS ColumnName, ccu.table_schema AS ReferenceSchemaName, ccu.table_name AS ReferenceTableName, ccu.column_name AS ReferenceColumnName FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema WHERE tc.constraint_type = 'FOREIGN KEY' and tc.table_name = @tableName AND tc.table_schema = @schemaName"; var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString(); var tableNameString = ParserName.Parse(tableName).ToString(); var schemaNameString = ParserName.Parse(schemaName).ToString(); // default as dbo schemaNameString = string.IsNullOrEmpty(schemaNameString) ? "dbo" : schemaNameString; var syncTable = new SyncTable(tableNameNormalized, schemaNameString); using (var sqlCommand = new NpgsqlCommand(commandRelations, connection)) { sqlCommand.Parameters.AddWithValue("@tableName", tableNameString); sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString); bool alreadyOpened = connection.State == ConnectionState.Open; if (!alreadyOpened) { await connection.OpenAsync().ConfigureAwait(false); } if (transaction != null) { sqlCommand.Transaction = transaction; } using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false)) syncTable.Load(reader); if (!alreadyOpened) { connection.Close(); } } return(syncTable); }
public static async Task<object> ExecuteScalarAsync(this NpgsqlConnection conn, string sql, NpgsqlTransaction tx = null) { var cmd = tx == null ? new NpgsqlCommand(sql, conn) : new NpgsqlCommand(sql, conn, tx); using (cmd) return await cmd.ExecuteScalarAsync(); }
/// <summary> /// Add the supplied value to the association link table indicated by the supplied property name. /// </summary> /// <param name="transaction"> /// The current transaction to the database. /// </param> /// <param name="partition"> /// The database partition (schema) where the requested resource will be stored. /// </param> /// <param name="propertyName"> /// The association property name that will be persisted. /// </param> /// <param name="iid"> /// The <see cref="ModellingThingReference"/> id that will be the source for each link table record. /// </param> /// <param name="value"> /// A value for which a link table record will be created. /// </param> /// <returns> /// True if the link was created. /// </returns> public bool AddToCollectionProperty(NpgsqlTransaction transaction, string partition, string propertyName, Guid iid, object value) { return(this.ModellingThingReferenceDao.AddToCollectionProperty(transaction, partition, propertyName, iid, value)); }