public bool ValidarSeNaoPartidoExiste(string nome, string sigla) { var partidoNaoExiste = false; string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "select COUNT(1) as total_partidos from Partido where Nome = @paramNome and Sigla = @paramSigla"; comando.AddParameter("paramNome", nome); comando.AddParameter("paramSigla", sigla); connection.Open(); IDataReader reader = comando.ExecuteReader(); if (reader.Read()) { var totalPartidos = Convert.ToInt32(reader["total_partidos"]); if ((totalPartidos == 0)) { partidoNaoExiste = true; } } connection.Close(); } return(partidoNaoExiste); }
private void SetTitleSlug(IDbConnection conn, IDbTransaction tran) { using (IDbCommand getSeriesCmd = conn.CreateCommand()) { getSeriesCmd.Transaction = tran; getSeriesCmd.CommandText = @"SELECT Id, Title, Year, TmdbId FROM Movies"; using (IDataReader seriesReader = getSeriesCmd.ExecuteReader()) { while (seriesReader.Read()) { var id = seriesReader.GetInt32(0); var title = seriesReader.GetString(1); var year = seriesReader.GetInt32(2); var tmdbId = seriesReader.GetInt32(3); var titleSlug = Parser.Parser.ToUrlSlug(title + "-" + tmdbId); using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE Movies SET TitleSlug = ? WHERE Id = ?"; updateCmd.AddParameter(titleSlug); updateCmd.AddParameter(id); updateCmd.ExecuteNonQuery(); } } } } }
private void Insert(PlaceholderData placeholder) { try { using (IDbConnection connection = this.connectionPool.GetConnection()) using (IDbCommand command = connection.CreateCommand()) { command.CommandText = "INSERT OR REPLACE INTO Placeholder (path, pathType, sha) VALUES (@path, @pathType, @sha);"; command.AddParameter("@path", DbType.String, placeholder.Path); command.AddParameter("@pathType", DbType.Int32, (int)placeholder.PathType); if (placeholder.Sha == null) { command.AddParameter("@sha", DbType.String, DBNull.Value); } else { command.AddParameter("@sha", DbType.String, placeholder.Sha); } lock (this.writerLock) { command.ExecuteNonQuery(); } } } catch (Exception ex) { throw new GVFSDatabaseException($"{nameof(PlaceholderTable)}.{nameof(this.Insert)}({placeholder.Path}, {placeholder.PathType}, {placeholder.Sha}) Exception", ex); } }
public bool AdicionarCargo(Cargo cargo) { bool podeCadastrar = PodeCadastrar(cargo); bool verificarNome = VerificarNomeNuloOuVazio(cargo); if (podeCadastrar && !Eleicao.Iniciou) { if (verificarNome) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "INSERT INTO Cargo (Nome, Situacao) values(@paramNome, @paramSituacao)"; comando.AddParameter("@paramNome", cargo.Nome); comando.AddParameter("@paramSituacao", cargo.Situacao); connection.Open(); comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } } return(true); } else { return(false); } }
private void ConvertProfile(IDbConnection conn, IDbTransaction tran) { var profiles = GetProfiles(conn, tran); foreach (var profile in profiles) { if (profile.Items.Any(p => p.Quality == 0)) { continue; } profile.Items.Insert(0, new ProfileItem71 { Quality = 0, Allowed = false }); var itemsJson = profile.Items.ToJson(); using (IDbCommand updateProfileCmd = conn.CreateCommand()) { updateProfileCmd.Transaction = tran; updateProfileCmd.CommandText = "UPDATE Profiles SET Items = ? WHERE Id = ?"; updateProfileCmd.AddParameter(itemsJson); updateProfileCmd.AddParameter(profile.Id); updateProfileCmd.ExecuteNonQuery(); } } }
public Client FindWithId(string id) { EnsureEnlisted(); Client client = null; short firmId = 1; int clientId = int.Parse(id); using (IDbCommand dbCommand = UnitOfWork.Connection.CreateCommand()) { dbCommand.CommandText = @" SELECT * FROM A_Clients WHERE FirmID = @FirmID AND ClientID = @ClientID "; dbCommand.AddParameter("@FirmID", firmId); dbCommand.AddParameter("@ClientID", clientId); using (IDataReader dataReader = dbCommand.ExecuteReader()) { if (dataReader.Read()) { // MapClient(dataReader, client); } } } return(client); }
public virtual void InsertCommand(IDbCommand command, object entity) { if (command == null) throw new ArgumentNullException("command"); if (entity == null) throw new ArgumentNullException("entity"); var columns = ""; var values = ""; foreach (var key in _keys) { var value = key.GetValue(entity); if (value == null) continue; columns += string.Format("{0}, ", key.ColumnName); values += string.Format("@{0}, ", key.PropertyName); command.AddParameter(key.PropertyName, value); } foreach (var prop in _values) { var value = prop.GetValue(entity); columns += string.Format("{0}, ", prop.ColumnName); values += string.Format("@{0}, ", prop.PropertyName); command.AddParameter(prop.PropertyName, value ?? DBNull.Value); } if (command.Parameters.Count == 0) throw new DataException("No values were added to the query for " + entity); command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", TableName, columns.Remove(columns.Length - 2, 2), values.Remove(values.Length - 2, 2)); }
private void ConvertQualityTitle(IDbConnection conn, IDbTransaction tran) { using (IDbCommand namingConfigCmd = conn.CreateCommand()) { namingConfigCmd.Transaction = tran; namingConfigCmd.CommandText = @"SELECT StandardEpisodeFormat, DailyEpisodeFormat, AnimeEpisodeFormat FROM NamingConfig LIMIT 1"; using (IDataReader configReader = namingConfigCmd.ExecuteReader()) { while (configReader.Read()) { var currentStandard = configReader.GetString(0); var currentDaily = configReader.GetString(1); var currentAnime = configReader.GetString(2); var newStandard = GetNewFormat(currentStandard); var newDaily = GetNewFormat(currentDaily); var newAnime = GetNewFormat(currentAnime); using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE NamingConfig SET StandardEpisodeFormat = ?, DailyEpisodeFormat = ?, AnimeEpisodeFormat = ?"; updateCmd.AddParameter(newStandard); updateCmd.AddParameter(newDaily); updateCmd.AddParameter(newAnime); updateCmd.ExecuteNonQuery(); } } } } }
/// <summary> /// Gets the upgrade ID of an entity. /// </summary> /// <param name="entityId">The Int64 ID of an entity.</param> public Guid GetUpgradeId(long entityId) { // TODO: This is a low use method, however consider offering some form of cache. if (entityId <= 0) { return(Guid.Empty); } const string sql = @"select UpgradeId from Entity where Id = @id and TenantId = @tenantId"; using (IDatabaseContext ctx = DatabaseProvider.GetContext()) { using (IDbCommand command = ctx.CreateCommand(sql)) { command.AddParameter("@tenantId", DbType.Int64, RequestContext.TenantId); command.AddParameter("@id", DbType.Int64, entityId); object oRes = command.ExecuteScalar(); if (oRes == null) { return(Guid.Empty); } return((Guid)oRes); } } }
public int AtualizarSituacao(Cargo t) { int linhasAfetadas = 0; if (!Eleicao.EleicoesIniciadas) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "UPDATE Cargo set situacao=@paramSituacao where idCargo = @paramIdCargo"; comando.AddParameter("paramSituacao", t.Situacao); comando.AddParameter("paramIdCargo", t.IdCargo); connection.Open(); linhasAfetadas = comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } return(linhasAfetadas); } else { return(linhasAfetadas); } }
public void Up(IDatabase db) { db.CreateTable(Tables[0].Name) .WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity() .WithNotNullableColumn(Tables[0].Columns[1], DbType.String) .WithNotNullableColumn(Tables[0].Columns[2], DbType.AnsiString); db.Execute(context => { bool isCeProvider = db.Context.ProviderMetadata.Name == ProviderNames.SqlServerCe35 || db.Context.ProviderMetadata.Name == ProviderNames.SqlServerCe4; IDbCommand command = context.Connection.CreateCommand(); command.Transaction = context.Transaction; IDataParameter text = command.AddParameter("@text", DbType.String, Tables[0].Value(0, 1)); if (isCeProvider) { SetSqlDbTypeToNText(text); } IDataParameter ansiText = command.AddParameter("@ansiText", isCeProvider ? DbType.String : DbType.AnsiString, Tables[0].Value(0, 2)); if (isCeProvider) { SetSqlDbTypeToNText(ansiText); } command.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"", ""{2}"") VALUES ({3}, {4})", Tables[0].Name, Tables[0].Columns[1], Tables[0].Columns[2], context.ProviderMetadata.GetParameterSpecifier(text), context.ProviderMetadata.GetParameterSpecifier(ansiText)); context.CommandExecutor.ExecuteNonQuery(command); }); }
public int Atualizar(Cargo t) { int linhasAfetadas = 0; if (!Eleicao.EleicoesIniciadas) { Cargo cargo = BuscarPorNome(t.Nome); if ((BuscarPorNome(t.Nome) != null && t.IdCargo != cargo.IdCargo) || !Validar(t)) { return(0); } string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "UPDATE Cargo set nome=@paramNome,situacao=@paramSituacao where idCargo = @paramIdCargo"; comando.AddParameter("paramNome", t.Nome); comando.AddParameter("paramSituacao", t.Situacao); comando.AddParameter("paramIdCargo", t.IdCargo); connection.Open(); linhasAfetadas = comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } return(linhasAfetadas); } else { return(linhasAfetadas); } }
private void UpdateSortTitles(IDbConnection conn, IDbTransaction tran) { using (IDbCommand getSeriesCmd = conn.CreateCommand()) { getSeriesCmd.Transaction = tran; getSeriesCmd.CommandText = @"SELECT Id, TvdbId, Title FROM Series"; using (IDataReader seriesReader = getSeriesCmd.ExecuteReader()) { while (seriesReader.Read()) { var id = seriesReader.GetInt32(0); var tvdbId = seriesReader.GetInt32(1); var title = seriesReader.GetString(2); var sortTitle = SeriesTitleNormalizer.Normalize(title, tvdbId); using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE Series SET SortTitle = ? WHERE Id = ?"; updateCmd.AddParameter(sortTitle); updateCmd.AddParameter(id); updateCmd.ExecuteNonQuery(); } } } } }
public bool Editar(Candidato c) { if (PodeCadastrar(c) && !Eleicao.Iniciou) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "UPDATE Candidato SET NomeCompleto = @paramNomeCompleto,NomePopular = @paramNomePopular,DataNascimento = @paramDataNascimento,RegistroTRE = @paramRegistroTRE,IDPartido = @paramIDPartido,Foto = @paramFoto, Numero = @paramNumero,IDCargo = @paramIDCargo, Exibe = @paramExibe WHERE IDCandidato = @paramIDCandidato"; comando.AddParameter("paramIDCandidato", c.IDCandidato); comando.AddParameter("paramNomeCompleto", c.NomeCompleto); comando.AddParameter("paramNomePopular", c.NomePopular); comando.AddParameter("paramDataNascimento", c.DataNascimento); comando.AddParameter("paramRegistroTRE", c.RegistroTRE); comando.AddParameter("paramIDPartido", c.IDPartido); comando.AddParameter("paramFoto", c.Foto); comando.AddParameter("paramNumero", c.Numero); comando.AddParameter("paramIDCargo", c.IDCargo); comando.AddParameter("paramExibe", c.Exibe); connection.Open(); comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } return(true); } else { return(false); } }
public virtual bool DeleteItem(SyncFoundation.Core.Interfaces.ISyncableItemInfo itemInfo) { long rowId = GetRowIdFromItemInfo(itemInfo); if (rowId != -1) { try { IDbCommand command = adapter.Connection.CreateCommand(); command.CommandText = String.Format("DELETE FROM {0} WHERE RowID=@RowID", DbTable); command.AddParameter("@RowID", rowId); command.ExecuteNonQuery(); return(true); } catch (SqliteExecutionException e) { if (e.SqliteErrorCode == 19) // Constraint failed { // Update Modified with incremented TickCount IDbCommand command = Adapter.Connection.CreateCommand(); command.CommandText = String.Format("UPDATE {0} SET ModifiedReplica=0, ModifiedTickCount=@ModifiedTick WHERE RowID=@RowID", DbTable); command.AddParameter("@RowID", rowId); command.AddParameter("@ModifiedTick", Adapter.IncrementLocalRepilcaTickCount()); command.ExecuteNonQuery(); return(false); } throw; } } return(true); }
private void SetSortTitles(IDbConnection conn, IDbTransaction tran) { using (IDbCommand getSeriesCmd = conn.CreateCommand()) { getSeriesCmd.Transaction = tran; getSeriesCmd.CommandText = @"SELECT Id, RelativePath FROM MovieFiles"; using (IDataReader seriesReader = getSeriesCmd.ExecuteReader()) { while (seriesReader.Read()) { var id = seriesReader.GetInt32(0); var relativePath = seriesReader.GetString(1); var result = Parser.Parser.ParseMovieTitle(relativePath, false); var edition = ""; if (result != null) { edition = Parser.Parser.ParseMovieTitle(relativePath, false).Edition; } using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE MovieFiles SET Edition = ? WHERE Id = ?"; updateCmd.AddParameter(edition); updateCmd.AddParameter(id); updateCmd.ExecuteNonQuery(); } } } } }
public bool ContainsWithId(string id) { EnsureEnlisted(); bool contains = false; short firmId = 1; int clientId = int.Parse(id); using (IDbCommand dbCommand = UnitOfWork.Connection.CreateCommand()) { dbCommand.CommandText = @" SELECT 0 FROM A_Clients WHERE FirmID = @FirmID AND ClientID = @ClientID "; dbCommand.AddParameter("@FirmID", firmId); dbCommand.AddParameter("@ClientID", clientId); using (IDataReader dataReader = dbCommand.ExecuteReader()) { if (dataReader.Read()) { contains = true; } } } return(contains); }
private void UpdateRelativePaths(IDbConnection conn, IDbTransaction tran) { using (IDbCommand getSeriesCmd = conn.CreateCommand()) { getSeriesCmd.Transaction = tran; getSeriesCmd.CommandText = @"SELECT Id, Path FROM Series"; using (IDataReader seriesReader = getSeriesCmd.ExecuteReader()) { while (seriesReader.Read()) { var seriesId = seriesReader.GetInt32(0); var seriesPath = seriesReader.GetString(1) + Path.DirectorySeparatorChar; using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "UPDATE EpisodeFiles SET RelativePath = REPLACE(Path, ?, '') WHERE SeriesId = ?"; updateCmd.AddParameter(seriesPath); updateCmd.AddParameter(seriesId); updateCmd.ExecuteNonQuery(); } } } } }
private void ConvertQualities(IDbConnection conn, IDbTransaction tran) { // Convert QualitySizes to a more generic QualityDefinitions table. using (IDbCommand qualitySizeCmd = conn.CreateCommand()) { qualitySizeCmd.Transaction = tran; qualitySizeCmd.CommandText = @"SELECT QualityId, MinSize, MaxSize FROM QualitySizes"; using (IDataReader qualitySizeReader = qualitySizeCmd.ExecuteReader()) { while (qualitySizeReader.Read()) { var qualityId = qualitySizeReader.GetInt32(0); var minSize = qualitySizeReader.GetInt32(1); var maxSize = qualitySizeReader.GetInt32(2); var defaultConfig = Quality.DefaultQualityDefinitions.Single(p => (int)p.Quality == qualityId); using (IDbCommand updateCmd = conn.CreateCommand()) { updateCmd.Transaction = tran; updateCmd.CommandText = "INSERT INTO QualityDefinitions (Quality, Title, Weight, MinSize, MaxSize) VALUES (?, ?, ?, ?, ?)"; updateCmd.AddParameter(qualityId); updateCmd.AddParameter(defaultConfig.Title); updateCmd.AddParameter(defaultConfig.Weight); updateCmd.AddParameter(minSize); updateCmd.AddParameter(maxSize); updateCmd.ExecuteNonQuery(); } } } } }
public bool JaExisteNoBanco(Partido partido) { int contador = 0; string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "SELECT count(1) as contador FROM Partido WHERE Nome = @paramNome or Sigla = @paramSigla"; comando.AddParameter("paramNome", partido.Nome); comando.AddParameter("paramSigla", partido.Sigla); connection.Open(); IDataReader reader = comando.ExecuteReader(); if (reader.Read()) { contador = Convert.ToInt32(reader["contador"]); } connection.Close(); } if (contador == 0) { return(false); } else { return(true); } }
public Eleitor BuscarPorRGouCPF(Eleitor eleitor) { Eleitor eleitorEncontrado; string cpf = eleitor.CPF; string rg = eleitor.RG; string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "SELECT IDEleitor, Nome, TituloEleitoral, RG, CPF, DataNascimento, ZonaEleitoral, Secao, Situacao, Votou " + "FROM Eleitor WHERE CPF=@paramCPF or RG=@paramRG"; comando.AddParameter("paramCPF", cpf); comando.AddParameter("paramRG", rg); connection.Open(); IDataReader reader = comando.ExecuteReader(); eleitorEncontrado = reader.Read() ? Parse(reader) : null; connection.Close(); } return(eleitorEncontrado); }
/// <summary> /// Write an event message to Microsoft SQL Server. /// </summary> /// <param name="message">EventMessage</param> public virtual void Write(IEventMessage message) { if (_connection.State == ConnectionState.Closed) { _connection.Open(); } using (IDbCommand command = _connection.CreateCommand()) { command.Connection = _connection; #pragma warning disable CA2100 // Cannot use parameter for table name. Tablename is already checked in the CreateTable method. command.CommandText = "INSERT INTO [" + _tableName + "]([EventType],[TimeStamp],[Source],[Content],[Application]) VALUES (@EventType,@TimeStamp,@Source,@Content,@Application)"; #pragma warning disable CA2100 // Cannot use parameter for table name. Tablename is already checked in the CreateTable method. command.AddParameter("EventType", (short)message.Level); command.AddParameter("TimeStamp", message.TimeStamp); command.AddParameter("Source", message.Source, 255); command.AddParameter("Content", message.Content); command.AddParameter("Application", _application, 255); if (command.ExecuteNonQuery() != 1) { throw new Exception("BlackBox.Write : Error writing to black box data store table"); } } if (!_keepConnectionOpen) { _connection.Close(); } }
static void Main(string[] args) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "UPDATE Cargo SET Nome = @paramNome WHERE IDCargo = @paramIdCargo"; comando.AddParameter("paramNome", "Prefeito"); comando.AddParameter("paramIdCargo", 1); connection.Open(); int linhasAfetadas = comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } Console.Read(); }
public int Inserir(Partido t) { int linhasAfetadas = 0; if (!Eleicao.EleicoesIniciadas) { if (BuscarPorSiglaENome(t) == null) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.CommandText = "INSERT into Partido(nome, slogan, sigla) values(@paramNome,@paramSlogan,@paramSigla)"; comando.AddParameter("paramNome", t.Nome); comando.AddParameter("paramSlogan", t.Slogan); comando.AddParameter("paramSigla", t.Sigla); connection.Open(); linhasAfetadas = comando.ExecuteNonQuery(); transacao.Complete(); connection.Close(); } } return(linhasAfetadas); } else { return(linhasAfetadas); } }
private void UpdateSeries(IDbConnection conn, IDbTransaction tran, IEnumerable <int> profileIds, int tagId) { using (IDbCommand getSeriesCmd = conn.CreateCommand()) { getSeriesCmd.Transaction = tran; getSeriesCmd.CommandText = "SELECT Id, Tags FROM Series WHERE ProfileId IN (?)"; getSeriesCmd.AddParameter(string.Join(",", profileIds)); using (IDataReader seriesReader = getSeriesCmd.ExecuteReader()) { while (seriesReader.Read()) { var id = seriesReader.GetInt32(0); var tagString = seriesReader.GetString(1); var tags = Json.Deserialize <List <int> >(tagString); tags.Add(tagId); using (IDbCommand updateSeriesCmd = conn.CreateCommand()) { updateSeriesCmd.Transaction = tran; updateSeriesCmd.CommandText = "UPDATE Series SET Tags = ? WHERE Id = ?"; updateSeriesCmd.AddParameter(tags.ToJson()); updateSeriesCmd.AddParameter(id); updateSeriesCmd.ExecuteNonQuery(); } } } getSeriesCmd.ExecuteNonQuery(); } }
public void AtualizarPorId(int id, Candidato candidato) { string connectionString = ConfigurationManager.ConnectionStrings["URNA"].ConnectionString; using (TransactionScope transacao = new TransactionScope()) using (IDbConnection connection = new SqlConnection(connectionString)) { IDbCommand comando = connection.CreateCommand(); comando.AddParameter("@paramIDCandidato", id); comando.AddParameter("@paramNomeCompleto", candidato.NomeCompleto); comando.AddParameter("@paramNomePopular", candidato.NomePopular); comando.AddParameter("@paramDataNascimento", candidato.DataNascimento); comando.AddParameter("@paramRegistroTRE", candidato.RegistroTRE); comando.AddParameter("@paramIDPartido", candidato.IDPartido); comando.AddParameter("@paramFoto", candidato.Foto); comando.AddParameter("@paramNumero", candidato.Numero); comando.AddParameter("@paramIDCargo", candidato.IDCargo); comando.AddParameter("@paramExibe", candidato.Exibe); comando.CommandText = "UPDATE Candidato SET NomeCompleto=@paramNomeCompleto, NomePopular=@paramNomePopular, DataNascimento=@paramDataNascimento, RegistroTRE=@paramRegistroTRE, IDPartido=@paramIDPartido, Foto=@paramFoto, Numero=@paramNumero, IDCargo=@paramIDCargo, Exibe=@paramExibe " + "WHERE IDCandidato=@paramIDCandidato"; connection.Open(); comando.ExecuteNonQuery(); transacao.Complete(); } }
public List <IPlaceholderData> RemoveAllEntriesForFolder(string path) { const string fromWhereClause = "FROM Placeholder WHERE path = @path OR path LIKE @pathWithDirectorySeparator;"; // Normalize the path to match what will be in the database path = GVFSDatabase.NormalizePath(path); try { using (IDbConnection connection = this.connectionPool.GetConnection()) using (IDbCommand command = connection.CreateCommand()) { List <IPlaceholderData> removedPlaceholders = new List <IPlaceholderData>(); command.CommandText = $"SELECT path, pathType, sha {fromWhereClause}"; command.AddParameter("@path", DbType.String, $"{path}"); command.AddParameter("@pathWithDirectorySeparator", DbType.String, $"{path + Path.DirectorySeparatorChar}%"); ReadPlaceholders(command, data => removedPlaceholders.Add(data)); command.CommandText = $"DELETE {fromWhereClause}"; lock (this.writerLock) { command.ExecuteNonQuery(); } return(removedPlaceholders); } } catch (Exception ex) { throw new GVFSDatabaseException($"{nameof(PlaceholderTable)}.{nameof(this.RemoveAllEntriesForFolder)}({path}) Exception", ex); } }
private void InsertDefaultLanguages(IDbConnection conn, IDbTransaction tran) { var profiles = GetLanguageProfiles(conn, tran); var languageConverter = new EmbeddedDocumentConverter(new LanguageIntConverter()); foreach (var profile in profiles.OrderBy(p => p.Id)) { using (IDbCommand insertNewLanguageProfileCmd = conn.CreateCommand()) { var itemsJson = languageConverter.ToDB(profile.Languages); insertNewLanguageProfileCmd.Transaction = tran; insertNewLanguageProfileCmd.CommandText = "INSERT INTO LanguageProfiles (Id, Name, Cutoff, Languages) VALUES (?, ?, ?, ?)"; insertNewLanguageProfileCmd.AddParameter(profile.Id); insertNewLanguageProfileCmd.AddParameter(profile.Name); insertNewLanguageProfileCmd.AddParameter(profile.Cutoff.Id); insertNewLanguageProfileCmd.AddParameter(itemsJson); insertNewLanguageProfileCmd.ExecuteNonQuery(); } using (IDbCommand updateSeriesCmd = conn.CreateCommand()) { foreach (var profileId in profile.ProfileIds) { updateSeriesCmd.Transaction = tran; updateSeriesCmd.CommandText = "UPDATE Series SET LanguageProfileId = ? WHERE ProfileId = ?"; updateSeriesCmd.AddParameter(profile.Id); updateSeriesCmd.AddParameter(profileId); updateSeriesCmd.ExecuteNonQuery(); } } } }
private ContestedLocationStatus DbGetContestedLocationStatus(string id) { ContestedLocation loc = ContestedLocationDefinitions.ContestedLocations.Where(l => l.Id == id).FirstOrDefault(); if (loc == null) { throw new KeyNotFoundException(string.Format("Contested location [{0}] does not exist", id)); } ContestedLocationStatus status = new ContestedLocationStatus() { Name = loc.Name, Abbreviation = loc.Abbreviation, OpenOn = new List <int>(), DefendOn = new List <int>(), CaptureOn = new List <int>() }; IDbCommand cmd = m_DbConn.CreateCommand(); try { cmd.CommandText = "SELECT world_id FROM contestedstatusapi_open WHERE location_id = @id"; cmd.AddParameter("@id", id); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { status.OpenOn.Add(int.Parse(reader["world_id"].ToString())); } } cmd.CommandText = "SELECT world_id FROM contestedstatusapi_defend WHERE location_id = @id"; cmd.AddParameter("@id", id); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { status.DefendOn.Add(int.Parse(reader["world_id"].ToString())); } } cmd.CommandText = "SELECT world_id FROM contestedstatusapi_capture WHERE location_id = @id"; cmd.AddParameter("@id", id); using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { status.CaptureOn.Add(int.Parse(reader["world_id"].ToString())); } } } catch (Exception e) { LOGGER.Error(string.Format("Exception thrown when attempting to get contested location data [{0}]", id), e); } return(status); }
private void InsertProductCategory(IDbCommand cmd, Row row) { cmd.CommandText = @"INSERT INTO ProductToCategories (ProductId,CategorySnapshotId) VALUES (@productid, @catid)"; cmd.AddParameter("productid", row["product_id"]); cmd.AddParameter("catid", row["cat_id"]); }
/// <summary> /// Called after saving of the specified enumeration of entities has taken place. /// </summary> /// <param name="entities">The entities.</param> /// <param name="state">The state.</param> public void OnAfterSave(IEnumerable <IEntity> entities, IDictionary <string, object> state) { object duplicates; HashSet <long> entitiesToDeleteSet = new HashSet <long>(); // Get any merged duplicates to delete if (state.TryGetValue(ResourceKeyHelper.MergedDuplicatesStateKey, out duplicates)) { var duplicatesAsHashSet = duplicates as HashSet <long>; if (duplicatesAsHashSet != null && duplicatesAsHashSet.Count != 0) { entitiesToDeleteSet.UnionWith(duplicatesAsHashSet.Select(id => EventTargetStateHelper.GetIdFromTemporaryId(state, id))); } } Dictionary <long, ResourceKeyDataHash> dataHashesToDelete = ResourceKeyHelper.GetResourceKeyDataHashToDeleteState(state); // Get any datahashes to delete if (dataHashesToDelete != null && dataHashesToDelete.Count != 0) { entitiesToDeleteSet.UnionWith(dataHashesToDelete.Keys); } if (entitiesToDeleteSet.Count != 0) { Entity.Delete(entitiesToDeleteSet); } object movedEntitiesObject; if (state.TryGetValue("movedEntities", out movedEntitiesObject)) { Dictionary <long, ISet <long> > movedEntities = movedEntitiesObject as Dictionary <long, ISet <long> >; if (movedEntities != null) { using (DatabaseContext context = DatabaseContext.GetContext( )) { foreach (KeyValuePair <long, ISet <long> > pair in movedEntities) { using (IDbCommand command = context.CreateCommand("spPostEntityMove", CommandType.StoredProcedure)) { command.AddIdListParameter("@entityIds", pair.Value); command.AddParameter("@tenantId", DbType.Int64, RequestContext.TenantId); command.AddParameter("@solutionId", DbType.Int64, pair.Key); command.ExecuteNonQuery( ); } } } } } // Save the resource key data hashes for any resources at the reverse end // of any key relationships. ResourceKeyHelper.SaveResourceKeyDataHashesForReverseRelationships(entities, state); }
public static void WritePeople(IDbCommand cmd, Row row) { cmd.CommandText = @"INSERT INTO People (UserId, FirstName, LastName, Email) VALUES (@UserId, @FirstName, @LastName, @Email)"; cmd.AddParameter("UserId", row["Id"]); cmd.AddParameter("FirstName", row["FirstName"]); cmd.AddParameter("LastName", row["LastName"]); cmd.AddParameter("Email", row["Email"]); }
public void UpdateCommand(IDbCommand command, object entity) { if (command == null) throw new ArgumentNullException("command"); if (entity == null) throw new ArgumentNullException("entity"); var updates = ""; var where = ""; foreach (var property in _values) { var value = property.GetValue(entity); updates += string.Format("{0}=@{1}, ", property.ColumnName, property.PropertyName); command.AddParameter(property.PropertyName, value); } if (command.Parameters.Count == 0) throw new DataException("At least one property (other than primary keys) must be specified."); foreach (var property in _keys) { var value = property.GetValue(entity); if (value == null || value == DBNull.Value) throw new DataException( string.Format("Entity {0}' do not contain a value for the key property '{1}'", entity, property.PropertyName)); where += property.ColumnName + "=" + "@" + property.PropertyName + " AND "; command.AddParameter(property.PropertyName, value); } command.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", TableName, updates.Remove(updates.Length - 2, 2), @where.Remove(@where.Length - 5, 5)); }
private void WriteRow(IDbCommand cmd, Row row) { cmd.CommandText = GetCachedQueryOdbc(row); foreach (string key in row.Keys) { cmd.AddParameter(key, row[key]); } }
/// <summary> /// Modifies the command to execute a DELETE statement /// </summary> /// <param name="command">Command that will be executed after this method call</param> /// <param name="entity">Only primary key properties are used in the WHERE clause</param> /// <exception cref="System.ArgumentNullException"> /// command /// or /// entity /// </exception> /// <exception cref="System.Data.DataException"></exception> public void DeleteCommand(IDbCommand command, object entity) { if (command == null) throw new ArgumentNullException("command"); if (entity == null) throw new ArgumentNullException("entity"); var where = ""; foreach (var property in _keys) { var value = property.GetValue(entity); if (value == null || value == DBNull.Value) throw new DataException( string.Format("Entity {0}' do not contain a value for the key property '{1}'", entity, property.PropertyName)); where += string.Format("{0}=" + "@{1} AND ", property.ColumnName, property.PropertyName); command.AddParameter(property.PropertyName, value); } command.CommandText = string.Format("DELETE FROM {0} WHERE {1}", TableName, @where.Remove(@where.Length - 5, 5)); }
private static string PopulateCommand(IDbCommand command, IEnumerable<string> messageTypes) { var builder = new StringBuilder(); var types = messageTypes.ToArray(); for (var i = 0; i < types.Length; i++) { command.AddParameter(SqlStatements.MessageTypeParameter + i, types[i]); builder.AppendFormat(command.CommandText, i); } return builder.ToString(); }
private static void SetParameters(Procedure procedure, IDbCommand cmd, IDictionary<string, object> suppliedParameters) { AddReturnParameter(cmd); int i = 0; foreach (var parameter in procedure.Parameters) { object value; if (!suppliedParameters.TryGetValue(parameter.Name.Replace("@", ""), out value)) { suppliedParameters.TryGetValue("_" + i, out value); } cmd.AddParameter(parameter.Name, value); i++; } }
private static void SetParameters(Procedure procedure, IDbCommand cmd, IDictionary<string, object> suppliedParameters) { if (procedure.Parameters.Any(p=>p.Direction == ParameterDirection.ReturnValue)) AddReturnParameter(cmd); int i = 0; foreach (var parameter in procedure.Parameters.Where(p=>p.Direction != ParameterDirection.ReturnValue)) { object value; if (!suppliedParameters.TryGetValue(parameter.Name.Replace("@", ""), out value)) { suppliedParameters.TryGetValue("_" + i, out value); } var cmdParameter = cmd.AddParameter(parameter.Name, value); cmdParameter.Direction = parameter.Direction; i++; } }