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.
- 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.
- 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:
connection = new MySqlConnection(ConnectionString);
DTItems = GetAllItems();
GetAllItems()
function returns all items from database table:
DataTable GetAllItems()
{
try
{
string query = "select * from items";
adapter = new MySqlDataAdapter(query, connection);
DataSet DS = new DataSet();
adapter.Fill(DS);
.
.
.
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.
.
.
.
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;
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;
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
{
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)
{
dataGridView1.Rows.Remove(dataGridView1.SelectedRows[0]);
adapter.Update(DTItems);
.
.
.
History
- 7th April, 2010: Initial post