นำมาฝาก : ตัวอย่างการดึงข้อมูลจาก MySQL ด้วย C#

ตัวอย่างการดึงข้อมูลจาก MySQL ด้วย C#


1. ทำการ add Reference เข้าไปใน C# โดย Add Reference ชื่อ MySql.Data

2. เพิ่ม using MySql.Data.MySqlClient;

3. โค้ดตัวอย่างการดึงข้อมูลมาใส่ใน Data Grid



string MyConString = "SERVER=localhost;" +
"DATABASE=test;" + "UID=root;" +
"PASSWORD=xxxxxxx;";

DataSet myData = new DataSet();
MySqlConnection connection;
MySqlCommand command;
MySqlDataAdapter myAdapter;

connection = new MySqlConnection(MyConString);
command = connection.CreateCommand();

myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

command.CommandText = "select * from test.t1";
command.Connection = connection;

myAdapter.SelectCommand = command;

//clear first
myData.Tables.Clear();


myAdapter.Fill(myData,"t1");

this.gridView1.DataSource = myData.Tables["t1"];

ตัวอย่างโค้ดการ InSert

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

conn.ConnectionString = MyConString;

try
{

conn.Open();
cmd.Connection = conn;

cmd.CommandText = "INSERT INTO test.t1 (name,email,note,val) VALUES(?name, ?email,?note, ?val)";
cmd.Prepare();

cmd.Parameters.Add("?name", MySqlDbType.VarChar, 50);
cmd.Parameters.Add("?email", MySqlDbType.VarChar, 50);
cmd.Parameters.Add("?note", MySqlDbType.Text);
cmd.Parameters.Add("?val", MySqlDbType.Float);

cmd.Parameters["?name"].Value = this.txtName.Text;
cmd.Parameters["?email"].Value = this.txtEmail.Text;
cmd.Parameters["?note"].Value = this.txtNote.Text;
cmd.Parameters["?val"].Value = float.Parse(this.txtValue.Text);

cmd.ExecuteNonQuery();

MessageBox.Show("Insert data successfully.", "Infomation", MessageBoxButtons.OK, MessageBoxIcon.Information);

this.txtName.Text = "";
this.txtEmail.Text = "";
this.txtNote.Text = "";
this.txtValue.Text = "";

}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

finally
{
conn.Close();
}