public int Update(int id, Registro novoRegistro)
{
ConexaoBD con = ConexaoBD.Get_Instance;
int result = 0;
string query = String.Format("UPDATE colmeia SET idade ='{1}', tipo = '{2}', nivel = '{3}' WHERE id = {0}",
id, novoRegistro.Idade, novoRegistro.Tipo, novoRegistro.Nivel);
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
result = MysqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
con.CloseConnection();
}
return(result);
;
}
//=========================== DELETE ================================
public int Delete(Registro idAbelha)
{
ConexaoBD con = ConexaoBD.Get_Instance;
query = "DELETE FROM colmeia WHERE id LIKE '" + idAbelha.Id + "'";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
//verificar se id existe
con.OpenConnection();
int i = MysqlCmd.ExecuteNonQuery();
if (i > 0)
{
return(i);
}
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(0);
}
//=========================== INSERT ================================
public int Insert(Registro registro)
{
int result = 0;
con = ConexaoBD.Get_Instance;
query = String.Format("INSERT INTO colmeia (idade, tipo, nivel) " +
"VALUES ('{0}', '{1}', '{2}')", registro.Idade, registro.Tipo, registro.Nivel);
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
result = MysqlCmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(result);
}
//=========================== INSERT ================================
public int Insert(Registro registro)
{
int result = 0;
con = ConexaoBD.Get_Instance;
query = String.Format("INSERT INTO produtos (nome, quantidade, tamanho) " +
"VALUES ('{0}', '{1}', '{2}')", registro.Nome, registro.Quantidade, registro.Tamanho);
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
result = MysqlCmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(result);
}
//=========================== DELETE ================================
public int Delete(Registro idPedido)
{
con = ConexaoBD.Get_Instance;
query = "DELETE FROM produtos WHERE id LIKE '" + idPedido.Id + "'";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
int i = MysqlCmd.ExecuteNonQuery();
if (i > 0)
{
return(i);
}
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(0);
}
//=========================== UPDATE ================================
public int Update(int id, Registro novoRegistro)
{
int result = 0;
con = ConexaoBD.Get_Instance;
query = String.Format("UPDATE produtos SET nome ='{0}', quantidade = '{1}', tamanho = '{2}'" +
" WHERE id = {3}", novoRegistro.Nome, novoRegistro.Quantidade, novoRegistro.Tamanho, id);
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
result = MysqlCmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(result);
}
//======================= SELECT POR ID ============================
public List <Registro> SelectByID(int id)
{
List <Registro> listRegistro = null;
Registro registroUnico = new Registro();
con = ConexaoBD.Get_Instance;
query = "SELECT * FROM produtos WHERE id LIKE '" + id + "'";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
dataReader = MysqlCmd.ExecuteReader();
if (dataReader.HasRows)
{
listRegistro = new List <Registro>();
while (dataReader.Read())
{
registroUnico = new Registro();
registroUnico.Id = (int)dataReader["id"];
registroUnico.Nome = (string)dataReader["nome"];
registroUnico.Quantidade = (int)dataReader["quantidade"];
registroUnico.Tamanho = (string)dataReader["tamanho"];
listRegistro.Add(registroUnico);
}
dataReader.Close();
}
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(listRegistro);
}
public int Insert(Contato contato)
{
int result = 0;
ConexaoBD conexao = ConexaoBD.GetInsta;
string query = String.Format("INSERT INTO contato(nome, email, telefone, data_cadastra) VALUES ('{0}','{1}','{2}','{3}');",
contato.Nome, contato.Email, contato.Telefone, contato.DataCadastro);
cmd = new MySqlCommand(query, conexao.GetConnection());
try
{
conexao.OpenConnection();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex) { }
finally
{
conexao.CloseConnection();
}
return(result);
}
//=========================== SELECT ================================
public List <Registro> SelectAll()
{
List <Registro> listRegistro = null;
Registro registro;
con = ConexaoBD.Get_Instance;
query = "SELECT * FROM colmeia";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
try
{
con.OpenConnection();
dataReader = MysqlCmd.ExecuteReader();
if (dataReader.HasRows)
{
listRegistro = new List <Registro>();
while (dataReader.Read())
{
registro = new Registro();
registro.Id = (int)dataReader["id"];
registro.Idade = (int)dataReader["idade"];
registro.Tipo = (string)dataReader["tipo"];
registro.Nivel = (string)dataReader["nivel"];
listRegistro.Add(registro);
}
dataReader.Close();
}
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(listRegistro);
}
//======================= SELECT CONTADOR ============================
public int SelectCount(string s)
{
con = ConexaoBD.Get_Instance;
query = "SELECT COUNT(tipo) FROM colmeia WHERE tipo = '" + s + "'";
//query = SELECT tipo FROM colmeia WHERE tipo = '"+ s + "'";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
int count = 0;
try
{
con.OpenConnection();
count = Convert.ToInt32(MysqlCmd.ExecuteScalar());
/*dataReader = MysqlCmd.ExecuteReader();
*
* if (dataReader.HasRows)
* {
*
* while (dataReader.Read())
* {
*
* i++;
*
* }
* dataReader.Close();
* }*/
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(count);
}
public int Delete(Contato contato)
{
int result = 0;
ConexaoBD conexao = ConexaoBD.GetInsta;
string query = String.Format("DELETE FROM contato WHERE id = {0}", contato.Id);
cmd = new MySqlCommand(query, conexao.GetConnection());
try
{
conexao.OpenConnection();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
conexao.CloseConnection();
}
return(result);
}
public int Update(int id, Contato novoContato)
{
int result = 0;
ConexaoBD conexao = ConexaoBD.GetInsta;
string query = String.Format("UPDATE contato SET nome ='{0}' WHERE id = {1}", novoContato.Nome, id);
cmd = new MySqlCommand(query, conexao.GetConnection());
try
{
conexao.OpenConnection();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
conexao.CloseConnection();
}
return(result);
}
public List <Contato> Select()
{
List <Contato> contatos = null;
Contato contato;
ConexaoBD conexao = ConexaoBD.GetInsta;
string query = "Select * from contato;";
cmd = new MySqlCommand(query, conexao.GetConnection());
try
{
conexao.OpenConnection();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
contatos = new List <Contato>();
while (reader.Read())
{
contato = new Contato();
contato.Id = (int)reader["id"];
contato.Nome = (string)reader["nome"];
contato.Email = (string)reader["email"];
contato.Telefone = (string)reader["telefone"];
//contato.DataCadastro = (DateTime)reader[5];
contatos.Add(contato);
}
reader.Close();
}
}
catch (Exception ex)
{ }
finally
{
conexao.CloseConnection();
}
return(contatos);
}
//======================= SELECT CONTADOR ============================
public int SelectCount(string s)
{
con = ConexaoBD.Get_Instance;
query = "SELECT COUNT(id) FROM produtos WHERE id = '" + s + "'";
MysqlCmd = new MySqlCommand(query, con.GetConnection());
int count = 0;
try
{
con.OpenConnection();
count = Convert.ToInt32(MysqlCmd.ExecuteScalar());
}
catch (Exception)
{
throw;
}
finally
{
con.CloseConnection();
}
return(count);
}