using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Diagnostics;
using System.IO;
using System.Collections;
using MySql.Data.MySqlClient;
using System.Data;
using MySQLDriverCS;

namespace SearchBiDui
{
    class Program
    {

        static void Main(string[] args)
        {
            MySqlConnection Conn = new MySqlConnection("Database='dmoz';Data Source='localhost';User Id='root';Password='123456';charset=utf8");//连接MySQL数据库

            MySqlDataAdapter Da = new MySqlDataAdapter("select * from 4500words ", Conn);//从数据表中取出数据，没有做下次不重复的工作
            DataTable Dt = new DataTable();
            Da.Fill(Dt);

            int count = Dt.Rows.Count;  //获取记录集的长度

            for (int i = 0; i < count; i++)
            {
                int id = Convert.ToInt32(Dt.Rows[i]["id"].ToString());
                string word = Dt.Rows[i]["word"].ToString();
                string pinyin = Dt.Rows[i]["pinyin"].ToString();
                string word01 = word + "_";
                string word02 = "_" + word;

                MySqlDataAdapter Da1 = new MySqlDataAdapter("SELECT word,pinyin FROM userword2 where word like '" + word01 + "' and one = '" + pinyin + "' union all select word,pinyin from userword2 where word like '" + word02 + "' and two = '" + pinyin + "' ", Conn);               
                DataTable Dt1 = new DataTable();
                Da1.Fill(Dt1);

                int count1 = Dt1.Rows.Count;
                string speech = "";
                int pos = 0;
                for (int j  = 0; j < count1; j++)
                {
                    string word1 = Dt1.Rows[j]["word"].ToString();
                    string pinyin1 = Dt1.Rows[j]["pinyin"].ToString();
                    if (word1[0].ToString() == word)
                    {
                        pos = 1;
                    }
                    else if (word1[1].ToString() == word)
                    {
                        pos = 2;
                    }
                    else
                    {
                        pos = 0;
                        Console.WriteLine("读词有误！");
                        break;
                        break;
                    }
                    if (pos != 0)
                    {
                        speech = speech + word1 + "|" + pinyin1 + "$" + pos.ToString() + "#";
                    }
                }
                string sql = "update 4500words set speech ='" + speech + "' where id='" + id + "' ";
                RunSqlDatacmd(sql);
                if (i % 10 == 0)
                {
                    Console.WriteLine("已经执行了" + i + "条");
                }
            }
        }



        public static long RunSqlDatacmd(string sql)    //sql语句执行成员
        {
            MySqlConnection dbconn = new MySqlConnection("Database='dmoz';Data Source='localhost';User Id='root';Password='123456';charset=utf8");
            MySqlCommand cmd = dbconn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            long ret = 0;
            try
            {
                if (cmd.Connection.State == ConnectionState.Broken)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Open();
                }
                else if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                // else if (cmd.Connection.State == ConnectionState.Open)
                // {
                ret = cmd.ExecuteNonQuery();
                // }
                //else
                // {
                //     ret = -102;
                // }
            }
            catch (Exception ex)
            {
                string m = ex.Message;
                ret = -5;
            }
            cmd.Dispose();
            dbconn.Close();
            return ret;
        }

     }
}