Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Connecting to MySQL Database using C# and .NET

, 7 Apr 2010
Rate this:
Please Sign up or sign in to vote.
This article shows you how to connect to MySQL database using MySQL Connector for .NET
Connecting to MySQL database using C# and .Net

Introduction

This article shows you how to 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 2005 or Visual Studio 2008
  • MySQL database installed on your local machine or remote host
  • 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 the 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.

    Connecting to MySQL database using C# and .Net
  • 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 the 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 the sample from the 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.

    Connection String

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

Source Code Description

The source code of this sample is very straight forward.

  • Initialize mysql connection using the following code:
    //Initialize mysql connection
    connection = new MySqlConnection(ConnectionString);
    
    //Get all items in datatable
    DTItems = GetAllItems();
  • GetAllItems() function returns all items from database table:
    //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];
      }
  • After retrieving all items in a datatable, fill grid view using datatable:
     dataGridView1.DataSource = DTItems;
  • When initializing dataset, set update, insert and delete commands with adapter.
    .
    .
    .
    // 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;
    .
    .
    .
  • When Save button is clicked, we need to update adapter in order to save records. Note that when the adapter is updated, corresponding commands (insert, update or delete) are executed against the database based on operations that you have done on grid.
    private void btnSave_Click(object sender, EventArgs e)
    {
        try
        {
          //Save records in database using DTItems which is datasource for Grid
          adapter.Update(DTItems);
    .
    .
    .
  • When Delete button is clicked, we need to remove row from datatable. After that, update adapter to save records.
    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);
    .
    .
    .

History

  • 7th April, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shabdar Ghata
Web Developer
Canada Canada
Software Developer
 
http://www.shabdar.org

Comments and Discussions

 
SuggestionA few security issues and warnings [modified] PinmemberDelphiCoder2-Jan-13 8:10 
QuestionHow to add Referene MySql.Data PinmemberSami Ciit31-Jul-12 3:29 
Thanks a lot for the nice article.
 
How you can add reference for MySql.Data (If It gives Error/Warning)
 
Delete MySql.Data from references of Your project(If Unable to do read last lines first)
Then
Right Click on project in solution explorer
Choose Add Reference
Click Browse from top menu
Go to c->program files->MySql->MySQL Connector Net->Assemblies->v2.0->
You will find here MySql.Data click it
Enjoy
 
Click +sign of references of your project (this is below the project name in solution explorer)..
You will find MySql.Data with a yellow sign. If it is not found follow above instructions to add it.. If it is found without yellow sign.. Then your reference to MySql.Data is already OK
Questionconnection string PinmemberSany Ahmed4-Jan-12 15:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 7 Apr 2010
Article Copyright 2010 by Shabdar Ghata
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid