Video of the week

This is a must-watch video about one of us trying to reach the stars :-)

Well done #HRejterzy

MySQL and .NET Framework

MySQL and .NET Framework

 app

I would like to give a good and quick example of how to use MySql.Data.dll in Your database project. We're going to create single window application. I'm gonna put a DataGridView control on the form, 3 TextBoxes, 3 Labels and 4 Buttons. This example comes from one of my major and mystery Orange-book examples (actually, very similar but not exact the same comes from Visual Studio 2008 Praktyczne przykłady by Mariusz Owczarek but I added this code to the group of those important and often used).

phpmyadmin

 

This is how the structure of our database looks. We've got SERIAL numer, varchar artysta and tytul and INTEGER - rok. Our application is going to store info about our cd collection. You can create similar in phpMyAdmin.

Download and install MySQL Connector, You will need one dynamic library file - MySql.Data.dll and You will add a reference to Your project then:

add.reference

mysql.data

Next, what You're gonna do is to add appropriate namespace: using MySql.Data.MySqlClient

  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Windows.Forms;
  9. using MySql.Data.MySqlClient;

1. How to connect (CONNECTION STRING) to MySQL database and get sth from it (SELECT)?

  1. private void Form1_Load(object sender, EventArgs e)
  2. {
  3. mysqlConn = new MySqlConnection("Server=127.0.0.1;Port=3306;Uid=k3v1n;Password=password;Database=net-tutorial");
  4. mysqlSelectComm = mysqlConn.CreateCommand();
  5. mysqlSelectComm.CommandText = "select * from exampletable";
  6. ds = new DataSet("Dane o plytach");
  7. adapter = new MySqlDataAdapter();
  8. adapter.SelectCommand = mysqlSelectComm;
  9. // SELECT
  10. try {
  11. mysqlConn.Open();
  12. adapter.Fill(ds);
  13. bindingSource1.DataSource = ds;
  14. bindingSource1.DataMember = ds.Tables[0].TableName;
  15. dataGridView1.DataSource = bindingSource1;
  16. mysqlConn.Close();
  17. } catch(Exception exc) {
  18. mysqlConn.Close();
  19. MessageBox.Show(exc.Message, "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  20. return;
  21. }
  22. }

2. How to add data or change data (INSERT & UPDATE)?

  1. // INSERT, UPDATE
  2. private void button4_Click(object sender, EventArgs e) {
  3. if (textBox1.Text.Length == 0) {
  4. MessageBox.Show("Podaj nazwę artysty!", "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  5. return;
  6. }
  7. if (textBox2.Text.Length == 0) {
  8. MessageBox.Show("Podaj tytuł płyty!", "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  9. return;
  10. }
  11. if (textBox3.Text.Length == 0) {
  12. MessageBox.Show("Podaj datę premiery płyty!", "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  13. return;
  14. }
  15. if (!update)
  16. {
  17. mysqlInsertComm = new MySqlCommand("insert into exampletable(artysta,tytul,rok) values(" + "'" +
  18. textBox1.Text.TrimEnd() + "'," + "'" +
  19. textBox2.Text.TrimEnd() + "'," +
  20. textBox3.Text.TrimEnd() + ")",
  21. mysqlConn);
  22. }
  23. else
  24. {
  25. mysqlUpdateComm = new MySqlCommand("update exampletable set artysta='" +
  26. textBox1.Text.TrimEnd() + "',tytul='" +
  27. textBox2.Text.TrimEnd() + "',rok=" +
  28. textBox3.Text.TrimEnd() + " where numer=" + numSelectedRow.ToString(),
  29. mysqlConn);
  30. }
  31. try
  32. {
  33. mysqlConn.Open();
  34. transaction = mysqlConn.BeginTransaction();
  35. if (!update)
  36. {
  37. mysqlInsertComm.Transaction = transaction;
  38. mysqlInsertComm.ExecuteNonQuery();
  39. }
  40. else
  41. {
  42. mysqlUpdateComm.Transaction = transaction;
  43. mysqlUpdateComm.ExecuteNonQuery();
  44. }
  45. transaction.Commit();
  46. mysqlConn.Close();
  47. update = false;
  48. }
  49. catch (Exception exc)
  50. {
  51. transaction.Rollback();
  52. mysqlConn.Close();
  53. update = false;
  54. MessageBox.Show(exc.Message, "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  55. return;
  56. }
  57. this.Form1_Load(sender, e);
  58. }

3. How to remove data (DELETE)?

  1. // DELETE
  2. private void button2_Click(object sender, EventArgs e)
  3. {
  4. mysqlDeleteComm = new MySqlCommand("delete from exampletable where numer=" + numSelectedRow.ToString(), mysqlConn);
  5. try
  6. {
  7. mysqlConn.Open();
  8. transaction = mysqlConn.BeginTransaction();
  9. mysqlDeleteComm.Transaction = transaction;
  10. mysqlDeleteComm.ExecuteNonQuery();
  11. transaction.Commit();
  12. mysqlConn.Close();
  13. } catch (Exception exc)
  14. {
  15. transaction.Rollback();
  16. mysqlConn.Close();
  17. MessageBox.Show(exc.Message, "Komunikat:", MessageBoxButtons.OK, MessageBoxIcon.Information);
  18. return;
  19. }
  20. Form1_Load(sender, e);
  21. }