public JogoDTO BuscarJogo(JogoDTO jogo)
{
var retorno = new JogoDTO();
DataSet dataSet = new DataSet();
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("JOGO_SELECIONAR", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id_Jogo", jogo.CodigoJogo);
command.Parameters.AddWithValue("@Score", jogo.Score);
command.Parameters.AddWithValue("@Id_Usuario", jogo.CodigoUsuario);
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
DataTableReader reader = dataSet.Tables[0].CreateDataReader();
while (reader.Read())
{
retorno = new JogoDTO()
{
CodigoJogo = (int)reader["ID_JOGO"],
CodigoUsuario = (int)reader["ID_USUARIO"],
Score = (int)reader["SCORE"]
};
}
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
return(retorno);
}
public void GravarPalavraRodada(PalavraRodadaDTO palavraRodada)
{
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("PALAVRARODADA_INSERIR", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id_Palavra", palavraRodada.ObjPalavra.CodigoPalavra);
command.Parameters.AddWithValue("@Id_Rodada", palavraRodada.ObjRodada.CodigoRodada);
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
command.ExecuteScalar().ToString();
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
}
public int GravarTokenEmail(TokenEmailDTO tokenEmail)
{
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("TOKENEMAIL_INSERIR", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Token", tokenEmail.Token);
command.Parameters.AddWithValue("@DataValida", tokenEmail.DataValida);
command.Parameters.AddWithValue("@Id_Usuario", tokenEmail.CodigoUsuario);
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
string retorno = command.ExecuteScalar().ToString();
transaction.Commit();
return(Convert.ToInt32(retorno));
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
}
public int SalvarRodada(RodadaDTO rodada)
{
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("RODADA_INSERIR", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Numero", rodada.Numero);
command.Parameters.AddWithValue("@Id_Jogo", rodada.CodigoJogo);
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
string retorno = command.ExecuteScalar().ToString();
transaction.Commit();
return(Convert.ToInt32(retorno));
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
}
public List <UsuarioDTO> ListarUsuario(UsuarioDTO usuario)
{
DataSet dataSet = new DataSet();
List <UsuarioDTO> lstUsuario = new List <UsuarioDTO>();
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("USUARIO_SELECIONAR", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id_Usuario", usuario.CodigoUsuario);
command.Parameters.AddWithValue("@Nome", (!String.IsNullOrEmpty(usuario.Nome)) ? usuario.Nome.Trim().ToUpper() : "");
command.Parameters.AddWithValue("@Sobrenome", (!String.IsNullOrEmpty(usuario.Sobrenome)) ? usuario.Sobrenome.Trim().ToUpper() : "");
command.Parameters.AddWithValue("@Email", (!String.IsNullOrEmpty(usuario.Email)) ? usuario.Email.Trim().ToUpper() : "");
command.Parameters.AddWithValue("@Username", (!String.IsNullOrEmpty(usuario.Username)) ? usuario.Username.Trim().ToUpper() : "");
command.Parameters.AddWithValue("@Senha", (!String.IsNullOrEmpty(usuario.Senha)) ? usuario.Senha : "");
command.Parameters.AddWithValue("@Ativo", StringAtivo(usuario.Ativo));
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
DataTableReader reader = dataSet.Tables[0].CreateDataReader();
while (reader.Read())
{
lstUsuario.Add(new UsuarioDTO()
{
CodigoUsuario = (int)reader["ID_USUARIO"],
Nome = reader["NOME"].ToString(),
Sobrenome = reader["SOBRENOME"].ToString(),
Email = reader["EMAIL"].ToString(),
Username = reader["USERNAME"].ToString(),
Senha = reader["SENHA"].ToString(),
Ativo = reader.GetBoolean(reader.GetOrdinal("ATIVO"))
});
}
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
return(lstUsuario);
}
public List <PalavraSinalDTO> BuscarPalavrasCorretas()
{
var lstPalavraSinal = new List <PalavraSinalDTO>();
DataSet dataSet = new DataSet();
var connection = conexao.CriaConexao();
conexao.AbrirConexao(connection);
SqlCommand command = new SqlCommand("PALAVRA_BUSCAR_CORRETA", connection);
command.CommandType = CommandType.StoredProcedure;
SqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dataSet);
DataTableReader reader = dataSet.Tables[0].CreateDataReader();
while (reader.Read())
{
lstPalavraSinal.Add(new PalavraSinalDTO()
{
ObjPalavra = new PalavraDTO()
{
CodigoPalavra = (int)reader["ID_PALAVRA"],
Palavra = reader["PALAVRA"].ToString(),
CodigoSinal = (int)reader["ID_SINAL"]
},
ObjSinal = new SinalDTO()
{
CodigoSinal = (int)reader["ID_SINAL"],
Diretorio = reader["DIRETORIO"].ToString()
}
});
}
;
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
throw new Exception(ex.Message);
}
catch (Exception ex2)
{
throw new Exception(ex2.Message);
}
}
finally
{
conexao.FecharConexao(connection);
}
return(lstPalavraSinal);
}