public mysql_query ( string sql ) : |
||
sql | string | |
Результат |
public SearchForm(List<string> dbInfo) { InitializeComponent(); this.dbInfo = dbInfo; postSort = new bool[levelOneList.Columns.Count]; string sql = "select id, name from pages_info"; DBConnection conn = new DBConnection(this.dbInfo); conn.MySqlConnect(); MySqlDataReader res = conn.mysql_query(sql); while (res.Read()) { pagesID.Add(res.GetString(0)); pagesName.Add(res.GetString(1)); page_list.Items.Add(res.GetString(1)); } page_list.Text = page_list.Items[0].ToString(); page_list.SelectedIndex = 0; res.Close(); }
private void startUpdateData(string newIDSince) { bool continueUpdate = false; if (updatePageLogsCheck.Checked) continueUpdate = true; while (true) { DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "select * from access_token where 1"; MySqlDataReader res = null; while (true) { try { res = conn.mysql_query(sql); res.Read(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } DateTime nextUpdateTime = DateTime.Now.AddDays((1.0) * (int)pagesLogUpdateF.Value); DateTime untileDate = DateTime.Now.AddDays((-1.0) * (int)recentlyDays.Value); string since = untileDate.Year.ToString() + "-" + untileDate.Month.ToString() + "-" + untileDate.Day.ToString(); string access_token = res.GetString(0); res.Close(); string IDString = ""; foreach (ListViewItem lvi in pagesInfo.CheckedItems) { IDString += lvi.Text + ","; } IDString = IDString.Substring(0, IDString.Length - 1); sql = "select id,name,new_id from pages_info where new_id >= 0 and id in (" + IDString + ")"; List<string[]> pageList = new List<string[]>(); while (true) { try { pageList.Clear(); res = conn.mysql_query(sql); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } while (res.Read()) { string[] tmpPage = { res.GetString(0), res.GetString(1), res.GetString(2) }; pageList.Add(tmpPage); } res.Close(); foreach (string[] pagesInfoArray in pageList) { DateTime startingTime = DateTime.Now; showMessage.AppendText("Now updating " + pagesInfoArray[1] + " at " + DateTime.Now.ToString() + "\n"); string url = "https://graph.facebook.com/" + pagesInfoArray[0]; //showMessage.AppendText("In firstThread since=" + newIDSince + "\n"); if (pagesInfoArray[2] == "1") { get_post(pagesInfoArray[0], access_token, newIDSince, null); sql = "update pages_info set new_id = 0 where id = \"" + pagesInfoArray[0] + "\""; while (true) { try { conn.mysql_query(sql).Close(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } } else { get_post(pagesInfoArray[0], access_token, since, null); } sql = "select count(*) from allinone where id = \"" + pagesInfoArray[0] + "\""; while (true) { try { res = conn.mysql_query(sql); res.Read(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } string feedsCount = res.GetString(0); res.Close(); sql = "select count(*) from pages_posts_comments where page_id = \"" + pagesInfoArray[0] + "\""; while (true) { try { res = conn.mysql_query(sql); res.Read(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } string commentsCount = res.GetString(0); res.Close(); List<string> pageInformation = pages_info(url); if (pageInformation != null) { sql = "insert into pages_logs (id,name,picture,link,category,website,founded,info,likes,talking_about_count,updatetime,feeds,comments) values (" + "?id,?name,?picture,?link,?category,?website,?founded,?info,?likes,?talking_about_count,?updatetime," + feedsCount + "," + commentsCount + ")"; while (true) { try { MySqlCommand thecmd = new MySqlCommand(sql, conn.conn); thecmd.Parameters.AddWithValue("?id", pageInformation[0]); thecmd.Parameters.AddWithValue("?name", pageInformation[1]); thecmd.Parameters.AddWithValue("?picture", pageInformation[2]); thecmd.Parameters.AddWithValue("?link", pageInformation[3]); thecmd.Parameters.AddWithValue("?category", pageInformation[4]); thecmd.Parameters.AddWithValue("?website", pageInformation[5]); thecmd.Parameters.AddWithValue("?founded", pageInformation[6]); thecmd.Parameters.AddWithValue("?info", pageInformation[7]); thecmd.Parameters.AddWithValue("?likes", pageInformation[8]); thecmd.Parameters.AddWithValue("?talking_about_count", pageInformation[9]); thecmd.Parameters.AddWithValue("?updatetime", DateTime.Now); MySqlDataReader themyData = thecmd.ExecuteReader(); themyData.Close(); thecmd.Parameters.Clear(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } } TimeSpan timeSpent = DateTime.Now.Subtract(startingTime); sql = "update pages_info set timespent = \"" + timeSpent.TotalMinutes.ToString("#.#0") + "分鐘" + "\" where id = \"" + pagesInfoArray[0] + "\""; while (true) { try { conn.mysql_query(sql).Close(); break; } catch (Exception ee) { Console.WriteLine(ee.StackTrace); conn.closeMySqlConnection(); conn.MySqlConnect(); } } conn.closeMySqlConnection(); showMessage.AppendText(pagesInfoArray[1] + " was updated complete at " + DateTime.Now.ToString() + "\n"); } if (continueUpdate) { try { TimeSpan ts = nextUpdateTime.Subtract(DateTime.Now); //七天更新一次 showMessage.AppendText("預計下次更新時間為" + nextUpdateTime.ToString() + "\n\n"); Thread.Sleep(Convert.ToInt32(ts.TotalMilliseconds)); } catch { } } else { showMessage.AppendText("更新結束 @" + DateTime.Now.ToString() + "\n"); startUpdate.Enabled = true; cancleUpdate.Enabled = false; break; } } }
public void updatePageIDList() { pagesInfo.Items.Clear(); DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "select id,name,new_id,timespent from pages_info where new_id >= 0"; MySqlDataReader res = conn.mysql_query(sql); if (res != null) { while (res.Read()) { ListViewItem item = new ListViewItem(res.GetString(0)); item.SubItems.Add(res.GetString(1)); if (res.GetInt32(2) == 0) { item.SubItems.Add("否"); } else { item.SubItems.Add("是"); } if (res.GetString(3).Length > 0) { item.SubItems.Add(res.GetString(3)); } else { item.SubItems.Add("Null"); } pagesInfo.Items.Add(item); } res.Close(); } conn.closeMySqlConnection(); }
private void SetAccessToken_Click(object sender, EventArgs e) { DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "truncate table access_token"; conn.mysql_query(sql).Close(); sql = "insert into access_token (access_token) values (\"" + accessTokenBox.Text + "\")"; conn.mysql_query(sql).Close(); pagesEditGroup.Enabled = true; conn.closeMySqlConnection(); init_Timer(); }
public void checkCanUpdate() { DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "select count(*) from pages_info where 1"; MySqlDataReader res = conn.mysql_query(sql); res.Read(); if (res.GetInt32(0) > 0) { startUpdate.Enabled = true; } else { startUpdate.Enabled = false; } res.Close(); conn.closeMySqlConnection(); }
private void dbConnect_Click(object sender, EventArgs e) { if (dbServerBox.Text.Length * dbAcoountBox.Text.Length * dbPasswordBox.Text.Length * dbTableBox.Text.Length == 0) { MessageBox.Show("資料庫資訊不得為空!"); return; } dbInfo.Clear(); dbInfo.Add(dbServerBox.Text); dbInfo.Add(dbAcoountBox.Text); dbInfo.Add(dbPasswordBox.Text); dbInfo.Add(dbTableBox.Text); string errorMessage = ""; DBConnection conn = new DBConnection(dbInfo); if (!conn.checkDBExist()) { MessageBox.Show("資料庫連線資訊錯誤!!"); return; } errorMessage = conn.MySqlConnect(); if (errorMessage.Length > 0) { MessageBox.Show(errorMessage); return; } else { accessTokenGroup.Enabled = true; string sql = "select count(*) from access_token where 1"; MySqlDataReader res = conn.mysql_query(sql); res.Read(); if (res.GetInt32(0) > 0) { res.Close(); sql = "select access_token from access_token where 1"; res = conn.mysql_query(sql); res.Read(); accessTokenBox.Text = res.GetString(0); pagesEditGroup.Enabled = true; res.Close(); checkCanUpdate(); init_Timer(); } else { res.Close(); MessageBox.Show("請先指定Access Token"); } } FileStream stream = new FileStream(dbInfoFileName, FileMode.OpenOrCreate); StreamWriter writer = new StreamWriter(stream); string baseServer = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbServerBox.Text)); string baseAccount = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbAcoountBox.Text)); string basePWD = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbPasswordBox.Text)); string baseTable = Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(dbTableBox.Text)); writer.WriteLine(baseServer + ":" + baseAccount + ":" + basePWD + ":" + baseTable); writer.Close(); stream.Close(); updatePageIDList(); conn.closeMySqlConnection(); dbConnInfo.Enabled = false; }
private void update_user_info_by_ids(StringBuilder sb, DBConnection conn) { Thread.Sleep(5 * 1000); string sql = ""; string url = "https://graph.facebook.com/fql?q=select uid,username,sex from user where uid in (" + sb.ToString() + ")&access_token=" + accessTokenBox.Text; //showMessage.AppendText(url); //return; FBGraph api = new FBGraph(); JSONObject result = api.FBGraphGet(url); result = resultCheck(result, url, 0); if (result == null) { return; } //try //{ // int erro_count = 0; // while (true) // { // if (result.Dictionary.ContainsKey("error_msg") || result.Dictionary.ContainsKey("error")) // { // //若發生error 檢查是何種error 若無error再透過exception離開迴圈 // string errorMessage = ""; // string errorNumber = ""; // if (result.Dictionary.ContainsKey("error_msg")) // { // errorMessage = result.Dictionary["error_msg"].String; // errorNumber = result.Dictionary["error_code"].String; // } // else // { // errorMessage = result.Dictionary["error"].Dictionary["message"].String; // errorNumber = result.Dictionary["error"].Dictionary["code"].String; // } // //Console.WriteLine("Error message(" + errorNumber + ") is : " + errorMessage); // if (errorNumber.Equals("602")) // { // messageLog(errorMessage); // //showMessage.AppendText(errorMessage + "\n"); // return; // } // else // { // Thread.Sleep(1000); // erro_count++; // if (erro_count > 30) // { // messageLog(errorMessage); // //showMessage.AppendText(errorMessage + "\n"); // return; // } // } // } // else // { // break; // } // } //} //catch { } sb.Clear(); Dictionary<string, string[]> uInfo = new Dictionary<string, string[]>(); if (result.Dictionary.ContainsKey("data") && result.Dictionary["data"].Array.Length > 0) { foreach (JSONObject pageData in result.Dictionary["data"].Array) { string[] tmpString = { pageData.Dictionary["username"].String, pageData.Dictionary["sex"].String }; uInfo.Add(pageData.Dictionary["uid"].String, tmpString); sb.Append(pageData.Dictionary["uid"].String + ","); } string allIDs = sb.ToString(); allIDs = allIDs.Remove(allIDs.Length - 1); sb.Clear(); sb.Append("update user_info set username = case id "); foreach (KeyValuePair<string, string[]> kvp in uInfo) { sb.Append(" when " + kvp.Key + " then '" + kvp.Value[0] + "' "); } sb.Append(" end, gender = case id "); foreach (KeyValuePair<string, string[]> kvp in uInfo) { sb.Append(" when " + kvp.Key + " then '" + kvp.Value[1] + "' "); } sb.Append(" end where id in (" + allIDs + ")"); sql = sb.ToString(); conn.mysql_query(sql).Close(); sql = "update user_info set updatetime = ?updatetime where id in (" + allIDs + ")"; MySqlCommand cmd = new MySqlCommand(sql, conn.conn); cmd.Parameters.AddWithValue("?updatetime", DateTime.Now); cmd.ExecuteReader().Close(); cmd.Parameters.Clear(); } }
private void update_user_info() { if (updatingUser) return; updatingUser = true; DateTime start = DateTime.Now; Console.WriteLine("啟動更新@" + DateTime.Now.ToString("HH:mm:ss") + "!!!"); if (accessTokenBox.Text.Length >= 0) { string sql = "select id from user_info where username = \"\" order by updatetime asc"; DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); List<string> userID = new List<string>(); MySqlDataReader res = conn.mysql_query(sql); while (res.Read()) { userID.Add(res.GetString(0)); } res.Close(); StringBuilder sb = new StringBuilder(); int index = 0; foreach (string uid in userID) { if (index % 1500 == 0) { if (index != 0) { update_user_info_by_ids(sb, conn); } sb.Clear(); sb.Append(uid); } else { sb.Append("," + uid); } index++; } update_user_info_by_ids(sb, conn); conn.closeMySqlConnection(); } updatingUser = false; Console.WriteLine("更新結束@" + DateTime.Now.ToString("HH:mm:ss") + "!!!(" + DateTime.Now.Subtract(start).TotalMinutes.ToString(".00Mins")); }
private void deletePageID_Click(object sender, EventArgs e) { int deleteCount = 0; string deleteID = ""; string deleteResultMessage = ""; foreach (ListViewItem lvi in pagesInfo.Items) { if (lvi.Checked) { deleteID += lvi.SubItems[0].Text + ","; deleteResultMessage += lvi.SubItems[1].Text + "(" + lvi.SubItems[0].Text + ")\n"; deleteCount++; } } if (deleteCount == 0) { MessageBox.Show("請先勾選要刪除的粉絲頁"); return; } deleteID = deleteID.Substring(0, deleteID.Length - 1); DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "delete from pages_info where id in (" + deleteID + ")"; try { conn.mysql_query(sql).Close(); } catch { } sql = "delete from pages_logs where id in (" + deleteID + ")"; try { conn.mysql_query(sql).Close(); } catch { } sql = "delete from allinone where id in (" + deleteID + ")"; try { conn.mysql_query(sql).Close(); } catch { } sql = "delete from pages_posts_comments where page_id in (" + deleteID + ")"; try { conn.mysql_query(sql).Close(); } catch { } foreach (ListViewItem lvi in pagesInfo.Items) { if (lvi.Checked) { sql = "delete from pages_posts_comments_likes where comment_id like \"" + lvi.SubItems[0].Text + "_%\""; conn.mysql_query(sql).Close(); sql = "delete from pages_posts_likes where post_id like \"" + lvi.SubItems[0].Text + "_%\""; conn.mysql_query(sql).Close(); } } conn.closeMySqlConnection(); updatePageIDList(); checkCanUpdate(); MessageBox.Show(deleteResultMessage + "已被刪除"); }
private void searchButton_Click(object sender, EventArgs e) { if (keywordBox.Text.Length == 0) { MessageBox.Show("請輸入要查詢的關鍵詞"); return; } searchButton.Text = "資料查詢中…"; searchButton.Enabled = false; pageName.Clear(); postMessage.Clear(); commentMessage.Clear(); allCommentMessage.Clear(); levelOneList.Items.Clear(); commentsList.Items.Clear(); DBConnection conn = new DBConnection(dbInfo); conn.MySqlConnect(); string sql = "select id,name from pages_info"; MySqlDataReader res = conn.mysql_query(sql); while (res.Read()) { pageName.Add(res.GetString(0), res.GetString(1)); } res.Close(); string keyword = keywordBox.Text.Replace("\"", ""); string allPostID = ""; keyword = keyword.Replace("%", "\\%"); keyword = keyword.Replace("_", "\\_"); sql = "select post_id, page_id, from_name, message,likes,comments,created_time, from_id from pages_posts where message like \"%" + keyword + "%\" and page_id in (select id from pages_info)"; //MySqlCommand thecmd = new MySqlCommand(sql, conn.conn); //thecmd.Parameters.AddWithValue("?keyword", keywordBox.Text); //res = thecmd.ExecuteReader(); res = conn.mysql_query(sql); Console.WriteLine(sql); /* if (res != null) { while (res.Read()) { string[] postData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6) }; //page_id, from_name, message, likes, comments, created_time postMessage.Add(res.GetString(0), postData); allPostID += "\"" + res.GetString(0) + "\","; } } */ while (res.Read()) { string[] postData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetDateTime(6).ToString("yyyy-MM-dd HH:mm:ss"), res.GetString(7) }; //page_id, from_name, message, likes, comments, created_time postMessage.Add(res.GetString(0), postData); allPostID += "\"" + res.GetString(0) + "\","; } res.Close(); if (allPostID.Length > 0) { allPostID = allPostID.Substring(0, allPostID.Length - 1); sql = "select comment_id, post_id, page_id, from_name, message, likes, created_time,from_id from pages_posts_comments where post_id in (" + allPostID + ")"; res = conn.mysql_query(sql); if (res != null) { while (res.Read()) { string[] commentData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6), res.GetString(7) }; allCommentMessage.Add(res.GetString(0), commentData); } res.Close(); } } //thecmd.Parameters.Clear(); sql = "select comment_id, post_id, page_id, from_name, message, likes, created_time, from_id from pages_posts_comments where message like \"%" + keyword + "%\" and page_id in (select id from pages_info)"; //MySqlCommand thecmd = new MySqlCommand(sql, conn.conn); //thecmd.Parameters.AddWithValue("?keyword", keywordBox.Text); //res = thecmd.ExecuteReader(); res = conn.mysql_query(sql); if (res != null) { while (res.Read()) { string[] commentData = { res.GetString(1), res.GetString(2), res.GetString(3), res.GetString(4), res.GetString(5), res.GetString(6), res.GetString(7) }; //post_id, page_id, from_name, message, likes, created_time commentMessage.Add(res.GetString(0), commentData); } res.Close(); } //thecmd.Parameters.Clear(); display_Message(); searchButton.Enabled = true; searchButton.Text = "查詢"; }