Accessing MySql DB in .NET using ODBC

準備工作都詳細的陳列在MySql的網頁上。
要注意的是在使用(建立connection)的時後必須指定Driver，像是：
public OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;uid=root;pwd=root's pwd");
使用MySql connector時所使用的username也要改成uid，
除此之外使用方法與MySql connector無異。

以下是這幾天測試時所寫的demo code(將圖片以binary格式加入db，將圖片由db取出並顯示在螢幕上，從db中刪除圖片)，還不太會用parameter，因此透過datarow的方式insert data，

// button1 --> load a picture into db
// button2 --> exit program
// button3 --> file selector; openFileDialog
// button4 --> retrieve picture from db and show it in a pictureBox
// button5 --> delete an entry in db

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//using MySql.Data.MySqlClient;
using System.Data.Odbc;
using System.IO;

namespace dryeh
{
public partial class dryeh_form_01 : Form
{
public OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;uid=root;pwd=root's pwd");

public dryeh_form_01()
{
InitializeComponent();
cn.Open();
UpdateNrEntry();
ChkFilePath();
ChkNrEntry();
}

private void button2_Click(object sender, EventArgs e)
{
cn.Close();
cn.Dispose();
this.Close();
}

private void button1_Click(object sender, EventArgs e)
{
Ex_InsertPicToDB();
ChkNrEntry();
}

private void button3_Click(object sender, EventArgs e)
{
Ex_RetrievePicFromDB();
}

private void button4_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
textBox1.Text = openFileDialog1.FileName;
}
ChkFilePath();
}

private void button5_Click(object sender, EventArgs e)
{
DelEntry();
UpdateNrEntry();
ChkNrEntry();
}

private void ChkFilePath()
{
if (textBox1.Text.Length == 0)
{
button1.Enabled = false;
}
else
{
if (File.Exists(textBox1.Text))
{
button1.Enabled = true;
}
}
}

private void ChkNrEntry()
{
if (numericUpDown1.Maximum == 0)
{
button5.Enabled = button3.Enabled = false;
}
else
{
if (button3.Enabled == false)
{
button5.Enabled = button3.Enabled = true;
numericUpDown1.Value = numericUpDown1.Minimum = 1;
}
else
{
if (numericUpDown1.Value == 0)
{
numericUpDown1.Value = 1;
}
}
}
}

private void UpdateNrEntry()
{
OdbcDataAdapter da = new OdbcDataAdapter("select * from blobtest", cn);
OdbcCommandBuilder myCB = new OdbcCommandBuilder(da);
DataSet ds = new DataSet("blobtest");

da.Fill(ds, "blobtest");
numericUpDown1.Maximum = ds.Tables["blobtest"].Rows.Count;
label1.Text = " / " + numericUpDown1.Maximum;
}

private void Ex_MySqlQueryRow()
{
OdbcDataReader myReader;
OdbcCommand myCommand = new OdbcCommand("SELECT film_id, title FROM film", cn);

myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine(myReader.GetValue(0) + ", " + myReader.GetString(1));
}
myReader.Close();
myCommand.Dispose();
}

private void Ex_Bitmap()
{
Bitmap img = new Bitmap(@"D:\Document\James\Visual Studio 2005\Projects\dryeh\dryeh\pic\Winter.jpg", false);
pictureBox1.Image = img;
}

private void Ex_InsertPicToDB()
{
OdbcDataAdapter da = new OdbcDataAdapter("select * from blobtest", cn);
OdbcCommandBuilder myCB = new OdbcCommandBuilder(da);
DataSet ds = new DataSet("blobtest");
// 讀圖檔，也可以是CCD照進來的memory stream，只是後面的operation要跟著改用memory stream的ops
FileStream fs;
if (textBox1.Text.Length == 0)
{
MessageBox.Show("Please provide path to the file.");
}
else
{
fs = new FileStream(textBox1.Text, FileMode.OpenOrCreate, FileAccess.Read);
// FileStream fs = new FileStream(@"D:\Document\James\Visual Studio 2005\Projects\dryeh\dryeh\pic\Sunset.jpg",
// FileMode.OpenOrCreate, FileAccess.Read);

byte[] MyData = new byte[fs.Length];
// 將file stream讀進byte array (MyData)中
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
// 利用DataAdapter將blobtest table填入ds中
da.Fill(ds, "blobtest");

DataRow myRow;
// 在blobtest table中新增一個entry
myRow = ds.Tables["blobtest"].NewRow();
// 設定該entry的各項欄位
myRow["blobdesc"] = "test3";
myRow["blobdata"] = MyData;
// 將該entry加入blobtest table中
ds.Tables["blobtest"].Rows.Add(myRow);
// update blobtest table，讓改變發生
da.Update(ds, "blobtest");
UpdateNrEntry();
}
}

private void Ex_RetrievePicFromDB()
{
OdbcDataAdapter da = new OdbcDataAdapter("select * from blobtest", cn);
OdbcCommandBuilder MyCB = new OdbcCommandBuilder(da);
DataSet ds = new DataSet("blobtest");

byte[] MyData = new byte[0];

// 將table填入ds中
da.Fill(ds, "blobtest");
DataRow myRow;
// 取table中的第二個row (第三個entry)
myRow = ds.Tables["blobtest"].Rows[System.Convert.ToInt32(numericUpDown1.Value-1)];
// 取出blobdata欄位中的資料放到byte陣列中(MyData)
MyData = (byte[])myRow["blobdata"];
// 將資料由raw binary轉換成memory stream再轉換成圖檔並顯示在picture box中(目前沒有處理resize to fit的部分)
MemoryStream stmBLOBData = new MemoryStream(MyData);
pictureBox1.Image = Image.FromStream(stmBLOBData);
}

private void DelEntry()
{
OdbcDataAdapter da = new OdbcDataAdapter("select * from blobtest", cn);
OdbcCommandBuilder MyCB = new OdbcCommandBuilder(da);
DataSet ds = new DataSet("blobtest");
DataRow myRow;
Int64 id;
da.Fill(ds, "blobtest");
myRow = ds.Tables["blobtest"].Rows[System.Convert.ToInt32(numericUpDown1.Value-1)];
id = System.Convert.ToInt64(myRow["blobid"]);
OdbcCommand myCommand = new OdbcCommand("delete from blobtest where blobid = " + id, cn);
myCommand.ExecuteReader();
myCommand.Dispose();
}
}
}