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:
- A instalação do MySQL Connector (ver link 1)
- Adicionar a referência do MySQL ao projecto (ver link 2)
- Criar uma base de dados com duas tabelas
jogadores
eclubes
(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.
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.
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:
- Função
get()
– Realiza a operação Ler (Read), cuja finalidade é devolver informações da base de dados. - 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 daDataGridView
); - É 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.
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
- MySQL Connector – http://tinyurl.com/2r3t2a
- MySQL Referência – http://tinyurl.com/lcdqoao
- Estrutura Base Dados – http://pastebin.com/jPFXtXhE
- Download do projecto – http://tinyurl.com/l2atxyr