Usc2017nsp by yazelin

第 06 週 (06/14) Server程式(MQTTClient)+MySQL資料庫(3)

滴水穿石，不是水多厲害，更不是石頭不厲害，而是時間太厲害。

建置MySQL資料表
Server程式設計

建置MySQL資料表

依參考影片，重新建立log資料庫，建立sensor與alarm資料表及建立使用者
sensor資料表內有_AI(int)、Record_time(datetime)、Message(text)三個欄位
alarm資料表內也有_AI(int)、Record_time(datetime)、Message(text)三個欄位

參考影片

介面設計參考

以第二週完成的專案新增2個Label和2個DataGridView用來顯示sensor和alarm
介面設計參考圖

Using MySQL 函式庫

請先確定完成第五週 mysql-connector-net-6.9.9 安裝
並在專案中加入參考(Mysql.Data)後在程式碼中加入

// including the MySQL Library
using MySql.Data.MySqlClient;

Server程式設計

Server程式步驟
1. 與mosquitto連線(第二週) + 與MySQL連線(本週)
2. 向mosquitto訂閱主題(第二週)
3. 接收到主題與訊息時顯示於文字框(第二週) + 將資料存入MySQL及顯示於DataGridView中(本週)
4. 向MySQL新增、查詢資料(本週)
5. 當程式結束時與mosquitto中斷連線(第二週) + 與MySQL中斷連線(本週)

<修改>連結MySQL資料庫 (請參考第五週程式做以下修改)

//在public partial class Form1 : Form { } 中加入下面2個宣告
delegate void UpdateDataGridViewCallback(string topic, string msg);//用來更新新增至MySQL的Callback

//連線參數   (請依實際Mysql設定自行修改)
//資料來源:本機localhost
//port:3306
//選擇某個資料庫:log
//使用者名稱:loguser，密碼：123
//建立MySQL連結參數
MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;Initial Catalog='log';username=loguser;password=123");

//當視窗載入時觸發
private void Form1_Load(object sender, EventArgs e)
{
	client = new MqttClient("localhost");
	client.MqttMsgPublishReceived += client_MqttMsgPublishReceived;
	clientId = Guid.NewGuid().ToString();
	client.Connect(clientId);

	//===NEW=== 
	connection.Open(); //與MySQL建立連線並使用log資料庫

	SelectMySQL(connection, "sensor", dataGridView_sensor);// 查詢(log資料庫內的)sensor資料表
	SelectMySQL(connection, "alarm", dataGridView_alarm);// 查詢(log資料庫內的)alarm資料表

	//訂閱訊息主題與異常紀錄主題(在Load時就訂閱所以不必再用輸入文字框來訂閱(Sensor、Alarm)主題)
	client.Subscribe(new string[] { "Sensor", "Alarm" }, new byte[] { 0, 0 });   // we need arrays as parameters because we can subscribe to different topics with one call
	SetText("");//先將RecText.TextBox清空
	//===NEW===
}

<修改>查詢資料表的方法 (請參考第五週程式)

//查詢某一個資料表的方法
private void SelectMySQL(MySqlConnection connection, string tablename, DataGridView datagrid)
{
	//從 MYSQL 中查詢資料並於 dataGridView 中顯示 

	//查詢資料表的select語法
	string sql = "SELECT * FROM " + tablename + " ORDER BY _AI DESC LIMIT 5";
	//在記憶體建立新的table空白表格
	DataTable table = new DataTable();
	//MySqlDataAdapter類別用connection去查詢MySQL的資料
	MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connection);
	//查詢後的adapter填入table
	adapter.Fill(table);
	//table顯示在dataGridView的DataSource
	datagrid.DataSource = table;
	//dataGridView欄位依照內容長短調整欄寬
	datagrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}

<修改>新增資料至某個資料表的方法 (請參考第五週程式)

//新增Message資料至某個資料表的方法
private void Insert(MySqlConnection connection, string tablename, string message)
{
	String Record_time;//紀錄資料加入的時間
	Record_time = String.Format("{0:yyyy/MM/dd HH:mm:ss}", DateTime.Now);
	string sql = "INSERT INTO " + tablename + "(Record_time,Message) VALUES('" + Record_time + "','" + message + "')";
	MySqlCommand command = new MySqlCommand(sql, connection);
	command.ExecuteNonQuery();//執行SQL
}

<新增>UpdateDataGridView (請參考第二週不同執行緒更新UI的方式(SetText)撰寫新的程式碼如下)

//當兩個不同執行緒上需要更新數值(UI執行緒)時的處理
private void UpdateDataGridView(string topic, string message)
{
	// we need this construction because the receiving code in the library and the UI with DataGridView run on different threads
	if (this.InvokeRequired)
	{
		//如果需要Invoke
		//設定CallBack,Invoke
		UpdateDataGridViewCallback d = new UpdateDataGridViewCallback(UpdateDataGridView);
		this.Invoke(d, new object[] { topic, message });
	}
	else
	{
		//若不需要Invoke直接新增至MySQL
		//新增資料至主題對應的資料表
		Insert(connection, topic, message);

		//查詢主題對應的資料表
		SelectMySQL(connection, topic, (DataGridView)this.Controls.Find("dataGridView_"+topic, true)[0]);
	}
}

<修改>當收到主題後的對應動作 (以第二週的程式新增以下幾行)

// this code runs when a message was received
void client_MqttMsgPublishReceived(object sender, MqttMsgPublishEventArgs e)
{
	string ReceivedMessage = Encoding.UTF8.GetString(e.Message);

	// we need this construction because the receiving code in the library and the UI with textbox run on different threads
	SetText(ReceivedMessage);

	//===NEW===
	string ReceivedTopic = e.Topic.ToLower();//收到的主題轉成小寫
	// we need this construction because the receiving code in the library and the UI with DataGridView run on different threads
	//將主題與訊息寫進datagridview框內，但因為MQTT接收的執行緒與UI執行緒不同，我們需要呼叫自訂的UpdateDataGridView函式做些處理
	UpdateDataGridView(ReceivedTopic, ReceivedMessage);
	//===NEW===

}

<修改>當關閉程式時與Mysql中斷連線

//當視窗關閉時
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
	client.Disconnect();//與MQTTServer(mosquitto) 中斷連線
	//===NEW===
	connection.Close(); //與MySQL 中斷連線
	//===NEW===
}

注意有坑
1. Mysql的帳號密碼
2. 元件名稱
參考影片

作業

將課堂做完的成果錄影後貼至FB社團內

備註

參考範例MQTTDB.zip