mysql_query() публичный Метод

public mysql_query ( string sql ) : MySqlDataReader
sql string
Результат MySql.Data.MySqlClient.MySqlDataReader
Пример #1
0
 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();
 }
Пример #2
0
        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;
                }
            }
        }
Пример #3
0
        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();
        }
Пример #4
0
        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();
        }
Пример #5
0
 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();
 }
Пример #6
0
 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;
 }
Пример #7
0
 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();
     }
 }
Пример #8
0
        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"));
        }
Пример #9
0
 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 + "已被刪除");
 }
Пример #10
0
        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 = "查詢";
        }