Connecting to MySQL database using C# and .Net

Download MySQL script file

Download Sample

This article shows you how you can connect to MySQL database using MySQL Connector for .Net. I will also show you how you can update mysql database records using C#.

Prerequisites for running sample

• Visual Studio 2019
• MySQL database installed on your local machine or remote host. Download link -> https://dev.mysql.com/downloads/windows/installer/
• MySQL database admin tool that allows you to create database and run sql statements. I am using phpMyAdmin which is a web interface.

Getting Started

• • Go to MySQL admin tool, and create a new database, call it inventorydb
  • Download MySQL script from following link. This is a .sql file. It contains items table structure and data.
    Download MySQL script file
    Open this file with MySQL Admin tool or copy paste sql syntax from this file into MySQL Admin tool. Run it and it should create items table in inventorydb database.

    

• • For Visual Studio, you need to install MySQL Connector for .Net which is basically a .Net library to support MySQL database connectivity in .Net. Go to following link to download connector and install it.

    http://dev.mysql.com/downloads/connector/net

    When you install connector make sure that you close Visual Studio before installing.

  • Once MySQL connector is installed successfully on your computer, download sample from following link and extract it in some folder.
    Download Sample
  • Open sample solution file with visual studio.
  • Inside Solution Explorer, open App.Config file and change connection string so that it points to MySQL database that you created before. Change database user name and password in connection string as per your database instance.

    

• Run sample and it should give you list of items in grid.You can update, insert or delete items from this list.

Sourcecode description

Source code of this sample is very straight forward.

• Initialize mysql connection using following code,
  {codecitation class=”brush: c-sharp;”}
  //Initialize mysql connection
  connection = new MySqlConnection(ConnectionString);

  //Get all items in datatable
  DTItems = GetAllItems();
  {/codecitation}

• GetAllItems() function returns all items from database table,{codecitation class=”brush: c-sharp;”}
  //Get all items from database into datatable
  DataTable GetAllItems()
  {
  try
  {
  //prepare query to get all records from items table
  string query = “select * from items”;
  //prepare adapter to run query
  adapter = new MySqlDataAdapter(query, connection);
  DataSet DS = new DataSet();
  //get query results in dataset
  adapter.Fill(DS);
  .
  .
  .
  //return datatable with all records
  return DS.Tables[0];
  }
  {/codecitation}
• After retrieving all items in a datatable, fill grid view using datatable,{codecitation class=”brush: c-sharp;”}
  dataGridView1.DataSource = DTItems;
  {/codecitation}
• 
  
• When initializing dataset, set update, insert and delete commands with adapter.
  {codecitation class=”brush: c-sharp;”}
  .
  .
  .
  // Set the UPDATE command and parameters.
  adapter.UpdateCommand = new MySqlCommand(
  “UPDATE items SET ItemName=@ItemName, Price=@Price, AvailableQuantity=@AvailableQuantity, Updated_Dt=NOW() WHERE ItemNumber=@ItemNumber;”,connection);
  adapter.UpdateCommand.Parameters.Add(“@ItemNumber”, MySqlDbType.Int16, 4, “ItemNumber”);
  adapter.UpdateCommand.Parameters.Add(“@ItemName”, MySqlDbType.VarChar, 100, “ItemName”);
  adapter.UpdateCommand.Parameters.Add(“@Price”, MySqlDbType.Decimal, 10, “Price”);
  adapter.UpdateCommand.Parameters.Add(“@AvailableQuantity”, MySqlDbType.Int16, 11, “AvailableQuantity”);
  adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

  // Set the INSERT command and parameter.
  adapter.InsertCommand = new MySqlCommand(
  “INSERT INTO items VALUES (@ItemNumber,@ItemName,@Price,@AvailableQuantity,NOW());”,connection);
  adapter.InsertCommand.Parameters.Add(“@ItemNumber”, MySqlDbType.Int16, 4, “ItemNumber”);
  adapter.InsertCommand.Parameters.Add(“@ItemName”, MySqlDbType.VarChar, 100, “ItemName”);
  adapter.InsertCommand.Parameters.Add(“@Price”, MySqlDbType.Decimal, 10, “Price”);
  adapter.InsertCommand.Parameters.Add(“@AvailableQuantity”, MySqlDbType.Int16, 11, “AvailableQuantity”);
  adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

  // Set the DELETE command and parameter.
  adapter.DeleteCommand = new MySqlCommand(
  “DELETE FROM items ” + “WHERE ItemNumber=@ItemNumber;”, connection);
  adapter.DeleteCommand.Parameters.Add(“@ItemNumber”, MySqlDbType.Int16, 4, “ItemNumber”);
  adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
  .
  .
  .
  {/codecitation}

• When Save button is clicked, we need to update adapter in order to save records. Note that when adapter is updated, corresponding commands (insert, update or delete) are executed against database based on operations that you have done on grid.
  {codecitation class=”brush: c-sharp;”}
  private void btnSave_Click(object sender, EventArgs e)
  {
  try
  {
  //Save records in database using DTItems which is datasource for Grid
  adapter.Update(DTItems);
  .
  .
  .
  {/codecitation}
• When Delete button is clicked, we need to remove row from datatable. After that update adapter to save records.
  {codecitation class=”brush: c-sharp;”}
  private void btnDelete_Click(object sender, EventArgs e)
  {
  if (dataGridView1.SelectedRows.Count > 0)
  {
  //Delete a row from grid first.
  dataGridView1.Rows.Remove(dataGridView1.SelectedRows[0]);

  //Save records again. This will delete record from database.
  adapter.Update(DTItems);
  .
  .
  .
  {/codecitation}

{kunena_discuss:14}