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++;
}
}