An example of a User CRUD
Connection to the bank
using System.Configuration;
using MySql.Data.MySqlClient;
namespace PrjGpaci
{
class sisBdConn
{
protected MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["PrjGpaci.Properties.Settings.gpaciConnectionString1"].ToString());
protected bool abrir()
{
try
{
conn.Open();
return true;
}
catch (MySqlException ex) { return false; }
}
protected bool fechar()
{
try
{
conn.Clone();
return true;
}
catch (MySqlException ex) { return false; }
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
namespace PrjGpaci
{
class sisBdUsr:sisBdConn
{
/// <summary>
/// Cadastra o usuario
/// </summary>
/// <param name="r"> Classe Usr</param>
/// <returns> verdadeiro se cadastrou ou falso se não cadastrou </returns>
public bool cadUsr(Usr r)
{
try
{
abrir();
string strquery = "INSERT INTO usr (id,psw,nome,tipo) VALUES (@id,@psw,@nome,@tipo)";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@id",r.Id);
comand.Parameters.AddWithValue("@psw",r.Psw);
comand.Parameters.AddWithValue("@nome",r.Nome);
comand.Parameters.AddWithValue("@tipo", r.Tipo);
comand.ExecuteNonQuery();
fechar();
return true;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Atualiza as informaçoes do usuario
/// </summary>
/// <param name="r"></param>
/// <returns></returns>
public bool upUsr(Usr r)
{
try
{
abrir();
string strquery = "UPDATE usr SET id =@id,psw =@psw,nome =@nome,tipo = @tipo WHERE codu = @codu";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@id", r.Id);
comand.Parameters.AddWithValue("@psw", r.Psw);
comand.Parameters.AddWithValue("@nome", r.Nome);
comand.Parameters.AddWithValue("@tipo", r.Tipo);
comand.Parameters.AddWithValue("@codu", r.Cod);
comand.ExecuteNonQuery();
fechar();
return true;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Consulta para login
/// </summary>
/// <param name="id">id</param>
/// <param name="psw">senha</param>
/// <returns> retorna classe usr null se não encontrar ou reotna usr com os dados do usr</returns>
public Usr login(string id, string psw)
{
try
{
Usr u = null;
abrir();
string strquery = "SELECT * FROM usr WHERE Id LIKE @id AND Psw LIKE @psw";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@id", id);
comand.Parameters.AddWithValue("@psw", psw);
MySqlDataReader ler = comand.ExecuteReader();
while (ler.Read())
{
u = new Usr();
u.Cod = Convert.ToInt32(ler["codu"]);
u.Id = ler["id"].ToString();
u.Psw = ler["psw"].ToString();
u.Nome = ler["nome"].ToString();
u.Tipo = Convert.ToInt32(ler["tipo"]);
fechar();
return u;
}
fechar();
return null;
}
catch (Exception)
{
return null;
throw;
}
}
/// <summary>
/// busca todos os usuario
/// </summary>
/// <returns>retorna dataset</returns>
public DataSet dataUsr()
{
try
{
abrir();
string strquery = "SELECT codu ,id ,psw,nome,tipo FROM usr";
MySqlCommand comand = new MySqlCommand(strquery, conn);
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(comand);
da.Fill(ds);
fechar();
return ds;
}
catch (Exception)
{
return null;
throw;
}
}
/// <summary>
/// Pesquisa por codigo
/// </summary>
/// <param name="cod"></param>
/// <returns>retorna dataset</returns>
public DataSet dataUsr(int cod)
{
try
{
abrir();
string strquery = "SELECT codu ,id ,psw,nome,tipo FROM usr WHERE codu = @cod";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@cod",cod);
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(comand);
da.Fill(ds, "tbUsuario");
fechar();
return ds;
}
catch (Exception)
{
return null;
throw;
}
}
/// <summary>
/// Pesquisa por id
/// </summary>
/// <param name="id"></param>
/// <returns>retorna dataset</returns>
public DataSet dataUsr(string id)
{
try
{
abrir();
string strquery = "SELECT codu ,id ,psw,nome,tipo FROM usr WHERE id LIKE @id";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@id", id);
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(comand);
da.Fill(ds);
fechar();
return ds;
}
catch (Exception)
{
return null;
throw;
}
}
/// <summary>
/// Pesquisa por nome
/// </summary>
/// <param name="nome"></param>
/// <returns></returns>
public DataSet pesqNome(string nome)
{
try
{
abrir();
string strquery = "SELECT codu ,id ,psw,nome,tipo FROM usr WHERE nome LIKE @nome";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@nome", nome);
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter(comand);
da.Fill(ds);
fechar();
return ds;
}
catch (Exception)
{
return null;
throw;
}
}
/// <summary>
/// Pesquisa o id exato do usario e retorna verdadeiro ou falso
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public bool pesqIdExiste(string id)
{
try
{
abrir();
string strquery = "SELECT codu ,id ,psw,nome,tipo FROM usr WHERE id = @id";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@id", id);
MySqlDataReader ler = comand.ExecuteReader();
while (ler.Read())
{
fechar();
return true;
}
return false;
}
catch (Exception)
{
return false;
throw;
}
}
public bool deletaUsr(int cod)
{
try
{
abrir();
string strquery = "DELETE FROM usr WHERE CodU = @codu";
MySqlCommand comand = new MySqlCommand(strquery, conn);
comand.Parameters.AddWithValue("@codu", cod);
comand.ExecuteNonQuery();
return true;
}
catch (MySqlException)
{
return false;
throw;
}
}
}
}
