/*
a、注意數據庫編碼要能兼容gb2312和big5，比如mysql中使用utf8
b、該代碼采用遍歷的方式，并用mysqlcommandbuilder進行批量更新，所以能轉換的表必須包含主鍵，不包括主鍵的表則不能轉換
c、引用了microsoft.visualbasic.dll進行簡繁轉換
*/
using system;
using system.data;
using mysql.data;
using mysql.data.mysqlclient;
using system.collections.generic;
using system.text;
using microsoft.visualbasic;

namespace gb2312tobig5
{
    class program
    {
        static void main(string[] args)
        {
            //入口
            console.writeline("請輸入數據庫所在ip：");
            string ip = console.readline().trim();

            console.writeline("請輸入數據庫名稱：");
            string db = console.readline().trim();

            console.writeline("請輸入登錄數據庫用戶名：");
            string user = console.readline().trim();

            console.writeline("請輸入登錄數據庫密碼：");
            string psw = console.readline();

            string connectionstring = "data source=" + ip + ";user id=" + user + ";password=" + psw + ";database=" + db + ";allow zero datetime=true;charset=utf8;";

            console.writeline("生成的數據庫連接字符串為：{0}，繼續嗎？(y/n)", connectionstring);
            if (console.readline().tostring().toupper() == "y")
            {
                //包含所有表名稱的datatable
                datatable dtall = tablelist(connectionstring);
                if (dtall != null)
                {
                    if (dtall.rows.count > 0)
                    {
                        console.write("轉換中,請稍候:");
                        for (int i = 0; i < dtall.rows.count; i++)
                        {
                            dtconvert(dtall.rows[i][0].tostring(), connectionstring);
                        }
                    }
                }
            }
        }

        //將datatable中每行每列轉為繁體
        private static void dtconvert(string dtname, string connectionstring)
        {
            string sql = "";
            mysqlcommand cmd = null;
            mysqldataadapter da = null;
            datatable dt = null;
            mysqlcommandbuilder builder = null;

            using (mysqlconnection conn = new mysqlconnection(connectionstring))
            {
                try
                {
                    sql = "select * from " + dtname;
                    cmd = new mysqlcommand(sql, conn);
                    conn.open();
                    da = new mysqldataadapter(cmd);
                    //添加主鍵映射
                    da.missingschemaaction = missingschemaaction.addwithkey;
                    dt = new datatable();
                    da.fill(dt);

                    //遍歷dt做替換
                    if (dt.rows.count > 0)
                    {
                        //如果表包含主鍵
                        if (dt.primarykey.length > 0)
                        {
                            #region 遍歷
                            for (int i = 0; i < dt.rows.count; i++)
                            {
                                for (int j = 0; j < dt.columns.count; j++)
                                {
                                    if (dt.columns[j].datatype.tostring() == "system.string")
                                    {
                                        if (dt.rows[i][j] != null)
                                        {
                                            if (dt.rows[i][j].tostring() != string.empty)
                                            {
                                                dt.rows[i][j] = getbig5(dt.rows[i][j].tostring());
                                                console.write(".");
                                            }
                                        }
                                    }
                                }
                            }
                            #endregion

                            builder = new mysqlcommandbuilder(da);
                            da.update(dt);
                        }
                    }
                    //釋放資源
                    builder.dispose();
                    cmd.dispose();
                    da.dispose();
                    dt.clear();
                    dt.dispose();
                   
                }
                catch (exception error)
                {
                    console.writeline(error.tostring());
                }
                finally
                {
                    conn.close();
                }
               
            }
        }

        //遍歷每個表
        private static datatable tablelist(string connectionstring)
        {
            datatable dt = new datatable();

            using (mysqlconnection conn = new mysqlconnection(connectionstring))
            {
                //show tables為mysql列出所有表，如sqlserver請使用相關命令
                mysqlcommand cmd = new mysqlcommand("show tables",conn);
                mysqldataadapter da = new mysqldataadapter(cmd);
                dataset ds = new dataset();

                try
                {
                    conn.open();
                    da.fill(ds, "temp_tables");
                    dt = ds.tables["temp_tables"];
                }
                catch (exception error)
                {
                    console.writeline(error.tostring());
                }
                finally
                {
                    conn.close();
                }
            }

            return dt;
        }

        //簡體轉繁體
        private static string getbig5(string gb2312)
        {
            string big5 = "";
            if ((gb2312 != null) && (gb2312 != string.empty))
            {
                gb2312 = gb2312.trim();
                big5 = strings.strconv(gb2312,vbstrconv.traditionalchinese,0);
            }
            return big5;
        }

    }
}