c#将指定数据库中所有数据由简体转换为繁体

2022-07-15 80酷酷网 80kuku.com

　　数据|数据库|转换

/*
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;
        }

}
}