用Visual Studio 2010來寫個簡單的MySQL BLOB應用示範

家中有台QNAP NAS內有MySQL的伺服器,平時都有拿來玩玩,今天示範用C#來上傳圖片和讀取MySQL裡的BLOB data type

首先先Create一個用來儲存圖片的Table

接下來在Visual Studio 2010中把要用的Controls拉出來,排好位置

最後的就是編碼

先是讀取的function

public DataSet ExecMySQL(string strSQL)
{
    DataSet DS = null;
    MySqlDataAdapter ada = null;
    try
    {
        DS = new DataSet();
        if (conn == null)
        {
            conn = new MySqlConnection(constr);
        }
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
        ada = new MySqlDataAdapter(strSQL, conn);
        ada.Fill(DS);
    }
    catch (Exception ex)
    {
        //MessageBox.Show(ex.Message);
    }
    return DS;
}

//接下來的是載入圖片的function,用System.IO的MemoryStream來處理從MySQL上讀取的BLOB
private void LoadPic(string filename)
{
    try
    {
        string sql = "select * from photos where filename ='" + filename + "'";
        db = new DB();
        DataSet ds = db.ExecMySQL(sql);
        byte[] image = (byte[])ds.Tables[0].Rows[0]["binaryConent"];
        MemoryStream ms = new MemoryStream(image, true);
        Bitmap bmp = (Bitmap)Bitmap.FromStream(ms);
        pictureBox1.Image = bmp;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

//把圖片名單載入listBox中
private void LoadList()
{
    db = new DB();
    DataSet ds = db.ExecMySQL("select filename from photos");
    DataTable dt = ds.Tables[0];
    listBox1.Items.Clear();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        listBox1.Items.Add(dt.Rows[i]["filename"].ToString());
    }
}

//這個上傳Button的Click事件
private void btnUpload_Click(object sender, EventArgs e)
{
    
    string errMsg = "";
    string filepath = tbUploadFile.Text.Trim();
    if (!String.IsNullOrEmpty(filepath))
    {
        try
        {
            db = new DB();
            if (!db.InsertPhoto(filepath, out errMsg))
            {
                MessageBox.Show("圖片上傳失敗!:"+errMsg);
            }
            else
            {
                MessageBox.Show("圖片上傳成功!");
                LoadList();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
}

//這是上傳圖片的function
public bool InsertPhoto(string filepath,out string errMsg)
{
    bool isSuccess = false;
    errMsg = "";
    if (!File.Exists(filepath))
    {
        return isSuccess;
    }
    string fileExt = Path.GetExtension(filepath);
    fileExt = fileExt.Remove(0, 1);
    string filename = Path.GetFileNameWithoutExtension(filepath);
    byte[] imgBinary = null;
    MySqlTransaction tran = null;
    if (conn == null)
    {
        conn = new MySqlConnection(constr);
    }
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }

    string sql = "insert photos (filename,binaryConent,extension) values (@filename,@binaryConent,@extension)";
    MySqlCommand myCommnad = null;
    MySqlParameter parm = null;
    try
    {
        tran = conn.BeginTransaction();
        imgBinary = File.ReadAllBytes(filepath);
        myCommnad = new MySqlCommand(sql, conn, tran);
        parm = new MySqlParameter("@filename", MySqlDbType.VarChar, 50);
        parm.Value = filename;
        myCommnad.Parameters.Add(parm);
        parm = new MySqlParameter("@binaryConent", MySqlDbType.Blob);
        parm.Value = imgBinary;
        myCommnad.Parameters.Add(parm);
        parm = new MySqlParameter("@extension", MySqlDbType.VarChar, 10);
        parm.Value = fileExt;
        myCommnad.Parameters.Add(parm);

        myCommnad.ExecuteNonQuery();
        tran.Commit();
        isSuccess = true;
    }
    catch (Exception ex)
    {
        errMsg = ex.Message;
        tran.Rollback();
    }
    return isSuccess;
}

測試上傳

測試讀取

總結:
- 這只是一個簡單的示範,可能有些bugs未處理好
- 把MySQL拿來學習編程其實還蠻方便的
- 原來VS2012都就快推