經驗記錄簿: MySQL

顯示具有 MySQL 標籤的文章。顯示所有文章

2016年9月28日星期三

[C#] MySQL 四大功能 (新增、刪除、更新、搜尋)

//載入MySQL元件
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ConnectMySQL
{
public partial class Form1 : Form
{
string dbHost = "127.0.0.1"; //資料庫位址
string dbUser = "root"; //資料庫使用者帳號
string dbPass = "xxxx"; //資料庫使用者密碼
string dbName = "TestDB"; //資料庫名稱

public Form1()
{
InitializeComponent();

MySQL_Select();
}

/// <summary>
/// 測試連結資料庫
/// </summary>
private void TestConnect()
{
// 如果有特殊的編碼在database後面請加上;CharSet=編碼, utf8請使用utf8_general_ci
string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
MySqlConnection conn = new MySqlConnection(connStr);

//MySQLCommand commn = new MySQLCommand("set names big5", conn);

// 連線到資料庫
try
{
conn.Open();
MessageBox.Show("mysql 連線成功");
}
catch
{
MessageBox.Show("mysql 連線失敗");
}
}

/// <summary>
/// 新增資料
/// </summary>
private void MySQL_Insert()
{
string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand command = conn.CreateCommand();
conn.Open();

String account;
String password;
int level;
for (int i = 0; i < 10; i++)
{
account = "account" + i.ToString();
password = "password" + i.ToString();
level = i * 10;
command.CommandText = "Insert into member(account,password,level) values('" + account + "','" + password + "'," + level + ")";
command.ExecuteNonQuery();
}
Console.ReadLine();
conn.Close();
}

/// <summary>
/// 刪除資料
/// </summary>
private void MySQL_Delete()
{
string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand command = conn.CreateCommand();
conn.Open();

command.CommandText = "Delete FROM member WHERE id=5";
int n = command.ExecuteNonQuery();

Console.WriteLine("共刪除 {0} 筆資料", n);
Console.ReadLine();
conn.Close();
}

/// <summary>
/// 更新資料
/// </summary>
private void MySQL_Update()
{
string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand command = conn.CreateCommand();
conn.Open();

command.CommandText = "Update member SET password='1234' WHERE account='account6'";
command.ExecuteNonQuery();

Console.ReadLine();
conn.Close();

}

string[] row1 = new string[4];
object[] rows = new object[] { };

/// <summary>
/// 搜尋資料
/// </summary>
private void MySQL_Select()
{
string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand command = conn.CreateCommand();
conn.Open();

String cmdText = "SELECT * FROM member WHERE level <= 90";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
MySqlDataReader reader = cmd.ExecuteReader(); //execure the reader

while (reader.Read())
{
for (int i = 0; i < 4; i++)
{
String s = reader.GetString(i);

switch (i)
{
case 0:
row1[0] = reader.GetString(i);
break;
case 1:
row1[1] = reader.GetString(i);
break;
case 2:
row1[2] = reader.GetString(i);
break;
case 3:
row1[3] = reader.GetString(i);
break;
default:
break;
}

rows = new object[] { row1 };
}

foreach (string[] rowArray in rows)
{
dataGridView1.Rows.Add(rowArray);
}

Console.Write("\n");
}

Console.ReadLine();
conn.Close();
}

}
}

參考出處：
http://roboardgod.blogspot.tw/2013/08/cmysql.html

程式檔案：

https://drive.google.com/open?id=0B-oSnsmCU0yMUDZ1c2xLOThSeWs