C# CRUD (Create, Read, Update & Delete)

Introdução

A partir deste artigo o leitor terá a possibilidade de aprender a utilizar as quatro operações mais comuns na interação com a base de dados.

O artigo utiliza o sistema de base de dados MySQL mas poderá ser adaptável, sem grande esforço, a outro SGBD.

Instalação

Para que o leitor consiga estabelecer uma conexão com o MySQL é necessário:

  1. A instalação do MySQL Connector (ver link 1)
  2. Adicionar a referência do MySQL ao projecto (ver link 2)
  3. Criar uma base de dados com duas tabelas jogadores e clubes (ver link 3)

Resultado final

O artigo também exemplifica a utilização do controlo DataGridView em pleno, usufruindo da possibilidade de imagens e listas de itens dentro do mesmo. Todo o código abaixo escrito será utilizado dentro dos eventos deste controlo, pelo que não será necessário a adição de mais nenhum objecto ao formulário.

Resultado final

O leitor poderá fazer download do projecto para fins educacionais (ver link 4).

Estrutura

Uma estrutura de projecto bem delineada poupa-nos imenso tempo. A estrutura deste projecto é muito simples.

Estrutura do projecto

Classes

Neste artigo é necessário a criação de duas classes distintas. A primeira classe (dbconfig) irá tratar da configuração do acesso à base de dados. A segunda classe (dbhandler) irá permitir a realização de queries / operações CRUD.

class dbconfig
  {
    private string server;
    private string user;
    private string password;
    private string database;

    /* Pode-se, desde logo, atribuir valores por defeito */

    public dbconfig(string __server = "localhost", string __user = "root", 
                    string __password = "", string __database = "footballmanager") 
    {
        this.server = __server;
        this.user = __user;
        this.password = __password;
        this.database = __database;
    }

    public string get() { 
        return "Server=" + this.server + ";Database=" + this.database + 
               ";Uid=" + this.user + ";Pwd=" + this.password;
        /* Output: Server=localhost;Database=footballmanager:Uid=root;Pwd= */
    }
}

No construtor da classe desde logo foi assumido quais os valores por defeito de ligação à base de dados. No entanto é dada a possibilidade de, aquando a iniciação da classe, definir a configuração desejada.

dbconfig db = new dbconfig("localhost", "root", "", "outra_base_dados");

Tudo o que é preciso para estabelecer uma ligação com o servidor de MySQL está nesta classe de configuração.

A classe dbhandler é simplista mas totalmente funcional e segura, em termos de realização de queries, pois permite a passagem de parâmetros. Apenas duas funções foram criadas:

  1. Função get() – Realiza a operação Ler (Read), cuja finalidade é devolver informações da base de dados.
  2. Função set() – Realiza as operações de Criar (Create), Actualizar (Update) e Eliminar (Delete).

Para que a utilização desta classe funcione em pleno será necessário adicionar quatro referências.

/* Permite a ligação com a base de dados */
using MySql.Data.MySqlClient; 
/* Permite a utilização da funcionalidade [Optional] <- disponível apenas na 
   framework 4.0 */ 
using System.Runtime.InteropServices; 
/* Permitem a chamada das funções directamente */
using System.Data;
using System.Text.RegularExpressions;

class dbhandler 
  { 
    private dbconfig db;
    private MySqlConnection connection;
    private MySqlCommand command;
    private MySqlDataAdapter dAdapter;
    private DataSet dSet;

    public dbhandler()
    {
        db = new dbconfig();
        connection = new MySqlConnection(db.get());
    }
}

Sempre que a classe dbhandler é iniciada o seu construtor irá chamar o construtor da classe dbconfig e por sua vez estabelecer uma ligação com a base de dados MySQL.

public DataSet get(string __query, [Optional] MySqlParameter[] __param)
  { 
    this.command = new MySqlCommand(__query, this.connection);

    if (__param != null) { this.command.Parameters.AddRange(__param); }

    this.dAdapter = new MySqlDataAdapter(this.command);
    this.dSet = new DataSet();

    /* input exemplo: 'SELECT * FROM tabela WHERE id = @id'
       output: 'FROM tabela' */
    Regex rgx = new Regex("FROM\\s\\w+");
    MatchCollection matches = rgx.Matches(__query);

    /* output: 'tabela' */
    string table = matches[0].Value.Replace("FROM ", ""); 

    this.dAdapter.Fill(this.dSet, table);

    return this.dSet;
}

A utilização de um DataSet é bastante benéfico (em comparação com o DataReader) por várias razões:

  • Não é preciso que estabeleçamos uma ligação, pois o método .Fill() já faz isso;
  • Podemos de imediato atribuir o DataSource a um controlo (como é o caso da DataGridView);
  • É possível fazer queries dentro do DataSet, pois este guarda os dados como se de uma tabela se tratasse.

O leitor poderá verificar que foi incutido nesta função o uso das expressões regulares, que tem como aplicação devolver o nome da tabela da query. Não é obrigatório a utilização deste método, o nome da tabela pode ser passado via parâmetro (para a função) se assim preferir.

public int set(string __query, [Optional] MySqlParameter[] __param)
  { 
    this.command = new MySqlCommand(__query, this.connection);

    if (__param != null) { command.Parameters.AddRange(__param); }
 
    if (this.connection.State != ConnectionState.Open) { 
        this.connection.Open(); 
    }

    return this.command.ExecuteNonQuery(); 
    // retorna o número de linhas afectadas
}

Sim, a função set() contém apenas este trecho de código. Por precaução é retornado o número de linhas afectadas para perceber se foi ou não executado com sucesso.

A nossa classe está concluída. A classe executa eficazmente os métodos estipulados pelo CRUD de forma segura e muito simples. Resta agora colocar esta classe em acção.

Formulário

No formulário apenas será necessário adicionar o controlo DataGridView. Todas as operações do CRUD serão realizadas directamente pelos diversos eventos (Load, userAddedRow, RowValidating, CellEndEdit, onClick, MouseDown).

Referências e Variáveis

Para que tudo funcione em pleno é necessário adicionar a referência do MySQL:

using MySql.Data.MySqlClient;

Bem como adicionar algumas variáveis privadas a este formulário:

private libs.dbhandler dbQuery = new libs.dbhandler();
/* Retêm o indíce e ID respectivos a cada jogador na DataGridView */
private Dictionary<int, int> _dicJogadores = new Dictionary<int, int>();

Eventos e Funções

No evento Load vamos criar toda a estrutura da DataGridView, desde os tipos de colunas (Textbox, Image e Combobox), bem como selecionar o conteúdo proveniente da base de dados.

Os dois eventos userAddedRow e RowValidating têm como finalidade a inserção de informação na base de dados.

O evento CellEndEdit terá como objectivo proporcionar a actualização da informação.

E por fim os eventos onClick e MouseDown vão criar uma estrutura capaz de eliminar informação, bem como de actualizar a foto de um jogador, tudo isto através da criação, em run-time, de um ContextMenuStrip atribuído à DataGridView.

Ler dados

O evento Load tem a seguinte estrutura:

DataSet dSetJogadores = dbQuery.get("SELECT jog.id, jog.nome, jog.data_nascimento, jog.valor_estimado, jog.foto, club.descricao, club.id " + 
"FROM jogadores AS jog " +
"LEFT JOIN clubes AS club ON club.id = jog.id_clube " +
"ORDER BY jog.nome ASC");

 /* A tabela clubes é uma tabela secundária que se interliga com a tabela jogadores. 
  * Para que a ComboboxColumn contenha todos os valores desta tabela, é necessário abastecê-la pela via do DataSource
  */
DataSet dSetClubes = dbQuery.get("SELECT id, descricao FROM clubes");

var nome = new DataGridViewTextBoxColumn(){ HeaderText = "Nome", MinimumWidth = 150 };
var data_nascimento = new DataGridViewTextBoxColumn(){ HeaderText = "Data Nascimento" };
var valor_estimado = new DataGridViewTextBoxColumn(){ HeaderText = "Valor Estimado(€)" };

var clube = new DataGridViewComboBoxColumn() { 
    HeaderText = "Clube",
    DataSource = dSetClubes.Tables[0],
    ValueMember = "id",
    DisplayMember = "descricao",
    AutoSizeMode = DataGridViewAutoSizeColumnMode.None,
    FlatStyle = FlatStyle.Standard
};

var foto = new DataGridViewImageColumn() { 
    HeaderText = "",
    AutoSizeMode = DataGridViewAutoSizeColumnMode.None,
    Width = 64
};

this.dataGridView1.Columns.AddRange(foto, nome, data_nascimento, valor_estimado, clube);
this.dataGridView1.RowTemplate.MinimumHeight = 50;

Como o leitor poderá comprovar, neste trecho de código apenas foram delineadas as queries necessárias à construção da DataGridView, assim como toda a estrutura das colunas. Falta então adicionar dados informativos provenientes da base de dados.

for (int i = 0; i <= dSetJogadores.Tables[0].Rows.Count - 1; i++) {

    string _imgDefault = "no_image.gif";

    Image _img = imageResize(new Bitmap(Application.StartupPath + "/imagens/jogadores/" + _imgDefault), new Size(64, 64));

    if (dSetJogadores.Tables[0].Rows[i].ItemArray[4].ToString() != string.Empty){ 
        _img = new Bitmap(imageResize(new Bitmap(Application.StartupPath + "/imagens/jogadores/" + dSetJogadores.Tables[0].Rows[i].ItemArray[4].ToString()), new Size(64, 64))); 
    }

    string _nome = dSetJogadores.Tables[0].Rows[i].ItemArray[1].ToString();
    string _datanascimento = dSetJogadores.Tables[0].Rows[i].ItemArray[2].ToString();
 
    /* O valor, que está em float (10,2), ultrapassa as casas decimais. 
     * É então preciso haver uma conversão desse valor para double e, por sua vez, formatar esse valor com duas casas 'N2'.
     */
    string _valorestimado = Convert.ToDouble(dSetJogadores.Tables[0].Rows[i].ItemArray[3].ToString()).ToString("N2");
    int _clube = Convert.ToInt32(dSetJogadores.Tables[0].Rows[i].ItemArray[6].ToString());

    /* Guarda a posição do ID na DataGridView */
    _dicJogadores.Add(i, Convert.ToInt32(dSetJogadores.Tables[0].Rows[i].ItemArray[0].ToString()));

    this.dataGridView1.Rows.Add(new object[] { _img, _nome, _datanascimento, _valorestimado, _clube });

}

E finalizamos o evento load. Recapitulando o processo: definimos as queries; definimos a estrutura da DataGridView; atribuímos a informação das queries às linhas.

Se o leitor correr a aplicação neste momento, possivelmente dar-lhe-á erro na função imageResize(), pois esta ainda não foi adicionada ao formulário.

Adicionar dados

Para que seja possível a adição de novas linhas directamente na base de dados, precisamos de dois eventos para garantir a fiabilidade dos dados e evitar possíveis erros.

private void dataGridView1_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
    _userAddedRowIndex = this.dataGridView1.CurrentRow.Index;
}

É definido qual o índice de linha actual para ser utilizado no evento RowValidating:

if(e.RowIndex == _userAddedRowIndex){

    /* Verifica se existe ou não valores nas células */

    string _nome = (string.IsNullOrEmpty(this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString()) ? "undefined" : this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString());

    DateTime _datanascimento = (this.dataGridView1.Rows[e.RowIndex].Cells[2].Value == null ? DateTime.Today : Convert.ToDateTime(this.dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString()));

    double _valorestimado = (this.dataGridView1.Rows[e.RowIndex].Cells[3].Value == null ? 0.0f : Convert.ToDouble(this.dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString()));

    int _id_clube = (this.dataGridView1.Rows[e.RowIndex].Cells[4].Value == null ? 1 : Convert.ToInt32(this.dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString()));
 
    MySqlParameter[] _sqlParams = new MySqlParameter[] {
        new MySqlParameter("@nome", _nome),
        new MySqlParameter("@datanascimento",_datanascimento),
        new MySqlParameter("@valorestimado", _valorestimado),
        new MySqlParameter("@id_clube", _id_clube)
    };

    if (dbQuery.set("INSERT INTO jogadores (nome, data_nascimento, valor_estimado, id_clube) " + "VALUES (@nome, @datanascimento, @valorestimado, @id_clube)", _sqlParams) > 0) { 

        // É necessário actualizar os indíces do dicionário
        int _lastid = Convert.ToInt32(dbQuery.get("SELECT id FROM jogadores ORDER BY id DESC LIMIT 1").Tables[0].Rows[0].ItemArray[0].ToString());

        _dicJogadores.Add(e.RowIndex, _lastid);
    }
}

O leitor decerto notará que nesta inserção não está incutida a coluna foto, por razões estruturais. A inserção da foto será possível após a conclusão da inserção dos dados na base de dados.

Actualizar dados

O trecho de código de actualização de dados é praticamente idêntico ao da inserção de dados, pelo que o leitor não notará grande diferença. É apenas estabelecida uma condição para prevenir enganos.

Portanto, no evento CellEndEdit (que ocorre após se clicar fora da célula), temos o seguinte código:

if (e.RowIndex != _userAddedRowIndex) { /* É necessário verificar se não se trata de uma adição de linha */
 
    string _nome = (string.IsNullOrEmpty(this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString()) ? "undefined" : this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString());

    DateTime _datanascimento = (this.dataGridView1.Rows[e.RowIndex].Cells[2].Value == null ? DateTime.Today : Convert.ToDateTime(this.dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString()));

    double _valorestimado = (this.dataGridView1.Rows[e.RowIndex].Cells[3].Value == null ? 0.0f : Convert.ToDouble(this.dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString()));

    int _id_clube = (this.dataGridView1.Rows[e.RowIndex].Cells[4].Value == null ? 1 : Convert.ToInt32(this.dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString()));

    MySqlParameter[] _sqlParams = new MySqlParameter[] {
        new MySqlParameter("@nome", _nome),
        new MySqlParameter("@datanascimento",_datanascimento),
        new MySqlParameter("@valorestimado", _valorestimado),
        new MySqlParameter("@id_clube", _id_clube),
        new MySqlParameter("@id", _dicJogadores[e.RowIndex])
    };

    dbQuery.set("UPDATE jogadores SET nome = @nome, data_nascimento = @datanascimento, valor_estimado = @valorestimado, id_clube = @id_clube " + "WHERE id = @id", _sqlParams);

}

Não é novamente atribuído nenhum valor para a foto pois esta não foi selecionada em nenhuma parte. Será então na criação de um ContextMenuStrip que iremos providenciar a eliminação de dados bem como a adição/alteração da foto.

Menu de contexto

Eliminação dados

Para que tudo funcione na perfeição, vamos precisar de criar dois métodos de click (Eliminar linha e Alterar foto) e utilizar o evento MouseDown que a DataGridView nos fornece para criar o ContextMenuStrip.

No evento MouseDown é colocado o seguinte código:

if (e.Button == System.Windows.Forms.MouseButtons.Right) {
    var _hit = this.dataGridView1.HitTest(e.X, e.Y);
 
    this.dataGridView1.ClearSelection();
    this.dataGridView1.Rows[_hit.RowIndex].Selected = true;
    this.dataGridView1.ContextMenuStrip = null; // Limpa os ContextMenuStrip existentes

    /* O 'ContextMenuStrip' só deve ser mostrado nas linhas existentes */
    if (this.dataGridView1.Rows[_hit.RowIndex].IsNewRow == false) {
        ContextMenuStrip _menu = new ContextMenuStrip();

        ToolStripMenuItem _eliminarLinha = new ToolStripMenuItem("Eliminar linha", Image.FromFile(Application.StartupPath + "/imagens/delete.png"));
        ToolStripMenuItem _alterarFoto = new ToolStripMenuItem("Alterar Foto", Image.FromFile(Application.StartupPath + "/imagens/pick_image.gif"));

        _menu.Items.AddRange(new ToolStripItem[] { _eliminarLinha, _alterarFoto });

        this.dataGridView1.ContextMenuStrip = _menu;

        /* É enviado por parâmetro o indíce da linha actual */
        _eliminarLinha.Click += delegate(object _sender, EventArgs _e) { eliminarLinha_click(sender, e, _hit.RowIndex); };
        _alterarFoto.Click += delegate(object _sender, EventArgs _e) { alterarFoto_click(sender, e, _hit.RowIndex); };
 
    }
}

O código acima representa a criação do controlo ContextMenuStrip apenas quando o botão direito do rato for pressionado. Verifica se não se trata de uma nova linha, pois caso se trate não faz sentido mostrar este controlo e por fim cria dois métodos capazes de tratar da informação da forma que desejarmos.

private void eliminarLinha_click(object sender, EventArgs e, int index) 
{
    int id = _dicJogadores[index];

    MySqlParameter[] _sqlParams = new MySqlParameter[] {
        new MySqlParameter("@id", _dicJogadores[index])
    };

    if (dbQuery.set("DELETE FROM jogadores WHERE id = @id", _sqlParams) > 0) {
        this.dataGridView1.Rows.RemoveAt(index);
        _dicJogadores.Remove(index); 
    }
}

Sempre que elimina uma linha da base de dados é necessário também eliminar essa mesma linha do dicionário de jogadores de forma a manter ambas as estruturas de dados correctas.

private void alterarFoto_click(object sender, EventArgs e, int index) {
 
    var _opf = new OpenFileDialog(){
        AddExtension = false,
        Multiselect = false,
        Filter = "Image files (*.jpg, *.jpeg, *.gif, *.png) | *.jpg; *.jpeg; *.gif; *.png",
        Title = "Escolha a imagem"
    };

    if (_opf.ShowDialog() == System.Windows.Forms.DialogResult.OK) {

        int id = _dicJogadores[index];

        MySqlParameter[] _sqlParams = new MySqlParameter[] {
            new MySqlParameter("@id", _dicJogadores[index]),
            new MySqlParameter("@foto", id + "/" + _opf.SafeFileName.ToString())
            /* O caminho correcto a inserir na base de dados: id/nome_ficheiro.extensão */
        };

        if (dbQuery.set("UPDATE jogadores SET foto = @foto WHERE id = @id", _sqlParams) > 0) {
            string _path = Application.StartupPath + "/imagens/jogadores/" + id + "/";
            System.IO.Directory.CreateDirectory(_path); 
            // Caso não exista, irá criar a pasta

            /* Copia o ficheiro seleccionado para a pasta do jogador */
            System.IO.File.Copy(_opf.FileName, _path + _opf.SafeFileName, true);
             this.dataGridView1.Rows[index].Cells[0].Value = new Bitmap(imageResize(new Bitmap(_path + _opf.SafeFileName), new Size(64, 64))); 
 
        }
    }
}

Por fim, não se permite a selecção de ficheiros que não preencham os requisitos do filtro; actualiza-se a informação na base de dados; envia-se a foto selecionada para a pasta do jogador; actualiza-se a célula da foto da DataGridView.

Conclusão

Neste artigo construímos uma estrutura vantajosa em termos de utilização de classes, seguindo um modelo mais orientado a objectos. Definimos também um modelo bastante reduzido em termos de código, mantendo mesmo assim o melhor da segurança e rapidez.

Links de referência

  1. MySQL Connector – http://tinyurl.com/2r3t2a
  2. MySQL Referência – http://tinyurl.com/lcdqoao
  3. Estrutura Base Dados – http://pastebin.com/jPFXtXhE
  4. Download do projecto – http://tinyurl.com/l2atxyr